Posted inMySQL

MySQL 8のウィンドウ関数：複雑なレポートクエリを解決する「救世主」的なソリューション

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

深夜の出来事：SQLに午前2時まで振り回された話

数年前のある夜のシステム当番のことを、今でも鮮明に覚えています。時計の針は午前2時を指しており、上司からは朝一番の会議のために至急売上レポートが必要だという連絡が入りました。お題はいたってシンプルに見えました。「注文一覧に、各注文の『累計売上合計』と、その日の売上ランキングの列を追加すること」です。

当時、プロジェクトはまだMySQL 5.7で動いていました。私は何層にも重なったSELF JOINと格闘し、@variable変数を使ってデータを累積させていきました。結果はどうだったでしょうか？ 500万件のデータセットに対してクエリの実行に25秒かかり、サーバーのCPU使用率は95%まで跳ね上がりました。もしあの時、MySQL 8のウィンドウ関数（Window Functions）を知っていれば、あと2時間は早く寝られたはずです。

ウィンドウ関数（Window Functions）は、単なる新機能ではありません。これは、システムのパフォーマンスを低下させることなく、プロフェッショナルなデータ分析（Data Analytics）の課題をMySQLで解決するための大きな転換点となりました。

5分で理解するウィンドウ関数

複数の行を1つにまとめるGROUP BYとは異なり、ウィンドウ関数を使用すると、各レコードの詳細を保持したまま、行のセットに対して計算を行うことができます。ここでの鍵となるのは、OVER句です。

salesテーブルを見てみましょう。複雑なサブクエリを書く代わりに、たった1行のコマンドで売上のランキングを作成できます。

SELECT 
    sale_date, 
    employee_id, 
    amount,
    RANK() OVER (ORDER BY amount DESC) as sales_rank
FROM sales;

結果には即座にsales_rank列が返されます。JOINも一時テーブルの作成も不要で、コードはクリーンで非常に読みやすくなります。

ウィンドウ関数の構造を解読する

このツールを使いこなすには、OVER句の3つの主要なコンポーネントを理解する必要があります。

1. PARTITION BY：分割して計算する

PARTITION BYを使用すると、データを独立したグループに分割して計算できます。例えば、会社全体ではなく、部署ごとに従業員のランキングを作成したい場合は次のようになります。

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

2. ORDER BY：処理順序の決定

この要素は、計算対象となる行の順序を決定します。SUM()などの関数でORDER BYを指定すると、MySQLは全体の合計ではなく、累計（running total）を計算したいのだと解釈します。

3. フレーム句 (ROWS/RANGE)：範囲を制限する

これは、現在の行の周囲の計算範囲を特定するための高度なテクニックです。例えば、「直近3日間の平均売上を計算する」（現在の行と前の2行）といった具合です。これは、移動平均（Moving Average）のグラフを描く際に非常に役立ちます。

本番環境で「必須」の3つのウィンドウ関数セット

実際、業務の90%は以下の関数グループに関連するものです。

ランキング系：ROW_NUMBER, RANK, DENSE_RANK

  • ROW_NUMBER()：単純な連番（1, 2, 3, 4）。
  • RANK()：同じ値は同じ順位になりますが、次の順位が飛びます（1, 2, 2, 4）。
  • DENSE_RANK()：RANKと同様ですが、順位を飛ばしません（1, 2, 2, 3）。

比較系：LAGとLEAD

これらは行間のデータを比較するための優れたツールです。今日の売上が昨日と比べて何%増減したかを知りたいですか？ LAGを使えば、前の行の値を取得して、同じ行内で計算を行うことができます。

SELECT 
    sale_date, 
    amount,
    LAG(amount, 1) OVER (ORDER BY sale_date) as prev_day_amount
FROM daily_sales;

累計計算系 (Running Total)

SUM関数をOVERおよびORDER BYと組み合わせるだけです。この方法は、古いMySQLバージョンでネストされたサブクエリを使用するよりも何倍も高速です。

実践的な応用：カテゴリごとの売上トップ3製品を特定する

以前は、この課題を解決するためにカテゴリごとにUNIONを使用するか、非常に重い クエリを書く必要がありました。ウィンドウ関数を使えば、はるかにすっきりと記述できます。

WITH RankedProducts AS (
    SELECT 
        category_id, 
        product_name, 
        total_sold,
        DENSE_RANK() OVER (PARTITION BY category_id ORDER BY total_sold DESC) as rnk
    FROM product_stats
)
SELECT * FROM RankedProducts WHERE rnk <= 3;

共通テーブル式 (CTE)とウィンドウ関数の組み合わせは最強のコンビです。コードはプロフェッショナルになり、後任者にとってもメンテナンスしやすくなります。

パフォーマンスに関する実体験に基づいた注意点

システムの最適化を何度も重ねた結果、ウィンドウ関数を使用する際の3つの重要な注意点にたどり着きました。

  • インデックスは必須： PARTITION BYやORDER BYで使用する列にはインデックスを貼りましょう。インデックスがない場合、MySQLはディスク上でfilesortを実行せざるを得なくなります。1,000万行のテーブルでは、クエリ速度が目に見えて低下します。
  • メモリの確認： ウィンドウ関数はRAM上で直接計算を行います。データセットが大きすぎる場合は、設定ファイル my.cnf の window_buffer_size パラメータを調整する必要があります。
  • 使い分けが肝心： 単純な総計（Grand Total）が必要なだけなら、従来の GROUP BY を使いましょう。ウィンドウ関数は、行間での比較やセグメントごとの計算が必要な場合にのみ使用すべきです。

ウィンドウ関数をマスターすることは、単にコードを速く書けるようになるだけではありません。システムの安定稼働を助け、重いクエリによる突然のサーバー「フリーズ」を防ぐことにも繋がります。レポートシステムや財務システムを管理している方は、ぜひ今日から古いステートメントのリファクタリングを試してみてください。

Share:
Tags: