カテゴリー: SQL

２年ほど前、Prestoのクエリを書き始めた時に知って感動して以来、
ずっと使い続けている文法に WITH句 があります。

イメージとしてはSQL中でVIEWのようなものを作れる構文で、
これを使うとクエリのネストを浅くするなど可読性を大幅に向上させることができます。

例えば次の2つのクエリは同じ結果を返してくれます。
(この例だとまだメリットがわかりにくいですね。そもそも一番外側のSELECT句が純粋に無駄)
伝えたいのはSELECT句のネストがなくなり、フラットな書き方ができるということです。
階層がより深かったり、数百行以上の規模のクエリになるとこの効果は劇的に大きく出ます。
各一時テーブルに名前をつけられることも意図を伝える上で非常に有益です。

通常のネスとした書き方。

SELECT
    col1,
    col2_max
FROM (
    SELECT
        col1,
        MAX(col2) AS col2_max
    FROM
        table1
    GROUP BY
        col1
);

WITH句を使った書き方。

WITH
    table2 AS (
        SELECT
            col1,
            MAX(col2) AS col2_max
        FROM
            table1
        GROUP BY
            col1
    )
SELECT
    col1,
    col2_max
FROM
    table2;

うちのチームでは(その人がSQLを十分書ける人であれば)
トレジャーデータのアカウントを渡して基本的な注意事項を説明したら、
すぐに WITH の使い方を教えています。

その時に使える公式なドキュメントとか無いのかな、と思って探していたのですが、
9.32. SELECT の WITH Clause がそれのようです。
(目次でWITHを検索しても出てこないのでこれまで見つけれてなかった。)
非常にあっさりとしか説明されてなくて、職場の既存クエリ読んでもらった方が良さそうな情報量ではあります。

普段の業務で利用しているPresto (treasuredata) で１ヶ月後の日付を求める機会があり、
こちらのドキュイメントになる、date_add という関数の挙動をテストした時に見つけた挙動のメモです。

6.13. Date and Time Functions and Operators

こちら、日付単位のデータのn日後やn日前を求める時には何も問題ないのですが、
nヶ月後を求める時に少し不思議な動きがありました。

まず前提として、元のデータの日付部分が、1〜28日の場合は、何も問題がありません。
5月16日の3ヶ月後は 8月16日ですし、
2ヶ月前は 3月16日です。

SELECT
    DATE_ADD('month', 3, timestamp '2019-05-16 12:00:00'),  -- 2019-08-16 12:00:00.000
    DATE_ADD('month', -2, timestamp '2019-05-16 12:00:00') -- 2019-03-16 12:00:00.000

問題は、月によって日数が違う点です。
例えば、8月は31日までありますが、9月は30日までしかありません。
そのため、8月31日の1ヶ月後をPrestoのDATE_ADDで算出すると、9月30日になります。
ちなみに、9月30日の1ヶ月前は8月30日です。
要するに、8月31日に1ヶ月足して1ヶ月引くと8月30日になり、元に戻らない。

SELECT
    DATE_ADD('month', 1, timestamp '2019-08-30'), -- 2019-09-30 00:00:00.000
    DATE_ADD('month', 1, timestamp '2019-08-31'),  -- 2019-09-30 00:00:00.000
    DATE_ADD('month', -1, timestamp '2019-09-30'),  -- 2019-08-30 00:00:00.000
    DATE_ADD('month', -1, timestamp '2019-10-01'),  -- 2019-09-01 00:00:00.000
    DATE_ADD('month', -1, DATE_ADD('month', 1, timestamp '2019-08-31')) -- 2019-08-30 00:00:00.000

こういう挙動を嫌って、いつもDATE_ADDは利用せず、60*60*24*30秒足したり引いたりするクエリを書いていたのですが、
30日と1ヶ月は厳密には違うので、その時に応じてよく考えて方法を選ぶ必要があります。

そして、問題はもう一点あります。
それは時刻まで含めて計算した時に、時刻の前後関係が前後することです。
8月30日と8月31日の１ヶ月後はどちらも9月30日と算出されますが、
これが時刻も含データの場合、時刻部分は1ヶ月足す前の値から変化しません。

要するに
(1)8月30日20時の１ヶ月後は9月30日20時で、
(2)8月31日7時の１ヶ月後は9月30日7時です。
元の時間は当然、(1)の方が前なのに、(1)と(2)の1ヶ月後の時刻は(2)の1ヶ月後の方が前になります。

SELECT
    DATE_ADD('month', 1, timestamp '2019-08-30 20:00:00'), -- 2019-09-30 20:00:00.000
    DATE_ADD('month', 1, timestamp '2019-08-31 07:00:00'),  -- 2019-09-30 07:00:00.000
    timestamp '2019-08-30 20:00:00' < timestamp '2019-08-31 07:00:00', -- true
    DATE_ADD('month', 1, timestamp '2019-08-30 20:00:00') < DATE_ADD('month', 1, timestamp '2019-08-31 07:00:00') --false

日付単位で行った時の不等号が等号になるのはまだ許容範囲かもしれませんが、
不等号の反転はちょっと困る。

幸い、nヶ月間以内の判定を時刻まで考慮して厳密に行う場面は少ない(これまでほぼなかった)ので
問題になることは少ないのですが、注意が必要です。
(timestampに変換する前に時刻を切り捨てるなどの処理を入れた方がいい。)

以上をまとめると、Prestoの DATE_ADDで月単位(month)の演算をする時の注意は次の3つです。

1. 異なる日付の±nヶ月後が同じ日付になることがある
2. ある日付のnヶ月後のnヶ月前が元の日付と異なることがある
3. ２つの時間のnヶ月後を計算すると時間の前後関係が入れ替わることがある

とくに2月がからむと最悪で、
１月28,29,30,31日の1ヶ月後は全部2月28日になります。

Webサービスのアクセスログデータを集計するとき、URLを扱う場面はよくあります。
WHERE句で特定の条件のURLを絞り込んだり、
SELECT句でパラメーターやパスごとに数えたりする場面はよくありますが、
Prestoにはそのようなときに便利な関数が準備されていいます。

ドキュメント　6.18. URL Functions

具体的には下記の関数群です。

• url_extract_fragment(url)
• url_extract_host(url)
• url_extract_parameter(url, name)
• url_extract_path(url)
• url_extract_port(url)
• url_extract_protocol(url)
• url_extract_query(url)

URLを次の形式とすると、それぞれの関数で url_extract_xxxx の　xxxx 部分を文字列として返してくれます。
[protocol:][//host[:port]][path][?query][#fragment]

これを知るまでは LIKE や正規表現を使うことが多く、稀に予期せぬパターンがマッチしてしまったりしていて困っていたのですが、
これらのURL関数群を使うようになってからは、そのようなエラーは起こりにくくなりましたし、
クエリも意図がわかりやすいものになりとても助かっています。

Prestoの便利な関数の紹介です。
Prestoに限らず、SQLデータを抽出するときにNULL値を別の値に置き換えたいケースは頻繁に発生します。
例えば、NULLならば0、その他の値はそのまま出力したいときは下記のような書き方になります。

CASE
    WHEN column_name1 IS NULL THEN 0
    ELSE column_name1
END AS column_name1

これを、下記のように少しだけスッキリ書くことがでます。

COALESCE(column_name1, 0) AS column_name1

ドキュメントはこちら

この他、次のように引数を複数並べて書くと、左から順番に評価して最初にNULLでなかった列の値を取得できます。
完全外部結合するときなどに便利ですね。

COALESCE(column_name1, column_name2, column_name3, 0) AS column_name1