3-22 相関サブクエリで順位付け

SQLパズル

Testテーブル
科目  点数
----  ----
英語    90
英語    90
英語    90
英語    80
英語    80
英語    70
数学    90
数学    80
数学    70
数学    70
数学    60
数学    60

科目ごとに順位を付けて出力する。

出力結果
科目  点数  Rank  dense_Rank  Row_Number
----  ----  ----  ----------  ----------
英語    90     1           1           1
英語    90     1           1           2
英語    90     1           1           3
英語    80     4           2           4
英語    80     4           2           5
英語    70     6           3           6
数学    90     1           1           1
数学    80     2           2           2
数学    70     3           3           3
数学    70     3           3           4
数学    60     5           4           5
数学    60     5           4           6

データ作成スクリプト

create table Test(
科目 char(4),
点数 number(2));

insert into Test values('英語',90);
insert into Test values('英語',90);
insert into Test values('英語',90);
insert into Test values('英語',80);
insert into Test values('英語',80);
insert into Test values('英語',70);
insert into Test values('数学',90);
insert into Test values('数学',80);
insert into Test values('数学',70);
insert into Test values('数学',70);
insert into Test values('数学',60);
insert into Test values('数学',60);
commit;

SQL

--■■■相関サブクエリを使う方法■■■
select 科目,点数,
(select count(*)+1
   from Test b
  where b.科目 = a.科目
    and b.点数 > a.点数) as "Rank",
(select count(distinct b.点数)+1
   from Test b
  where b.科目 = a.科目
    and b.点数 > a.点数) as "dense_Rank",
(select count(*)+1
   from Test b
  where b.科目 = a.科目
    and (b.点数 > a.点数 or b.点数 = a.点数 and b.RowID > a.RowID)) as "Row_Number"
from Test a
order by 科目,"Row_Number";

--■■■分析関数を使う方法■■■
select 科目,点数,
Rank() over(partition by 科目 order by 点数 desc) as "Rank",
dense_Rank() over(partition by 科目 order by 点数 desc) as "dense_Rank",
Row_Number() over(partition by 科目 order by 点数 desc) as "Row_Number"
from Test
order by 科目,"Row_Number";

解説

相関サブクエリで、Rank関数、dense_Rank関数、Row_Number関数
を模倣できます。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
自己結合とhavingを使って、
Rank,dense_Rank,Row_Number
が3のデータを出力する方法は、下記となります。

SQL> select a.科目,a.点数
  2    from test a,test b
  3   where a.科目 = b.科目
  4     and a.点数 < b.点数
  5  group by a.科目,a.点数,a.RowID
  6  having count(*)+1 = 3
  7  order by a.科目,a.点数;

科目  点数
----  ----
数学    70
数学    70

SQL> select a.科目,a.点数
  2    from test a,test b
  3   where a.科目 = b.科目
  4     and a.点数 < b.点数
  5  group by a.科目,a.点数,a.RowID
  6  having count(distinct b.点数)+1 = 3
  7  order by a.科目,a.点数;

科目  点数
----  ----
英語    70
数学    70
数学    70

SQL> select a.科目,a.点数
  2    from test a,test b
  3   where a.科目 = b.科目
  4     and (a.点数 < b.点数 or a.点数 = b.点数 and a.RowID < b.RowID)
  5  group by a.科目,a.点数,a.RowID
  6  having count(*)+1 = 3
  7  order by a.科目,a.点数;

科目  点数
----  ----
英語    90
数学    70

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

3-4 順位にupdate
3-5 順位(DENSE)が指定した範囲のデータを取得
7-14 古のSQL(順位付け)

---