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

17318 ワード
SQL PrestoDB PostgreSQL Presto TreasureData SQL テキストリンク

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

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

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

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

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

C++新特性～lambda抜粋
C++コピー構造関数のいくつかの詳細