グループごとの順位と、直近3日間の平均
PARTITION BY は、窓関数をグループごとに分けて計算するための書き方です。
クラスごとの順位や、直近3日間の売上の平均といった分析が、この書き方で書けます。
このレッスンでは、グループの分け方と、平均を取る範囲の決め方を学びます。
このレッスンのゴール
メモ
グループの中で順位を付け、動く範囲で平均を出せる。
重要な言葉
| ことば | いみ | れい |
|---|---|---|
| パーティション | 窓関数が計算するときの、行のグループ | クラスごと |
| PARTITION BY | 行をグループに分ける、OVER の中の書き方 | |
| フレーム | 1行ごとに、計算に使う前後の行の範囲 | |
| ROWS BETWEEN | フレームを、行の数で決める書き方 | |
| 移動平均 | 範囲をずらしながら取る平均 | 直近3日間の平均 |
| row_number | 行に1から順に番号を付ける窓関数 |
学生が入力するセルは、このプレビューでは空白です。
PARTITION BY で、グループごとに計算する
over (partition by class_name ...) と書くと、クラスごとに行が分かれます。計算は、分かれた中だけで行われます。
順位は、クラスが変わるたびに1位からやり直しです。行の数はそのままです。
例: クラスごとの順位
| クラス | 生徒 | 点数 | クラス内の順位 |
|---|---|---|---|
| A組 | 石川 | 91 | 1 |
| A組 | 青木 | 82 | 2 |
| B組 | 木村 | 93 | 1 |
| B組 | 岡田 | 88 | 2 |
学生が入力するセルは、このプレビューでは空白です。
試す: クラス内の順位
-- クラスごとに、点数の高い順に順位を付けます。
select class_name, student, score,
rank() over (partition by class_name order by score desc) as rank_in_class
from marks
order by class_name, rank_in_class;
-- partition by class_name を消すと、順位は学年全体の順位になります。
試す: クラスの平均との差
-- クラスの平均点と、自分との差を並べます。
select class_name, student, score,
avg(score) over (partition by class_name) as class_avg,
score - avg(score) over (partition by class_name) as diff
from marks
order by class_name, score desc;
上位2人は、順位を付けてから絞る
窓関数の結果は、同じ select の where では使えません。where のほうが先に働くからです。
そこで、順位を付ける select を括弧で包み、外側の where で絞ります。同点のない列には row_number が便利です。
試す: クラスごとの上位2人
-- クラスごとに、上位2人を取り出します。
select class_name, student, score
from (
select class_name, student, score,
row_number() over (partition by class_name order by score desc) as rn
from marks
)
where rn <= 2
order by class_name, score desc;
ROWS BETWEEN で、平均を取る範囲を決める
rows between 2 preceding and current row は、「2行前から今の行まで」という意味です。つまり3行です。
日ごとに範囲がずれるので、移動平均になります。最初の2日は、前の行が足りません。あるだけの行で平均します。
例: 直近3日間の平均の計算
| 日付 | 売上 | 使う行 | 平均(四捨五入) |
|---|---|---|---|
| 4月1日 | 42000 | 4月1日だけ | 42000 |
| 4月2日 | 38500 | 4月1日から2日 | 40250 |
| 4月3日 | 51000 | 4月1日から3日 | 43833 |
| 4月4日 | 51000 | 4月2日から4日 | 46833 |
学生が入力するセルは、このプレビューでは空白です。
試す: 直近3日間の移動平均
-- 直近3日間の売上の平均を出します。四捨五入して整数にします。
select sale_date, amount,
round(avg(amount) over (
order by sale_date
rows between 2 preceding and current row
)) as avg3
from daily_sales
order by sale_date;
-- 2 preceding を 1 preceding に変えて、もう一度実行しましょう。
よくある間違い
注意
where rn <= 2 を、rn を作った同じ select の中に書くと、エラーになります。窓関数の結果は、括弧で包んだ外側で絞ります。
さらに挑戦
移動平均の範囲を「前後1日ずつ」にするには、rows between 1 preceding and 1 following と書きます。直近3日間の平均との違いを、自分の言葉で説明してください。
そのあと、最初の日と最後の日の平均が何行から出ているかを、表で確かめましょう。