はじめに
他のまとめ記事もたくさんありますが、勉強も兼ねて改めてウィンドウ関数(分析関数)についてまとめてみました。
今回は Mysql8.x 系を対象として、記事を作成しています。
ウィンドウ関数とは？
ウィンドウ関数についての説明は、下記の方の記事がとてもわかりやすかったです。
ウィンドウ関数（分析関数）をわかりやすく説明してみた
また、ウィンドウ関数を以前よりサポートしているPostgreSQLの公式ドキュメント上には、下記のような文言が書いてありました。
ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。
ウィンドウ関数は問い合わせ結果による現在行だけでなく、それ以上の行にアクセスすることができます。
文言だけ見ても若干イメージしづらいですね。
ということで、実際に試していきたいと思います。
その前に
ウィンドウ関数を理解するには、 ウィンドウフレーム という概念を知っておくとよさそうです。
ウィンドウフレームとは？
PostgreSQLの公式ドキュメントには、下記の説明文が記述してあります。
ウィンドウ関数に関連した別の重要な概念があります。
それぞれの行に対して、そのウィンドウフレームと呼ばれる、そのパーティション内の行の集合が存在します。
ウィンドウ関数の中には、パーティション全体ではなく、ウィンドウフレームの行のみに対して作用するものもあります。
若干わかりづらいですが、ウィンドウ関数はウィンドウフレームと呼ばれる部分を定義して、
そのフレーム内の行を対象として、関数が実行されます。
フレームに関して詳しく知りたい方は、下記の方の記事が非常に参考になりましたので、そちらを参考にして頂けると良いかと思います。
(当記事では詳しく触れません。）
https://masahikosawada.github.io/2018/07/07/Window-Frame/
今回使用するテーブルのスキーマ
下記のテーブルを使用します。
CREATE TABLE `emp_salary` (
`dep_name` varchar(255) NOT NULL, -- 部署名
`emp_no` int(11) NOT NULL, -- 従業員ID
`salary` int(11) NOT NULL -- 給与
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci
;
また、サンプルとして下記データを登録しておきます。
INSERT INTO emp_salary
VALUE ('営業',1,15000000)
,('営業',2,6800000)
,('営業',3,9200000)
,('営業',4,8500000)
,('営業',5,5400000)
,('営業',6,9200000)
,('営業',7,8500000)
,('システム開発',8,8200000)
,('システム開発',9,9800000)
,('システム開発',10,5400000)
,('システム開発',11,3900000)
,('システム開発',12,4600000)
,('システム開発',13,5400000)
,('システム開発',14,8200000)
,('マーケティング',15,5600000)
,('マーケティング',16,6200000)
,('マーケティング',17,3700000)
,('マーケティング',18,4400000)
,('マーケティング',19,6200000)
,('マーケティング',20,4200000)
,('マーケティング',21,4200000)
;
ウィンドウ関数の構文について
ウィンドウ関数は下記のような構文で記述します。
下記の例(よく使われてる例)では、従業員の部署名、給与と、所属部署に対しての平均給与を算出しています。
SELECT
dep_name -- 部署名
, emp_no -- 従業員ID
, salary -- 給与
, avg(salary) OVER (PARTITION BY dep_name) -- ここがウインドウ関数。部署名毎に区間(ウィンドウフレーム)を区切り、給与の平均を算出している。
FROM
emp_salary;
今回試す関数
下記関数を試していきたいと思います。
| 関数名 | 説明 |
|---|---|
| DENSE_RANK | order by で指定した順にソートした際の順位を返します。 同率があった場合順位は同じになり、その次は順位を飛ばしません。 |
| FIRST_VALUE | ソート項目でソートした結果から最初の値を返します。 |
| LAG | ソート項目でソートして現在行からN行前の値を返します。先行する行が存在しない場合はNULLを返します。 |
| NTH_VALUE | ソート項目でソートした結果からN番目の値を返します。 |
| NTILE | ソート項目でソートした結果を指定数でグループ分けします。 |
| RANK | DENSE_RANK（）関数と同様ですが、同率があった場合順位は同じになり、その次は順位を飛ばします。 |
| ROW_NUMBER | 行番号を返します。行番号は、ソート項目でソートした結果に対して設定されます。 |
DENSE_RANK
order by で指定した順にソートした際の順位を返します。
同率があった場合順位は同じになり、その次は順位を飛ばしません。
実行するクエリ
SELECT
dep_name
, emp_no
, salary
, DENSE_RANK() OVER (PARTITION BY dep_name order by salary) AS d_rank
FROM
emp_salary
;
結果
dep_nameを区切りとして、 salary の昇順で順位付けした結果を返しています。
+-----------------------+--------+----------+--------+
| dep_name | emp_no | salary | d_rank |
+-----------------------+--------+----------+--------+
| システム開発 | 11 | 3900000 | 1 |
| システム開発 | 12 | 4600000 | 2 |
| システム開発 | 10 | 5400000 | 3 |
| システム開発 | 13 | 5400000 | 3 |
| システム開発 | 8 | 8200000 | 4 |
| システム開発 | 14 | 8200000 | 4 |
| システム開発 | 9 | 9800000 | 5 |
| マーケティング | 17 | 3700000 | 1 |
| マーケティング | 20 | 4200000 | 2 |
| マーケティング | 21 | 4200000 | 2 |
| マーケティング | 18 | 4400000 | 3 |
| マーケティング | 15 | 5600000 | 4 |
| マーケティング | 16 | 6200000 | 5 |
| マーケティング | 19 | 6200000 | 5 |
| 営業 | 5 | 5400000 | 1 |
| 営業 | 2 | 6800000 | 2 |
| 営業 | 4 | 8500000 | 3 |
| 営業 | 7 | 8500000 | 3 |
| 営業 | 3 | 9200000 | 4 |
| 営業 | 6 | 9200000 | 4 |
| 営業 | 1 | 15000000 | 5 |
+-----------------------+--------+----------+--------+
FIRST_VALUE
ソート項目でソートした結果から最初の値を返します。
実行するクエリ
SELECT
dep_name
, emp_no
, salary
, FIRST_VALUE(salary) OVER (PARTITION BY dep_name order by salary) AS flrst_val
FROM
emp_salary
;
結果
dep_nameを区切りとして、 salary の昇順で並べた中の、最初の値を返しています。
+-----------------------+--------+----------+-----------+
| dep_name | emp_no | salary | flrst_val |
+-----------------------+--------+----------+-----------+
| システム開発 | 11 | 3900000 | 3900000 |
| システム開発 | 12 | 4600000 | 3900000 |
| システム開発 | 10 | 5400000 | 3900000 |
| システム開発 | 13 | 5400000 | 3900000 |
| システム開発 | 8 | 8200000 | 3900000 |
| システム開発 | 14 | 8200000 | 3900000 |
| システム開発 | 9 | 9800000 | 3900000 |
| マーケティング | 17 | 3700000 | 3700000 |
| マーケティング | 20 | 4200000 | 3700000 |
| マーケティング | 21 | 4200000 | 3700000 |
| マーケティング | 18 | 4400000 | 3700000 |
| マーケティング | 15 | 5600000 | 3700000 |
| マーケティング | 16 | 6200000 | 3700000 |
| マーケティング | 19 | 6200000 | 3700000 |
| 営業 | 5 | 5400000 | 5400000 |
| 営業 | 2 | 6800000 | 5400000 |
| 営業 | 4 | 8500000 | 5400000 |
| 営業 | 7 | 8500000 | 5400000 |
| 営業 | 3 | 9200000 | 5400000 |
| 営業 | 6 | 9200000 | 5400000 |
| 営業 | 1 | 15000000 | 5400000 |
+-----------------------+--------+----------+-----------+
LAG
ソート項目でソートして現在行からN行前の値を返します。先行する行が存在しない場合はNULLを返します。
実行するクエリ
SELECT
dep_name
, emp_no
, salary
, LAG(salary, 2, 'none') OVER (PARTITION BY dep_name order by salary) AS lag_val
FROM
emp_salary
;
結果
dep_nameを区切りとして、 salary の昇順で並べた中で、2つ前の値を取得しています。
存在しない場合は、noneを返しています。
+-----------------------+--------+----------+---------+
| dep_name | emp_no | salary | lag_val |
+-----------------------+--------+----------+---------+
| システム開発 | 11 | 3900000 | none |
| システム開発 | 12 | 4600000 | none |
| システム開発 | 10 | 5400000 | 3900000 |
| システム開発 | 13 | 5400000 | 4600000 |
| システム開発 | 8 | 8200000 | 5400000 |
| システム開発 | 14 | 8200000 | 5400000 |
| システム開発 | 9 | 9800000 | 8200000 |
| マーケティング | 17 | 3700000 | none |
| マーケティング | 20 | 4200000 | none |
| マーケティング | 21 | 4200000 | 3700000 |
| マーケティング | 18 | 4400000 | 4200000 |
| マーケティング | 15 | 5600000 | 4200000 |
| マーケティング | 16 | 6200000 | 4400000 |
| マーケティング | 19 | 6200000 | 5600000 |
| 営業 | 5 | 5400000 | none |
| 営業 | 2 | 6800000 | none |
| 営業 | 4 | 8500000 | 5400000 |
| 営業 | 7 | 8500000 | 6800000 |
| 営業 | 3 | 9200000 | 8500000 |
| 営業 | 6 | 9200000 | 8500000 |
| 営業 | 1 | 15000000 | 9200000 |
+-----------------------+--------+----------+---------+
LEAD
ソート項目でソートして現在行からN行後の値を返します。後続の行が存在しない場合はNULLを返します。
実行するクエリ
SELECT
dep_name
, emp_no
, salary
, LEAD(salary, 2, 'none') OVER (PARTITION BY dep_name order by salary) AS lead_val
FROM
emp_salary
;
結果
dep_nameを区切りとして、 salary の昇順で並べた中で、2つ後の値を取得しています。
存在しない場合は、noneを返しています。
+-----------------------+--------+----------+----------+
| dep_name | emp_no | salary | lead_val |
+-----------------------+--------+----------+----------+
| システム開発 | 11 | 3900000 | 5400000 |
| システム開発 | 12 | 4600000 | 5400000 |
| システム開発 | 10 | 5400000 | 8200000 |
| システム開発 | 13 | 5400000 | 8200000 |
| システム開発 | 8 | 8200000 | 9800000 |
| システム開発 | 14 | 8200000 | none |
| システム開発 | 9 | 9800000 | none |
| マーケティング | 17 | 3700000 | 4200000 |
| マーケティング | 20 | 4200000 | 4400000 |
| マーケティング | 21 | 4200000 | 5600000 |
| マーケティング | 18 | 4400000 | 6200000 |
| マーケティング | 15 | 5600000 | 6200000 |
| マーケティング | 16 | 6200000 | none |
| マーケティング | 19 | 6200000 | none |
| 営業 | 5 | 5400000 | 8500000 |
| 営業 | 2 | 6800000 | 8500000 |
| 営業 | 4 | 8500000 | 9200000 |
| 営業 | 7 | 8500000 | 9200000 |
| 営業 | 3 | 9200000 | 15000000 |
| 営業 | 6 | 9200000 | none |
| 営業 | 1 | 15000000 | none |
+-----------------------+--------+----------+----------+
NTH_VALUE
ソート項目でソートした結果からN番目の値を返します。
実行するクエリ
SELECT
dep_name
, emp_no
, salary
, NTH_VALUE(salary, 2) OVER (PARTITION BY dep_name order by salary) AS nth_val
FROM
emp_salary
;
結果
dep_nameを区切りとして、 salary の昇順で並べた中で、2つ目の値を取得しています。
+-----------------------+--------+----------+---------+
| dep_name | emp_no | salary | nth_val |
+-----------------------+--------+----------+---------+
| システム開発 | 11 | 3900000 | NULL |
| システム開発 | 12 | 4600000 | 4600000 |
| システム開発 | 10 | 5400000 | 4600000 |
| システム開発 | 13 | 5400000 | 4600000 |
| システム開発 | 8 | 8200000 | 4600000 |
| システム開発 | 14 | 8200000 | 4600000 |
| システム開発 | 9 | 9800000 | 4600000 |
| マーケティング | 17 | 3700000 | NULL |
| マーケティング | 20 | 4200000 | 4200000 |
| マーケティング | 21 | 4200000 | 4200000 |
| マーケティング | 18 | 4400000 | 4200000 |
| マーケティング | 15 | 5600000 | 4200000 |
| マーケティング | 16 | 6200000 | 4200000 |
| マーケティング | 19 | 6200000 | 4200000 |
| 営業 | 5 | 5400000 | NULL |
| 営業 | 2 | 6800000 | 6800000 |
| 営業 | 4 | 8500000 | 6800000 |
| 営業 | 7 | 8500000 | 6800000 |
| 営業 | 3 | 9200000 | 6800000 |
| 営業 | 6 | 9200000 | 6800000 |
| 営業 | 1 | 15000000 | 6800000 |
+-----------------------+--------+----------+---------+
NTILE
ソート項目でソートした結果を指定数でグループ分けします。
実行するクエリ
SELECT
dep_name
, emp_no
, salary
, NTILE(4) OVER(ORDER BY salary) AS ntile_val
FROM
emp_salary
;
結果
salary の昇順で並べた中で、4つのグルーピングを行っています。
+-----------------------+--------+----------+-----------+
| dep_name | emp_no | salary | ntile_val |
+-----------------------+--------+----------+-----------+
| マーケティング | 17 | 3700000 | 1 |
| システム開発 | 11 | 3900000 | 1 |
| マーケティング | 20 | 4200000 | 1 |
| マーケティング | 21 | 4200000 | 1 |
| マーケティング | 18 | 4400000 | 1 |
| システム開発 | 12 | 4600000 | 1 |
| 営業 | 5 | 5400000 | 2 |
| システム開発 | 10 | 5400000 | 2 |
| システム開発 | 13 | 5400000 | 2 |
| マーケティング | 15 | 5600000 | 2 |
| マーケティング | 16 | 6200000 | 2 |
| マーケティング | 19 | 6200000 | 3 |
| 営業 | 2 | 6800000 | 3 |
| システム開発 | 8 | 8200000 | 3 |
| システム開発 | 14 | 8200000 | 3 |
| 営業 | 4 | 8500000 | 3 |
| 営業 | 7 | 8500000 | 4 |
| 営業 | 3 | 9200000 | 4 |
| 営業 | 6 | 9200000 | 4 |
| システム開発 | 9 | 9800000 | 4 |
| 営業 | 1 | 15000000 | 4 |
+-----------------------+--------+----------+-----------+
RANK
DENSE_RANK（）関数と同様ですが、同率があった場合順位は同じになり、その次は順位を飛ばします。
実行するクエリ
SELECT
dep_name
, emp_no
, salary
, RANK() OVER (PARTITION BY dep_name order by salary) AS rank_val
FROM
emp_salary
;
結果
dep_nameを区切りとして、 salary の昇順で並べたものを、順位付けします。
salary が同じ場合は、同一順位になります。
+-----------------------+--------+----------+----------+
| dep_name | emp_no | salary | rank_val |
+-----------------------+--------+----------+----------+
| システム開発 | 11 | 3900000 | 1 |
| システム開発 | 12 | 4600000 | 2 |
| システム開発 | 10 | 5400000 | 3 |
| システム開発 | 13 | 5400000 | 3 |
| システム開発 | 8 | 8200000 | 5 |
| システム開発 | 14 | 8200000 | 5 |
| システム開発 | 9 | 9800000 | 7 |
| マーケティング | 17 | 3700000 | 1 |
| マーケティング | 20 | 4200000 | 2 |
| マーケティング | 21 | 4200000 | 2 |
| マーケティング | 18 | 4400000 | 4 |
| マーケティング | 15 | 5600000 | 5 |
| マーケティング | 16 | 6200000 | 6 |
| マーケティング | 19 | 6200000 | 6 |
| 営業 | 5 | 5400000 | 1 |
| 営業 | 2 | 6800000 | 2 |
| 営業 | 4 | 8500000 | 3 |
| 営業 | 7 | 8500000 | 3 |
| 営業 | 3 | 9200000 | 5 |
| 営業 | 6 | 9200000 | 5 |
| 営業 | 1 | 15000000 | 7 |
+-----------------------+--------+----------+----------+
ROW_NUMBER
行番号を返します。行番号は、ソート項目でソートした結果に対して設定されます。
実行するクエリ
SELECT
dep_name
, emp_no
, salary
, ROW_NUMBER() OVER (PARTITION BY dep_name order by salary) AS rownum_val
FROM
emp_salary
;
結果
dep_nameを区切りとして、 salary の昇順で並べたものの、行番号を返しています。
+-----------------------+--------+----------+------------+
| dep_name | emp_no | salary | rownum_val |
+-----------------------+--------+----------+------------+
| システム開発 | 11 | 3900000 | 1 |
| システム開発 | 12 | 4600000 | 2 |
| システム開発 | 10 | 5400000 | 3 |
| システム開発 | 13 | 5400000 | 4 |
| システム開発 | 8 | 8200000 | 5 |
| システム開発 | 14 | 8200000 | 6 |
| システム開発 | 9 | 9800000 | 7 |
| マーケティング | 17 | 3700000 | 1 |
| マーケティング | 20 | 4200000 | 2 |
| マーケティング | 21 | 4200000 | 3 |
| マーケティング | 18 | 4400000 | 4 |
| マーケティング | 15 | 5600000 | 5 |
| マーケティング | 16 | 6200000 | 6 |
| マーケティング | 19 | 6200000 | 7 |
| 営業 | 5 | 5400000 | 1 |
| 営業 | 2 | 6800000 | 2 |
| 営業 | 4 | 8500000 | 3 |
| 営業 | 7 | 8500000 | 4 |
| 営業 | 3 | 9200000 | 5 |
| 営業 | 6 | 9200000 | 6 |
| 営業 | 1 | 15000000 | 7 |
+-----------------------+--------+----------+------------+
最後に
いかがでしたでしょうか？
自身として使ったことないものもいくつかあり、良い勉強になりました。
分析・集計時にも非常に便利ですし、過去にレコメンドエンジンを作っていたときにも、非常にお世話になっていました。
使いこなせれば非常に有益な関数だと思いますので、是非ご参考にしてください。
お付き合い頂きありがとうございました！
共に働くWebエンジニアを募集しています!
不動産SHOPナカジツでは自社サービスを作っていく仲間を募集しています。
詳しくはWantedlyからお問い合わせください。
お待ちしております！