行を減らさずに、累計や順位を付ける
窓関数とは、行を減らさずに、累計や順位を各行の横に付ける SQL の機能です。
売上の累計を出したいとき、GROUP BY を使うと、行が1つにまとまってしまいます。窓関数なら、元の行がすべて残ります。
1週間の売上表で試しましょう。
このレッスンのゴール
メモ
行を減らさずに、累計や順位を各行の横に付けられる。
重要な言葉
| ことば | いみ | れい |
|---|---|---|
| 窓関数 | 各行の周りの行を見て、行ごとに値を出す関数 | |
| OVER 句 | 窓関数に、どの行を見るかを教える句 | over (order by sale_date) |
| 累計 | 先頭からその行までを足し合わせた値 | |
| 順位 | 値の大きさの順に付ける番号 | rank() |
| 同順位 | 同じ値の行に、同じ順位が付くこと | |
| lag | 1つ前の行の値を取り出す窓関数 |
学生が入力するセルは、このプレビューでは空白です。
GROUP BY は行をまとめ、窓関数は行を残す
group by は、同じ種類の行を1つにまとめます。行の数は減ります。
窓関数は、集計の結果を、元の行のそれぞれに付けます。行の数は変わりません。
書き方は、関数のあとに over (...) を付けるだけです。
例: 売上と累計
| 日付 | 売上 | 累計 |
|---|---|---|
| 4月1日 | 42000 | 42000 |
| 4月2日 | 38500 | 80500 |
| 4月3日 | 51000 | 131500 |
| 4月4日 | 51000 | 182500 |
学生が入力するセルは、このプレビューでは空白です。
試す: 売上の累計
-- 日ごとの売上に、その日までの累計を付けます。
select sale_date, amount,
sum(amount) over (order by sale_date) as running_total
from daily_sales
order by sale_date;
-- over の中の order by を消して、累計がどう変わるか見ましょう。
OVER の中の ORDER BY が、累計の向きを決める
over (order by sale_date) は、日付の順に並べて、先頭からその行までを足す、という意味です。
order by がないと、窓は表の全部の行になります。どの行にも、同じ合計が付きます。
順位は、同点をどう扱うかで2通りある
rank() は、同点に同じ順位を付けて、次の順位を飛ばします。1位、2位、3位、3位、5位となります。
dense_rank() は、同点でも順位を飛ばしません。1位、2位、3位、3位、4位です。
試す: rank と dense_rank
-- 売上の多い順に、2通りの順位を付けます。
select sale_date, amount,
rank() over (order by amount desc) as rnk,
dense_rank() over (order by amount desc) as drnk
from daily_sales
order by amount desc, sale_date;
例: 2つの順位の違い
| 売上 | rank | dense_rank |
|---|---|---|
| 63000 | 1 | 1 |
| 55000 | 2 | 2 |
| 51000 (2日) | 3 | 3 |
| 47500 | 5 | 4 |
学生が入力するセルは、このプレビューでは空白です。
試す: 前の日との差
-- 前の日の売上と、その差を並べます。
select sale_date, amount,
lag(amount) over (order by sale_date) as prev_amount,
amount - lag(amount) over (order by sale_date) as diff
from daily_sales
order by sale_date;
よくある間違い
注意
累計を出したいのに、over () と書いてしまうことがあります。順番がないので、どの行にも全体の合計が付きます。order by を忘れないでください。
さらに挑戦
各日の売上が、1週間の合計の何%かを出すには、どう書きますか。ヒントは、sum(amount) over () を分母にすることです。
次に、同じ 51000 円の2日が、rank では同じ順位になる理由を、自分の言葉で説明してください。