[SQLのはじめ方] Part23: ウィンドウ関数（OVER, RANK, ROW_NUMBERなど）

ウィンドウ関数を使う上で最も重要なのが `OVER` 句です。この句を使って、関数がどの範囲の行（ウィンドウ）を対象にするかを指定します。`OVER` 句の中では、主に以下の2つの要素を指定します。

• PARTITION BY <列名>: データを特定の列の値に基づいてグループ（パーティション）に分割します。`GROUP BY` と似ていますが、行を集約しません。省略した場合、テーブル全体が1つのパーティションとみなされます。
• ORDER BY <列名> [ASC|DESC]: 各パーティション内での行の順序を定義します。`RANK` や `ROW_NUMBER` などの順位付け関数や、累計計算などで必要になります。

基本的な構文は以下のようになります。

SELECT
  列1,
  列2,
  ウィンドウ関数名(引数) OVER (
    PARTITION BY 列名1  -- どのグループで区切るか (省略可能)
    ORDER BY 列名2 [ASC|DESC] -- グループ内でどう並べるか (関数によって必須/任意)
  ) AS 新しい列名
FROM
  テーブル名;
        

例えば、「部署ごとに」「給与の高い順に」ランキングを付けたい、といった場合に `PARTITION BY 部署名 ORDER BY 給与 DESC` のように指定します。

`ROW_NUMBER()` は、ウィンドウ内の各行に対して、重複のない一意の連番を振る関数です。`ORDER BY` で指定された順序に従って、1から始まる連続した整数を割り当てます。

例: 社員テーブル（employees）の給与（salary）が高い順に連番を振る

SELECT
  employee_name,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM
  employees;
        

この結果、`row_num` 列には給与が最も高い社員から順に1, 2, 3… という連番が表示されます。

`RANK()` は、ウィンドウ内の各行に対して順位を付ける関数です。`ORDER BY` で指定された順序に基づきます。同じ値を持つ行には同じ順位が与えられ、その次の順位は飛ばされます（例: 1位, 2位, 2位, 4位）。

例: 社員テーブル（employees）の給与（salary）が高い順に順位を付ける

SELECT
  employee_name,
  salary,
  RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM
  employees;
        

もし同額の給与の社員が複数いる場合、彼らには同じ `salary_rank` が付き、その次の順位は欠番になります。

`DENSE_RANK()` も `RANK()` と同様に順位を付けますが、同じ値を持つ行に同じ順位を与えた後、次の順位を飛ばしません（例: 1位, 2位, 2位, 3位）。

例: 社員テーブル（employees）の給与（salary）が高い順に、欠番なしで順位を付ける

SELECT
  employee_name,
  salary,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_salary_rank
FROM
  employees;
        

`RANK()` との違いは、同順位があった場合でも、次の順位が連続することです。

3つの順位付け関数の違いを理解するために、簡単な例で比較してみましょう。

• `ROW_NUMBER()`: 常にユニークな連番
• `RANK()`: 同順位は同じ番号、次の番号は飛ぶ
• `DENSE_RANK()`: 同順位は同じ番号、次の番号は飛ばない

`ROW_NUMBER`, `RANK`, `DENSE_RANK` 以外にも、様々なウィンドウ関数があります。

• 集計関数 (`SUM`, `AVG`, `COUNT`, `MAX`, `MIN`): `OVER`句と組み合わせることで、パーティションごとの累計や移動平均などを計算できます。
• `LAG()`: パーティション内で、現在の行より前の行の値を取得します。
• `LEAD()`: パーティション内で、現在の行より後の行の値を取得します。
• `FIRST_VALUE()` / `LAST_VALUE()`: パーティション内の最初/最後の値を取得します。
• `NTILE(n)`: パーティションを `n` 個のグループに分割し、各行がどのグループに属するかを示す番号を返します。

これらの関数も `OVER (PARTITION BY … ORDER BY …)` と組み合わせて使います。

ウィンドウ関数は、集計関数のように行をまとめることなく、各行に対して関連する行セット（ウィンドウ）に基づいた計算を行える強力な機能です。

• `OVER`句でウィンドウ（対象範囲と順序）を定義します (`PARTITION BY`, `ORDER BY`)。
• `ROW_NUMBER()` は一意の連番を振ります。
• `RANK()` は同順位を許容し、順位を飛ばします。
• `DENSE_RANK()` は同順位を許容し、順位を飛ばしません。
• 集計関数や `LAG`/`LEAD` などもウィンドウ関数として利用できます。

最初は少し複雑に感じるかもしれませんが、使いこなせるとSQLでのデータ分析の幅が格段に広がります。ぜひ色々なデータで試してみてくださいね！