🍣
PARTITION BYを使ってユーザーの最後のイベントから対象者を抽出するクエリを書く
最後に購入してから10日以内のユーザーにメッセージを送りたい等、ユーザーが行った一番最後のイベントを抽出したい場合があります。
そうした時はWindow関数を使うと、以下のように書くことができます。
WITH
user_events_desc AS (
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC ) AS row_num
FROM
user_events )
SELECT
*
FROM
user_events_desc
WHERE
row_num = 1 AND name = "purchase" AND DATETIME_DIFF(CURRENT_DATETIME("Asia/Tokyo"), created_at, DAY) <= 10
「OVER」はWindow関数を使うためのお決まりのキーワードです。OVERの後にどのようにウィンドウを作るかを定義します。
「PARTITION BY」で分けたいグループを指定し、「ORDER BY」で並び順を指定し、「ROW_NUMBER」で連番を振ります。
これによって最後に購入してから10日以内のユーザーを取得することができます。
Discussion