Treasure Data(Presto/Hive)で月の最終日を取得する方法

8182 ワード
TreasureData hive Presto hive テキストリンク

始めに

SQLで月の最終日かどうかWHERE句で判別したい、なんてことありますよね？
Treasure Data(Presto/Hive)でどうやって月の最終日を取得する方法について紹介します。

Hiveの場合

Hiveの場合は何も考える必要はありません。 last_day という関数があるからです。
扱いが不安だと思う人も多いであろう、うるう年の検証も含めて下記クエリで各年の2月の最終日を取得してみます。

select last_day('2016-02-10')
union all
select last_day('2017-02-10')
union all
select last_day('2018-02-10')
union all
select last_day('2019-02-10')
union all
select last_day('2020-02-10')
;

結果は下記の通りです。無事最終日が取得できましたね。

Prestoの場合

Presto 326だと last_day_of_month という素敵な関数があるのですが、Treasure DataがホストしているPrestoのバージョン(0.205と317)ではこの関数がありません。
そのため、下記のように頑張る必要があります。

select date_format(date_parse(date_format(timestamp '2016-02-10' + INTERVAL '1' month, '%Y-%m-01'), '%Y-%m-%d') - INTERVAL '1' day, '%Y-%m-%d')
union all
select date_format(date_parse(date_format(timestamp '2017-02-10' + INTERVAL '1' month, '%Y-%m-01'), '%Y-%m-%d') - INTERVAL '1' day, '%Y-%m-%d')
union all
select date_format(date_parse(date_format(timestamp '2018-02-10' + INTERVAL '1' month, '%Y-%m-01'), '%Y-%m-%d') - INTERVAL '1' day, '%Y-%m-%d')
union all
select date_format(date_parse(date_format(timestamp '2019-02-10' + INTERVAL '1' month, '%Y-%m-01'), '%Y-%m-%d') - INTERVAL '1' day, '%Y-%m-%d')
union all
select date_format(date_parse(date_format(timestamp '2020-02-10' + INTERVAL '1' month, '%Y-%m-01'), '%Y-%m-%d') - INTERVAL '1' day, '%Y-%m-%d')
;

やっていることは、下記のように次の月の初日を計算して1日引き算しているだけです。

timestamp '2016-02-10' <--元データ
↓
timestamp '2016-02-10' + INTERVAL '1' month <--1ヶ月足す
↓
date_format(timestamp '2020-02-10' + INTERVAL '1' month, '%Y-%m-01') <--月の初日(01日)にする
↓
date_format(date_parse(date_format(timestamp '2020-02-10' + INTERVAL '1' month, '%Y-%m-01'), '%Y-%m-%d') - INTERVAL '1' day, '%Y-%m-%d') <--フォーマットを整えて1日引く

下記クエリで各段階を表示するとわかりやすいかもしれません。

select timestamp '2020-02-10',
       timestamp '2020-02-10' + INTERVAL '1' month,
       date_format(timestamp '2020-02-10' + INTERVAL '1' month, '%Y-%m-01'),
       date_format(date_parse(date_format(timestamp '2020-02-10' + INTERVAL '1' month, '%Y-%m-01'), '%Y-%m-%d') - INTERVAL '1' day, '%Y-%m-%d')
;

結果は下記のようになります。

最後に

いかがでしょうか？
Prestoのほうが便利な関数が揃っているイメージがあるかと思いますが、月の最終日を取得する部分に関してはHiveに軍配が上がりますね！