Posted inMySQL

Window Functions MySQL 8：ROW_NUMBER、RANK、LAG、LEADと実践Tips

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

5分で動かす — まず結果を見てから理解する

社員のランキングに3〜4層のサブクエリを書き続けていますか？あるいは今月と先月の売上を比較するためにself-joinを使っていますか？MySQL 8のWindow Functionsはどちらも一つのクエリで解決します — サブクエリも追加のjoinも不要。ランキング、隣接行との比較、累積合計：すべてSELECTの中に直接書けます。

テストテーブルを作成してすぐ試してみましょう：

CREATE TABLE sales (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_name VARCHAR(50),
  department VARCHAR(50),
  sale_amount DECIMAL(10,2),
  sale_date DATE
);

INSERT INTO sales (employee_name, department, sale_amount, sale_date) VALUES
('Minh', 'Tech', 15000000, '2024-01-15'),
('Lan',  'Tech', 22000000, '2024-01-16'),
('Hùng', 'Sales', 18000000, '2024-01-15'),
('Thảo', 'Sales', 18000000, '2024-01-17'),
('Nam',  'Tech', 22000000, '2024-01-18'),
('Hoa',  'Sales', 25000000, '2024-01-19'),
('Khoa', 'Tech', 12000000, '2024-01-20');
-- 部門ごとに社員をランキング — 3つの関数を並べて比較
SELECT
  employee_name,
  department,
  sale_amount,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY sale_amount DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS dense_rnk
FROM sales;

実行してみると、LanとNamは同じ22百万でも3つの関数が全く異なる結果を返すことに気づくでしょう。以下で各ケースを説明します。

詳細解説：各関数の動作

ROW_NUMBER() — 絶対的な連番、重複なし

2行が同じ値を持っていても、ROW_NUMBERは異なる番号を割り当てます。タイとなった2行の順序は不定で、エンジンが独自に決定するため予測できません。ORDER BYにタイブレーカー列を追加して（例：sale_amount DESCの後にid）、実行ごとに一貫した結果を得ましょう。

-- 各部門の最高売上社員を正確に1人取得（2行返ることはない）
SELECT * FROM (
  SELECT
    employee_name, department, sale_amount,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS rn
  FROM sales
) t
WHERE rn = 1;

RANK() — 同順位、次の番号をスキップ

LanとNamは同じ22百万 → 同じrank 1。次のrankは2を飛ばして3になります。実際のランキングでは理にかなっています：同じ実力の2人がいれば、どちらも2位と呼ぶ必要はありません。

-- 各部門のTop 2 — 同順位1位がいる場合は3行返ることもある
SELECT * FROM (
  SELECT
    employee_name, department, sale_amount,
    RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS rnk
  FROM sales
) t
WHERE rnk <= 2;

DENSE_RANK() — 同順位、連続した番号でスキップなし

基本的にRANKと同じですが、番号がスキップされません。同順位1 → 次は2、3ではありません。グループレポートに便利です：top-1グループ、top-2グループ、top-3グループ — 順位の意味がより明確になります。

-- 違いを明確に比較
SELECT
  employee_name,
  sale_amount,
  RANK()       OVER (ORDER BY sale_amount DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY sale_amount DESC) AS dense_val
FROM sales;
-- Hoa: 25百万 → rank 1 / dense 1
-- Lan, Nam: 22百万 → rank 2 / dense 2  
-- Hùng, Thảo: 18百万 → rank 4 / dense 3  ← これが違いのポイント

LAG() と LEAD() — 隣接行との比較

個人的に月次レポートを作成するときにこの2つの関数を最もよく使います。LAG()は前の行から値を取得し、LEAD()は次の行を参照します — 同じpartitionと並び順で。

-- 各社員の前回取引と比較した売上変化率（%）を計算
SELECT
  employee_name,
  sale_date,
  sale_amount,
  LAG(sale_amount)  OVER (PARTITION BY employee_name ORDER BY sale_date) AS prev_sale,
  LEAD(sale_amount) OVER (PARTITION BY employee_name ORDER BY sale_date) AS next_sale,
  ROUND(
    (sale_amount - LAG(sale_amount) OVER (PARTITION BY employee_name ORDER BY sale_date))
    / LAG(sale_amount) OVER (PARTITION BY employee_name ORDER BY sale_date) * 100, 2
  ) AS pct_change
FROM sales;

LAG/LEADは最大3つのパラメータを受け取ります：LAG(column, offset, default)。offsetのデフォルトは1、前後の行がない場合はNULLを返します：

-- 2行前の値を取得、存在しない場合は0を返す
LAG(sale_amount, 2, 0) OVER (PARTITION BY department ORDER BY sale_date)

応用編 — 実践的な組み合わせテクニック

SUM() OVERとframe clauseによるRunning Total

ランキング関数は氷山の一角です。SUM、AVG、MAX、MINもすべてOVERで使えます — そしてframe clauseこそがこの機能が本当に役立つ場面です：

SELECT
  employee_name,
  department,
  sale_date,
  sale_amount,
  -- 部門内の日別累積合計
  SUM(sale_amount) OVER (
    PARTITION BY department
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  -- 部門全体の平均（ORDER BYに依存しない）
  AVG(sale_amount) OVER (PARTITION BY department) AS dept_avg
FROM sales
ORDER BY department, sale_date;

Named Window — 一度書いて何度でも使う

複数の関数が同じpartitionとorderを使う場合、4回コピーペーストする代わりに、MySQL 8ではwindowに名前を付けて再利用できます。partitionのロジックを変更するときも一箇所だけ修正すればOK：

SELECT
  employee_name,
  department,
  sale_amount,
  ROW_NUMBER() OVER w AS row_num,
  RANK()       OVER w AS rnk,
  DENSE_RANK() OVER w AS dense_rnk,
  LAG(sale_amount) OVER w AS prev_amount
FROM sales
WINDOW w AS (PARTITION BY department ORDER BY sale_amount DESC);
-- (PARTITION BY department ORDER BY sale_amount DESC)を4回繰り返す代わりに

CTEとの組み合わせで複雑な問題を解く

CTEとWindow Functionsは非常に自然に組み合わせられます：CTEが1回のスキャンでrankとdept_avgを計算し、外側のクエリは結果でフィルタリングするだけ — ネストされたサブクエリは一切不要：

-- 部門平均より売上が高い社員を、部門内ランクと共に取得
WITH dept_ranked AS (
  SELECT
    employee_name,
    department,
    sale_amount,
    AVG(sale_amount) OVER (PARTITION BY department) AS dept_avg,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS dept_rank
  FROM sales
)
SELECT
  employee_name,
  department,
  sale_amount,
  ROUND(dept_avg, 0) AS dept_avg,
  dept_rank
FROM dept_ranked
WHERE sale_amount > dept_avg
ORDER BY department, dept_rank;

データシーケンスのギャップを検出

小さなテクニックですが役立ちます：LEAD()はself-joinなしでシーケンス内の欠損IDを検出するのにも使えます：

-- シーケンス内の欠損IDを検索
SELECT
  id,
  LEAD(id) OVER (ORDER BY id) AS next_id,
  LEAD(id) OVER (ORDER BY id) - id - 1 AS gap_size
FROM sales
HAVING gap_size > 0;

経験からの実践的なTips

1. デプロイ前にEXPLAIN ANALYZEを実行

私が運用している本番データベースはMySQL 8.0で、約50GBのデータがあります。800万行の取引テーブルでwindow functionのクエリが15秒タイムアウトしたことがありました — composite indexを追加したら0.3秒になりました。なぜ遅いのか？Window Functionsは計算前にpartition全体をRAMにバッファリングする必要があります。デプロイ前に必ず確認しましょう：

EXPLAIN ANALYZE
SELECT
  employee_name,
  RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS rnk
FROM sales;
-- 注意："Using temporary"と"Using filesort"の出力に気をつける
-- tmpディスク使用量が大きい場合 → インデックスまたは事前集計が必要

2. 適切なインデックス — partitionカラムを先に、orderカラムを後に

-- PARTITION BY department ORDER BY sale_amountの場合
CREATE INDEX idx_dept_sale ON sales (department, sale_amount DESC);

-- SELECTが少数のカラムのみ必要な場合はCovering index
CREATE INDEX idx_dept_sale_cover ON sales (department, sale_amount DESC, employee_name);

3. ROWSとRANGEの違いを理解する

frame clauseを使い始めたときに最もよく混同されるポイント：

-- ROWS: 物理的なN行を正確にカウント
SUM(sale_amount) OVER (
  ORDER BY sale_date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
-- → 直近の3行（物理的）を合計、値に関係なく

-- RANGE: 値の範囲でカウント、同一値はまとめて計算
SUM(sale_amount) OVER (
  ORDER BY sale_date
  RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
)
-- → 直近7日間のすべての行を合計

ロジックが「値/時間の範囲内」の場合はRANGEを使用。正確にN行が必要な場合はROWSを使用。

4. 大きなテーブルではROW_NUMBERをLIMITの代替として使わない

ROW_NUMBERをページネーションに使うのは良さそうに聞こえますが、MySQLはテーブル全体をスキャンしてすべての行の番号を計算してからフィルタリングします — 通常のOFFSETと変わらないコストです。10万行以下のテーブルなら許容範囲。百万行以上になったら、keyset paginationに切り替えましょう。

MySQL 5.7から8.xへのアップグレードを検討しているなら、Window Functionsが最も現実的な理由です。以前は3〜4層のネストされたサブクエリが必要だった問題が、今はCTE + window clauseだけでシンプルに解決できます。コードは短く、メンテナンスも容易 — そしてパースするネストされたサブクエリがなくなるので、クエリプランナーの最適化もより効果的になります。

Share:
Tags: