分析ノート

WINDOW関数の話はひと段落しましたが、今回もPrestoの話です。例によって僕はトレジャーデータで検証しています。(今回の記事で紹介する内容はMySQLでは動かないという話も見かけました。)

ここ最近の記事で掲載したサンプルのSQLでは、テーブルに保存されたデータではなくSQLで生成したデータを使っていました。事前にこういうデータを用意してますとか書くのが面倒だったので。その際、ARRAY関数で作った配列を並べて、UNNESTでテーブル型に変換していたのですが、実はVALUES関数をつかってダミーデータを作ることもできるそうです。

この機会に、VALUES関数について調べたので、わかったことを記事にまとめておきます。PrestoのVALUES関数のドキュメントはこちらです。
参考: VALUES — Presto 0.261 Documentation

Description を読むと、VALUESは問い合わせ(query)が使用できる場所ならどこでも使用できる、トップレベルでさえ使える、といきなり衝撃的なことが書かれています。僕は、INSERT文などの特定の構文の中でしか使えないと思っていました。

試しにトップレベルで使ってみましょう。

VALUES 1, 2, 3

-- 出力結果
_col0
1
2
3

確かに、無名列(_col0)に3行のレコードが生成されました。

複数列のデータを作ることもできます。

VALUES
  (1, 'a'),
  (2, 'b'),
  (3, 'c')

-- 出力結果
_col0, _col1
1,     'a'
2,     'b'
3,     'c'

ドキュメントには、ASを使えばテーブルと列に名前も付けれる、と書かれているので、色々試したのですが、トップレベルでVALUES を使った場合にASで列名をつける方法は結局見つけられませんでした。いろんな位置にカッコをつけたりして検証したのですが。

無名テーブルではなく、テーブル名と列名をつけたい場合は、次のようにSELECT文のFROM句の中で使うことで実現できます。

SELECT
  id,
  name
FROM
  (
    VALUES
      (1, 'a'),
      (2, 'b'),
      (3, 'c')
  ) AS t(id, name)

-- 出力結果
id, name
1,  'a'
2,  'b'
3,  'c'

この結果は、UNNESTを使って書いた下のクエリの結果と同じになります。

SELECT
  id,
  name
FROM
  UNNEST(
    ARRAY[1, 2, 3],
    ARRAY['a', 'b', 'c']
  ) AS t(id, name)

UNNEST と VALUES のどちらがいいか、という話ですが 読みやすさというか、直感的でぱっと見で結果の予想がつきやすいのは VALUESの方なんじゃないかなと思います。ただ、10行くらいのダミーデータを生成しようとすると、UNNESTの方が行数が少なくなりやすく、このブログのサンプルコードとしてはそちらの方が使いやすかったりします。非本質的な部分なのであまりスペースを取りたくないので。

処理速度等の性能面の比較は行っていません。性能が気になるほどの莫大なデータをこのような方法でSQLに直接書くべきではないと思いますので、もしそのような大規模データをサンプルとして用意したい場合は、VALUESを使うとか言ってないで、素直にどの下のテーブルに格納してそこからSELECTした方が良いと思います。

6記事連続ですが今回もPrestoのWINDOW関数の記事です。例によって僕はトレジャーデータで検証しています。

実は前回のframeの記事でWINDOW関数シリーズは終わりにしようかと思っていたのですが、ドキュメントを見ると、まだ紹介してない関数はあと3つだけになっており、試しに動かしてみたら予想とちょっと違う動きも見られたので切りよくこれらまで紹介することにしました。

ドキュメントはこの記事もここです。
参考: Window Functions

この記事で紹介する関数は次の3つです。
- first_value(x)
- last_value(x)
- nth_value(x, offset)

first_value の説明は、 Returns the first value of the window. とあり、
last_value の説明は、Returns the last value of the window. とどちらも簡潔に書かれています。WINDOW内の最初の値と最後の値を返してくれると読めますね。

そして、 nth_value は offset で指定した数値番目(数値は1以上)の値を返してくれます。

さて、いつもみたいにダミーデータを使ってやってみましょう。
試したコードは下記です。

WITH
    sample_table AS(
        SELECT
            id,
            category,
            value
        FROM
            UNNEST(
                ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
                ARRAY['A', 'A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
                ARRAY[20, 10, 20, 30, 5, 50, 15, 30, 20, 40]
            ) AS t(
                id,
                category,
                value
            )
    )
SELECT
    id,
    category,
    value,
    first_value(value) OVER(PARTITION BY category ORDER BY value) AS first_value_,
    last_value(value) OVER(PARTITION BY category ORDER BY value) AS last_value_,
    nth_value(value, 4) OVER(PARTITION BY category ORDER BY value) AS nth_value_4
FROM
    sample_table

さて、この結果はどうなるでしょうか？3関数とも、OVER(PARTITION BY category ORDER BY value) をつけたので、 categoryの値で、’A’ と ‘B’ に分けられ、 value 列の値でソートされた上で、関数が実行されそうですね。

僕は、 first_value(value) の結果は、category ‘A’と’B’ でそれぞれ一番小さい値である、5と15が返され、 last_value(value) の結果は、それぞれのカテゴリで一番大きい、50と40が返されると予想していました。
そして、nth_value(value, 4) は’A’,’B’それぞれの小さい方から4番目の値である、20と40が帰ってくると思っていました。

ところが実行結果は以下の通りです。

id, category, value, first_value_, last_value_, nth_value_4
5,  'A',      5,     5,            5,           NULL
2,  'A',      10,    5,            10,          NULL
1,  'A',      20,    5,            20,          20
3,  'A',      20,    5,            20,          20
4,  'A',      30,    5,            30,          20
6,  'A',      50,    5,            50,          20
7,  'B',      15,    15,           15,          NULL
9,  'B',      20,    15,           20,          NULL
8,  'B',      30,    15,           30,          NULL
10, 'B',      40,    15,           40,          40

いかがでしょうか。この結果が予想通りだった！って人は(元々仕様を正確に理解していた人以外に)いらっしゃいますか？

first_value_ 列はいいですね。行をcategory列でグループ分けして、valueでソートした後、それぞれのcategoryのグループの最初の値である 5と15 を返してくれています。

問題は、last_value_ 列と、 nth_value_4 列です。
last_value_ の方なんて、同じ行の value 列の値をそのまま返してきていて、関数を使った意味がないです。

なぜこのような挙動になるのかを理解するには、前回の記事で紹介したframeについて知る必要があります。
参考: WINDOW関数のframeのROWSモードについて

WINDOW関数には frameというオプションがあり、何も指定しないとこれが、
RANGE UNBOUNDED PRECEDING
になるのでした。

つまり、PARTITION BY で区切ったグループ内の、最初の行からその行と同じ値の行までを関数の適用範囲とするわけです。これにより、 last_value が指し示す最後の行とは、その行と(ORDER BYで指定した行の値が)同じ値の行まで、の範囲で見た場合の最後の行となり、その結果として同じ行のvalue列の値をそのまま返してきてきたわけです。

ちなみに、 last_value(value) ではなく、 last_value(id) とするとまた微妙に結果が変わります。
category Aで、value列が20の行が二つありますが、2行とも 3が返ってきます。

nth_value_4　の方も原理は同じです。最初の行からその行と同じ値の行まで、の範囲に含まれる行数が4行未満の場合、返す値がないのでNULLになってしまうのです。
未指定の場合 frame は ROWSモードではなくRANGEモードなので、 その行までに4行含まなくても、その行と同じ値の行まで含めて4行確保できれば値を返してくれます。
だから、結果の3行目(id:1の行)は20が返ってきているのです。

ここまでの説明で、 last_value と nth_value って使えない、もしくは使いにくいなと感じられた人も多いかと思います。
これらの関数でイメージ通りに PARTITION BY で区切った範囲の最終行や、 n番目の行の値を素直に出力したい場合は、 合わせて frameを指定してあげることで可能になります。
PARTITION BY で区切った範囲の最初から最後までなので、
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
ですね。

要するに次のように書けば良いです。
(記述量が多くなるので、SELECT句内の該当行だけ書きました)

  last_value(VALUE) OVER(
    PARTITION BY
　　　　　　　　　　category
  　　　　ORDER BY
    　　VALUE
　　　　　　　　ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_value_,
  nth_value(VALUE,　4) OVER(
    PARTITION BY
      category
    ORDER BY
      VALUE
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS nth_value_4

これで出力結果を載せる前に書いてた、イメージしてた通りの結果が得られます。

またWINDOW関数の記事です。例によって、SQLクエリエンジンはPrestoを仮定し、僕はトレジャーデータで検証して記事を書いています。
今回は前回の記事でチラチラと登場していたframeについて説明を試みます。
(正直、僕もこれまであまり使ってこなかったので詳しくはありません。)

ドキュメントは最近ずっと参照しているここ。
参考: Window Functions

さて、改めてWINDOW関数の説明を見ると、WINDOW関数は次の形で呼び出すと書かれています。

function(args) OVER (
    [PARTITION BY expression]
    [ORDER BY expression [ASC|DESC]]
    [frame]
)

function は SUMとか ROW_NUMBER などのことですね。
大括弧書きの部分はオプションなので省略可能です。よく見ると、ORDER BY の次に [frame] というのがあります。これが今回の記事のテーマです。

WINDOW関数で集計する時は、まず各レコードを、PARTITION BY で指定した列の値によってグループ分け、ORDER BY で指定した列と、指定したソート順(ASC/DESC)によって整列します。
そして、 「frame で指定した範囲」で集計するのです。

前回の記事で見た通りframe を何も指定しないと、ORDER BY がなければPARTITION BY で区切ったグループ全体を集計し、ORDER BYがあれば、PARTITION BYで区切ったグループ内の最初の行から、その行と同順位の行までを集計します。

これが、frameを指定することで、 ORDER BYを指定した上でも、「PARTITION BY で区切ったグループ全体」とか、「その行からPARTITION BY で区切った最後の行まで」とか、「その行の3行前から5行後ろまで」など、さまざまなレンジで集計ができます。これによって「直近7レコードの移動平均」みたいなこともできるわけですね。

frameの指定方法ですが、ドキュメントには次のように記載されています。(次の2行のどちらか)

{RANGE|ROWS} frame_start
{RANGE|ROWS} BETWEEN frame_start AND frame_end

最初に、RANGEモードかROWSモードをを指定し、1番目の記法では開始行(frame_start)のみ指定、２番目の記法では開始行(frame_start)と終了行(frame_end)を指定します。1番目の記法を採用した場合は、終了行はCURRENT ROW を指定するのと同じ動きになります。

そして、frame_start/ frame_start は 次の5種類の表記が使えます。 (ROWSモードでしかサポートされていないのが2種類あるのでRANGEモードでは3種類)

UNBOUNDED PRECEDING
expression PRECEDING  -- only allowed in ROWS mode
CURRENT ROW
expression FOLLOWING  -- only allowed in ROWS mode
UNBOUNDED FOLLOWING

UNBOUNDED PRECEDING は一番最初の行からで、UNBOUNDED FOLLOWINGは一番最後の行までの意味です。CURRENT ROW は ROWSモードであればその行まで、RANGEモードであれば、その行と同じ順位の行までです。(前回の記事で試してるのは未指定なのでデフォルトのRANGEモードの方です。)

expression PRECEDING と expression FOLLOWING は ROWSモードでしか使えませんが、expression に数字を入れて、 3 PRECEDING とか、 5 FOLLOWING のように書きます。それぞれ3行前、5行後ろの意味です。

さて、だらだら説明を書いてきたのでこの辺で一つやってみましょう。設定できるオプションが多くておそらく用途も多いROWS モードの方をやってみます。
例が無駄に複雑になるので次の実行例ではPARTITION BY は使いませんでしたが、もちろん本当は使えます。集計関数は挙動を確認しやすいSUMを例にしていますがこちらももちろん他の関数も使えます。

WITH
    sample_table AS(
        SELECT 
            id,
            value
        FROM
            UNNEST(
                ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
                ARRAY[20, 10, 40, 30, 50, 30, 20, 60, 10, 30]
            ) AS t(
                id,
                value
            )
)
SELECT 
    id,
    value,
    SUM(value) OVER() AS sum_1,
    SUM(value) OVER(ROWS UNBOUNDED PRECEDING) AS sum_2,
    SUM(value) OVER(ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS sum_3,
    SUM(value) OVER(ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_4,
    SUM(value) OVER(ORDER BY id ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS sum_5
FROM
    sample_table

-- 以下出力結果
id, value, sum_1, sum_2, sum_3, sum_4, sum_5
1,  20,    300,   20,    300,   30,    NULL
2,  10,    300,   30,    280,   70,    20
3,  40,    300,   70,    270,   80,    30
4,  30,    300,   100,   230,   120,   70
5,  50,    300,   150,   200,   110,   80
6,  30,    300,   180,   150,   100,   120
7,  20,    300,   200,   120,   110,   110
8,  60,    300,   260,   100,   90,    100
9,  10,    300,   270,   40,    100,   110
10, 30,    300,   300,   30,    40,    90

色々試したので順番に見ていきましょう。
sum_1 列はただの比較用です。前回の記事でも見ましたが、ORDER BY含めて何も指定しなければ全体の和になります。これは、デフォルトのRANGEモードが作動しており、行同士の間に順序が定義されていないので、全行が同一順位として扱われ、同じ順位の行まで足すという挙動により全行が足されたからです。

sum_2 以降が ROWSモードになります。 まず sum_2 ではROWSモードへの変更のみ行いました。(RANGEモードも未指定の場合の挙動は RANGE UNBOUNDED PRECEDING なので、本当にモードだけ変えています。)
ご覧の通り、ROWS モードでは ORDER BYがなくてもvalue列の累積和になっています。
これはROWSモードでは、「先頭の行から、その行まで」集計するという挙動になったからです。前回の記事でソートに指定した列に同じ値ががあった場合に少し不思議な挙動をするという話を書きましたが、ROWSモードではそれも発生せず、普通に累積和になります。

さて、この後のsum_3以降がframe機能の本番で初期設定ではなく、frameの記法を使って範囲を指定しています。
sum_3 では、 BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING として、その行から(例ではPARTITION BY使ってないけど、使ってればそのグループ内の)最後の行まで、という指定になっています。逆順の累積和っぽい挙動になっているのが見て取れると思います。

sum_4 は、 BETWEEN 1 PRECEDING AND 1 FOLLOWING と指定しており、これは1行前から1行後ろまで、合計3行の和をとっています。
例えば id 5 の行を見ると、 30+50+30 = 110 になっていますね。

sum_5 は、 BETWEEN 3 PRECEDING AND 1 PRECEDING と指定していて、これは3行前から1行前までの合計3行の和を取るものです。1行目(id:1の行)は集計対象のデータがないのでNULLになってますね。
ここで言いたかったことは、BETWEENで指定するレンジの中にその行自身を含める必要はないということです。
これを使うと、例えば日別の時系列データで、BETWEEN 7 PRECEDING AND 1 PRECEDING とすることで、前日までの過去1週間の集計値(平均など)とその日の値を比べる、といったことも可能になります。

さて、ここまでの説明で、 frameのROWSモードの使い方は概ね網羅できたのではないかと思います。集計の幅がグンと広がると思うのでぜひ使ってみてください。

一方で、 RANGE モードについてはほぼ放置しています。

Presto 以外の エンジンの中には、 実はRANGEモードをよりしっかりサポートしているものもあるそうです。その場合は、ORDER BYで指定した列の値に従って、値の差分が何以下の行を集計対象にするとか、そういう指定ができるそうです。(expression PRECEDING/ expression FOLLOWING がRANGEモードでもサポートされているとか。)

ただ、Prestoではサポートされておらず、トレジャーデータに試しに打ち込んでみても
Window frame RANGE PRECEDING is only supported with UNBOUNDED
などと、エラーが返ってくるだけなので試せていません。
(ではなぜRANGEモードをデフォルトにしているのかとか言いたいことはあるのですが)動かないものは仕方ないので、今回の記事はROWSモード中心に紹介させていただきました。
実際、ROWSモードが動けば大抵の要件には対応可能だと思います。

今回もPrestoのWINDOW関数の話です。例によってトレジャーデータで動作を確認しています。参照するドキュメントは直近の数記事と同じこちら。
参考: Window Functions

All Aggregate Functions can be used as window functions by adding the OVER clause. The aggregate function is computed for each row over the rows within the current row’s window frame.

とドキュメントにある通り、Prestoの全ての集約関数はその後ろにOVER()をつけることで、WINDOW関数として動作させることができます。この場合、GROUP BY は要らないくなるので注意してください。

ちなみに、 Aggregate Functions (集約関数) の一覧はこちらのページにあります。
参考: Aggregate Functions

OVER() の中には他のWINDOW 関数と同じように、PARTITION BY と ORDER BY を指定できます。PARTITION BY を指定する場合はもちろん PARTITION BYで指定した列の値でグループ分けして、そのグループ内で集計が走ります。そしてさらに、 ORDER BY の有無によっても、集計される範囲が変わるので注意が必要です。この辺の概念を正しく理解するには、WINDOW関数の Frame という概念(というかオプション)について説明する必要があるので、別記事で紹介しようと思うのですが、今記事Frameを指定しない場合の挙動で説明します。

ORDER BY を指定しない場合、集計範囲は、PARTITION BYで指定されたグループ内の全行になります。PARTITION BYもORDER BYもどちらもしてしてない場合は単純にSELECTされた前レコードが集計範囲です。

ORDER BY を指定する場合、集計範囲は、PARTITION BYで指定されたグループ内のレコードをORDER BYでソートしたものの、「先頭からその行と同じ順位の行まで」になります。

このように言葉で書いてもわかりにくいと思うので、一番基本的な集約関数であるSUM() を使って、実際にやってみます。

WITH sample_table AS(
    SELECT 
        id,
        category,
        value
    FROM
        UNNEST(
            ARRAY[1, 2, 3, 4, 5, 6],
            ARRAY['A', 'A', 'A', 'A', 'B', 'B'],
            ARRAY[20, 10, 20, 30, 15, 30]
        ) AS t(
            id,
            category,
            value
        )
)
SELECT 
    id,
    category,
    value,
    SUM(value) OVER() AS sum_over_1,
    SUM(value) OVER(PARTITION BY category) AS sum_over_2,
    SUM(value) OVER(PARTITION BY category ORDER BY id) AS sum_over_3,
    SUM(value) OVER(PARTITION BY category ORDER BY value) AS sum_over_4
FROM
    sample_table

-- 以下出力結果
id, category, value, sum_over_1, sum_over_2, sum_over_3
1,  'A',      20,    125,        80,         20 
2,  'A',      10,    125,        80,         30
3,  'A',      20,    125,        80,         50
4,  'A',      30,    125,        80,         80
5,  'B',      15,    125,        45,         15
6,  'B',      30,    125,        45,         45

OVERの中に何も指定していないsum_over_1 の列の値は全行等しく6行のvalue 列の値の合計である125になりましたね。
そして、PARTITON BY を指定した sum_over_2 列の値は、category列の値が ‘A’なのか ‘B’なのかによって変わり、それぞれ該当する行のvalue列の値になっています。

そして、注目するのは ORDER BY も指定した sum_over_3 列です。
これは、 id でソートした後のvalue列の累積和が返されています。

どちらの値にも用途はたくさんありそうですね。目的に応じて使い分けていきましょう。
例えば累積和の方は、日々の売り上げから累積売り上げを算出するといったことができますし、全体の集計値の方はもう少し工夫して、そのグループ内のシェアを計算するなどの用途で使えます。要するにSELECT句に次のように入れたりです。(1.0を最初に掛けるのは型をDOUBLEに変換するため。CASTしても良し)
1.0 * value / SUM(value) OVER(PARTITION BY category)
次のように、グループ内の平均との差分を求めるというのも良いです。
value – AVG(value) OVER(PARTITION BY category)

さて、ORDER BYした時の挙動について、もう一点補足です。
先ほど説明の中で、「先頭からその行と同じ順位の行まで」などというまどろこしい表現を使いました。「先頭からその行まで」ではないんですね。
先ほどの例では ORDER BY で id列を指定し、 id列には重複した値がなかったのであまり気にしなくてよかったのですが、値の重複があり、ソートした順位が同順になる行が複数あると少し気をつける必要があります。 ということで、 id ではなくvalue 列でソートしてみたのが次の結果です。

WITH sample_table AS(
    SELECT 
        id,
        category,
        value
    FROM
        UNNEST(
            ARRAY[1, 2, 3, 4, 5, 6],
            ARRAY['A', 'A', 'A', 'A', 'B', 'B'],
            ARRAY[20, 10, 20, 30, 15, 30]
        ) AS t(
            id,
            category,
            value
        )
)
SELECT 
    id,
    category,
    value,
    SUM(value) OVER(PARTITION BY category ORDER BY value) AS sum_over_4
FROM
    sample_table

-- 以下出力結果
id, category, value, sum_over_4
2,  'A',      10,    10
1,  'A',      20,    50
3,  'A',      20,    50
4,  'A',      30,    80
5,  'B',      15,    15
6,  'B',      30,    30

さて、sum_over_4 列を順番に見ていきましょう。 1行目の結果はいいですね。 valueと同じ10です。そして、4行目の結果は1〜4行目の和で、 10+20+20+30=80 となります。
注目すべきは2行目と3行目です。これがどちらも50になっています。これは、10+20+20の結果です。

3行目が50なのはいいとして、2行目も50なのは少し違和感ありますね。
これが先ほど説明した、「先頭からその行と同じ順位の行まで」を集計するということです。

このような挙動になる理由を正しく理解するには、この記事の最初の方にも書きましたがFrameという概念を理解する必要があります。

別の記事でちゃんと書こうと思うのですが、Frameには行を基準に集計範囲を決めるROWモードと、値を基準に集計範囲を決めるRANGEモードというのがあります。
そして、Frameを指定しなかった場合の挙動は、値を基準とするRANGEモードなのです。

もう少し書くと、Frameを指定しないと、
RANGE UNBOUNDED PRECEDING
と指定するのと同じになり、これが「先頭からその行と同じ順位の行まで」を意味します。
もっと正確にいうと、 UNBOUNDED PRECEDING は、「先頭から」という意味で、「どこまで」は省略されており、「どこまで」を省略した時のデフォルトの挙動が「CURRENT ROW」を指定した場合に等しく、RANGEモードにおけるCURRENT ROWはその行ではなく、「その行と同じ値の行まで」を意味します。ややこしいですね。

とりあえず、Frameについては何も指定しなかった場合にどんな挙動になるのか、をしっかり理解しておくことが重要だと思います。想定と違った動きをしていた場合、SUMの場合は比較的気付きやすいですが平均(AVG)や分散(VAR_SAMP)の場合は見落としがちです。

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の意味はなくなります。

前回の記事の続きです。
参考: Prestoで行番号や順位を振る関数まとめ

Presto(トレジャーデータ)で行番号を振る3つの関数を紹介したのですが、ドキュメントの同じページの同じセクションには、これら3つの関数以外にも複数の関数が紹介されています。
実は僕はこれらを使ったことがなくて、ドキュメントをみて初めて存在を知ったので、この機会に挙動を確認しておこうと思いました。
参考: Window Functions の Ranking Functionsのセクション

一つ目は CUME_DIST() です。
ドキュメントによると、値の累積分布を返す関数だとあります。各行について、その行より前の行か同じ値の行の数をグループ内の行数で割った値を返すそうです。前の行が小さい値を指すのか、大きい値を指すのかは、ORDER BY で指定した昇順(ASC)/降順(DESC)によって決まります。
例えば、値が 10, 20, 20, 40 ならば、 0.25, 0.75, 0.75, 1 を返してくるイメージですね。
一瞬、RANK()を行数で割った値のことか？と勘違いしたのですが、RANK()は1,2,2,4 なので、これを行数(4)で割ると0.25, 0.5, 0.5, 1 なので少し違います。

少し多めに8個の値で実行してみたのが次の例です。
比較用にRANK() を8で割った結果も一緒につけました。
結果をシンプルにするためにこの例では使いませんでしたが、もちろんOVERの中でPARTITION BY を使ってグループごとに算出することもできますよ。

WITH
    sample_table AS (
        SELECT 
            id,
            value
        FROM
            UNNEST(
                ARRAY[1, 2, 3, 4, 5, 6, 7, 8],
                ARRAY[20, 10, 20, 30, 15, 30, 25, 40]
            ) AS t(
                id,
                value
            )
  )
SELECT
    id,
    value,
    CUME_DIST() OVER (ORDER BY value) AS cume_dist_,
    CAST(RANK() OVER (ORDER BY value) AS DOUBLE)/8 AS rank_norm
FROM
    sample_table

-- 以下出力結果
id, value, cume_dist_, rank_norm
2,  10,    0.125,      0.125
5,  15,    0.25,       0.25
1,  20,    0.5,        0.375
3,  20,    0.5,        0.375
7,  25,    0.625,      0.625
4,  30,    0.875,      0.75
6,  30,    0.875,      0.75
8,  40,    1.0,        1.0

CUME_DIST と RANK(を行数で割った値)を比較すると、同じ値になる行もあれば、複数行で同じ値になっている行については結果が異なることも見て取れますね。

次の関数は、NTILE です。これは NTILE(n) OVER(〜) のように、バケット数n を指定して使います。これは、PARTITION BYで区切られた各グループの行たちを、さらにそれぞれn個のバケットに分割して、バケット番号を振るものです。8行のデータに対して、ntile(4)を実行したら、1,1,2,2,3,3,4,4 と番号を振る感じですね。元の行数がバケット数でちょうど割り切れない場合は、最初の方のバケットに優先的に割り当てられます。つまり、7行のデータを4バケットに割ったら、1〜3番目のバケットに2個、4番目のバケットに1個割り当てられて、1,1,2,2,3,3,4 となります。

実際にやってみます。(この例でもPARTITION BY使ってませんが、実際は使えます。)

WITH
    sample_table AS (
        SELECT 
            id,
            value
        FROM
            UNNEST(
                ARRAY[1, 2, 3, 4, 5, 6, 7],
                ARRAY[20, 10, 20, 30, 35, 30, 25]
            ) AS t(
                id,
                value
            )
  )
SELECT
    id,
    value,
    NTILE(4) OVER (ORDER BY value) AS ntile_
FROM
    sample_table

-- 以下出力結果
id, value, ntile_
2,  10,    1
3,  20,    1
1,  20,    2
7,  25,    2
4,  30,    3
6,  30,    3
5,  35,    4

想定通りの結果が出ましたね。今回の例では意図的に、バケットの境目になる2番目と3番目(id 1と3)のvalue を同じ20にしていたのですが、一方がバケット番号1でもう一方は2と振り分けられました。値が同じことよりも各バケットに規定の個数振り分けることが優先されている動きになりました。

この記事で最後に紹介するのは、 PERCENT_RANK() です。
その行のRANK() の結果を $r$、そのグループの行数を$n$とすると、$(r-1)/(n-1)$を返す、というドキュメント通りの説明が一番わかりやすいと思います。
そのままなので、やってみます。(先の2関数と同じように、PERCENT_RANKも本当はPARTITION BYが使えます。) 比較用にRANK() の結果もつけました。

WITH
    sample_table AS (
        SELECT 
            id,
            value
        FROM
            UNNEST(
                ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9],
                ARRAY[20, 10, 20, 30, 35, 30, 25, 15, 40]
            ) AS t(
                id,
                value
            )
  )
SELECT
    id,
    value,
    RANK() OVER (ORDER BY value) AS rank_,
    PERCENT_RANK() OVER (ORDER BY value) AS percent_rank_
FROM
    sample_table

-- 以下出力結果
id, value, rank_, percent_rank_
2,  10,    1,     0.0
8,  15,    2,     0.125
3,  20,    3,     0.25
1,  20,    3,     0.25
7,  25,    5,     0.5
4,  30,    6,     0.625
6,  30,    6,     0.625
5,  35,    8,     0.875
9,  40,    9,     1.0

0〜1 までの値を振ってくれるので個人的には結構好みの結果です。
さて、(そんな利用例は滅多にないと思いますが、)元々抽出されたレコードが1件だたり、PARTITION BY で区切られたグループ内のデータの行数が1件だけだった場合、
$r=1, n=1$ なので、$(r-1)/(n-1)=0/0$となり、$0$除算のエラーにより結果はNULLになるんじゃないか、と思ってたのですが、どうやらこの場合は$0$を返してくるようです。
NULL考慮をしなくていいので楽ですね。(ちなみにこれは、トレジャーデータで試した場合の挙動の話です。もしかしたら他のPresto環境やDBMSの種類によっては違う動きになるかもしれません。)