10-32 distinct後の件数を取得

SQLパズル

DistinctTestテーブル
val1  val2
----  ----
   1     1
   2     2
   3     3
   3     3
   4     4
   4     4
   4     4
   5     1
   5     2
   6     1
   6     1
   6     3
   6     3
   7     1
   7     2
   7     3
   7     3
   7     3
   7     3

select文でdistinctを指定して、
重複行を排除した後の、レコード数を取得する。

出力結果
val1  val2  RecordCount
----  ----  -----------
   1     1           11
   2     2           11
   3     3           11
   4     4           11
   5     1           11
   5     2           11
   6     1           11
   6     3           11
   7     1           11
   7     2           11
   7     3           11

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table DistinctTest as
select 1 as val1,1 as val2 from dual
union all select 2,2 from dual
union all select 3,3 from dual
union all select 3,3 from dual
union all select 4,4 from dual
union all select 4,4 from dual
union all select 4,4 from dual
union all select 5,1 from dual
union all select 5,2 from dual
union all select 6,1 from dual
union all select 6,1 from dual
union all select 6,3 from dual
union all select 6,3 from dual
union all select 7,1 from dual
union all select 7,2 from dual
union all select 7,3 from dual
union all select 7,3 from dual
union all select 7,3 from dual
union all select 7,3 from dual;

SQL

--■■■インラインビューを使う方法■■■
select val1,val2,count(*) over() as RecordCount
from (select distinct val1,val2
        from DistinctTest)
order by val1,val2;

--■■■逆ソートを使う方法■■■
select distinct val1,val2,
-1+dense_rank() over(order by val1 asc ,val2 asc)
  +dense_rank() over(order by val1 desc,val2 desc)
as RecordCount
  from DistinctTest
order by val1,val2;

--■■■分析関数を使う方法■■■
select val1,val2,count(*) over() as RecordCount
  from DistinctTest
group by val1,val2
order by val1,val2;

--■■■dump関数を使う方法■■■
select distinct val1,val2,
count(distinct dump(val1) || dump(val2)) over() as RecordCount
  from DistinctTest
order by val1,val2;

解説

逆ソートを使えば、インラインビューを使わないで済みます。

逆ソートを使う方法は、集合の要素数の公式
n(A∪B)=n(A)+n(B)-n(A∩B)
の感覚に近いですね。

COUNT + GROUP BY - MSDN Forums

逆ソートの代わりに、dump関数を使ってもいいでしょう。
DUMP - オラクル・Oracle SQL 関数リファレンス

---