2-3-25 集合関数のdense_rank関数

SQLパズル

deriveCountテーブル
Col1  Col2
----  ----
   1     2
   1     1
   1     1
   1     1
   1     1
   1     1
   1     2
   1     3
   3     1
   3     2
   3     8
   4     1
   4     1
   4     1
   4     1
   6     1
   7     1

Col1の最大値と、Col2の最大値と、
重複を排除したCol1の数と、
重複を排除したCol2の数と、
重複を排除したCol1,Col2の組み合わせの数
を出力する。

出力結果
maxCol1  maxCol2  DistinctCol1  DistinctCol2  DistinctCol1Col2
-------  -------  ------------  ------------  ----------------
      7        8             5             4                 9

データ作成スクリプト

create table deriveCount(Col1,Col2) as
select 1,1 from dual union all
select 1,1 from dual union all
select 1,1 from dual union all
select 1,1 from dual union all
select 1,1 from dual union all
select 1,1 from dual union all
select 1,2 from dual union all
select 1,3 from dual union all
select 3,1 from dual union all
select 3,2 from dual union all
select 3,8 from dual union all
select 4,1 from dual union all
select 4,1 from dual union all
select 4,1 from dual union all
select 4,1 from dual union all
select 6,1 from dual union all
select 7,1 from dual;

SQL

--■■■方法1■■■
select max(Col1) as maxCol1,
max(Col2) as maxCol2,
-1+dense_rank(null) within group(order by Col1) as DistinctCol1,
-1+dense_rank(null) within group(order by Col2) as DistinctCol2,
-1+dense_rank(null,null) within group(order by Col1,Col2) as DistinctCol1Col2
  from deriveCount;

--■■■方法2■■■
select max(Col1) as maxCol1,
max(Col2) as maxCol2,
count(distinct Col1) as DistinctCol1,
count(distinct Col2) as DistinctCol2,
-1+dense_rank(null,null) within group(order by Col1,Col2) as DistinctCol1Col2
  from deriveCount;

--■■■dump関数を使う方法■■■
select max(Col1) as maxCol1,
max(Col2) as maxCol2,
count(distinct Col1) as DistinctCol1,
count(distinct Col2) as DistinctCol2,
count(distinct dump(Col1,16) || dump(Col2,16)) as DistinctCol1Col2
  from deriveCount;

解説

集合関数のdense_rank関数を使ってます。
列値が非nullでない場合は、case式でnullの有無を調べる必要があるでしょう。

ちなみに、集合関数のRank関数というのもあります。
集合関数のRow_Number関数は、10gR2の時点では、ないみたいです。

-1+dense_rank(null1,null) within group(order by 列1,列2)
によって、count(distinct 列1,列2) を求めることができるのです。

分析関数のcount(distinct 列1,列2) の代用法は、
2-3-26 正順位と逆順位で扱ってます。

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

dump関数を使う方法は、Laurent Schneiderさんの本
の113ページに載っていたものです。

---