GROUP BY を覚えると、たいていの集計は書けるようになります。部署ごとの合計も、担当ごとの合計も出ます。
前回: 相関サブクエリとNOT IN — 行の数だけ実行される これは深掘りです。本線の順路ではありません。 本線のプロセスを止めても消えない場所 — MySQLとSQLの基本で
SELECTとGROUP BYを打ったところの、その先だけを扱います。読まなくても本線は先に進めます。 作業場所はVMの中(Ubuntu)です。MySQL が動いていれば、他に用意するものはありません。練習用のテーブルはこの記事の中で作ります。
それで、こう聞かれます。
「各部署で、一番売った人を出して」
書けません。正確に言うと、金額までは出るのに、その人の名前が出てきません。
SELECT dept, MAX(total) FROM ( ... ) t GROUP BY dept;
800 と 1200 は出ます。でも「誰が」が無い。
これは書き方が下手なのではなく、GROUP BY がそういう道具だからです。
集計関数は、複数の行を1行に潰します。 潰した時点で、潰された側の行が持っていた「名前」はもう存在しません。
この記事でやるのは、潰さずに集計することです。それをやるのがウィンドウ関数です。
対象レベル: 中級〜上級 / 区分: 発展
この記事でわかること
GROUP BYは行を潰し、ウィンドウ関数は潰さないこと。同じSUMでも、返る行数が変わることROW_NUMBER/RANK/DENSE_RANKは同点のときだけ違うこと。同点が無いデータで試すと、3つの違いが一生分からないこと- 累計を出すと、既定のフレームが
RANGEなので同じ値がまとめて足されること。ROWSと書かないと直らないこと WHEREの中でウィンドウ関数は使えないこと。それが好き嫌いではなく実行順の話であること- ウィンドウ関数には並べ替えが要るので、タダではないこと
この記事でやること
- MySQL のバージョンを確認する。 8.0 未満だと、この記事の SQL は1行も動きません
- 練習用のテーブル
d_scoreを作る(部署・担当・月・金額。同点を含めてあります) - 同じ
SUMをGROUP BYとOVER ()の両方で打って、行数を並べて比べる ROW_NUMBER/RANK/DENSE_RANKを同時に3列出して、同点の行で何が変わるかを見るLAGで前月比を出し、累計でフレームの罠を踏んで直し、移動平均まで作る- 後片づけをして、練習用のデータベースごと消す
先に知る言葉
この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| ウィンドウ関数 | 行を潰さずに、その行から見える範囲(=ウィンドウ)を集計して、横に1列足す関数。OVER が付いているものが全部これ |
OVER | 「どの範囲を見るか」を書く場所。ウィンドウ関数は OVER 抜きでは書けない |
| パーティション | ウィンドウを区切る単位。部署ごと・人ごとのように分ける。区切っても行は潰れない |
| フレーム | パーティションの中で、その行から見て何行目から何行目までを足すかの指定。「先頭から今の行まで」なら累計になる |
| 同点(タイ) | 集計値がまったく同じ行が2つ以上あること。順位関数の違いは、ここでしか出ません |
LAG / LEAD | 1つ前の行 / 1つ後の行の値を、いまの行に横付けする関数。前月比はこれで出す |
「集計関数」と「ウィンドウ関数」は別々の関数ではありません。
SUMもCOUNTもAVGも、OVERを付けた瞬間にウィンドウ関数として振る舞います。 覚え直す関数はほとんどありません。
① まずバージョンを確認する(ここを飛ばすと全部落ちます)
ウィンドウ関数は MySQL 8.0 で入りました。 それ以前の 5.7 系には存在しません。
VM の中で打ちます。
mysql Ver 8.0.43-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu))
Ver 8. 以上なら大丈夫です。 Ver 5.7 と出た人はこの記事の SQL が動きません(対処は下に書きます)。
クライアントの版とサーバーの版は別物なので、サーバー側も見ます。ログインします。
SELECT VERSION();
+-------------------------+
| VERSION() |
+-------------------------+
| 8.0.43-0ubuntu0.24.04.1 |
+-------------------------+
この VERSION() のほうが本番です。 ここが 5.7.x なら、以降は動きません。
5.7 だった場合、どうするか
5.7 では、この記事の SQL は文法エラーになります。 こういう出方をします。
ERROR 1064 (42000): You have an error in your SQL syntax; ... near 'OVER (PARTITION BY dept ORDER BY total DESC)' at line 1
「関数が無い」ではなく「文法が違う」と言われるのがポイントです。OVER という書き方自体を知らないので、パーサーがそこで止まります。綴りを疑って時間を溶かさないでください。 版の問題です。
選べる道は3つあります。
| 道 | 中身 |
|---|---|
| MySQL 8 に上げる | 一番まっすぐ。ただし本番機では移行の検討が要るので、学習用の VM でやる |
| PostgreSQL を使う | PostgreSQL は昔からウィンドウ関数を持っています。 本線のMySQLとPostgreSQLの違いは1割の回で入れたものがあれば、この記事の SQL はほぼそのまま通ります |
| 自己結合で書く | ウィンドウ関数を使わずに同じ結果を出す。書けます。ただし後述するとおり、長くて遅いです |
学習用の VM なら、素直に 8 系を入れるのが早いです。 以降は 8.0 以降が動いている前提で進めます。
② 練習用のテーブルを作る
この記事は、これだけで完結します。 前の回のデータは使いません。
まだ mysql に入っていなければ入ります。
練習用のデータベースを新しく作ります。既存の appdb は触りません。 最後に丸ごと消せるようにするためです。
CREATE DATABASE IF NOT EXISTS deepdb CHARACTER SET utf8mb4;
USE deepdb;
テーブルを作ります。部署・担当・月・金額の4つだけです。
CREATE TABLE d_score (
id INT AUTO_INCREMENT PRIMARY KEY,
dept VARCHAR(20) NOT NULL,
name VARCHAR(20) NOT NULL,
ym CHAR(7) NOT NULL,
amount INT NOT NULL
) CHARACTER SET utf8mb4;
ymをCHAR(7)にして'2026-01'の形で入れています。 この形なら、 文字列として並べ替えても、月の順番と一致します。(2026-01<2026-02< … <2026-10) 桁を揃えていない'2026-1'にすると'2026-10'が'2026-2'より前に来て崩れます。
データを入れます。5人 × 6か月 = 30行です。同点を混ぜてあります(後で効きます)。
INSERT INTO d_score (dept, name, ym, amount) VALUES
('東京','佐藤','2026-01',100),('東京','佐藤','2026-02',120),('東京','佐藤','2026-03',130),
('東京','佐藤','2026-04',150),('東京','佐藤','2026-05',140),('東京','佐藤','2026-06',160),
('東京','鈴木','2026-01',130),('東京','鈴木','2026-02',120),('東京','鈴木','2026-03',110),
('東京','鈴木','2026-04',140),('東京','鈴木','2026-05',150),('東京','鈴木','2026-06',150),
('東京','高橋','2026-01', 90),('東京','高橋','2026-02', 80),('東京','高橋','2026-03',100),
('東京','高橋','2026-04',110),('東京','高橋','2026-05',120),('東京','高橋','2026-06',130),
('大阪','田中','2026-01',200),('大阪','田中','2026-02',180),('大阪','田中','2026-03',210),
('大阪','田中','2026-04',190),('大阪','田中','2026-05',220),('大阪','田中','2026-06',200),
('大阪','伊藤','2026-01',150),('大阪','伊藤','2026-02',150),('大阪','伊藤','2026-03',160),
('大阪','伊藤','2026-04',170),('大阪','伊藤','2026-05',180),('大阪','伊藤','2026-06',190);
入ったか確かめます。
SELECT COUNT(*) FROM d_score;
+----------+
| COUNT(*) |
+----------+
| 30 |
+----------+
30 でなければ、INSERT のどこかが落ちています。 一度 DROP TABLE d_score; して作り直してください。
人ごとの合計を先に見ておきます。この記事の話は全部この5つの数字の上で起きます。
SELECT dept, name, SUM(amount) AS total FROM d_score GROUP BY dept, name ORDER BY dept, total DESC;
+--------+--------+-------+
| dept | name | total |
+--------+--------+-------+
| 大阪 | 田中 | 1200 |
| 大阪 | 伊藤 | 1000 |
| 東京 | 佐藤 | 800 |
| 東京 | 鈴木 | 800 |
| 東京 | 高橋 | 630 |
+--------+--------+-------+
東京の佐藤と鈴木が、両方 800 です。 これがこの記事の主役です。
[スクリーンショット: 01_バージョンとデータ確認.png] SELECT VERSION(); と、上の人ごと合計の結果が続けて写っている画面を撮ってください。
③ 「名前が消える」を、実際に消してみる
さっきの結果から、部署ごとの最高額を出します。
SELECT dept, MAX(total) AS best
FROM (SELECT dept, name, SUM(amount) AS total FROM d_score GROUP BY dept, name) t
GROUP BY dept;
+--------+------+
| dept | best |
+--------+------+
| 大阪 | 1200 |
| 東京 | 800 |
+--------+------+
金額は出ました。名前がありません。 では name を足せばいいはずです。
SELECT dept, name, MAX(total) AS best
FROM (SELECT dept, name, SUM(amount) AS total FROM d_score GROUP BY dept, name) t
GROUP BY dept;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
怒られます。そして、この怒りは正しいです。
GROUP BY dept と書いた時点で、東京の3行は1行に潰れています。 その1行に対して「name は何ですか」と聞かれても、候補が3つあって決められません。 データベースは「決められないので出しません」と言っているだけです。
only_full_group_byを外せば「通り」ます。 通りますが、出てくる名前はデタラメです。 3人のうち誰か1人が黙って選ばれ、それが最高額の人である保証はどこにもありません。 設定を外して黙らせるのは、間違った答えを黙って受け取ることです。やめてください。
問題は書き方ではありません。 行を潰したのに、潰される前の情報を要求している、という構造の問題です。
なので、潰さない道具を使います。
④ 潰れないことを、目で見る
言葉で説明するより、同じ SUM を2回打つのが早いです。
まず GROUP BY のほう。全体の合計を出します。
SELECT SUM(amount) AS zentai FROM d_score;
+--------+
| zentai |
+--------+
| 4430 |
+--------+
1行です。 30行が1行に潰れました。
次に、同じ SUM(amount) に OVER () を付けます。
SELECT id, dept, name, ym, amount, SUM(amount) OVER () AS zentai FROM d_score;
+----+--------+--------+---------+--------+--------+
| id | dept | name | ym | amount | zentai |
+----+--------+--------+---------+--------+--------+
| 1 | 東京 | 佐藤 | 2026-01 | 100 | 4430 |
| 2 | 東京 | 佐藤 | 2026-02 | 120 | 4430 |
| 3 | 東京 | 佐藤 | 2026-03 | 130 | 4430 |
...
| 30 | 大阪 | 伊藤 | 2026-06 | 190 | 4430 |
+----+--------+--------+---------+--------+--------+
30 rows in set (0.00 sec)
最後の 30 rows in set を見てください。
| 書き方 | 返る行数 | 4430 の出方 | |
|---|---|---|---|
| 集計 | SUM(amount) | 1行 | 1回だけ |
| ウィンドウ | SUM(amount) OVER () | 30行(元のまま) | 全部の行に横付け |
足した数字は同じ 4430 です。違うのは、それをどう返すかだけです。
ウィンドウ関数の理解は、これ1個で足ります。 「元の行はそのまま残して、集計結果を横に1列足す」。 これだけです。
idもnameもymも消えていません。消えないから、「誰が」が言えます。
「全体に占める割合」も、これで一発で出ます。
SELECT name, ym, amount,
ROUND(amount / SUM(amount) OVER () * 100, 2) AS pct
FROM d_score
ORDER BY amount DESC
LIMIT 5;
+--------+---------+--------+------+
| name | ym | amount | pct |
+--------+---------+--------+------+
| 田中 | 2026-05 | 220 | 4.97 |
| 田中 | 2026-03 | 210 | 4.74 |
| 田中 | 2026-01 | 200 | 4.51 |
| 田中 | 2026-06 | 200 | 4.51 |
| 伊藤 | 2026-06 | 190 | 4.29 |
+--------+---------+--------+------+
分母(全体合計)と分子(その行の金額)が、同じ1行の上に並んでいます。 GROUP BY ではこうなりません。分母を出した時点で分子の行が消えているからです。
[スクリーンショット: 02_GROUPBYとOVERの比較.png] SELECT SUM(amount) FROM d_score;(1行)と SUM(amount) OVER ()(30行)を続けて打って、 1 row in set と 30 rows in set の両方が同じ画面に写っている状態を撮ってください。
⑤ OVER の中身は3つの部品でできている
OVER () の丸かっこの中には、3つまで書けます。全部省略できます。
| 部品 | 書き方 | 何をするか | 省略したら |
|---|---|---|---|
PARTITION BY | PARTITION BY dept | 区切る。 部署ごとに別々のウィンドウにする | 区切らない(全行が1つのウィンドウ) |
ORDER BY | ORDER BY ym | ウィンドウの中で並べる。 順位・前後・累計に必須 | 並べない(順位も累計も出せない) |
| フレーム | ROWS BETWEEN ... | 並べた中の、どこからどこまでを足すか | 後述(ここが罠) |
書く順番はこの順で固定です。OVER (PARTITION BY dept ORDER BY ym ROWS ...) になります。
PARTITION BY は GROUP BY に似ているが、潰さない
ここが一番の勘所です。
SELECT dept, name, ym, amount,
SUM(amount) OVER (PARTITION BY dept) AS dept_total
FROM d_score
WHERE ym = '2026-01'
ORDER BY dept, name;
+--------+--------+---------+--------+------------+
| dept | name | ym | amount | dept_total |
+--------+--------+---------+--------+------------+
| 大阪 | 伊藤 | 2026-01 | 150 | 350 |
| 大阪 | 田中 | 2026-01 | 200 | 350 |
| 東京 | 佐藤 | 2026-01 | 100 | 320 |
| 東京 | 高橋 | 2026-01 | 90 | 320 |
| 東京 | 鈴木 | 2026-01 | 130 | 320 |
+--------+--------+---------+--------+------------+
大阪の2行には 350、東京の3行には 320 が付きました。
GROUP BY dept なら2行になります。PARTITION BY dept は5行のままです。
同じ「部署で区切る」でも、
GROUP BYは区切って潰し、PARTITION BYは区切るだけです。 潰さないので、nameもamountも生きています。
WHERE は先に効く、という点にも注意してください。上の dept_total は 6か月分の合計ではなく、 ym = '2026-01' で絞ったあとの合計です。ウィンドウ関数が見るのは、WHERE を通過した行だけです。 (この「順番」の話は、後で WHERE の節でもう一度出てきます)
⑥ 順位を付ける3つの関数は、同点でしか違わない
順位を付ける関数は3つあります。 そして、同点が無いデータで試すと、3つとも同じ結果になります。 だから違いが分かりません。
なので、同点を仕込んだ d_score で、3つ同時に打ちます。
SELECT dept, name, SUM(amount) AS total,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY SUM(amount) DESC) AS rn,
RANK() OVER (PARTITION BY dept ORDER BY SUM(amount) DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY SUM(amount) DESC) AS drnk
FROM d_score
GROUP BY dept, name
ORDER BY dept, total DESC;
+--------+--------+-------+----+-----+------+
| dept | name | total | rn | rnk | drnk |
+--------+--------+-------+----+-----+------+
| 大阪 | 田中 | 1200 | 1 | 1 | 1 |
| 大阪 | 伊藤 | 1000 | 2 | 2 | 2 |
| 東京 | 佐藤 | 800 | 1 | 1 | 1 |
| 東京 | 鈴木 | 800 | 2 | 1 | 1 |
| 東京 | 高橋 | 630 | 3 | 3 | 2 |
+--------+--------+-------+----+-----+------+
大阪(同点なし)は3列とも同じです。東京(800 が2人)だけ、3列が割れます。
| 関数 | 800 の2人 | 630 の高橋 | ひとことで言うと |
|---|---|---|---|
ROW_NUMBER | 1, 2(同点でも必ず別番号) | 3 | 通し番号。同点を認めない |
RANK | 1, 1 | 3(2 が飛ぶ) | 競技の順位。同着の次は人数分ずれる |
DENSE_RANK | 1, 1 | 2(飛ばない) | 値の順位。何位の値かを数える |
「1位が2人いたら、次は2位か3位か」 —— 3つの違いは、実質これだけです。
どれを使うべきか
| やりたいこと | 使うもの | 理由 |
|---|---|---|
| 各グループから1行だけ取り出す(重複行の掃除、最新1件だけ残す) | ROW_NUMBER | 必ず1行に決まる。 RANK だと同点のとき2行返る |
| 表彰する・順位を人に見せる | RANK | 同着は同着と出るべき。 1位が2人なら次は3位、が世の中の慣習 |
| 「上位3種類の金額」を取りたい | DENSE_RANK | 人ではなく値を数える。 drnk <= 3 で金額の上位3水準が取れる |
ROW_NUMBERには、1つだけ気をつける点があります。 800 が2人いるとき、「佐藤が1で鈴木が2」になる根拠はどこにもありません。ORDER BY SUM(amount) DESCは 800 と 800 の間に順序を持たないので、どちらが 1 になるかは実行のたびに変わり得ます。 必ず決めたいなら、決めるための列を足します。ROW_NUMBER() OVER (PARTITION BY dept ORDER BY SUM(amount) DESC, name ASC)
nameを足した瞬間に、結果は毎回同じになります。 「同点なら名前順」と自分で決めたからです。
そして本題 — 「各部署の1位だけ」を出す
冒頭で書けなかったやつです。型が決まっています。
1. ウィンドウ関数で順位の列を作る → 2. それを丸ごとサブクエリにする → 3. 外側で
WHEREする
SELECT dept, name, total
FROM (
SELECT dept, name, SUM(amount) AS total,
RANK() OVER (PARTITION BY dept ORDER BY SUM(amount) DESC) AS rnk
FROM d_score
GROUP BY dept, name
) t
WHERE rnk = 1
ORDER BY dept;
+--------+--------+-------+
| dept | name | total |
+--------+--------+-------+
| 大阪 | 田中 | 1200 |
| 東京 | 佐藤 | 800 |
| 東京 | 鈴木 | 800 |
+--------+--------+-------+
名前が出ました。 そして東京は2行返っています。 これは不具合ではなく、同点なのだから2人が1位であるという、正しい答えです。
「1部署につき必ず1行」が欲しければ、RANK を ROW_NUMBER に替えるだけです。 どちらが欲しいかを、自分で決めることになります。 その判断ができるようになったのが、この節の成果です。
[スクリーンショット: 03_3つの順位関数.png] ROW_NUMBER / RANK / DENSE_RANK の3列が並び、東京の3行で数字が割れている結果を撮ってください。
⑦ LAG と LEAD で、前の行を横に持ってくる
「前月比」を出したいとします。SQL は本来、行をまたいで「1つ前」を見るのが苦手です。表には「1つ前」という概念が無いからです。
LAG は、その「1つ前」を作ります。
SELECT name, ym, amount,
LAG(amount) OVER (PARTITION BY name ORDER BY ym) AS prev,
LEAD(amount) OVER (PARTITION BY name ORDER BY ym) AS next
FROM d_score
WHERE name = '佐藤'
ORDER BY ym;
+--------+---------+--------+------+------+
| name | ym | amount | prev | next |
+--------+---------+--------+------+------+
| 佐藤 | 2026-01 | 100 | NULL | 120 |
| 佐藤 | 2026-02 | 120 | 100 | 130 |
| 佐藤 | 2026-03 | 130 | 120 | 150 |
| 佐藤 | 2026-04 | 150 | 130 | 140 |
| 佐藤 | 2026-05 | 140 | 150 | 160 |
| 佐藤 | 2026-06 | 160 | 140 | NULL |
+--------+---------+--------+------+------+
prev は1つ上の amount、next は1つ下の amount です。 縦にずれた列が横に並びました。
ORDER BY は「あったほうがいい」ではなく「無いと意味を持たない」
LAG から ORDER BY を外してみます。
SELECT name, ym, amount, LAG(amount) OVER (PARTITION BY name) AS prev
FROM d_score WHERE name = '佐藤';
エラーにはなりません。 MySQL はこのまま実行し、prev に値を入れて返します。ただし、どの行を「1つ前」とみなしたかは決まっていません。 手元で ym の順に並んで見えても、それはたまたまです。
MySQL は止めてくれません。 そして、止めてくれないことが一番危険です。間違った結果が、エラーも出ずに返ってくるからです。
理由は単純です。「1つ前の行」は、並べて初めて決まります。
テーブルの中の行に、もともとの順番はありません。
idの順に入っているように見えるのはたまたまで、データベースは 「そう並べろ」と言われない限り、どんな順で返しても正しいことになっています。つまり
ORDER BYを書かないLAGは、「何かの1つ前」ではなく「何でもいいから1つ前」です。 数字は出ます。意味がありません。 これが一番たちの悪い壊れ方です。
PARTITION BY name も同じくらい大事です。 これを外すと、佐藤の1月の「前月」が、鈴木の6月になります。人が変わったところで区切りが要ります。
前月比を出す
SELECT name, ym, amount,
amount - LAG(amount) OVER (PARTITION BY name ORDER BY ym) AS diff,
ROUND(amount / LAG(amount) OVER (PARTITION BY name ORDER BY ym) * 100, 1) AS pct
FROM d_score
WHERE name = '佐藤'
ORDER BY ym;
+--------+---------+--------+------+-------+
| name | ym | amount | diff | pct |
+--------+---------+--------+------+-------+
| 佐藤 | 2026-01 | 100 | NULL | NULL |
| 佐藤 | 2026-02 | 120 | 20 | 120.0 |
| 佐藤 | 2026-03 | 130 | 10 | 108.3 |
| 佐藤 | 2026-04 | 150 | 20 | 115.4 |
| 佐藤 | 2026-05 | 140 | -10 | 93.3 |
| 佐藤 | 2026-06 | 160 | 20 | 114.3 |
+--------+---------+--------+------+-------+
1行目が NULL です。 これは正しい動作です。1月の前月は、このデータの中に存在しません。
最初の行の NULL を埋める(そして、埋めていいのか考える)
LAG は引数を3つ取れます。LAG(列, 何行前, 無かったときの値) です。
SELECT name, ym, amount,
LAG(amount, 1, 0) OVER (PARTITION BY name ORDER BY ym) AS prev0,
amount - LAG(amount, 1, 0) OVER (PARTITION BY name ORDER BY ym) AS diff0
FROM d_score
WHERE name = '佐藤'
ORDER BY ym;
+--------+---------+--------+-------+-------+
| name | ym | amount | prev0 | diff0 |
+--------+---------+--------+-------+-------+
| 佐藤 | 2026-01 | 100 | 0 | 100 |
| 佐藤 | 2026-02 | 120 | 100 | 20 |
| 佐藤 | 2026-03 | 130 | 120 | 10 |
| 佐藤 | 2026-04 | 150 | 130 | 20 |
| 佐藤 | 2026-05 | 140 | 150 | -10 |
| 佐藤 | 2026-06 | 160 | 140 | 20 |
+--------+---------+--------+-------+-------+
NULL は消えました。でも、1月の増加が 100 になりました。
1月は「100 増えた」のではありません。「前月が分からない」のです。
NULLを 0 で埋めるのは、「分からない」を「ゼロだった」に書き換える行為です。 合計を出す集計なら害はありませんが、増減や比率では嘘になります。
割合のほうは、0 で埋めるともっと分かりやすく壊れます。
SELECT ym, ROUND(amount / LAG(amount, 1, 0) OVER (PARTITION BY name ORDER BY ym) * 100, 1) AS pct
FROM d_score WHERE name = '佐藤' ORDER BY ym LIMIT 2;
+---------+-------+
| ym | pct |
+---------+-------+
| 2026-01 | NULL |
| 2026-02 | 120.0 |
+---------+-------+
0 で割ったので、結局 NULL に戻りました。(MySQL は 0 除算をエラーにせず NULL を返します)
既定値を渡すのは、「無い」を「この値」と読み替えてよいと自分で判断したときだけにしてください。判断できないなら、NULL のまま出すのが正直です。
⑧ 累計 — そして、フレームの罠を踏む
ORDER BY を付けた SUM は、累計になります。
SELECT name, ym, amount,
SUM(amount) OVER (PARTITION BY name ORDER BY ym) AS cum
FROM d_score
WHERE name = '佐藤'
ORDER BY ym;
+--------+---------+--------+------+
| name | ym | amount | cum |
+--------+---------+--------+------+
| 佐藤 | 2026-01 | 100 | 100 |
| 佐藤 | 2026-02 | 120 | 220 |
| 佐藤 | 2026-03 | 130 | 350 |
| 佐藤 | 2026-04 | 150 | 500 |
| 佐藤 | 2026-05 | 140 | 640 |
| 佐藤 | 2026-06 | 160 | 800 |
+--------+---------+--------+------+
きれいに出ました。 ORDER BY を書いたときの既定のフレームが「先頭から、いまの行まで」だからです。
ここで終わると、あとで確実に事故ります。
罠 — ORDER BY した列に同じ値があるとき
「売った人の多い順に累計して、上位何人でどこまで行くか」を出します。さっきの東京、800 が2人です。
SELECT name, total,
SUM(total) OVER (ORDER BY total DESC) AS cum
FROM (SELECT name, SUM(amount) AS total FROM d_score WHERE dept = '東京' GROUP BY name) t;
+--------+-------+------+
| name | total | cum |
+--------+-------+------+
| 佐藤 | 800 | 1600 |
| 鈴木 | 800 | 1600 |
| 高橋 | 630 | 2230 |
+--------+-------+------+
1行目の累計が、いきなり 1600 です。
800 の人が1人目なのに、2人分が足されています。 「上位1人で 1600」は明らかにおかしい。
原因はこれです。
ORDER BYを書いてフレームを書かないとき、既定はRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWです。RANGEは「行」ではなく「値」で範囲を決めます。 だからORDER BYの値が同じ行は、全部まとめて「いまの行」の扱いになります。 佐藤の行を計算するとき、同じ 800 の鈴木も一緒に入ってしまうわけです。
さっきの佐藤の月別累計が正しかったのは、ym に重複が無かったからです。たまたまです。
直す — ROWS と書く
SELECT name, total,
SUM(total) OVER (ORDER BY total DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum
FROM (SELECT name, SUM(amount) AS total FROM d_score WHERE dept = '東京' GROUP BY name) t;
+--------+-------+------+
| name | total | cum |
+--------+-------+------+
| 佐藤 | 800 | 800 |
| 鈴木 | 800 | 1600 |
| 高橋 | 630 | 2230 |
+--------+-------+------+
直りました。
| 書き方 | 範囲の決め方 | 同じ値の行 |
|---|---|---|
RANGE(既定) | 値で決める | まとめて1つとして扱われる |
ROWS | 物理的な行数で決める | 1行ずつ別に扱われる |
累計を書くときは、
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWを明示してください。 長いですが、書かないと「同じ値が入ってきたときだけ壊れる」という、一番見つけにくい壊れ方をします。 テストデータに重複が無いと、本番で初めて表面化します。
⑨ 移動平均 — フレームを自分で切る
ROWS を覚えると、「直近3か月の平均」のようなものが書けます。
東京の月別合計に対して、3か月移動平均を出します。
SELECT ym, SUM(amount) AS m,
ROUND(AVG(SUM(amount)) OVER (ORDER BY ym ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS ma3
FROM d_score
WHERE dept = '東京'
GROUP BY ym
ORDER BY ym;
+---------+-----+-------+
| ym | m | ma3 |
+---------+-----+-------+
| 2026-01 | 320 | 320.0 |
| 2026-02 | 320 | 320.0 |
| 2026-03 | 340 | 326.7 |
| 2026-04 | 400 | 353.3 |
| 2026-05 | 410 | 383.3 |
| 2026-06 | 440 | 416.7 |
+---------+-----+-------+
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = 「2行前から、いまの行まで」の3行です。
最初の2行に注目してください。
- 1月は、前が無いので1行だけの平均(320)
- 2月は、2行だけの平均((320+320)/2 = 320)
- 3月から、やっと3行((320+320+340)/3 = 326.7)
端では、要求した行数が揃いません。 そして SQL は何も言わずに、揃った分だけで平均します。 「最初の2か月の移動平均は、移動平均ではない」ことを知らずにグラフにすると、冒頭だけ形の違う線が出ます。 揃っていない期間を出したくないなら、
COUNT(*) OVER (同じフレーム) = 3の行だけ残すのが素直です。
AVG(SUM(amount)) という二重の書き方にも触れておきます。これは打ち間違いではありません。
内側の
SUMがGROUP BY ymの集計で、まず月ごとに1行へ潰します。 外側のAVG(...) OVER (...)は、その潰れたあとの6行に対して動きます。 集計が先、ウィンドウが後。この順番は次の節でもう一度出てきます。
⑩ WHERE の中では使えない(好き嫌いではなく、順番の話)
さっきサブクエリで包んだのは、面倒だからではありません。包まないと動かないからです。
SELECT dept, name, SUM(amount) AS total
FROM d_score
WHERE RANK() OVER (PARTITION BY dept ORDER BY SUM(amount) DESC) = 1
GROUP BY dept, name;
ERROR 3593 (HY000): You cannot use the window function 'rank' in this context.
HAVING に置いても同じです。
SELECT dept, name, SUM(amount) AS total
FROM d_score
GROUP BY dept, name
HAVING RANK() OVER (PARTITION BY dept ORDER BY SUM(amount) DESC) = 1;
ERROR 3593 (HY000): You cannot use the window function 'rank' in this context.
理由は、SQL が各句を評価する順番です。 書く順番とは違います。
| 順 | 句 | このときウィンドウ関数は |
|---|---|---|
| 1 | FROM / JOIN | まだ |
| 2 | WHERE | まだ計算されていない → 使えない |
| 3 | GROUP BY | まだ |
| 4 | HAVING | まだ → 使えない |
| 5 | ウィンドウ関数 | ここで計算される |
| 6 | SELECT の列 | 使える |
| 7 | ORDER BY | 使える |
| 8 | LIMIT | 使える |
WHEREは「どの行をウィンドウ関数に渡すか」を決める側です。 そのWHEREの中でウィンドウ関数の結果を使おうとすると、「順位を出すために順位が要る」ことになります。 禁止されているのではなく、順番として不可能です。
ORDER BY では使えます。5 のあとだからです。
SELECT dept, name, SUM(amount) AS total
FROM d_score GROUP BY dept, name
ORDER BY RANK() OVER (PARTITION BY dept ORDER BY SUM(amount) DESC), dept;
回避の型は2つ(MySQL では1つ)
| 方法 | 使えるか | 書き方 |
|---|---|---|
| サブクエリで包む | MySQL / PostgreSQL 両方で使える | 内側で順位の列を作り、外側で WHERE |
QUALIFY | MySQL にも PostgreSQL にも無い | 一部のデータベースにある専用の句 |
QUALIFY は「ウィンドウ関数専用の WHERE」で、こう書ける言語仕様です。
-- MySQL では動きません(こういうものがある、という紹介です)
SELECT dept, name, SUM(amount) AS total
FROM d_score GROUP BY dept, name
QUALIFY RANK() OVER (PARTITION BY dept ORDER BY SUM(amount) DESC) = 1;
便利ですが、あるデータベースとないデータベースがあります。 MySQL で打つと文法エラーです。MySQL と PostgreSQL では、サブクエリで包む型を手癖にしてください。
読みやすくしたいときは、WITH で名前を付けます(MySQL 8.0 以降で使えます)。
WITH ranked AS (
SELECT dept, name, SUM(amount) AS total,
RANK() OVER (PARTITION BY dept ORDER BY SUM(amount) DESC) AS rnk
FROM d_score GROUP BY dept, name
)
SELECT dept, name, total FROM ranked WHERE rnk = 1 ORDER BY dept;
やっていることはサブクエリと同じです。 入れ子が浅く見えるだけですが、式が長くなるほど効きます。
⑪ タダではない — 並べ替えが要るという話
ウィンドウ関数は、必ず「並べてから」計算します。 つまりソートが要ります。
EXPLAIN SELECT name, ym, amount,
LAG(amount) OVER (PARTITION BY name ORDER BY ym) AS prev
FROM d_score;
+----+-------------+---------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | type | key | ref | rows | filtered | Extra |
+----+-------------+---------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | d_score | ALL | NULL | NULL | 30 | 100.00 | Using temporary; Using filesort |
+----+-------------+---------+------+---------+------+------+----------+---------------------------------+
見るのは Extra の2語です。
Using filesort= 並べ替えをしているUsing temporary= 途中結果を置く一時テーブルを作っている
30行なので一瞬です。100万行だと一瞬ではありません。
PARTITION BY name ORDER BY ym に合わせた索引を張ると、その並びで読めるので、並べ替えを省ける可能性が出ます。
CREATE INDEX idx_name_ym ON d_score (name, ym);
EXPLAIN SELECT name, ym, amount,
LAG(amount) OVER (PARTITION BY name ORDER BY ym) AS prev
FROM d_score;
ただし、30行では変わらないことのほうが多いです。「全部読んだほうが速い」と判断されるからです。索引が効くかどうかは、行数を増やして測らないと分かりません。
ここは本線の速くする前に必ず測る — パフォーマンス改善とEXPLAINの回とまったく同じ話です。
EXPLAINの読み方、索引の張り方、そして「測ってから直す」という順番は、あちらに全部あります。 この記事で足したのは「ウィンドウ関数はソートを呼ぶ」という1点だけです。
実際にかかった時間を見たいときは EXPLAIN ANALYZE が使えます(MySQL 8.0.18 以降)。
EXPLAIN ANALYZE SELECT name, ym, amount,
LAG(amount) OVER (PARTITION BY name ORDER BY ym) AS prev
FROM d_score;
Window aggregate という行が出てきます。そこが、ウィンドウ関数に使った時間です。
それでも、多くの場合ウィンドウ関数のほうが速いです。 比べる相手が自己結合だからです。同じ「部署内の順位」を、ウィンドウ関数なしで書くとこうなります。
SELECT a.dept, a.name, a.total,
(SELECT COUNT(*) + 1 FROM
(SELECT dept, name, SUM(amount) AS total FROM d_score GROUP BY dept, name) b
WHERE b.dept = a.dept AND b.total > a.total) AS rnk
FROM (SELECT dept, name, SUM(amount) AS total FROM d_score GROUP BY dept, name) a
ORDER BY a.dept, a.total DESC;
結果は RANK() と同じです。 ただし同じ集計を2回書いていて、行ごとに内側を数え直します。 読みにくく、行が増えるほど不利です。5.7 で戦うというのは、これを書くということです。
つまずいたところ
書いた順に、実際に詰まった場所を残します。
1. OVER () の空のかっこを省いて怒られた
SELECT SUM(amount) OVER FROM d_score;
ERROR 1064 (42000): You have an error in your SQL syntax; ... near 'FROM d_score' at line 1
中身が無くても () は要ります。 OVER () です。「省略できるのは中身であって、かっこではない」と覚えました。
2. RANK を RANK() と書かずに落ちた
SELECT RANK OVER (ORDER BY amount) FROM d_score;
RANK は関数なので () が要ります。 引数を取らないので空に見えて、つい落とします。ROW_NUMBER() DENSE_RANK() も同じです。
3. 累計が最初から合わなくて、SUM を疑った
⑧ の 1600 です。 最初、サブクエリの GROUP BY を間違えたと思って30分ほどそこを見ていました。
間違っていたのは SUM ではなくフレームでした。 気づけたのは、ym(重複なし)では正しくて total(重複あり)では狂う、という差に目が行ったからです。
「同じ書き方なのに、あるデータでだけ狂う」ときは、たいてい重複が絡んでいます。 ウィンドウ関数では、それが
RANGEとROWSの違いとして出ます。
4. PARTITION BY を書き忘れて、他人の売上が混ざった
SELECT name, ym, amount, LAG(amount) OVER (ORDER BY ym) AS prev FROM d_score ORDER BY name, ym;
エラーは出ません。数字も出ます。 ただ、佐藤の1月の prev に、別の人の値が入っています。
一番怖いのはこれでした。 落ちてくれれば気づきます。それらしい数字が出るので気づきません。 以来、LAG / LEAD / 累計を書いたら、「この区切りは誰単位か」を口に出して確認するようにしました。
5. ORDER BY ym DESC にしたら累計が逆から積み上がった
当たり前でした。 累計は「先頭からいまの行まで」なので、DESC にすれば「最後から」になります。 ORDER BY は表示順ではなく、計算に使う順です。 表示順を変えたいだけなら、一番外側の ORDER BY を変えます。
後片づけ
この記事で作ったものは、練習用のデータベース1つに閉じています。 丸ごと消せます。
いま mysql の中にいるはずです。
DROP DATABASE deepdb;
消えたことを確認します。
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| appdb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
deepdb が一覧から消えていれば完了です。 抜けます。
EXIT;
appdbなど、本線で作ったものはそのままです。 この記事は最初からdeepdbの中だけで作業しました。DROP DATABASEは確認を聞いてきません。 打つ前に、名前がdeepdbであることを必ず目で見てください。
途中で作った索引も deepdb と一緒に消えているので、個別に消す作業はありません。
まとめ — 理解できたこと
ウィンドウ関数は「難しい新しい関数」ではありませんでした。 知っている SUM や COUNT に、OVER を付けて「潰さないでくれ」と頼むだけでした。
GROUP BYは行を潰す。だから潰された行のnameは返せない。 これは制限ではなく定義ですOVER ()を付けると行数が変わらない。 30行入れたら30行返る。集計結果は横に1列足されるPARTITION BYはGROUP BYに似ているが、区切るだけで潰さないROW_NUMBER/RANK/DENSE_RANKの違いは、同点のときだけ出る。 同点の無いデータで試すと違いは見えません。
1行だけ欲しいなら ROW_NUMBER、順位を見せるなら RANK、値の種類を数えるなら DENSE_RANK
ROW_NUMBERは同点の並びを保証しない。 決めたいならORDER BYに決める列を足すLAG/LEADはORDER BYが無いと意味を持たない。 そしてPARTITION BYを忘れると、他人の値が黙って混ざるLAG(x, 1, 0)の既定値は、「分からない」を「0だった」に書き換える。 増減や比率では嘘になります- 累計のフレームの既定は
RANGE。同じ値の行がまとめて足される。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWと書いて潰す - 移動平均は端で行数が揃わない。 SQL は何も言わずに、揃った分だけで平均します
WHEREとHAVINGではウィンドウ関数を使えない。 計算されるのがそのあとだからです。サブクエリかWITHで包む- ソートが必要なので、タダではない。
EXPLAINのUsing filesortで見えます
一番効いたのは、④ で同じ SUM を2回打って 1 row と 30 rows を並べたところでした。 そこから先は、OVER の中に何を書くかの話でしかありませんでした。
この記事の SQL は、AUTO_INCREMENT と CHARACTER SET utf8mb4 を除けば、ほぼ PostgreSQL でも通ります。 ただし割り算は別です。 PostgreSQL では整数どうしの / が切り捨ての割り算になるので、割合や前月比の式(amount / SUM(amount) OVER () など)は 0 や切り捨てた値になります。amount::numeric のように型を変えてから割ってください。 ウィンドウ関数は特定のデータベースの機能ではなく、SQL の標準だからです。 2つ目のデータベースを触る話は、本線のMySQLとPostgreSQLの違いは1割 — 移して確かめるにあります。
参考リンク
- roadmap.sh — SQL
- roadmap.sh — Backend
- MySQL 公式ドキュメント(Reference Manual の Window Functions の章)
- PostgreSQL — ウィンドウ関数のチュートリアル
- PostgreSQL — ウィンドウ関数の一覧
- SQLite — Window Functions(フレームの説明が短くて読みやすい)
- 本線: プロセスを止めても消えない場所 — MySQLとSQLの基本
- 本線: 速くする前に必ず測る — パフォーマンス改善とEXPLAIN
- 本線: MySQLとPostgreSQLの違いは1割 — 移して確かめる
次に読む
この記事は 【深掘り】SQL の 10 / 17 本目です。
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
- ROW_NUMBER / RANK / DENSE_RANK
- LAG / LEAD と フレーム指定
この記事は、上の項目を実際に手を動かして確かめる回です。

コメント