MySQL - ウインドウ関数のソースを表示

== 概要 ==
MySQLのウインドウ関数は、MySQL 8.0で導入された高度なSQL分析機能である。<br>
ウインドウ関数を使用することで、<code>GROUP BY</code>句を用いずに集計やランキング、行間の値参照を実行しながら、全ての行を結果セットに保持できる。<br>
<br>
ウインドウ関数の主要な特徴は以下の通りである。<br>
<br>
<center>
{| class="wikitable"
|+ ウインドウ関数の主要な特徴
|-
! 特徴 !! 説明
|-
| パーティション分割 || <code>PARTITION BY</code>句により、結果セットを論理的なグループに分割する
|-
| 順序付け || <code>ORDER BY</code>句により、パーティション内の行の順序を制御する
|-
| フレーム仕様 || 計算対象となる行の範囲を定義する
|-
| 全ての行を保持 || <code>GROUP BY</code>句とは異なり、元の行数を変更しない
|}
</center>
<br>
ウインドウ関数は、ランキング、累積計算、前後の値参照、移動平均等の複雑な分析タスクを簡潔に記述できる。<br>
<u>MySQL 8.0以降で使用可能である。</u><br>
<br>
ウインドウ関数は、<u>ランキング関数</u>、<u>値参照関数</u>、<u>集約ウインドウ関数</u>の3つのカテゴリに分類される。<br>
<br>
また、<code>WHERE</code> 句、<code>GROUP BY</code> 句、<code>HAVING</code> 句では使用できないため、サブクエリまたはCTEでウインドウ関数を包み、外側のクエリでフィルタリングを行う。<br>
<br><br>

== 基本構文 ==
==== OVER句の構文 ====
ウインドウ関数は、<code>OVER</code> 句を伴って使用する。<br>
<code>OVER</code> 句は、ウインドウの定義を指定する。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 window_function_name(expression) OVER (
    [PARTITION BY partition_expression [, ...]]
    [ORDER BY order_expression [ASC|DESC] [, ...]]
    [frame_specification]
 )
 </syntaxhighlight>
<br>
下表に、各要素の説明を示す。<br>
<br>
<center>
{| class="wikitable"
|+ ウインドウ関数の構成要素
|-
! 要素 !! 説明
|-
| <code>window_function_name</code> || ウインドウ関数の名前 (ROW_NUMBER、RANK、SUM等)
|-
| <code>expression</code> || 関数への引数 (列名、式等)
|-
| <code>PARTITION BY</code>
|
* 結果セットを論理的なグループに分割する。
* 省略した場合、全ての行が1つのパーティションとして扱われる。
|-
| <code>ORDER BY</code>
|
* パーティション内の行の順序を定義する。
* ランキング関数では必須、集約関数では累積計算に影響
|-
| <code>frame_specification</code> || 計算対象となる行の範囲を定義する (ROWS、RANGE、GROUPS)
|}
</center>
<br>
基本的な使用例を以下に示す。<br>
<br>
以下の例では、<u>department</u> ごとに行をパーティション分割し、<u>salary</u> の降順で順位を付けている。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
 FROM employees;
 </syntaxhighlight>
<br>
<code>OVER</code> 句を空にすることも可能である。<br>
以下の例では、全行の平均給与を各行に表示している。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT
    employee_id,
    salary,
    AVG(salary) OVER () AS overall_avg
 FROM employees;
 </syntaxhighlight>
<br>
==== 名前付きウインドウ (WINDOW句) ====
同じウインドウ定義を複数のウインドウ関数で使用する場合、<code>WINDOW</code>句で名前付きウインドウを定義できる。<br>
<code>WINDOW</code> 句は、<code>HAVING</code> 句 と <code>ORDER BY</code> 句の間に配置する。<br>
<br>
構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT ...
 FROM table_name
 [WHERE ...]
 [GROUP BY ...]
 [HAVING ...]
 WINDOW window_name AS (window_spec) [, window_name AS (window_spec)] ...
 [ORDER BY ...]
 [LIMIT ...]
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
以下の例では、<u>w</u> という名前付きウインドウを定義し、3つのランキング関数で再利用している。<br>
<br>
 <syntaxhighlight lang="mysql">
 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);
 </syntaxhighlight>
<br>
名前付きウインドウは、基本ウインドウを拡張することも可能である。<br>
以下の例では、<u>w2</u> は <u>w1</u> のパーティション定義を継承し、<code>ORDER BY</code> 句を追加している。<br>
<br>
 <syntaxhighlight lang="mysql">
 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);
 </syntaxhighlight>
<br>
循環参照は許可されない。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- エラー : 循環参照
 WINDOW
    w1 AS (w2 ORDER BY salary),
    w2 AS (w1 PARTITION BY department);
 </syntaxhighlight>
<br><br>

== ランキング関数 ==
==== ROW_NUMBER() ====
<code>ROW_NUMBER()</code> 関数は、パーティション内の各行に一意の連続した整数を割り当てる。<br>
戻り値の型は、<code>BIGINT</code> である。<br>
<br>
同値の行が存在する場合でも、異なる番号が付与される。<br>
番号の順序は、<code>ORDER BY</code> 句で指定した順序に従う。<br>
<br>
構文を以下に示す。<br>
<br>
<u>※注意</u><br>
<u><code>ORDER BY</code> 句は必須である。</u><br>
<br>
 <syntaxhighlight lang="mysql">
 ROW_NUMBER() OVER (
    [PARTITION BY partition_expression]
    ORDER BY order_expression [ASC|DESC]
 )
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
以下の例では、全従業員を給与の降順で順位付けしている。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
 FROM employees;
 </syntaxhighlight>
<br>
パーティション分割を使用した例を以下に示す。<br>
<br>
以下の例では、部署ごとに給与の降順で順位付けしている。<br>
各部署の順位は1から開始する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
 FROM employees;
 </syntaxhighlight>
<br>
<code>ROW_NUMBER()</code> 関数は、トップN問題の解決に頻繁に使用される。<br>
<br>
以下の例では、各部署の給与上位3名を取得する。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
==== RANK() と DENSE_RANK() ====
<code>RANK()</code> 関数 と <code>DENSE_RANK()</code> 関数は、行にランキングを付与する。<br>
両関数とも、戻り値の型は <code>BIGINT</code> である。<br>
<br>
<code>RANK()</code> 関数 と <code>DENSE_RANK()</code> 関数の違いは、同値行の処理方法である。<br>
* RANK()
*: 同値行には同じ順位を付与し、次の順位にはギャップが生じる (1, 1, 3, 4)
* DENSE_RANK()
*: 同値行には同じ順位を付与し、次の順位は連続する (1, 1, 2, 3)
<br>
構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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]
 )
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
実行結果の例を以下に示す。<br>
 employee_id | salary | rank_num | dense_rank_num
 ------------+--------+----------+----------------
 101         | 90000  | 1        | 1
 102         | 90000  | 1        | 1
 103         | 85000  | 3        | 2
 104         | 80000  | 4        | 3
<br>
<code>RANK()</code>では、同値行の後の順位が3となり、ギャップが生じている。<br>
<code>DENSE_RANK()</code>では、順位が2となり、連続している。<br>
<br>
パーティション分割を使用した例を以下に示す。<br>
以下の例では、部署ごとに給与のランキングを付与している。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
 FROM employees;
 </syntaxhighlight>
<br>
==== NTILE() ====
<code>NTILE(n)</code> 関数は、パーティション内の行をN個のバケットに分割し、各行にバケット番号 (1からN) を割り当てる。<br>
戻り値の型は、<code>BIGINT</code> である。<br>
<br>
行数がNで均等に割り切れない場合、余りの行は先頭のバケットから順に1行ずつ追加される。<br>
<br>
構文を以下に示す。<br>
nは、正の整数定数である。<br>
<br>
 <syntaxhighlight lang="mysql">
 NTILE(n) OVER (
    [PARTITION BY partition_expression]
    ORDER BY order_expression [ASC|DESC]
 )
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
以下の例では、従業員を給与に基づいて4つの四分位に分割している。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT
    employee_id,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
 FROM employees;
 </syntaxhighlight>
<br>
実行結果の例を以下に示す。(10行の場合)<br>
 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
<br>
10行を4つのバケットに分割すると、バケット1は3行、バケット2と3は各2行、バケット4は2行となる。<br>
<br>
<code>NTILE()</code> 関数は、パーセンタイル分析やランキングのグループ化に使用される。<br>
<br>
==== PERCENT_RANK() と CUME_DIST() ====
<code>PERCENT_RANK()</code> 関数 と <code>CUME_DIST()</code> 関数は、パーティション内の行の相対的な順位を返す。<br>
戻り値の型は、<code>DOUBLE</code> である。<br>
<br>
<center>
{| class="wikitable"
|+ 分布関数
|-
! 関数 !! 説明
|-
| <code>PERCENT_RANK()</code>
|
* (rank - 1) / (total_rows - 1) で計算される
* 値の範囲は 0 から 1
* パーティション内の最初の行は常に 0
|-
| <code>CUME_DIST()</code>
|
* 累積分布値を返す
* 値の範囲は 0 より大きく 1 以下
* パーティション内の最後の行は常に 1
|}
</center>
<br>
構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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]
 )
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT
    employee_id,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank,
    CUME_DIST() OVER (ORDER BY salary) AS cume_dist
 FROM employees;
 </syntaxhighlight>
<br>
実行結果の例を以下に示す。(5行の場合)<br>
 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
<br>
<code>PERCENT_RANK()</code> 関数は、最初の行が0、最後の行が1となる。<br>
<code>CUME_DIST()</code> 関数は、最初の行が0.20 (1/5)、最後の行が1.00となる。<br>
<br><br>

== 値参照関数 ==
==== LAG() と LEAD() ====
<code>LAG()</code> 関数 と <code>LEAD()</code> 関数は、パーティション内の前後の行の値を参照する。<br>
<br>
* LAG()
*: 現在の行より前の行の値を取得
* LEAD()
*: 現在の行より後の行の値を取得
<br>
構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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]
 )
 </syntaxhighlight>
<br>
パラメータの説明を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ LAG/LEADパラメータ
! パラメータ !! デフォルト値 !! 説明
|-
| expr || - || 取得する列または式
|-
| offset || 1 || 前後の行数オフセット (正の整数)
|-
| default || NULL || 参照先の行が存在しない場合の戻り値
|}
</center>
<br>
基本的な使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
実行結果の例を以下に示す。<br>
 year | revenue | prev_year_revenue | next_year_revenue
 -----+---------+-------------------+-------------------
 2021 | 100000  | NULL              | 120000
 2022 | 120000  | 100000            | 150000
 2023 | 150000  | 120000            | NULL
<br>
最初の行の<code>LAG()</code>と最後の行の<code>LEAD()</code>は<code>NULL</code>を返す。<br>
<br>
* デフォルト値を指定した例
*: 以下の例では、前年の売上がない場合に0を返している。
*: <syntaxhighlight lang="mysql">
 SELECT
    year,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY year) AS prev_year_revenue
 FROM annual_sales;
 </syntaxhighlight>
*: <br>
* 前期比較と差分計算の例
*: 以下の例では、前年からの差分と成長率を計算している。
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
*: <br>
* パーティション分割を使用した例
*: 以下の例では、製品ごとに前年の売上を取得している。
*: <syntaxhighlight lang="mysql">
 SELECT
    product_id,
    year,
    sales,
    LAG(sales) OVER (PARTITION BY product_id ORDER BY year) AS prev_year_sales
 FROM product_sales;
 </syntaxhighlight>
<br>
==== FIRST_VALUE() と LAST_VALUE() ====
<code>FIRST_VALUE()</code> 関数 と <code>LAST_VALUE()</code> 関数は、フレーム内の最初または最後の行の値を返す。<br>
<br>
構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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]
 )
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u><code>LAST_VALUE()</code> 関数のデフォルトフレームは、<code>RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</code> である。</u><br>
<u>このため、<code>LAST_VALUE()</code> 関数は現在の行までの最後の値 (すなわち現在の行の値) を返す。</u><br>
<br>
パーティション全体の最後の値を取得するには、フレーム仕様を明示的に指定する必要がある。<br>
以下の例では、<code>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</code> を指定することにより、パーティション全体を対象としている。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
==== NTH_VALUE() ====
<code>NTH_VALUE()</code> 関数は、フレーム内のN番目の行の値を返す。<br>
N番目の行が存在しない場合、<code>NULL</code>を返す。<br>
<br>
構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 NTH_VALUE(expr, n) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC|DESC]]
    [frame_specification]
 )
 </syntaxhighlight>
<br>
nは正の整数定数である (1から開始)。<br>
<br>
使用例を以下に示す。<br>
以下の例では、各部署の2番目に高い給与を取得している。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
<code>NTH_VALUE()</code> 関数 も <code>LAST_VALUE()</code> 関数と同様に、デフォルトフレームの影響を受ける。<br>
フレーム全体を対象とする場合は、<code>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</code> を指定する。<br>
<br><br>

== 集約ウインドウ関数 ==
==== SUM() / AVG() / COUNT() OVER ====
通常の集約関数 <code>SUM()</code>、<code>AVG()</code>、<code>COUNT()</code> を <code>OVER</code> 句と組み合わせることで、ウインドウ関数として使用できる。<br>
<br>
構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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]
 )
 </syntaxhighlight>
<br>
全体の合計を計算する例を以下に示す。<br>
以下の例では、全従業員の合計給与と平均給与を各行に表示している。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT
    employee_id,
    department,
    salary,
    SUM(salary) OVER () AS total_salary,
    AVG(salary) OVER () AS avg_salary
 FROM employees;
 </syntaxhighlight>
<br>
パーティション分割を使用した例を以下に示す。<br>
以下の例では、部署ごとの合計給与、平均給与、従業員数を各行に表示している。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
累積合計を計算する例を以下に示す。<br>
以下の例では、注文日順に累積金額を計算している。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS cumulative_amount
 FROM orders;
 </syntaxhighlight>
<br>
<code>ORDER BY</code> 句を指定した場合、デフォルトフレームは <code>RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</code> である。<br>
これにより、パーティションの最初から現在の行までの累積計算が行われる。<br>
<br>
移動平均を計算する例を以下に示す。<br>
以下の例では、現在の行を含む過去7日間の移動平均を計算している。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT
    order_date,
    amount,
    AVG(amount) OVER (
       ORDER BY order_date
       ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7days
 FROM orders;
 </syntaxhighlight>
<br>
比率計算の例を以下に示す。<br>
以下の例では、部署内の給与総額に対する各従業員の給与の比率を計算している。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT
    employee_id,
    department,
    salary,
    ROUND(salary / SUM(salary) OVER (PARTITION BY department) * 100, 2) AS pct_of_dept_total
 FROM employees;
 </syntaxhighlight>
<br>
==== MAX() / MIN() OVER ====
<code>MAX()</code> 関数 と <code>MIN()</code> 関数も <code>OVER</code> 句と組み合わせてウインドウ関数として使用できる。<br>
<br>
構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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]
 )
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
以下の例では、部署ごとの最高給与と最低給与を各行に表示している。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
累積最大値を計算する例を以下に示す。<br>
以下の例では、注文日順に累積最大金額を計算している。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT
    order_date,
    amount,
    MAX(amount) OVER (ORDER BY order_date) AS max_amount_so_far
 FROM orders;
 </syntaxhighlight>
<br>
範囲内の最大・最小値を計算する例を以下に示す。<br>
以下の例では、前後3日間の範囲内の最大金額を計算している。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT
    order_date,
    amount,
    MAX(amount) OVER (
       ORDER BY order_date
       ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ) AS max_in_range
 FROM orders;
 </syntaxhighlight>
<br><br>

== フレーム仕様 ==
==== ROWS / RANGE / GROUPS ====
フレーム仕様は、ウインドウ関数が処理する行の範囲を定義する。<br>
フレームタイプには、<code>ROWS</code>、<code>RANGE</code>、<code>GROUPS</code> の3種類がある。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 {ROWS | RANGE | GROUPS} BETWEEN frame_start AND frame_end
 </syntaxhighlight>
<br>
各フレームタイプの説明を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ ROWS/RANGE/GROUPSの比較
! フレームタイプ !! 説明 !! 用途
|-
| ROWS || 物理的な行位置でフレームを定義 || 固定行数のウインドウ (移動平均、移動合計等)
|-
| RANGE || 値の範囲でフレームを定義<br>同値行 (ピアーズ) は同じCURRENT ROW || 値ベースのウインドウ (累積計算で同値行を同時に処理)
|-
| GROUPS || ピアーズグループ単位でフレームを定義<br>MySQL 8.0では構文のみサポート (実行時エラー) || 将来バージョンで実装予定
|}
</center>
<br>
<code>ROWS</code> と <code>RANGE</code> の違いの例を以下に示す。<br>
 <syntaxhighlight lang="mysql">
 -- テストデータ
 -- 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として扱われる
 </syntaxhighlight>
<br>
<code>ROWS</code> は、物理的な行数でカウントするため、同値行でも異なる結果となる。<br>
<code>RANGE</code> は、値の範囲でカウントするため、同値行は同じ結果となる。<br>
<br>
<code>GROUPS</code> は、MySQL 8.0では構文解析されるが、実行時にエラーとなる。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- エラー: GROUPSは現在サポートされていない
 SELECT
    value,
    SUM(value) OVER (ORDER BY value GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
 FROM test_data;
 </syntaxhighlight>
<br>
==== フレーム境界 ====
フレーム境界は、フレームの開始位置と終了位置を定義する。<br>
<br>
下表に、使用可能なフレーム境界を示す。<br>
<br>
<center>
{| class="wikitable"
|+ フレーム境界一覧
! フレーム境界 !! 説明
|-
| UNBOUNDED PRECEDING || パーティションの最初の行
|-
| <n> PRECEDING || 現在の行のn行前 (ROWSの場合)<br>現在の行の値 - n (RANGEの場合)
|-
| CURRENT ROW || 現在の行
|-
| <n> FOLLOWING || 現在の行のn行後 (ROWSの場合)<br>現在の行の値 + n (RANGEの場合)
|-
| UNBOUNDED FOLLOWING || パーティションの最後の行
|}
</center>
<br>
フレーム境界の組み合わせ例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- パーティション全体
 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
 </syntaxhighlight>
<br>
フレーム境界の制約を以下に示す。<br>
* frame_start は frame_end より前でなければならない
* <code>UNBOUNDED FOLLOWING</code> は frame_start に使用できない
* <code>UNBOUNDED PRECEDING</code> は frame_end に使用できない
<br>
簡略構文も使用可能である。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 以下は同等
 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
 </syntaxhighlight>
<br>
==== デフォルトフレーム ====
フレーム仕様を省略した場合、デフォルトフレームが使用される。<br>
デフォルトフレームは、<code>ORDER BY</code> 句の有無によって異なる。<br>
<br>
デフォルトフレームの動作を以下に示す。<br>
* ORDER BY句あり
*: <code>RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</code>
*: パーティションの先頭から現在の行 (と同値行) まで
*: 累積計算に適している
* ORDER BY句なし
*: <code>RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</code>
*: パーティション全体
*: 全体集計に適している
<br>
デフォルトフレームの例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 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
 </syntaxhighlight>
<br>
フレーム仕様が有効な関数と無効な関数を以下に示す。<br>
* フレーム仕様が有効
*: <code>FIRST_VALUE()</code>、<code>LAST_VALUE()</code>、<code>NTH_VALUE()</code>、
*: <code>SUM()</code>、<code>AVG()</code>、<code>COUNT()</code>、<code>MAX()</code>、<code>MIN()</code>
* フレーム仕様を無視 (常にパーティション全体を使用)
*: <code>RANK()</code>、<code>DENSE_RANK()</code>、<code>ROW_NUMBER()</code>、
*: <code>NTILE()</code>、<code>LAG()</code>、<code>LEAD()</code>、<code>CUME_DIST()</code>、<code>PERCENT_RANK()</code>
<br><br>

== サンプルクエリ ==
==== トップN問題 ====
トップN問題は、各グループの上位N件を取得する問題である。<br>
ウインドウ関数を使用することにより、簡潔に記述できる。<br>
<br>
各部署の給与上位3名を取得する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
<code>ROW_NUMBER()</code>、<code>RANK()</code>、<code>DENSE_RANK()</code> の選択基準を以下に示す。<br>
* ROW_NUMBER()
*: 同値行でも異なる順位を付与
*: 厳密にN件を取得する場合に使用
* RANK()
*: 同値行には同じ順位を付与
*: 同値行がある場合、N件を超える可能性がある
*: 同順位を許容する場合に使用
* DENSE_RANK()
*: 同値行には同じ順位を付与、順位にギャップなし
*: 同順位を許容し、順位の連続性を保つ場合に使用
<br>
* <code>RANK()</code> 関数を使用した例
*: 以下の例では、同順位が存在する場合、3位以内の全ての従業員を取得している。
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
*: <br>
* サブクエリを使用した例
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
CTEとサブクエリの選択は、可読性の好みによる。<br>
<br>
==== 累積計算 ====
累積計算は、時系列データの累積合計、累積平均等を計算する処理である。<br>
<br>
累積売上を計算する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
<code>ORDER BY</code>句を指定した場合、デフォルトフレームは<code>RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</code>となる。<br>
これにより、累積計算が自動的に行われる。<br>
<br>
* 月次累積売上を計算する例
*: 以下の例では、年ごとに累積売上 (Year To Date) を計算している。
*: <syntaxhighlight lang="mysql">
 SELECT
    year,
    month,
    monthly_sales,
    SUM(monthly_sales) OVER (PARTITION BY year ORDER BY month) AS ytd_sales
 FROM monthly_summary;
 </syntaxhighlight>
*: <br>
* 累積比率を計算する例
*: 以下の例では、全体の売上に対する累積売上の比率を計算している。
*: <syntaxhighlight lang="mysql">
 SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) /
       SUM(amount) OVER () * 100 AS cumulative_pct
 FROM orders;
 </syntaxhighlight>
*: <br>
* 累積カウントを計算する例
*: 以下の例では、注文の累積件数を計算している。
*: <syntaxhighlight lang="mysql">
 SELECT
    order_date,
    customer_id,
    COUNT(*) OVER (ORDER BY order_date) AS cumulative_order_count
 FROM orders;
 </syntaxhighlight>
<br>
==== 前期比較と差分計算 ====
<code>LAG()</code> 関数を使用することにより、前期との比較や差分計算を簡潔に記述できる。<br>
<br>
前年比較の例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
実行結果の例を以下に示す。<br>
 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
<br>
* 製品ごとの前月比較の例
*: 以下の例では、製品ごとに前月の売上と差分を計算している。
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
*: <br>
* 前週比較の例
*: 以下の例では、1週前と4週前の売上を取得している。
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
*: <br>
* ギャップ検出の例
*: 以下の例では、前回のイベントからの日数を計算している。
*: <syntaxhighlight lang="mysql">
 SELECT
    event_date,
    DATEDIFF(event_date, LAG(event_date) OVER (ORDER BY event_date)) AS days_since_last_event
 FROM events;
 </syntaxhighlight>
*: <br>
* 値の変化を検出する例
*: 以下の例では、ステータスの変化を検出している。
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br><br>

== パフォーマンス ==
ウインドウ関数のパフォーマンスは、クエリの複雑さとデータ量に依存する。<br>
<br>
下表に、主要なパフォーマンス要因を示す。<br>
<br>
<center>
{| class="wikitable"
|+ 主要なパフォーマンス要因
|-
! 要因 !! 説明
|-
| ORDER BY列へのインデックス
|
* ソートコストを削減
* <code>ORDER BY</code>句で使用する列にインデックスを作成することを推奨
|-
| PARTITION BY + ORDER BY の複合インデックス
|
* パーティション分割とソートの両方を最適化
* <code>(partition_column, order_column)</code>の複合インデックスが有効
|-
| 同じウインドウ定義の再利用
|
* 同じ<code>ORDER BY</code>句を持つ複数のウインドウ関数は、ソートが1回のみ実行される
* 名前付きウインドウ (<code>WINDOW</code>句) の使用を推奨
|-
| サブクエリのマテリアライズ
|
* ウインドウ関数を含むサブクエリは常にマテリアライズされる
* 派生テーブルマージは無効化される
|}
</center>
<br>
* インデックスの例
*: <syntaxhighlight lang="mysql">
 -- 単一列のインデックス
 CREATE INDEX idx_order_date ON orders(order_date);
 
 -- 複合インデックス
 CREATE INDEX idx_dept_salary ON employees(department, salary DESC);
 </syntaxhighlight>
*: <br>
* 名前付きウインドウによるパフォーマンス改善例
*: <syntaxhighlight lang="mysql">
 -- 非効率: 同じウインドウ定義を繰り返す
 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);
 </syntaxhighlight>
<br>
* <code>EXPLAIN</code>による実行計画の確認
*: 実行計画に <code>windowing</code> 要素が表示される。
*: <syntaxhighlight lang="mysql">
 EXPLAIN FORMAT=JSON
 SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
 FROM employees;
 </syntaxhighlight>
<br>
パフォーマンスチューニングにおいて、推奨される事項を以下に示す。<br>
* 必要な列のみを選択
*: <code>SELECT *</code> を避ける
* 適切なインデックスの作成
*: <code>PARTITION BY</code> と <code>ORDER BY</code> の列にインデックスを作成
* フィルタリングの最適化
*: ウインドウ関数の前にフィルタリングを実行 (<code>WHERE</code> 句を使用)
* 名前付きウインドウの使用
*: 同じウインドウ定義の再利用によりソートコストを削減
* サブクエリの最小化
*: 必要最小限のサブクエリを使用
<br>
システム変数 <code>windowing_use_high_precision</code> により、精度の制御が可能である。<br>
 <syntaxhighlight lang="mysql">
 -- デフォルト: ON (高精度モード)
 SET windowing_use_high_precision = ON;
 
 -- 低精度モード (パフォーマンス優先)
 SET windowing_use_high_precision = OFF;
 </syntaxhighlight>
<br>
高精度モードでは、<code>DOUBLE</code> 型の代わりに <code>DECIMAL</code> 型を使用する。<br>
精度が重要な場合は <code>ON</code>、パフォーマンスが重要な場合は <code>OFF</code> を設定する。<br>
<br><br>

== 関連ページ ==
* [[MySQL - 副問い合わせ]]
*: ウインドウ関数の代替手法としての副問い合わせ
* [[MySQL - CTE]]
*: ウインドウ関数と組み合わせた複雑なクエリの構築
* [[MySQL - GROUP BY]]
*: GROUP BYとウインドウ関数の違いと使い分け
* [[MySQL - ORDER BY]]
*: ORDER BY句とウインドウ関数のソート処理
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,MySQL,SQL,ウインドウ関数,Window Function,OVER,PARTITION BY,ROW_NUMBER,RANK,LAG,LEAD,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - MySQLのウインドウ関数の完全ガイド | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]