Window関数とは?｜ブログ｜株式会社リヴァイト

はじめに 本記事では、MySQL8から登場したWindow関数について、まとめます。 Window関数とは? Window関数は、SQLにおける強力な分析機能の一つです。 Window関数を利用すれば通常、GROUP BYを用いた集約関数で集約されていた結果を集約することなく結果を表示することができます。 Window関数の主な特徴は以下の通りです 1．PARTITION BY句によるレコード集合の分割 2．ORDER BY句によるレコードの順序付け 3．フレーム句によるカレントレコードを中心としたサブセットの定義 まずは例を見てみましょう。 簡単な例 今回、使用するデータセットは下記になります。 サンプルデータ sql -- テーブル作成 CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary INT ); -- データ挿入 INSERT INTO employees (id, name, department, salary) VALUES (1, 'test1', '営業部', 500000), (2, 'test2', '営業部', 550000), (3, 'test3', '営業部', 600000), (4, 'test4', '経理部', 600000), (5, 'test5', '経理部', 650000), (6, 'test6', '経理部', 700000), (7, 'test7', 'システム部', 800000), (8, 'test8', 'システム部', 850000), (9, 'test9', 'システム部', 900000); まずはこのemployeesテーブルから部署事に集約して、部署事の給料を求めてみます。 通常通りGROUP BYを利用して集約関数を使う場合は、下記のように部署事の出力になります。 sql SELECT id, department, SUM(salary) AS 'departmenttotalsalary' FROM employees GROUP BY department; | id | department | departmenttotalsalary | | --- | --- | --- | | 1 | 営業部 | 1650000 | | 4 | 経理部 | 1950000 | | 7 | システム部 | 2550000 | この結果では、各部署の給与合計は求められていますが、個人の給与情報は失われています。 次に、Window関数を使って同じ計算を行ってみましょう。 sql SELECT id, name, department, salary, SUM(salary) OVER (PARTITION BY department) AS 'departmenttotalsalary' FROM employees; Window関数を使った場合、個人の情報（id、name、salary）を保持しながら、部署ごとの給与合計（departmenttotalsalary）が計算されています。これが、Window関数の強力な点です。 Window関数の構文 sql functionname OVER ( [PARTITION BY 式1, 式2, ...] [ORDER BY 式1 [ASC | DESC], 式2 [ASC | DESC], ...] [frameclause] ) functionname: 使用するWindow関数の名前を指定します。例えば、SUM, AVG, COUNT, RANKなどがあります。 PARTITION BY: 行をグループ化します。PARTITION BYで指定された式の値が同じ行は、同じグループ（パーティション）に属することになります。パーティションは、Window関数の計算の単位となります。 ORDER BY: パーティション内の行を並べ替える式を指定します frameclause: 現在の行を基準としたサブセットを定義します。ROWS、RANGE、またはGROUPSキーワードを使用します PARTITION BYを使ってOVER句でパーティション方法を指定すると、指定した条件ごとに行がパーティション化され、パーティションごとの集計結果が生成されます。 先ほどのクエリでは、PARTITION BYでdepartmentを指定し、部署ごとにグループ化を行いました。 これにより、各部署が1つのパーティションとして扱われ、パーティションごとにSUM関数が適用されました。 PARTITION BYが未指定の場合 PARTITION BYが未指定の場合、クエリー行のセット全体が単一のパーティションとして扱われ、 グローバルな集計結果が各行に対して生成されます sql SELECT id, name, department, salary, SUM(salary) OVER () AS 'totalsalary' FROM employees; OVER句が空の場合、給料(salary)の合計がすべての行に対して計算されています。つまり、テーブル全体の給料総額（6150000）が各行のtotalsalary列に表示されています。 これは、PARTITION BYが指定されていないため、テーブルのすべての行が1つのパーティションとして扱われ、そのパーティション全体に対して集計関数（ここではSUM）が適用されるためです。 window関数として使える関数 MySQLでは、多くの集計関数がWindow関数としても使用できます。 OVER句の有無によって、通常の集計関数とWindow関数を使い分けることができます。 以下は、Window関数として使用できる主な集計関数です。 AVG() BITAND() BITOR() BITXOR() COUNT() JSONARRAYAGG() JSONOBJECTAGG() MAX() MIN() STDDEVPOP(), STDDEV(), STD() STDDEVSAMP() SUM() VARPOP(), VARIANCE() VARSAMP() また、MySQLには非集計関数でありながらWindow関数としてのみ使用される関数もあります。 これらの関数を使う場合は、OVER句が必須です。 具体的には以下の通りです。 | 関数名 | 動作 | | --- | --- | | CUMEDIST | 現在の行の値以下の行の割合を計算する。 | | DENSERANK | 現在の行の順位を計算する。同順位が続く場合、順位は飛ばさない。 | | FIRSTVALUE | パーティション内の最初の行の値を返す。 | | LAG() | 現在の行より前の行の値を返す。オフセットを指定可能。 | | LASTVALUE | パーティション内の最後の行の値を返す。 | | LEAD | 現在の行より後の行の値を返す。オフセットを指定可能。 | | NTHVALUE | パーティション内のN番目の行の値を返す。 | | NTILE | 行をN個のグループに分割し、現在の行が属するグループの番号を返す。 | | PERCENTRANK | 現在の行の相対的な順位を0から1の範囲で計算する。 | | RANK | 現在の行の順位を計算する。同順位があると、次の順位は飛ばす。 | | ROWNUMBER | 各行に一意の連番を割り当てる。 | 具体例 ROWNUMBER、DENSERANK、RANKを使用した例を見ていきましょう。 sql SELECT department, salary, ROWNUMBER() OVER (ORDER BY salary DESC) AS 'rownumber', DENSERANK() OVER (ORDER BY salary DESC) AS 'denserank', RANK() OVER (ORDER BY salary DESC) AS 'rank' FROM employees; 実行結果 | department | salary | rownumber | denserank | rank | | --- | --- | --- | --- | --- | | システム部 | 900000 | 1 | 1 | 1 | | システム部 | 850000 | 2 | 2 | 2 | | システム部 | 800000 | 3 | 3 | 3 | | 経理部 | 700000 | 4 | 4 | 4 | | 経理部 | 650000 | 5 | 5 | 5 | | 経理部 | 600000 | 6 | 6 | 6 | | 営業部 | 600000 | 7 | 6 | 6 | | 営業部 | 550000 | 8 | 7 | 8 | | 営業部 | 500000 | 9 | 8 | 9 | この実行結果からわかる通り、 ROWNUMBERは、給与の高い順に一意の連番を付けています。 同じ給与の行（経理部の600000と営業部の600000）でも、連番は異なります（6と7）。連番は1から始まり、1ずつ増加しています。 DENSERANKは、給与の高い順に順位を付けています。 同じ給与の行には同じ順位が付けられています（経理部の600000と営業部の600000はともに6位） 同順位の後の順位は飛ばされず、次の順位（7）が続いています。 RANKは、給与の高い順に順位を付けています。 同じ給与の行には同じ順位が付けられています（経理部の600000と営業部の600000はともに6位） 同順位の後の順位は飛ばされ、次の順位（8）が続いています。 DENSERANKとRANKの違いは、同順位の後の順位の付け方です。 DENSERANKは次の順位を飛ばさないのに対し、RANKは飛ばします。 これが、営業部の550000の行でDENSERANKが7、RANKが8になっている理由です。 これらの関数を使い分けることで、要件に応じた順位付けや連番の生成ができます。 ビジネスの現場では、売上のランキングや成績の順位付けなど、様々なシーンでこれらの関数が活用されています。 まとめ 今回はMySQL8で登場したWindow関数について解説しました。