分析クエリのメモ

## 前置き
仕事で使っているクエリのメモです
使用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関数があります。
[参考](https://prestodb.io/docs/0.172/functions/url.html)

### 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で並び替えて最後の値をとっています。
[ドキュメント](https://prestodb.io/docs/current/functions/window.html)にも書かれているので注意しましょう。