3記事連続ですが、今回もPrestoのWindow関数の話です。例によって、TreasureDataで動かすことを想定しています。
Window関数の話なのでドキュメントの参照するページもこちら。
参考: Window Functions
今回は、Value Functionsのセクションですね。
さて、SQLで集計業務を行なっていて、同じ行内の別の列の値ではなく、別の行の値を参照する必要が出てくる場面は結構あります。特に前後隣(上下隣と呼ぶ方が直感的かも)の値を参照する場面は多々あります。
例えば、Webサイトのアクセス分析において、ユーザーが1つ前に見ていたページを取得してページ遷移を確認したり、次のページのアクセス時刻との差分からそのページの滞在時間を測ったりする場合です。そのほかにも日毎に集計された時系列データにおいて前日からの変化量を見る、といった用途もあるでしょう。
そのように、前後の値を取得したい時は、LAG() と LEAD() という関数を使用することができます。前の値を取得したい場合が LAG()で、後の値を取得したい場合に使うのがLEAD()です。
LEADの方を例に使い方を説明すると、次のようになります。
LEAD({値を取得したい列名}, {何行後のレコードを取得したいか}, {デフォルト値})
OVER(PARTITION BY {グループ分けに使う列名} ORDER BY {ソートに使う列名} {ASC/DESC})
{何行後のレコードを取得したいか} は 0以上の値が指定でき、省略した場合は1になります。
{デフォルト値}は、PARTITION BY で指定したグループ内に指定した行数だけ後のデータがなかった場合に返す値です。省略した場合はNULLになります。
PARTITION BY、ORDER BY は他の Window関数と同じですね。PARTITION BYを使わなかった場合は全レコードまとめて処理されます。ORDER BY も省略できますが、意図せぬ順番で結果が返ってくることがあるので、原則何か指定しましょう。
説明が長くなってきたので実際にやってみます。次の例は、A/B/C 3人のユーザーの架空のWEBページのアクセスログを集計するものです。(1時間おきにアクセスするWebサイトって現実的にあり得ないとか色々ツッコミどころはありますがあくまでも関数の動きを見やすくするための例なのでご了承ください。)
LAGとLEADを使って、ユーザーごとに、そのページの前後にアクセスしたページを出力しています。
WITH
sample_table AS (
SELECT
access_at,
user,
page
FROM
UNNEST(
ARRAY['00:00:00', '01:00:00', '02:00:00', '03:00:00', '04:00:00',
'05:00:00', '06:00:00', '07:00:00', '08:00:00', '09:00:00'],
ARRAY['A', 'A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C'],
ARRAY['page0', 'page1', 'page2', 'page3', 'page4',
'page5', 'page6', 'page7', 'page8', 'page9']
) AS t(
access_at,
user,
page
)
)
SELECT
access_at,
user,
page,
LAG(page) OVER (PARTITION BY user ORDER BY access_at) AS rag_page,
LEAD(page) OVER (PARTITION BY user ORDER BY access_at) AS lead_page
FROM
sample_table
ORDER BY
access_at
-- 以下出力結果
access_at, user, page, rag_page, lead_page
'00:00:00', 'A', 'page0', NULL, 'page1'
'01:00:00', 'A', 'page1', 'page0', 'page2'
'02:00:00', 'A', 'page2', 'page1', 'page3'
'03:00:00', 'A', 'page3', 'page2', NULL
'04:00:00', 'B', 'page4', NULL, 'page5'
'05:00:00', 'B', 'page5', 'page4', 'page6'
'06:00:00', 'B', 'page6', 'page5', NULL
'07:00:00', 'C', 'page7', NULL, 'page8'
'08:00:00', 'C', 'page8', 'page7', 'page9'
'09:00:00', 'C', 'page9', 'page8', NULL{何行後のレコードを取得したいか} (OFFSET) は省略したので、RAGとLEADは、それぞれ1行前と後のpage列の値を取得しています。 page, rag_page, lead_pageの各列の結果を見比べるとわかりやすいと思います。
rag_pageで1つ前にアクセスしたページ、lead_pageで1つ後にアクセスしたページが取れていますね。
userでグループを切っているので、各ユーザーごとの最初のアクセスのLAGと、最後のアクセスのLEADは対象のレコードがないのでNULLになっています。
もし、関数を呼び出すときに3つ目の引数としてデフォルト値を指定していたら、NULLではなくそのデフォルト値が入った状態で戻されます。
ORDER BY で指定した、access_atに対しては、 ASC/DESCを指定しなかったので、デフォルトのASC(昇順)で動作しています。もし、それぞれDESCを指定したらaccess_atについて降順になり、前後が入れ替わるのでLAGとLEADの結果が入れ替わります。
ORDER BYで自分が何順を指定しているかと、 LAGとLEADのどちらを使っているかはセットで確認し、想定した結果が得られているかどうかは慎重に検証しましょう。
LAGとLEAD、わざわざ２関数使わなくても、LAGのOFFSET に -1 を入れたら LEADの動きになるんじゃないの?と思って試したのですが、これはエラーになりました。OFFSETは0以上の値しか受け付けないようです。 ちなみにOFFSETに0を入れるとその行の値をそのまま返すので、LAGやLEADの意味はなくなります。