Row_Numberとrankとdense_rank
順位や連番を求める
順位や連番を求めるのに使うのが、Row_Number関数とrank関数とdense_rank関数です。
create table RankingSample(ID,Score) as
select 1,100 from dual union all
select 1, 90 from dual union all
select 1, 90 from dual union all
select 1, 80 from dual union all
select 1, 70 from dual union all
select 2,100 from dual union all
select 2,100 from dual union all
select 2,100 from dual union all
select 2, 90 from dual union all
select 2, 80 from dual;
select ID,Score,
Row_Number() over(partition by ID order by Score desc) as "Row_Number",
rank() over(partition by ID order by Score desc) as "rank",
dense_rank() over(partition by ID order by Score desc) as "dense_rank"
from RankingSample
order by ID,Score desc;
出力結果
ID Score Row_Number rank dense_rank
-- ----- ---------- ---- ----------
1 100 1 1 1
1 90 2 2 2
1 90 3 2 2
1 80 4 4 3
1 70 5 5 4
2 100 1 1 1
2 100 2 1 1
2 100 3 1 1
2 90 4 4 2
2 80 5 5 3
Row_Number関数は、1から始まって、必ず連番になります。 rank関数は、同点があると順位が飛びます。 dense_rank関数は、同点があっても順位が飛びません。denseは、形容詞で密集したという意味です。
SQLのイメージは下記です。partition by IDで、IDごとに区切る赤線を引いて、 Row_Number関数,rank関数,dense_rank関数で青線と黄緑線を引いてます。
LagとLead
指定したソートキーでの、前後の行の値を取得
指定したソートキーでの、前の行の値が欲しい時に使うのが、Lag関数で、後の行の値が欲しい時に使うのが、Lead関数です。
create table LagLeadSample(ID,SortKey,Val) as
select 111,1,99 from dual union all
select 111,3,88 from dual union all
select 111,7,77 from dual union all
select 111,9,66 from dual union all
select 222,2,55 from dual union all
select 222,4,44 from dual union all
select 222,5,33 from dual;
IDごとにSortKeyの昇順で、前の行のValをPrev列として求め、後の行のValをNext列として求めてみます
select ID,SortKey,
Lag(Val) over(partition by ID order by SortKey) as Prev,
Val,
Lead(Val) over(partition by ID order by SortKey) as Next
from LagLeadSample
order by ID,SortKey;
出力結果
ID SortKey Prev Val Next
--- ------- ---- --- ----
111 1 null 99 88
111 3 99 88 77
111 7 88 77 66
111 9 77 66 null
222 2 null 55 44
222 4 55 44 33
222 5 44 33 null
SQLのイメージは下記です。partition by IDで、IDごとに区切る赤線を引いて、Lag関数で青線、Lead関数で黄緑線を引いてます。
sum(Val) over(order by SortKey)
累計を求める
累計を求める時に使われるのが、order byを指定した分析関数のsum関数です。
create table RunSumSample(ID,SortKey,Val) as
select 111,1, 1 from dual union all
select 111,3, 2 from dual union all
select 111,5, 6 from dual union all
select 222,1,10 from dual union all
select 222,2,20 from dual union all
select 222,3,60 from dual union all
select 222,4, 6 from dual union all
select 333,1, 1 from dual union all
select 333,2, 2 from dual union all
select 333,3,20 from dual union all
select 333,3,30 from dual;
IDごとに、SortKeyの昇順でValの累計を求めてみます。
select ID,SortKey,Val,
sum(Val) over(partition by ID order by SortKey) as runSum
from RunSumSample;
出力結果
ID SortKey Val runSum
--- ------- --- ------
111 1 1 1
111 3 2 3
111 5 6 9
222 1 10 10
222 2 20 30
222 3 60 90
222 4 6 96
333 1 1 1
333 2 2 3
333 3 20 53
333 3 30 53
SQLのイメージは下記です。partition by IDで、IDごとに区切る赤線を引いて、sum(Val) over(partition by ID order by SortKey)で黄緑線を引いてます。
