10-214 重複を除いた訪問者数

SQLパズル

visitTable
day1   visitor
-----  -------
10-01  AAAAAAA
10-01  AAAAAAA
10-01  BBBBBBB
10-01  BBBBBBB
10-01  CCCCCCC
10-01  CCCCCCC
10-02  AAAAAAA
10-02  CCCCCCC
10-02  CCCCCCC
10-02  DDDDDDD
10-02  DDDDDDD
10-03  BBBBBBB
10-03  BBBBBBB
10-03  BBBBBBB
10-03  AAAAAAA
10-03  AAAAAAA
10-03  FFFFFFF
10-04  AAAAAAA
10-04  AAAAAAA
10-04  FFFFFFF

その日の、重複を除いた訪問者数と
その日までの、重複を除いた訪問者数
を求める。

出力結果
DAY1   DayCount  TotalCount
-----  --------  ----------
10-01         3           3
10-02         3           4
10-03         3           5
10-04         2           5

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

データ作成スクリプト

create table visitTable(day1,visitor) as
select '10-01','A' from dual union all
select '10-01','A' from dual union all
select '10-01','B' from dual union all
select '10-01','B' from dual union all
select '10-01','C' from dual union all
select '10-01','C' from dual union all
select '10-02','A' from dual union all
select '10-02','C' from dual union all
select '10-02','C' from dual union all
select '10-02','D' from dual union all
select '10-02','D' from dual union all
select '10-03','B' from dual union all
select '10-03','B' from dual union all
select '10-03','B' from dual union all
select '10-03','A' from dual union all
select '10-03','A' from dual union all
select '10-03','F' from dual union all
select '10-04','A' from dual union all
select '10-04','A' from dual union all
select '10-04','F' from dual;

SQL

--■■■最後にgroup化する方法■■■
select day1,count(distinct visitor) as cnt,
sum(sum(willSum)) over(order by day1) as cnt1
from (select day1,visitor,
      case Row_Number() over(partition by visitor order by day1)
           when 1 then 1 else 0 end as willSum
        from visitTable)
group by day1
order by day1;

--■■■最後にgroup化しない方法■■■
select distinct day1,count(distinct visitor) over(partition by day1) as cnt,
sum(willSum) over(order by day1) as cnt1
from (select day1,visitor,
      case Row_Number() over(partition by visitor order by day1)
           when 1 then 1 else 0 end as willSum
        from visitTable)
order by day1;

--■■■model句を使う方法(10g以降)■■■
select distinct day1,DayCount,TotalCount
  from visitTable
 model
 dimension by (day1,RowNum as soeji)
 measures(visitor,0 as DayCount,0 as TotalCount)
 rules(DayCount[any,any]   = count(distinct visitor)[CV(),any],
       TotalCount[any,any] = count(distinct visitor)[CV() >= day1,any]);

解説

最後にgroup化する方法のほうが分かりやすいと思います。

10-190 distinctオプションとorder by指定の分析関数を代用
10-229 重複を除いた累計
10-259 重複を除いた集計の累計

---