Zenn
🪟

さやしいウィンドウ関数入門

2024/08/06に公開
SQL
ウィンドウ関数
tech

基本文法

<ウィンドウ関数> OVER (
    [PARTITION BY <パーティション>]
    [ORDER BY <順序>]
    { ROWS | RANGE } [BETWEEN <始点> AND <終点>]
)

どのように処理されるのか

FROM句で指定されたテーブルがOVER句で指定された範囲がウィンドウフレームになる。
そのウィンドウフレーム内のレコードに対して、ウィンドウ関数で演算される。

sql
SELECT 
    id,
    name,
    score,
    SUM(score) OVER (
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING --現在行とその前後１行
    ) as sum_score
FROM 
    student_socres;

言葉だけだと分かりづらいため、下記の図を使って動作を確認していきましょう。

テーブルの最初の行から最後の行までが一行ずつ処理されます。

  1. 行が選択される。
  2. 現在行とその前後の1行がウィンドウフレームとして切り出される。
  3. ウィンドウフレームが合算され、それがsum_scoreの値となる。
  4. 最後の行ならば終了。そうでないなら１に戻る。
動作イメージ
"student_scores"テーブル             1行目のとき(現在、選択されている行)
|----------------------|           |----------------------|
| id |  name   | score |     ┌---->| id |  name   | score |
|----|---------|-------|     |     |----|---------|-------|
| 1  | sato    |  10   |-----┘     | 1  | sato    |  10   |
| 2  | yamada  |  20   |-----┐     | 2  | yamada  |  20   |
| 3  | yoshida |  30   |--┐  |     |----------------------|
|----|---------|-------|  |  |
                          |  |　　　　　　　　　　　２行目のとき
                          |  |     |----------------------|
                          |  └---->| id |  name   | score |
                          |        |----|---------|-------|
                          |        | 1  | sato    |  10   |
                          |        | 1  | yamada  |  20   |
                          |        | 1  | yoshida |  30   |
                          |        |----------------------|
                          |
                          |        ３行目のとき
                          |        |----------------------|
                          └------->| id |  name   | score |
                                   |----|---------|-------|
                                   | 1  | yamada  |  20   |
                                   | 1  | yoshida |  30   |
                                   |----------------------|

結果
|----------------------------------|
| id |  name   | score | sum_score |
|----|---------|-------|-----------|
| 1  | sato    |  10   |    30     |
| 2  | yamada  |  20   |    60     |
| 3  | yoshida |  30   |    50     |
|----------------------|-----------|
*/

ウィンドウフレームの範囲選択

何を基準に、どこからどこまでをウィンドウフレームとするからを指定します。
何も指定しない場合は、FROM句で指定されたテーブルの全てのレコードがウィンドウフレームの範囲になります。

選択範囲の開始点と終了点

下記のアンカーを単体もしくは、BETWEEN　 <開始点> AND <終了点>のように指定する。
BETWEENを指定しない場合、終了点はデフォルトでCURRENT ROWになります。

UNBOUNDED PRECEDING

ウィンドウフレームの最初の行を基準に範囲を指定します。
例: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (最初の行から現在の行の範囲)

n PRECEDING

現在の行よりも前にある n 行目を基準に範囲を指定します。
例: ROWS BETWEEN 3 PRECEDING AND CURRENT ROW (現在の行を含む直前の3行の範囲)

CURRENT ROW

現在の行を基準に範囲を指定します。

n FOLLOWING

現在の行よりも後にある n 行目を基準に範囲を指定します。
例: ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING (現在の行を含む、次の2行を含む範囲)

UNBOUND FOLLOWING

終了点としてのみ使用可能です。ウィンドウフレームの最後の行までの範囲を指定します。
例: ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING (現在の行から最後の行までの範囲)

アンカーの配置早見表

アンカー 開始点として使用可能 終了点として使用可能 単体で使用可能（BETWEEN無し）
UNBOUNDED PRECEDING ✅ ❌ ✅
n PRECEDING ✅ ✅ ✅
CURRENT ROW ✅ ✅ ✅
n FOLLOWING ❌ ✅ ❌
UNBOUNDED FOLLOWING ❌ ✅ ❌

ウィンドウフレームの組み合わせのルール

  • 開始点として使用できるアンカー:

    • UNBOUNDED PRECEDING
    • n PRECEDING
    • CURRENT ROW

  • 終了点として使用できるアンカー:

    • CURRENT ROW
    • n FOLLOWING
    • UNBOUNDED FOLLOWING

特記事項

  • n FOLLOWING と UNBOUNDED FOLLOWING は開始点として使えません。
  • BETWEEN を使わずに単体で使用できるのは、UNBOUNDED PRECEDING、n PRECEDING、および CURRENT ROW のみです。
  • n FOLLOWING および UNBOUNDED FOLLOWING は終了点としてのみ使用可能であり、BETWEEN 句を使わずに単体で指定することはできません。

有効な組み合わせの例

  1. BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  2. BETWEEN n PRECEDING AND CURRENT ROW
  3. BETWEEN CURRENT ROW AND n FOLLOWING
  4. BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  5. BETWEEN n PRECEDING AND n FOLLOWING

範囲選択の基準

ROWSとRANGEは何を基準に範囲選択をするかを指定します。

DBに格納されている順序、若しくは指定された順序でレコードに対して「n行目から現在の行まで」と指定するか。又は指定したカラムの値に対して「10から20まで」と指定するかです。

具体的には、それぞれ次のような違いがあります。

ROWS

行数ベースの範囲指定を行います。
例えば、ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING と指定すると、現在の行を基準に、その前後1行を含む範囲が指定されます。

ROWSの場合、n は非負の整数でなければならず、負の数を指定するとエラーになります。

"student_scores"テーブル　             1行目のとき(現在、選択されている行)
|----------------------|           |----------------------|
| id |  name   | score |     ┌---->| id |  name   | score |
|----|---------|-------|     |     |----|---------|-------|
| 1  | sato    |  10   |-----┘     | 1  | sato    |  10   |
| 2  | yamada  |  20   |-----┐     | 2  | yamada  |  20   |
| 3  | yoshida |  30   |--┐  |     |----------------------|
|----|---------|-------|  |  |
                          |  |　　　　　　　　　　　２行目のとき
                          |  |     |----------------------|
                          |  └---->| id |  name   | score |
                          |        |----|---------|-------|
                          |        | 1  | sato    |  10   |
                          |        | 1  | yamada  |  20   |
                          |        | 1  | yoshida |  30   |
                          |        |----------------------|
                          |
                          |        ３行目のとき
                          |        |----------------------|
                          └------->| id |  name   | score |
                                   |----|---------|-------|
                                   | 1  | yamada  |  20   |
                                   | 1  | yoshida |  30   |
                                   |----------------------|

RANGE

論理的な値に基づく範囲指定を行います。
ORDER BYで指定される値に基づいて範囲を決定します。例えば、ORDER BY score RANGE BETWEEN 0 PRECEDING AND CURRENT ROWのように指定すると、scoreが0から現在の行のscoreの値までの範囲が指定されます。

"student_scores"テーブル　             1行目のとき(現在、選択されている行)
|----------------------|           |----------------------|
| id |  name   | score |     ┌---->| id |  name   | score |
|----|---------|-------|     |     |----|---------|-------|
| 1  | sato    |  10   |-----┘     | 1  | sato    |  10   |
| 2  | yamada  |  20   |-----┐     |----------------------|
| 3  | yoshida |  30   |--┐  |
|----|---------|-------|  |  |
                          |  |　　　　　　　　　　　２行目のとき
                          |  |     |----------------------|
                          |  └---->| id |  name   | score |
                          |        |----|---------|-------|
                          |        | 1  | sato    |  10   |
                          |        | 1  | yamada  |  20   |
                          |        |----------------------|
                          |
                          |
                          |        ３行目のとき
                          |        |----------------------|
                          └------->| id |  name   | score |
                                   |----|---------|-------|
                                   | 1  | sato    |  10   |
                                   | 1  | yamada  |  20   |
                                   | 1  | yoshida |  30   |
                                   |----------------------|

ウィンドウフレーム内のグループピング

PARTITION BYで指定されたカラムの値に基づいてデータを論理的に分割することができます。これにより、各パーティションごとにウィンドウ関数が適用されます。

SELECT 
    id,
    name,
    score,
    SUM(score) OVER (PARTITION BY class) AS sum_scores
FROM 
    student_scores;

"student_scores"テーブル　                 1行目のとき(現在、選択されている行)
|------------------------------|       |------------------------------|
| id |  name   | score | class |   ┌-->| id |  name   | score | class | 
|----|---------|-------|-------|   |   |----|---------|-------|-------|
| 1  | sato    |  10   |   A   |---┘   | 1  | sato    |  10   |   A   |
| 2  | yamada  |  20   |   A   |---┐   | 2  | yamada  |  20   |   A   |
| 3  | yoshida |  30   |   B   |-┐ |   |------------------------------|
|------------------------------| | |   2行目のとき
                                 | |   |------------------------------|
                                 | └-->| id |  name   | score | class |
                                 |     |----|---------|-------|-------|
                                 |     | 1  | sato    |  10   |   A   |
                                 |     | 2  | yamada  |  20   |   A   |
                                 |     |------------------------------|
                                 |     3行目のとき
                                 |     |------------------------------|
                                 └ --->| id |  name   | score | class |
                                       |----|---------|-------|-------|
                                       | 3  | yoshida |  30   |   B   |
                                       |------------------------------|

結果
|----------------------------------|
| id |  name   | score | sum_score |
|----|---------|-------|-----------|
| 1  | sato    |  10   |    30     |
| 2  | yamada  |  20   |    30     |
| 3  | yoshida |  30   |    30     |
|----------------------------------|

ウィンドウフレーム内の行順

詳しい説明は不要かと思いますが、ウィンドウフレームのレコードを並び替えます。
ORDER BY column_name { ASC | DESC }

ウィンドウ関数

  • SUM(column_name)
    ウィンドウフレーム内のレコードの指定のカラムの値の合計を計算します。

  • AVG(column_name)
    ウィンドウフレーム内のレコードの指定のカラムの値平均を計算します。

  • MIN(column_name)
    ウィンドウフレーム内のレコードの指定のカラムの値の最小値を返します。

  • MAX(column_name)
    ウィンドウフレーム内のレコードの指定のカラムの値の最大値を返します。

  • COUNT(column_name or *)
    ウィンドウフレーム内の レコード数を返します。
    *で指定するとNULLを含む行も数え上げます。

  • LAG(column_name, n, default_value)
    指定されたウィンドウフレーム内で、現在の行からn行前の行の値を取得します。
    参照する行が存在しない場合にdefault_valueを返す。

  • LEAD(column_name, n, default_value)
    指定されたウィンドウフレーム内で、現在の行からn行後の行の値を取得します。
    参照する行が存在しない場合にdefault_valueを返す。

  • ROW_NUMBER()
    ウィンドウフレーム内の各行に対して連番を割り当てます。ORDER BY 句が必須です。

  • RANK()
    ウィンドウフレーム内の各行に対してランクを割り当てます。同じ値には同じランクが付き、次のランクはその後に続きます。ORDER BY 句が必須です。

  • DENSE_RANK()
    ウィンドウフレーム内の各行に対してランクを割り当てます。同じ値には同じランクが付き、次のランクはその次に続きます。ORDER BY 句が必須です。

  • NTILE(n)
    指定されたウィンドウフレーム内で、行を n 個のバケットに分割し、そのバケット番号を返します。

  • FIRST_VALUE(column_name)
    指定されたウィンドウフレーム内の最初の値を返します。

  • LAST_VALUE(column_name)
    指定されたウィンドウフレーム内の最後の値を返します。

Discussion