2-3-26 正順位と逆順位

SQLパズル

RevSortTestテーブル
Val
---
 10
 10
 10
 20
 20
 30
 40
 40
 40

Row_Number関数での順位付けと、count関数による全体の件数
および
Dense_Rank関数での順位付けと、count関数による全体の件数(重複を排除)
との間で成立する式について考察します。

データ作成スクリプト

create table RevSortTest as
select 10 as Val from dual
union all select 10 from dual
union all select 10 from dual
union all select 20 from dual
union all select 20 from dual
union all select 30 from dual
union all select 40 from dual
union all select 40 from dual
union all select 40 from dual;

SQL

SQL> select Val,
  2  Row_Number() over(order by Val asc ) as "Row_Number",
  3  Row_Number() over(order by Val desc) as "RevRow_Number",
  4  count(*) over() as "Count"
  5  from (select distinct Val from RevSortTest)
  6  order by Val;

Val■Row_Number■RevRow_Number■Count
---■----------■-------------■-----
 10■         1■            4■    4
 20■         2■            3■    4
 30■         3■            2■    4
 40■         4■            1■    4

SQL> select Val,
  2  Dense_Rank() over(order by Val asc ) as "Dense_Rank",
  3  Dense_Rank() over(order by Val desc) as "RevDense_Rank",
  4  count(Distinct Val) over() as "DistinctCount"
  5  from RevSortTest
  6  order by Val;

Val■Dense_Rank■RevDense_Rank■DistinctCount
---■----------■-------------■-------------
 10■         1■            4■            4
 10■         1■            4■            4
 10■         1■            4■            4
 20■         2■            3■            4
 20■         2■            3■            4
 30■         3■            2■            4
 40■         4■            1■            4
 40■         4■            1■            4
 40■         4■            1■            4

解説

Row_Number関数での順位付けと、count関数による全体の件数
および
Dense_Rank関数での順位付けと、count関数による全体の件数(重複を排除)
において

正順位 + 逆順位 = 件数 + 1
が成り立ちます。

集合の要素数の公式
n(A∪B)=n(A)+n(B)-n(A∩B)
の感覚に近いですね。

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

分析関数ROW_NUMBERの使用例 - 非決定的な書き方 (非決定的のため、結果が不定) 
をふまえて、Row_Number関数を1回しか使わないように、
正順位 + 逆順位 = 件数 + 1
を移項した
逆順位 = 1 - 正順位 + 件数
を使用したSQL

SQL> select Val,
  2  Row_Number() over(order by Val) as "Row_Number",
  3  1-Row_Number() over(order by Val)+count(*) over() as "RevRow_Number",
  4  count(*) over() as "Count"
  5  from RevSortTest
  6  order by Val;

VAL■Row_Number■RevRow_Number■Count
---■----------■-------------■-----
 10■         1■            9■    9
 10■         2■            8■    9
 10■         3■            7■    9
 20■         4■            6■    9
 20■         5■            5■    9
 30■         6■            4■    9
 40■         7■            3■    9
 40■         8■            2■    9
 40■         9■            1■    9

ソートキーによって、一意にならないRow_Numberを2回使用したSQL
この場合は、
正順位 + 逆順位 = 件数 + 1
が成立しない行があります。

SQL> select Val,
  2  Row_Number() over(order by Val asc ) as "Row_Number",
  3  Row_Number() over(order by Val desc) as "RevRow_Number",
  4  count(*) over() as "Count"
  5  from RevSortTest
  6  order by Val;

Val■Row_Number■RevRow_Number■Count
---■----------■-------------■-----
 10■         1■            7■    9
 10■         2■            8■    9
 10■         3■            9■    9
 20■         4■            5■    9
 20■         5■            6■    9
 30■         6■            4■    9
 40■         7■            1■    9
 40■         8■            2■    9
 40■         9■            3■    9

2-3-6 メジアン(中央値)を取得
2-3-24 逆ソート(リバースソート)
10-32 distinct後の件数を取得

分析関数の衝撃6 (応用編)

---