sucher

sucher(ズーハー)はエンジニアを応援します。

[PrestoSQL] 日付範囲(from~to)のデータを日毎に変換して集約したい時のメモ

223 views
約 2 分

どうも!SQLのドリルが欲しいSQL初心者の佐々木です。
PrestoSQLというかAthenaをよく叩く中で、困るケースがあり、その備忘録です。

スポンサーリンク



今回想定するケース

ユーザの契約期間を保持する以下のようなuserテーブルを考えます。
カラムは、 ユーザid (user_id), 契約開始日 (date_from), 契約終了日 (date_to) とします。
ここから、ある日付の契約ユーザ数を求めたい時です。

解法

解説

一言で言えば、
sequence関数で行毎に date_from から date_to まで1日毎の配列を作成し、それをunnest関数とcross join関数で配列をレコードにして、日付毎にgroup byをしてカウント しています。

sequence(start, stop, step) -> array(bigint|date|timestamp)
sequence関数は、開始(start), 終了(stop), 間隔(step) を引数にとり、ある間隔での開始から終了までの配列を生成します。
date型もOKです。

unnest
unnest関数は配列やmapを展開します。配列であればシングルカラムにしますし、mapであれば2カラムになります。
一般にはcross joinと併用します。

cross join
joinの一種で、直積結合をします。2関係をcrossしてjoinしてるわけですね。

参考

スポンサーリンク





この記事を書いた人

Toshiki Sasaki
主にSQLとPythonの記事を書きます。
高専出身で電気電子を専攻していましたが、気まぐれで大学院では機械読解の研究に従事していました。
電気電子・NLP系の記事もちらほら書いていけたらいいなと思ってます。
Follow :

コメント

*
*
* (公開されません)

Share / Subscribe
Facebook Likes
Tweets
Hatena Bookmarks
Pinterest
Pocket
Evernote
Feedly
Send to LINE