PrestoでIGNORE NULLSが非対応なのでMAX_BYで解決した話

`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
```sql:postgres_last_val.sql
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
```sql:presto_last_val.sql
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(値を取得したい列名, 最大値の対象となる列名)`
こちらを使って同様の結果を得たいと思います。

```sql:presto_max_by.sql
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`の場合はその行の値は取得されません。
それを踏まえて改良したクエリがこちら。

```sql:presto_max_by_seikai.sql
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`の指定がいらなくなくなります。
見た目にも簡略されますし、こっちの方がいいですね。