どうも！SQLのドリルが欲しいSQL初心者の佐々木です。
PrestoSQLというかAthenaをよく叩く中で、困るケースがあり、その備忘録です。
今回想定するケース
ユーザの契約期間を保持する以下のようなuserテーブルを考えます。
カラムは、 ユーザid (user_id), 契約開始日 (date_from), 契約終了日 (date_to) とします。
ここから、ある日付の契約ユーザ数を求めたい時です。
解法
1 2 3 4 5 6 7 | select date, count(date) from user cross join unnest( sequence(cast(date_from as date), cast(date_to as date), interval '1' day) ) as t(date) group by date order by date |
解説
一言で言えば、
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してるわけですね。
