MochiuWiki : SUSE, EC, PCB

MySQL - ウインドウ関数

📢 Webサイト閉鎖と移転のお知らせ
このWebサイトは2026年9月に閉鎖いたします。
新しい記事は移転先で追加しております。（旧サイトでは記事を追加しておりません）

概要

MySQLのウインドウ関数は、MySQL 8.0で導入された高度なSQL分析機能である。
ウインドウ関数を使用することで、GROUP BY句を用いずに集計やランキング、行間の値参照を実行しながら、全ての行を結果セットに保持できる。

ウインドウ関数の主要な特徴は以下の通りである。

ウインドウ関数の主要な特徴
特徴 説明
パーティション分割 PARTITION BY句により、結果セットを論理的なグループに分割する
順序付け ORDER BY句により、パーティション内の行の順序を制御する
フレーム仕様 計算対象となる行の範囲を定義する
全ての行を保持 GROUP BY句とは異なり、元の行数を変更しない


ウインドウ関数は、ランキング、累積計算、前後の値参照、移動平均等の複雑な分析タスクを簡潔に記述できる。
MySQL 8.0以降で使用可能である。

ウインドウ関数は、ランキング関数、値参照関数、集約ウインドウ関数の3つのカテゴリに分類される。

また、WHERE 句、GROUP BY 句、HAVING 句では使用できないため、サブクエリまたはCTEでウインドウ関数を包み、外側のクエリでフィルタリングを行う。


基本構文

OVER句の構文

ウインドウ関数は、OVER 句を伴って使用する。
OVER 句は、ウインドウの定義を指定する。

基本構文を以下に示す。

 window_function_name(expression) OVER (
    [PARTITION BY partition_expression [, ...]]
    [ORDER BY order_expression [ASC|DESC] [, ...]]
    [frame_specification]
 )


下表に、各要素の説明を示す。

ウインドウ関数の構成要素
要素 説明
window_function_name ウインドウ関数の名前 (ROW_NUMBER、RANK、SUM等)
expression 関数への引数 (列名、式等)
PARTITION BY
  • 結果セットを論理的なグループに分割する。
  • 省略した場合、全ての行が1つのパーティションとして扱われる。
ORDER BY
  • パーティション内の行の順序を定義する。
  • ランキング関数では必須、集約関数では累積計算に影響
frame_specification 計算対象となる行の範囲を定義する (ROWS、RANGE、GROUPS)


基本的な使用例を以下に示す。

以下の例では、department ごとに行をパーティション分割し、salary の降順で順位を付けている。

 SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
 FROM employees;


OVER 句を空にすることも可能である。
以下の例では、全行の平均給与を各行に表示している。

 SELECT
    employee_id,
    salary,
    AVG(salary) OVER () AS overall_avg
 FROM employees;


名前付きウインドウ (WINDOW句)

同じウインドウ定義を複数のウインドウ関数で使用する場合、WINDOW句で名前付きウインドウを定義できる。
WINDOW 句は、HAVING 句 と ORDER BY 句の間に配置する。

構文を以下に示す。

 SELECT ...
 FROM table_name
 [WHERE ...]
 [GROUP BY ...]
 [HAVING ...]
 WINDOW window_name AS (window_spec) [, window_name AS (window_spec)] ...
 [ORDER BY ...]
 [LIMIT ...]


使用例を以下に示す。

以下の例では、w という名前付きウインドウを定義し、3つのランキング関数で再利用している。

 SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER w AS row_num,
    RANK() OVER w AS rank_num,
    DENSE_RANK() OVER w AS dense_rank_num
 FROM employees
 WINDOW w AS (PARTITION BY department ORDER BY salary DESC);


名前付きウインドウは、基本ウインドウを拡張することも可能である。
以下の例では、w2 は w1 のパーティション定義を継承し、ORDER BY 句を追加している。

 SELECT
    employee_id,
    department,
    salary,
    RANK() OVER w1 AS rank_all,
    RANK() OVER w2 AS rank_desc
 FROM employees
 WINDOW
    w1 AS (PARTITION BY department),
    w2 AS (w1 ORDER BY salary DESC);


循環参照は許可されない。

 -- エラー : 循環参照
 WINDOW
    w1 AS (w2 ORDER BY salary),
    w2 AS (w1 PARTITION BY department);



ランキング関数

ROW_NUMBER()

ROW_NUMBER() 関数は、パーティション内の各行に一意の連続した整数を割り当てる。
戻り値の型は、BIGINT である。

同値の行が存在する場合でも、異なる番号が付与される。
番号の順序は、ORDER BY 句で指定した順序に従う。

構文を以下に示す。

※注意
ORDER BY 句は必須である。

 ROW_NUMBER() OVER (
    [PARTITION BY partition_expression]
    ORDER BY order_expression [ASC|DESC]
 )


使用例を以下に示す。

以下の例では、全従業員を給与の降順で順位付けしている。

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


パーティション分割を使用した例を以下に示す。

以下の例では、部署ごとに給与の降順で順位付けしている。
各部署の順位は1から開始する。

 SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
 FROM employees;


ROW_NUMBER() 関数は、トップN問題の解決に頻繁に使用される。

以下の例では、各部署の給与上位3名を取得する。

 WITH ranked AS (
    SELECT
       employee_id,
       department,
       salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
 )
 SELECT employee_id, department, salary
 FROM ranked
 WHERE rn <= 3;


RANK() と DENSE_RANK()

RANK() 関数 と DENSE_RANK() 関数は、行にランキングを付与する。
両関数とも、戻り値の型は BIGINT である。

RANK() 関数 と DENSE_RANK() 関数の違いは、同値行の処理方法である。

  • RANK()
    同値行には同じ順位を付与し、次の順位にはギャップが生じる (1, 1, 3, 4)
  • DENSE_RANK()
    同値行には同じ順位を付与し、次の順位は連続する (1, 1, 2, 3)


構文を以下に示す。

 RANK() OVER (
    [PARTITION BY partition_expression]
    ORDER BY order_expression [ASC|DESC]
 )
 
 DENSE_RANK() OVER (
    [PARTITION BY partition_expression]
    ORDER BY order_expression [ASC|DESC]
 )


使用例を以下に示す。

 SELECT
    employee_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
 FROM employees;


実行結果の例を以下に示す。

employee_id | salary | rank_num | dense_rank_num
------------+--------+----------+----------------
101         | 90000  | 1        | 1
102         | 90000  | 1        | 1
103         | 85000  | 3        | 2
104         | 80000  | 4        | 3


RANK()では、同値行の後の順位が3となり、ギャップが生じている。
DENSE_RANK()では、順位が2となり、連続している。

パーティション分割を使用した例を以下に示す。
以下の例では、部署ごとに給与のランキングを付与している。

 SELECT
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
 FROM employees;


NTILE()

NTILE(n) 関数は、パーティション内の行をN個のバケットに分割し、各行にバケット番号 (1からN) を割り当てる。
戻り値の型は、BIGINT である。

行数がNで均等に割り切れない場合、余りの行は先頭のバケットから順に1行ずつ追加される。

構文を以下に示す。
nは、正の整数定数である。

 NTILE(n) OVER (
    [PARTITION BY partition_expression]
    ORDER BY order_expression [ASC|DESC]
 )


使用例を以下に示す。
以下の例では、従業員を給与に基づいて4つの四分位に分割している。

 SELECT
    employee_id,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
 FROM employees;


実行結果の例を以下に示す。(10行の場合)

employee_id | salary | quartile
------------+--------+----------
101         | 95000  | 1
102         | 90000  | 1
103         | 88000  | 1
104         | 85000  | 2
105         | 80000  | 2
106         | 75000  | 2
107         | 70000  | 3
108         | 65000  | 3
109         | 60000  | 4
110         | 55000  | 4


10行を4つのバケットに分割すると、バケット1は3行、バケット2と3は各2行、バケット4は2行となる。

NTILE() 関数は、パーセンタイル分析やランキングのグループ化に使用される。

PERCENT_RANK() と CUME_DIST()

PERCENT_RANK() 関数 と CUME_DIST() 関数は、パーティション内の行の相対的な順位を返す。
戻り値の型は、DOUBLE である。

分布関数
関数 説明
PERCENT_RANK()
  • (rank - 1) / (total_rows - 1) で計算される
  • 値の範囲は 0 から 1
  • パーティション内の最初の行は常に 0
CUME_DIST()
  • 累積分布値を返す
  • 値の範囲は 0 より大きく 1 以下
  • パーティション内の最後の行は常に 1


構文を以下に示す。

 PERCENT_RANK() OVER (
    [PARTITION BY partition_expression]
    ORDER BY order_expression [ASC|DESC]
 )
 
 CUME_DIST() OVER (
    [PARTITION BY partition_expression]
    ORDER BY order_expression [ASC|DESC]
 )


使用例を以下に示す。

 SELECT
    employee_id,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank,
    CUME_DIST() OVER (ORDER BY salary) AS cume_dist
 FROM employees;


実行結果の例を以下に示す。(5行の場合)

employee_id | salary | pct_rank | cume_dist
------------+--------+----------+-----------
101         | 50000  | 0.00     | 0.20
102         | 60000  | 0.25     | 0.40
103         | 70000  | 0.50     | 0.60
104         | 80000  | 0.75     | 0.80
105         | 90000  | 1.00     | 1.00


PERCENT_RANK() 関数は、最初の行が0、最後の行が1となる。
CUME_DIST() 関数は、最初の行が0.20 (1/5)、最後の行が1.00となる。


値参照関数

LAG() と LEAD()

LAG() 関数 と LEAD() 関数は、パーティション内の前後の行の値を参照する。

  • LAG()
    現在の行より前の行の値を取得
  • LEAD()
    現在の行より後の行の値を取得


構文を以下に示す。

 LAG(expr [, offset [, default]]) OVER (
    [PARTITION BY partition_expression]
    ORDER BY order_expression [ASC|DESC]
 )
 
 LEAD(expr [, offset [, default]]) OVER (
    [PARTITION BY partition_expression]
    ORDER BY order_expression [ASC|DESC]
 )


パラメータの説明を以下に示す。

LAG/LEADパラメータ
パラメータ デフォルト値 説明
expr - 取得する列または式
offset 1 前後の行数オフセット (正の整数)
default NULL 参照先の行が存在しない場合の戻り値


基本的な使用例を以下に示す。

 SELECT
    year,
    revenue,
    LAG(revenue) OVER (ORDER BY year) AS prev_year_revenue,
    LEAD(revenue) OVER (ORDER BY year) AS next_year_revenue
 FROM annual_sales;


実行結果の例を以下に示す。

year | revenue | prev_year_revenue | next_year_revenue
-----+---------+-------------------+-------------------
2021 | 100000  | NULL              | 120000
2022 | 120000  | 100000            | 150000
2023 | 150000  | 120000            | NULL


最初の行のLAG()と最後の行のLEAD()はNULLを返す。

  • デフォルト値を指定した例
    以下の例では、前年の売上がない場合に0を返している。
     SELECT
    year,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY year) AS prev_year_revenue
 FROM annual_sales;

  • 前期比較と差分計算の例
    以下の例では、前年からの差分と成長率を計算している。
     SELECT
    year,
    revenue,
    revenue - LAG(revenue) OVER (ORDER BY year) AS diff,
    ROUND((revenue / LAG(revenue) OVER (ORDER BY year) - 1) * 100, 2) AS growth_rate
 FROM annual_sales;

  • パーティション分割を使用した例
    以下の例では、製品ごとに前年の売上を取得している。
     SELECT
    product_id,
    year,
    sales,
    LAG(sales) OVER (PARTITION BY product_id ORDER BY year) AS prev_year_sales
 FROM product_sales;


FIRST_VALUE() と LAST_VALUE()

FIRST_VALUE() 関数 と LAST_VALUE() 関数は、フレーム内の最初または最後の行の値を返す。

構文を以下に示す。

 FIRST_VALUE(expr) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC|DESC]]
    [frame_specification]
 )
 
 LAST_VALUE(expr) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC|DESC]]
    [frame_specification]
 )


使用例を以下に示す。

 SELECT
    employee_id,
    department,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_salary,
    LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS lowest_salary
 FROM employees;


※注意
LAST_VALUE() 関数のデフォルトフレームは、RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW である。
このため、LAST_VALUE() 関数は現在の行までの最後の値 (すなわち現在の行の値) を返す。

パーティション全体の最後の値を取得するには、フレーム仕様を明示的に指定する必要がある。
以下の例では、ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING を指定することにより、パーティション全体を対象としている。

 SELECT
    employee_id,
    department,
    salary,
    LAST_VALUE(salary) OVER (
       PARTITION BY department
       ORDER BY salary DESC
       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_salary
 FROM employees;


NTH_VALUE()

NTH_VALUE() 関数は、フレーム内のN番目の行の値を返す。
N番目の行が存在しない場合、NULLを返す。

構文を以下に示す。

 NTH_VALUE(expr, n) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC|DESC]]
    [frame_specification]
 )


nは正の整数定数である (1から開始)。

使用例を以下に示す。
以下の例では、各部署の2番目に高い給与を取得している。

 SELECT
    employee_id,
    department,
    salary,
    NTH_VALUE(salary, 2) OVER (
       PARTITION BY department
       ORDER BY salary DESC
       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest_salary
 FROM employees;


NTH_VALUE() 関数 も LAST_VALUE() 関数と同様に、デフォルトフレームの影響を受ける。
フレーム全体を対象とする場合は、ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING を指定する。


集約ウインドウ関数

SUM() / AVG() / COUNT() OVER

通常の集約関数 SUM()、AVG()、COUNT() を OVER 句と組み合わせることで、ウインドウ関数として使用できる。

構文を以下に示す。

 SUM(expr) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC|DESC]]
    [frame_specification]
 )
 
 AVG(expr) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC|DESC]]
    [frame_specification]
 )
 
 COUNT(expr) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC|DESC]]
    [frame_specification]
 )


全体の合計を計算する例を以下に示す。
以下の例では、全従業員の合計給与と平均給与を各行に表示している。

 SELECT
    employee_id,
    department,
    salary,
    SUM(salary) OVER () AS total_salary,
    AVG(salary) OVER () AS avg_salary
 FROM employees;


パーティション分割を使用した例を以下に示す。
以下の例では、部署ごとの合計給与、平均給与、従業員数を各行に表示している。

 SELECT
    employee_id,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total_salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
    COUNT(*) OVER (PARTITION BY department) AS dept_count
 FROM employees;


累積合計を計算する例を以下に示す。
以下の例では、注文日順に累積金額を計算している。

 SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS cumulative_amount
 FROM orders;


ORDER BY 句を指定した場合、デフォルトフレームは RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW である。
これにより、パーティションの最初から現在の行までの累積計算が行われる。

移動平均を計算する例を以下に示す。
以下の例では、現在の行を含む過去7日間の移動平均を計算している。

 SELECT
    order_date,
    amount,
    AVG(amount) OVER (
       ORDER BY order_date
       ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7days
 FROM orders;


比率計算の例を以下に示す。
以下の例では、部署内の給与総額に対する各従業員の給与の比率を計算している。

 SELECT
    employee_id,
    department,
    salary,
    ROUND(salary / SUM(salary) OVER (PARTITION BY department) * 100, 2) AS pct_of_dept_total
 FROM employees;


MAX() / MIN() OVER

MAX() 関数 と MIN() 関数も OVER 句と組み合わせてウインドウ関数として使用できる。

構文を以下に示す。

 MAX(expr) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC|DESC]]
    [frame_specification]
 )
 
 MIN(expr) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC|DESC]]
    [frame_specification]
 )


使用例を以下に示す。
以下の例では、部署ごとの最高給与と最低給与を各行に表示している。

 SELECT
    employee_id,
    department,
    salary,
    MAX(salary) OVER (PARTITION BY department) AS dept_max_salary,
    MIN(salary) OVER (PARTITION BY department) AS dept_min_salary
 FROM employees;


累積最大値を計算する例を以下に示す。
以下の例では、注文日順に累積最大金額を計算している。

 SELECT
    order_date,
    amount,
    MAX(amount) OVER (ORDER BY order_date) AS max_amount_so_far
 FROM orders;


範囲内の最大・最小値を計算する例を以下に示す。
以下の例では、前後3日間の範囲内の最大金額を計算している。

 SELECT
    order_date,
    amount,
    MAX(amount) OVER (
       ORDER BY order_date
       ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ) AS max_in_range
 FROM orders;



フレーム仕様

ROWS / RANGE / GROUPS

フレーム仕様は、ウインドウ関数が処理する行の範囲を定義する。
フレームタイプには、ROWS、RANGE、GROUPS の3種類がある。

基本構文を以下に示す。

 {ROWS | RANGE | GROUPS} BETWEEN frame_start AND frame_end


各フレームタイプの説明を以下に示す。

ROWS/RANGE/GROUPSの比較
フレームタイプ 説明 用途
ROWS 物理的な行位置でフレームを定義 固定行数のウインドウ (移動平均、移動合計等)
RANGE 値の範囲でフレームを定義
同値行 (ピアーズ) は同じCURRENT ROW		 値ベースのウインドウ (累積計算で同値行を同時に処理)
GROUPS ピアーズグループ単位でフレームを定義
MySQL 8.0では構文のみサポート (実行時エラー)		 将来バージョンで実装予定


ROWS と RANGE の違いの例を以下に示す。

 -- テストデータ
 -- value: 10, 20, 20, 30, 40
 
 -- ROWSの場合
 SELECT
    value,
    SUM(value) OVER (ORDER BY value ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows
 FROM test_data;
 
 -- value=20 (1行目)の場合: 10 + 20 + 20 = 50
 -- value=20 (2行目)の場合: 20 + 20 + 30 = 70
 
 -- RANGEの場合
 SELECT
    value,
    SUM(value) OVER (ORDER BY value RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_range
 FROM test_data;
 
 -- value=20 (両行)の場合: 10 + 20 + 20 + 30 = 80
 -- 同値行は同じCURRENT ROWとして扱われる


ROWS は、物理的な行数でカウントするため、同値行でも異なる結果となる。
RANGE は、値の範囲でカウントするため、同値行は同じ結果となる。

GROUPS は、MySQL 8.0では構文解析されるが、実行時にエラーとなる。

 -- エラー: GROUPSは現在サポートされていない
 SELECT
    value,
    SUM(value) OVER (ORDER BY value GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
 FROM test_data;


フレーム境界

フレーム境界は、フレームの開始位置と終了位置を定義する。

下表に、使用可能なフレーム境界を示す。

フレーム境界一覧
フレーム境界 説明
UNBOUNDED PRECEDING パーティションの最初の行
<n> PRECEDING 現在の行のn行前 (ROWSの場合)
現在の行の値 - n (RANGEの場合)
CURRENT ROW 現在の行
<n> FOLLOWING 現在の行のn行後 (ROWSの場合)
現在の行の値 + n (RANGEの場合)
UNBOUNDED FOLLOWING パーティションの最後の行


フレーム境界の組み合わせ例を以下に示す。

 -- パーティション全体
 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 
 -- パーティションの先頭から現在の行まで (累積計算)
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 
 -- 現在の行から最後まで
 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 
 -- 前3行から現在の行まで
 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
 
 -- 前3行から後3行まで (7行ウインドウ)
 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
 
 -- 現在の行から後2行まで
 ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING


フレーム境界の制約を以下に示す。

  • frame_start は frame_end より前でなければならない
  • UNBOUNDED FOLLOWING は frame_start に使用できない
  • UNBOUNDED PRECEDING は frame_end に使用できない


簡略構文も使用可能である。

 -- 以下は同等
 ROWS UNBOUNDED PRECEDING
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 
 -- 以下は同等
 ROWS CURRENT ROW
 ROWS BETWEEN CURRENT ROW AND CURRENT ROW
 
 -- 以下は同等
 ROWS 3 PRECEDING
 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW


デフォルトフレーム

フレーム仕様を省略した場合、デフォルトフレームが使用される。
デフォルトフレームは、ORDER BY 句の有無によって異なる。

デフォルトフレームの動作を以下に示す。

  • ORDER BY句あり
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    パーティションの先頭から現在の行 (と同値行) まで
    累積計算に適している
  • ORDER BY句なし
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    パーティション全体
    全体集計に適している


デフォルトフレームの例を以下に示す。

 -- ORDER BY句あり: 累積合計
 SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS cumulative_amount
 FROM orders;
 -- デフォルトフレーム: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 
 -- ORDER BY句なし: 全体合計
 SELECT
    employee_id,
    salary,
    SUM(salary) OVER () AS total_salary
 FROM employees;
 -- デフォルトフレーム: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING


フレーム仕様が有効な関数と無効な関数を以下に示す。

  • フレーム仕様が有効
    FIRST_VALUE()、LAST_VALUE()、NTH_VALUE()、
    SUM()、AVG()、COUNT()、MAX()、MIN()
  • フレーム仕様を無視 (常にパーティション全体を使用)
    RANK()、DENSE_RANK()、ROW_NUMBER()、
    NTILE()、LAG()、LEAD()、CUME_DIST()、PERCENT_RANK()



サンプルクエリ

トップN問題

トップN問題は、各グループの上位N件を取得する問題である。
ウインドウ関数を使用することにより、簡潔に記述できる。

各部署の給与上位3名を取得する例を以下に示す。

 WITH ranked AS (
    SELECT
       employee_id,
       department,
       salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
 )
 SELECT employee_id, department, salary
 FROM ranked
 WHERE rn <= 3;


ROW_NUMBER()、RANK()、DENSE_RANK() の選択基準を以下に示す。

  • ROW_NUMBER()
    同値行でも異なる順位を付与
    厳密にN件を取得する場合に使用
  • RANK()
    同値行には同じ順位を付与
    同値行がある場合、N件を超える可能性がある
    同順位を許容する場合に使用
  • DENSE_RANK()
    同値行には同じ順位を付与、順位にギャップなし
    同順位を許容し、順位の連続性を保つ場合に使用


  • RANK() 関数を使用した例
    以下の例では、同順位が存在する場合、3位以内の全ての従業員を取得している。
     WITH ranked AS (
    SELECT
       employee_id,
       department,
       salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num
    FROM employees
 )
 SELECT employee_id, department, salary, rank_num
 FROM ranked
 WHERE rank_num <= 3;

  • サブクエリを使用した例 
     SELECT *
 FROM (
    SELECT
       employee_id,
       department,
       salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
 ) AS ranked
 WHERE rn <= 3;


CTEとサブクエリの選択は、可読性の好みによる。

累積計算

累積計算は、時系列データの累積合計、累積平均等を計算する処理である。

累積売上を計算する例を以下に示す。

 SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS cumulative_sales,
    AVG(amount) OVER (ORDER BY order_date) AS cumulative_avg
 FROM orders;


ORDER BY句を指定した場合、デフォルトフレームはRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWとなる。
これにより、累積計算が自動的に行われる。

  • 月次累積売上を計算する例
    以下の例では、年ごとに累積売上 (Year To Date) を計算している。
     SELECT
    year,
    month,
    monthly_sales,
    SUM(monthly_sales) OVER (PARTITION BY year ORDER BY month) AS ytd_sales
 FROM monthly_summary;

  • 累積比率を計算する例
    以下の例では、全体の売上に対する累積売上の比率を計算している。
     SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) /
       SUM(amount) OVER () * 100 AS cumulative_pct
 FROM orders;

  • 累積カウントを計算する例
    以下の例では、注文の累積件数を計算している。
     SELECT
    order_date,
    customer_id,
    COUNT(*) OVER (ORDER BY order_date) AS cumulative_order_count
 FROM orders;


前期比較と差分計算

LAG() 関数を使用することにより、前期との比較や差分計算を簡潔に記述できる。

前年比較の例を以下に示す。

 SELECT
    year,
    revenue,
    LAG(revenue) OVER (ORDER BY year) AS prev_year_revenue,
    revenue - LAG(revenue) OVER (ORDER BY year) AS diff,
    ROUND((revenue / LAG(revenue) OVER (ORDER BY year) - 1) * 100, 2) AS growth_rate
 FROM annual_sales;


実行結果の例を以下に示す。

year | revenue | prev_year_revenue | diff   | growth_rate
-----+---------+-------------------+--------+-------------
2021 | 100000  | NULL              | NULL   | NULL
2022 | 120000  | 100000            | 20000  | 20.00
2023 | 150000  | 120000            | 30000  | 25.00


  • 製品ごとの前月比較の例
    以下の例では、製品ごとに前月の売上と差分を計算している。
     SELECT
    product_id,
    year,
    month,
    sales,
    LAG(sales) OVER (PARTITION BY product_id ORDER BY year, month) AS prev_month_sales,
    sales - LAG(sales) OVER (PARTITION BY product_id ORDER BY year, month) AS mom_diff
 FROM monthly_product_sales;

  • 前週比較の例
    以下の例では、1週前と4週前の売上を取得している。
     SELECT
    week_start_date,
    sales,
    LAG(sales, 1) OVER (ORDER BY week_start_date) AS prev_week_sales,
    LAG(sales, 4) OVER (ORDER BY week_start_date) AS four_weeks_ago_sales
 FROM weekly_sales;

  • ギャップ検出の例
    以下の例では、前回のイベントからの日数を計算している。
     SELECT
    event_date,
    DATEDIFF(event_date, LAG(event_date) OVER (ORDER BY event_date)) AS days_since_last_event
 FROM events;

  • 値の変化を検出する例
    以下の例では、ステータスの変化を検出している。
     SELECT
    timestamp,
    status,
    LAG(status) OVER (ORDER BY timestamp) AS prev_status,
    CASE
       WHEN status != LAG(status) OVER (ORDER BY timestamp) THEN 1
       ELSE 0
    END AS status_changed
 FROM status_log;



パフォーマンス

ウインドウ関数のパフォーマンスは、クエリの複雑さとデータ量に依存する。

下表に、主要なパフォーマンス要因を示す。

主要なパフォーマンス要因
要因 説明
ORDER BY列へのインデックス
  • ソートコストを削減
  • ORDER BY句で使用する列にインデックスを作成することを推奨
PARTITION BY + ORDER BY の複合インデックス
  • パーティション分割とソートの両方を最適化
  • (partition_column, order_column)の複合インデックスが有効
同じウインドウ定義の再利用
  • 同じORDER BY句を持つ複数のウインドウ関数は、ソートが1回のみ実行される
  • 名前付きウインドウ (WINDOW句) の使用を推奨
サブクエリのマテリアライズ
  • ウインドウ関数を含むサブクエリは常にマテリアライズされる
  • 派生テーブルマージは無効化される


  • インデックスの例 
     -- 単一列のインデックス
 CREATE INDEX idx_order_date ON orders(order_date);
 
 -- 複合インデックス
 CREATE INDEX idx_dept_salary ON employees(department, salary DESC);

  • 名前付きウインドウによるパフォーマンス改善例 
     -- 非効率: 同じウインドウ定義を繰り返す
 SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num,
    AVG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS avg_sal
 FROM employees;
 
 -- 効率的: 名前付きウインドウを使用
 SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER w AS rn,
    RANK() OVER w AS rank_num,
    AVG(salary) OVER w AS avg_sal
 FROM employees
 WINDOW w AS (PARTITION BY department ORDER BY salary DESC);


  • EXPLAINによる実行計画の確認
    実行計画に windowing 要素が表示される。
     EXPLAIN FORMAT=JSON
 SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
 FROM employees;


パフォーマンスチューニングにおいて、推奨される事項を以下に示す。

  • 必要な列のみを選択
    SELECT * を避ける
  • 適切なインデックスの作成
    PARTITION BY と ORDER BY の列にインデックスを作成
  • フィルタリングの最適化
    ウインドウ関数の前にフィルタリングを実行 (WHERE 句を使用)
  • 名前付きウインドウの使用
    同じウインドウ定義の再利用によりソートコストを削減
  • サブクエリの最小化
    必要最小限のサブクエリを使用


システム変数 windowing_use_high_precision により、精度の制御が可能である。

 -- デフォルト: ON (高精度モード)
 SET windowing_use_high_precision = ON;
 
 -- 低精度モード (パフォーマンス優先)
 SET windowing_use_high_precision = OFF;


高精度モードでは、DOUBLE 型の代わりに DECIMAL 型を使用する。
精度が重要な場合は ON、パフォーマンスが重要な場合は OFF を設定する。


関連ページ



「http://mochiuwiki.e2.valueserver.jp/index.php?title=MySQL_-_ウインドウ関数&oldid=14282」から取得