分析クエリのメモ

前置き

仕事で使っているクエリのメモです
使用DBはトレジャーデータでエンジンはPrestoです
トレジャーデータの独自関数なども含まれています

集約関数

BOOL_OR

IF(BOOL_OR(hoge_flag), NULL, COUNT(DISTINCT user_id) FILTER(WHERE hogehoge_flag AND NOT fugafuga_flag))

hoge_flagが1つでもtrueの場合はNULL。そうじゃない場合は、user_idを重複排除でカウントします。さらに集計の対象をhogehoge_flagがtrueかつfugafuga_flagがfalseのものに絞ります。

BOOL_OR(TD_TIME_RANGE(time, install_timestamp, TD_TIME_ADD(install_timestamp, '30d', 'JST'), 'JST') AND purchase_flag)

TD_TIME_RANGEとのあわせ技で、簡単に言うとインストールから30日以内に購入があったかをチェックしています。

BOOL_AND

BOOL_AND(IF(event_value NOT IN ('N/A', ''), JSON_EXTRACT_SCALAR(JSON_PARSE(event_value), '$.gender'), '') != 'female')

JSON系の関数とのあわせ技です。event_valueカラムにJSON形式で値が入っているので、ゴミデータを除いた後にJSON関数でgenderがfemaleかどうか見てる感じですね。

BOOL_AND(TD_TIME_RANGE(CAST(install_timestamp AS BIGINT), TD_TIME_ADD(TD_SCHEDULED_TIME(), '-72h', 'Asia/Tokyo'), NULL, 'Asia/Tokyo'))

これはTD_TIME_RANGEとのあわせ技で直近72時間でインストールしているかどうかを見ているみたいです。

前後の行の値をとる

LEAD

CASE
  WHEN LEAD(IF(COALESCE(session_count, 0) > 0, 1, 0), 1) OVER (PARTITION BY idfa ORDER BY time_span) IS NULL 
THEN NULL
  WHEN LEAD(IF(COALESCE(session_count, 0) > 0, 1, 0), 1) OVER (PARTITION BY idfa_android_id ORDER BY time_span) = 1 
THEN 1
  ELSE 0
END AS is_activated_30days

こんな感じにCASEと組み合わせたりなんかも昔していました。引数にIFとか渡したりもできます。
やってることは、次の行のsession_countをみて判定しているだけですね。

LAG

COALESCE(total_session_count, 0) - COALESCE(LAG(total_session_count) OVER(PARTITION BY id ORDER BY time), 0)

これはシンプルに前の行との差分をとっているだけ。

JSON系

JSON_EXTRACT_SCALAR(JSON_PARSE(MIN_BY(event_value, time) FILTER(WHERE event_value IS NOT NULL)), '$.hoge_type')

JSON_PARSEでJSON形式の文字列をJSONとして返して、JSON_EXTRACT_SCALARでhoge_typeキーの値を文字列で取得しています。

IF(event_value NOT IN ('N/A', ''), JSON_EXTRACT_SCALAR(JSON_PARSE(event_value), '$.gender'))

BOOL_ANDで紹介したのとほぼ同じですね。
ゴミデータを除いた後にgenderの値を取得しています。

その他

WINDOW関数

BOOL_OR(課金_flag) OVER (PARTITION BY id ORDER BY day
                                         ROWS BETWEEN dormancy_period PRECEDING
                                         AND IF(7 - dormancy_period >= 0, 7 - dormancy_period, 0) FOLLOWING)

ポイントとしてはORDER BY以降のWINDOW (FRAME)部分にも色々書くことができるということですね。
7日以内に課金しているかを見ているらしいです。もう少し簡単に書けそう

MIN_BY(hoge, IF(hpge IS NOT NULL, time)) OVER (PARTITION BY id)

pgeがNULLの行を除いた中でtimeが一番小さいhogeをとっています。
MIN()だとNULLを除いた値をとってくれるけど、MIN_BY()はNULLも含めた値をとっちゃうので、わざわざこう書いています。

URL関数

URLを色々いじれる関数も色々あります。

CARDINALITY(SPLIT(SUBSTR(URL_EXTRACT_PATH(referer), 2), '/')) splited_referer_size

URL_EXTRACT_PATHはディレクトリパスを抽出できる関数です。
この場合はrefererの数を数えているっぽいですね。

SPLIT(SUBSTR(URL_EXTRACT_PATH(referer), 2), '/') splited_referer

そしてこれはrefererを分割して配列で持っているようです
他にも色々なURL関数があります。
参考

LAST_VALUE、MAX関数の罠

どんな罠があるかというと、これらの関数は 現在行までの最後、最大の値 しかとってくれません。
なので使用するときは、「ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING」をつけて明示的に最後の値まで見るようにしなければなりません。

LAST_VALUE(hoge) OVER (PARTITION BY id ORDER BY time 
                                       ROWS BETWEEN UNBOUNDED PRECEDING 
                                       AND UNBOUNDED FOLLOWING) AS last_hoge

ただ、毎回これを書くのはすごくめんどくさいので下記のように書くことが推奨されています

FIRST_VALUE(hoge) OVER (PARTITION BY id ORDER BY time DESC) AS last_hoge

MIN(hoge) OVER (PARTITION BY id ORDER BY time DESC) AS min_hoge

FIRST_VALUEやMINを使用してORDER BY time DESCで並び替えて最後の値をとっています。
ドキュメントにも書かれているので注意しましょう。