BETWEEN句を使うときには境界と精度に気をつけましょう。これを知らないで書くと要件と違う結果が返される場合があります！
BETWEEN句の境界
仕様
BETWEENの境界値は指定した2つの項を含みます。
[column] BETWEEN [x] AND [y]
上のクエリーは以下と同等です。
[x] <= [column] AND [column] <= [y]
境界値を含めたくない場合はbetweenを使えないので、それぞれの項の条件を指定する必要があります。
[x] < [column] AND [column] <= [y] or [x] <= [column] AND [column] < [y] or [x] < [column] AND [column] < [y]
しかしながら、カラムの精度を考えないと正しいSQL（プログラム）を書けない問題を見つけました。
BETWEENを使った悪い例
時間を区切った集計でBETWEEN句を使っている場合は、間違ったコードを書いている可能性があります。
間違った例をあげます。
BETWEEN A AND B BETWEEN B AND C
この場合ですと、Bの期間が1行目と2行目に含まれてしまいます。正しくは以下のようなコードになります。
BETWEEN A AND (B - 最小単位) BETWEEN B AND (C - 最小単位)
具体的な正しいコードは以下です。（仮定: カラムの精度が秒の場合。この過程の理由は後述。）
BETWEEN '2022-01-01 00:00:00' AND '2022-01-01 23:59:59' BETWEEN '2022-01-02 00:00:00' AND '2022-01-02 23:59:59'
精度
カラムの精度によって結果が変わってしまう書き方
datetimeのカラムに小数点が無い前提では以下は等価です。
[column] between '2020-01-01 00:00:00' and '2020-01-01 23:59:59' = 2020-01-01 00:00:00' <= [column] and [column] <= '2020-01-01 23:59:59'
datetimeには小数を持つことができるので、もしdatetimeカラムに小数の精度で値が保存されている場合は等価ではなくなります。
例えば、最初のデータベースの実装では精度が秒までだったのに、途中から小数第2位まで持つようになるケースがありえます。
上記のようなコードを書いた場合はカラムの精度によってビジネス要件と異なる結果が返されます。。プログラマーはカラムの精度に関係なく意図したとおりに動くコードを書くべきだと思いますので上記のようなコードを書かれると合格かと言われるとグレーです。
ストレージやストレージの型に対してできるだけ依存性のないコードを書くのが理想です。
MySQLではDATETIME型は物理スキーマでは秒を小数点6桁まで保持できます。カラムを作成する際に精度を省略すれば秒単位で作成されます。
もし、小数6桁まで保存するオプションによってカラムが作られている場合は以下のようなデータが保存されます。
- 2019-01-31 23:59:59.000000
- 2019-01-31 23:59:59.000001
上記のようなデータが入ることから、プログラマは物理スキーマに依存した特定の精度で動くコードを書くか、物理スキーマの精度に依存しないクエリーを書く必要があります。
精度を考慮していないで書いたSQLの例
例えば、月の集計をしたいから以下のようなクエリーを書くケースがあると思います。カラムの精度が秒ならば問題なく動きます。カラムの精度が小数点の場合には集計漏れが発生します。
[column] BETWEEN '2019-01-01 00:00:00' AND '2019-01-31 23:59:59'
例えば、以下のように、”2019-01-31 23:59:59.x” のデータが入っていても集計対象には入りません。
/* NOT THE BEST CODE EXAMPLE */ > select '2019-01-31 23:59:59.1' between '2019-01-01 00:00:00' and '2019-01-31 23:59:59' ; +-----------------------------------------------------------------------------------+ | '2019-01-31 23:59:59.1' between '2019-01-01 00:00:00' and '2019-01-31 23:59:59' | +-----------------------------------------------------------------------------------+ | 0 | +-----------------------------------------------------------------------------------+ 1 row in set (0.004 sec)
よって、精度が小数点の場合は以下のように書く必要があります。
/* THE BEST CODE EXAMPLE */ > set @columnA='2019-02-01 00:00:00'; Query OK, 0 rows affected (0.002 sec) > select '2019-01-01 00:00:00' <= @columnA AND @columnA < '2019-02-01 00:00:00'; +-------------------------------------------------------------------------+ | '2019-01-01 00:00:00' <= @columnA AND @columnA <= '2019-02-01 00:00:00' | +-------------------------------------------------------------------------+ | 0 | +-------------------------------------------------------------------------+ 1 row in set (0.007 sec)
betweenをどうしても使いたいのであれば、以下のように書く必要があります。しかし、これも小数点の精度が最大6桁という前提で書かれているのでMySQL以外のRDBMS、例えばOracleだと9桁まで保存できるのでストレージ依存のコードになります。
/* NOT THE BEST CODE EXAMPLE */ root@> select '2019-01-31 23:59:59.1' between '2019-01-01 00:00:00' and '2019-01-31 23:59:59.999999' as result; +--------+ | result | +--------+ | 1 | +--------+ 1 row in set (0.00 sec)
以下に関連するMySQLのマニュアルを引用しておきます。
https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-type-overview.html
DATETIME[(fsp)]
日付と時間の組み合わせです。サポートしている範囲は'1000-01-01 00:00:00.000000'から'9999-12-31 23:59:59.999999'です。MySQL は、'YYYY-MM-DD HH:MM:SS[.fraction]'の形式でDATETIME値を表示しますが、文字列または数値のどちらかを使用したDATETIMEカラムへの値の割り当てを許可しています。
MySQL 5.6.4 以降では、小数秒の精度を指定するために 0 から 6 の範囲でオプションのfsp値を指定できます。0 の値は、小数部がないことを表します。省略した場合、デフォルトの精度は 0 です。
MySQL 5.6.5 以降、DATETIMEカラムに対する自動初期化および現在の日時への自動更新は、セクション11.3.5「TIMESTAMP および DATETIME の自動初期化および更新機能」で説明しているように、DEFAULTおよびON UPDATEカラム定義句を使用して指定できます。
MySQLにおけるdatetimeはオプションによって秒より高い精度で保存されている可能性があるので、このクエリーでは意図しない結果が返ってくる可能性があります。
日付と時間の指定の注意
上記の理由から「betweenの2つ目の項は含む」とだけ覚えると、以下のようなケースで問題が起きます。
2つ目の後に、2019年1月31日を指定して含まれるならば同日の何時でも良さそうと考えられますが、実際には’2019-01-31’は00:00:00と扱われるようです。
精度の違うカラムの比較は注意しましょう。
mysql> select cast('2019-01-31 00:00:00' as datetime) BETWEEN '2019-01-01' AND '2019-01-31';
+-------------------------------------------------------------------------------+
| cast('2019-01-31 00:00:00' as datetime) BETWEEN '2019-01-01' AND '2019-01-31' |
+-------------------------------------------------------------------------------+
| 1 |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select cast('2019-01-31 00:00:01' as datetime) BETWEEN '2019-01-01' AND '2019-01-31';
+-------------------------------------------------------------------------------+
| cast('2019-01-31 00:00:01' as datetime) BETWEEN '2019-01-01' AND '2019-01-31' |
+-------------------------------------------------------------------------------+
| 0 |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
同じ型のチェックであればあれば普通に含まれるので大丈夫です。
mysql> select cast('2019-01-31' as date) BETWEEN '2019-01-01' AND '2019-01-31';
+------------------------------------------------------------------+
| cast('2019-01-31' as date) BETWEEN '2019-01-01' AND '2019-01-31' |
+------------------------------------------------------------------+
| 1 |
+------------------------------------------------------------------+
1 row in set (0.01 sec)
上記により、datetimeのカラムに対してbetweenを使っての集計は困難かと思います。
betweenを使うならば、カラムの精度の要件（実装の前段階）が決まった状態でないと正しいかどうか判断できなくてコードレベルでは判定できません。
コードレベルで判定するためには、betweenの利用は避けて書く必要があります。
怪しいコード、問題のあるコードの見つけ方
コードレビューで見つける
このページに有る境界と精度についてちゃんと理解した上で人間がチェックすることです。
実装が意図通りかどうかは最終的にはビジネスロジックの要件によるので人間が判断する必要があります。
CIで見つける
コードレビューの時に見逃しそうで怖いので、ある程度はユニットテストで担保するのが良さそうです。
世の中の人はどうしているのか気になってグーグルしてみたら、MySQLだけで6万件ぐらい怪しい記述が出てきました。なぜ怪しいかというと、BETWEEN句と23:59:59を使うと境界値がおそらく誤って使われているからです。
言い換えると、プログラムのソースコードを全文検索して、23:59:59という表記があったら要注意です。
集計の悪い例
集計用のプログラムを書く際に、betweenを使って値の部分を変数にして書くと、以下のようなコードが実行されることになりますが、これは重複した範囲を集計することになるので間違いです。
between A and B
between B and C
between句を使って重複してカウントされる例
root@> select '2019-01-02 00:00:00' between '2019-01-01 00:00:00' and '2019-01-02 00:00:00' as result; +--------+ | result | +--------+ | 1 | +--------+ 1 row in set (0.00 sec) root@> select '2019-01-02 00:00:00' between '2019-01-02 00:00:00' and '2019-01-03 00:00:00' as result; +--------+ | result | +--------+ | 1 | +--------+ 1 row in set (0.00 sec)
境界値として23:59:59を使うと逆に含まれない例
root@> select '2019-01-01 23:59:59.1' between '2019-01-01 00:00:00' and '2019-01-01 23:59:59' as result; +--------+ | result | +--------+ | 0 | +--------+ 1 row in set (0.00 sec) root@> select '2019-01-01 23:59:59.1' between '2019-01-02 00:00:00' and '2019-01-02 23:59:59' as result; +--------+ | result | +--------+ | 0 | +--------+ 1 row in set (0.01 sec)
精度を考慮して、betweenを集計で使う場合の正しい例
root@> select '2019-01-01 23:59:59.1' between '2019-01-01 00:00:00' and '2019-01-01 23:59:59.999999' as result; +--------+ | result | +--------+ | 1 | +--------+ 1 row in set (0.00 sec) root@> select '2019-01-01 23:59:59.1' between '2019-01-02 00:00:00' and '2019-01-02 23:59:59.999999' as result; +--------+ | result | +--------+ | 0 | +--------+ 1 row in set (0.00 sec)
こう考えると、between句の使い所って無い気がします。
正しくは以下のようなコードを書く必要があります。
A <= column and column < B
B <= column and column < C
まとめ
- 境界値の表現方法と、カラムの精度をちゃんと理解して実装する必要がある。
- datetimeのカラムの精度を秒までと仮定して、Between句を使う。or datetimeのカラムの精度が変わっても同じ結果を得られるようにするために、A <= column AND column < Bという表現を使う。
- between句を使った場合、datetime型のカラムの精度を変更すると予期しない結果が出てくる場合がある。
- big dataを扱う場合は特にテストのときの件数が膨大になるので境界値の問題に気づきづらいので要注意です。
Comments