処理しにくい最もたるものはDATE型でしょう。
Oracleは、常に時刻までを含むDATE型の1つしかないので、特に厄介だと言えます。
時間の成分を排除するためにTRUNC関数を使うのは、
一般的な方法になっています。実際には、Oracleには純粋なDATE型は存在しないので、この関数は時間成分を削除
してしまうわけではなく、その代わり時刻を0時0分に設定します。検索時に時間成分を無視するには、比較の両辺で
TRUNC関数を使用します。前日の売り上げを知りたい時は以下のようにします。
SELECT ...
FROM sales
WHERE TRUNC(sale_date) = TRUNC(sysdate - INTERVAL '1' DAY)これは完璧に有効で正しい文ですが、SALE_DATEに
作られたインデックスを正しく使うことはできません。この理由は、「UPPERとLOWERを使った
大文字・小文字を区別する検索」で説明されています。関数は、データベースにとってブラックボックス
なので、TRUNC(sale_date)とSALE_DATEは
全く異なるものとして扱われてしまうのです。
協力してください
この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。
これには、関数インデックスと いう簡単な解決策があります。
CREATE INDEX index_name
ON sales (TRUNC(sale_date))しかしこれだと、where句で常に
TRUNC(sale_date)を使う必要があります。
TRUNCを使ったり使わなかったりする場合は、2つのインデックスが必要になってしまうのです！
純粋な日付型を持っているデータベースでも、より長い期間を 検索する時には同じ問題があります。以下はMySQLのクエリの例です。
SELECT ...
FROM sales
WHERE DATE_FORMAT(sale_date, "%Y-%M")
= DATE_FORMAT(now() , "%Y-%M")これは年と月のみを含んだ日付のフォーマットを指定していて、完全に正しいクエリなのですが、前と同じ問題があります。MySQLには 関数インデックスの機能がないので、上述の解決策は使えません。
代替策として、明確な範囲条件を使います。これは、全てのデータベースで使える一般的な解決策です。
SELECT ...
FROM sales
WHERE sale_date BETWEEN quarter_begin(?)
AND quarter_end(?)ここまでに出た宿題を済ませているなら、このパターンが42歳の従業員全てを求める問題と 同じであることに気付いたでしょう。
SALE_DATEに対するインデックスで、このクエリを
最適化できます。QUARTER_BEGINと
QUARTER_END関数で、上限と下限を計算できます。between演算子は常に
上限と下限を含むので、この計算は少々複雑になります。SALE_DATEが時間成分を含んでいるなら、
QUARTER_END関数は、次の四半期初日の直前の時刻を返さ
なくてはなりません。このロジックが関数に隠されています。
以下の例は、各種データベースでの
QUARTER_BEGINとQUARTER_ENDの実装です。
- Db2 (LUW)
CREATE FUNCTION quarter_begin(dt TIMESTAMP) RETURNS TIMESTAMP RETURN TRUNC(dt, 'Q')CREATE FUNCTION quarter_end(dt TIMESTAMP) RETURNS TIMESTAMP RETURN TRUNC(dt, 'Q') + 3 MONTHS - 1 SECOND- MySQL
CREATE FUNCTION quarter_begin(dt DATETIME) RETURNS DATETIME DETERMINISTIC RETURN CONVERT ( CONCAT ( CONVERT(YEAR(dt),CHAR(4)) , '-' , CONVERT(QUARTER(dt)*3-2,CHAR(2)) , '-01' ) , datetime )CREATE FUNCTION quarter_end(dt DATETIME) RETURNS DATETIME DETERMINISTIC RETURN DATE_ADD ( DATE_ADD ( quarter_begin(dt), INTERVAL 3 MONTH ) , INTERVAL -1 MICROSECOND)- Oracle
CREATE FUNCTION quarter_begin(dt IN DATE) RETURN DATE AS BEGIN RETURN TRUNC(dt, 'Q'); ENDCREATE FUNCTION quarter_end(dt IN DATE) RETURN DATE AS BEGIN -- the Oracle DATE type has seconds resolution -- subtract one second from the first -- day of the following quarter RETURN TRUNC(ADD_MONTHS(dt, +3), 'Q') - (1/(24*60*60)); END- PostgreSQL
CREATE FUNCTION quarter_begin(dt timestamp with time zone) RETURNS timestamp with time zone AS $$ BEGIN RETURN date_trunc('quarter', dt); END; $$ LANGUAGE plpgsqlCREATE FUNCTION quarter_end(dt timestamp with time zone) RETURNS timestamp with time zone AS $$ BEGIN RETURN date_trunc('quarter', dt) + interval '3 month' - interval '1 microsecond'; END; $$ LANGUAGE plpgsql- SQL Server
CREATE FUNCTION quarter_begin (@dt DATETIME ) RETURNS DATETIME BEGIN RETURN DATEADD (qq, DATEDIFF (qq, 0, @dt), 0) ENDCREATE FUNCTION quarter_end (@dt DATETIME ) RETURNS DATETIME BEGIN RETURN DATEADD ( ms , -3 , DATEADD(mm, 3, dbo.quarter_begin(@dt)) ); END
異なる期間に対しても、同様の補助的な関数を使うことができます。多くは、上の例よりも複雑にはならないでしょう。特に、
betweenの代わりに以上(>=)や
未満(<)を使える時はそうです。もちろん、上限や下限の
日付をアプリケーション内で計算しても構いません。
ヒント
連続する期間を表す時は、明確な範囲条件を 指定したクエリにしましょう。1日だけを指定する場合も同じです。Oracleの場合は以下のようになります。
sale_date >= TRUNC(sysdate)
AND sale_date < TRUNC(sysdate + INTERVAL '1' DAY)もう1つ処理しにくい例としてよくあるのが、次のPostgreSQLの例のように、 日付を文字列として比較してしまう場合です。
SELECT ...
FROM sales
WHERE TO_CHAR(sale_date, 'YYYY-MM-DD') = '1970-01-01'ここでも問題になるのは、SALE_DATEの変換です。
このような条件は、数値と文字列のような異なる型をデータベースに渡せないという思い込みから作られます。ところが、バインドパラメータは、
全てのデータ型をサポートしています。例えば、java.util.Date
オブジェクトをバインドパラメータに使うこともできるのです。これも、バインドパラメータの利点の1つです。
もしそうできない場合は、テーブルの列の代わりに検索語の方を変換してしまいましょう。
SELECT ...
FROM sales
WHERE sale_date = TO_DATE('1970-01-01', 'YYYY-MM-DD')このクエリは、SALE_DATEのインデックスをちゃんと
使うことができます。しかもこの場合、入力文字列を1度しか変換しません。前の文だと、検索語との比較を行う前に、テーブルに保存された全ての日付を
変換する必要がありました。
バインドパラメータを使っても、比較式の違うところを変換する
ようにしても、SALE_DATEが時間成分を含んでいる場合は、
簡単にバグが発生してしまいます。このような場合には、明示的な範囲条件を使わなくてはなりません。
SELECT ...
FROM sales
WHERE sale_date >= TO_DATE('1970-01-01', 'YYYY-MM-DD')
AND sale_date < TO_DATE('1970-01-01', 'YYYY-MM-DD')
+ INTERVAL '1' DAY日付を比較する際は、常に明確な範囲条件を使うよう心がけましょう。
