Prestoではwindow関数でIGNORE NULLSがサポートされていません。
集計の際にPostgreSQLではLAST_VALUEでIGNORE NULLSを使いたかったのですが代替案を模索した結果を書き記しておきます。
結論
MAX_BYとIFの組み合わせで同じ集計ができる！！
やろうとしたこと
以下のように日毎にパラメータが入っているテーブルがありました
| id | date | param_1 | param_2 | param_3 | param_4 |
|---|---|---|---|---|---|
| 1 | 2019/10/17 | null | 1 | 3 | 2 |
| 1 | 2019/10/18 | null | 1 | null | 1 |
| 1 | 2019/10/19 | 1 | 2 | null | 2 |
| 1 | 2019/10/20 | null | 2 | null | 1 |
| 2 | 2019/10/19 | 1 | 2 | 3 | null |
| 2 | 2019/10/20 | 1 | 2 | null | null |
ここからnullではなく一番直近の値を取得したい
| id | param_1 | param_2 | param_3 | param_4 |
|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 1 |
| 2 | 1 | 2 | 3 | null |
このような形ですね
（id=2のparam_4はnullしかないのでその場合はnull)
BEFORE
###PostgresSQL
select
DISTINCT id
,LAST_VALUE(param_1 IGNORE NULLS) OVER (
PARTITION BY id
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as param_1
,LAST_VALUE(param_2 IGNORE NULLS) OVER (
PARTITION BY id
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as param_2
,LAST_VALUE(param_3 IGNORE NULLS) OVER (
PARTITION BY id
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as param_3
,LAST_VALUE(param_4 IGNORE NULLS) OVER (
PARTITION BY id
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as param_4
from
test
order by
id
IGNORE NULLSが使えるのであればwindow関数と組み合わせて、要求通りの結果を出力することができます。
###Presto
select
DISTINCT id
,LAST_VALUE(param_1) OVER (
PARTITION BY id
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as param_1
,LAST_VALUE(param_2) OVER (
PARTITION BY id
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as param_2
,LAST_VALUE(param_3) OVER (
PARTITION BY id
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as param_3
,LAST_VALUE(param_4) OVER (
PARTITION BY id
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as param_4
from
test
order by
id
出力結果がこれ
| id | param_1 | param_2 | param_3 | param_4 |
|---|---|---|---|---|
| 1 | null | 2 | null | 1 |
| 2 | 1 | 2 | null | null |
これだとDATEの最大値（10/20）の値を抜き出しただけで、要求通りの結果ではありません。
AFTER
Prestoには独自関数でMAX_BYという独自関数が存在します。
MAX_BY(値を取得したい列名, 最大値の対象となる列名)
こちらを使って同様の結果を得たいと思います。
select
id
,max_by(param_1,date) as param_1
,max_by(param_2,date) as param_2
,max_by(param_3,date) as param_3
,max_by(param_4,date) as param_4
from
test
group by
id
order by
id
これでよし！！！！
・・・
とはなりませんでした。
これだとnullが除去できず先ほどの結果と同じになります。
では、どうしたら良いか。
MAX_BYでは値を取得したい列の値がnullでもそのまま取得しますが、最大値の対象となる列名がnullの場合はその行の値は取得されません。
それを踏まえて改良したクエリがこちら。
select
id
,max_by(param_1,if(param_1 is null,null,date)) as param_1
,max_by(param_2,if(param_2 is null,null,date)) as param_2
,max_by(param_3,if(param_3 is null,null,date)) as param_3
,max_by(param_4,if(param_4 is null,null,date)) as param_4
from
test
group by
id
order by
id
| id | param_1 | param_2 | param_3 | param_4 |
|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 1 |
| 2 | 1 | 2 | 3 | null |
最大値の対象となるdateに対して、取得するparamがnullならnullを渡すとしてあげます。
これにより目的としていた結果が取得できました。
余談
###Prestoについて
理由はわかりませんがPrestoでは一向にIGNORE NULLSが実装されません。
Issue自体は2016年くらいから上がってはいるのですが、なんででしょうね。
ちなみにPrestoは最近PrestoDBとPrestoSQLに分裂しました。
TreasureDataは現在、PrestoDBを採用しています。
PrestoSQLは実はIGNORE NULLSがすでに実装されているので、もし移行した際にはそちらの記述もできるようになります。
参考：https://prestosql.io/docs/current/functions/window.html
###LAST_VALUEについて
今回はMAX_BYとの対比でLAST_VALUEを使いましたがFIRST_VALUEを使うことをお勧めします。
理由はOVER句のROWSのデフォルト値がRANGE UNBOUNDED PRECEDING AND CURRENT ROWとなっているからです。
これは区切った中の1行目から自分の行までを対象とするという意味で、自分より後続の行を取得することはできません。
今回は全行を対象とするためにROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGと指定しています。
FIRST_VALUEに置き換えると
LAST_VALUE(param_1) OVER (
PARTITION BY id
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as param_1
これが
FIRST_VALUE(param_1) OVER (
PARTITION BY id
ORDER BY date DESC
) as param_1
こうなります。
ORDER BYをDESCにしてあげてROWSの指定がいらなくなくなります。
見た目にも簡略されますし、こっちの方がいいですね。