## Forum Stats

• 3,733,254 Users
• 2,246,738 Discussions

Discussions

#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# [SQL] how can i get this result....??(accumulation distinct count)

Member Posts: 2
edited December 2007
[SQL] how can i get this result....??(accumulation distinct count)

Hi everybody,

pls tell me how can it possible to get result?

### sample data

date visitor
----- ------
10-01 A
10-01 A
10-01 B
10-01 B
10-01 C
10-01 C
10-02 A
10-02 C
10-02 C
10-02 D
10-02 D
10-03 B
10-03 B
10-03 B
10-03 A
10-03 A
10-03 F
10-04 A
10-04 A
10-04 F

result that i want...like this.

date date_unqiue_visitors acc_date_unique_visitors
----- ------------- --------------------
10-01 3 3
10-02 3 4
10-03 3 5
10-04 2 5

date distinct visitors : count(distinct visitor)

but how can i get accumulation distinct visitor count???

Thanks to every body..

• Member Posts: 345
edited November 2007
Sorry, deleted !
• Member Posts: 7,168
edited November 2007
```SQL> select dt,cnt,sum(cnt1) over(order by dt) cnt1
2  from(
3  select dt,count(distinct visitor) cnt,sum(flg) cnt1
4  from
5   (select dt,visitor,(select decode(count(*),0,1,0)
6                           from test
7                           where rowid < t.rowid
8                           and visitor = t.visitor) flg
9   from test t)
10  group by dt);
DT                CNT       CNT1
---------- ---------- ----------
10-01               3          3
10-02               3          4
10-03               3          5
10-04               2          5
Message was edited by:
jeneesh
Wrong...                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                ```
• Member Posts: 2,525
edited November 2007
```WITH t1 AS (select trunc(date) date, count(distinct visitor) cnt
from sample
group by trunc(date))
, t2 as (select trunc(t1.date) date, count(distinct t2.visitor) cnt
from sample t2, t1
where trunc(t2.date) <= trunc(t1.date)
group by trunc(t1.date))
select t1.date, t1.cnt visitors_per_day, t2.cnt cum_visitors
from t1, t2
where t1.date = t2.date ```
Message was edited by:
Dave Hemming
This was much more complicated than I first thought it would be...
• Member Posts: 2
thanks a lot for quick response
• Member Posts: 1,072 Bronze Badge
edited December 2007
```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;```
```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;```
```DAY1   CNT  CNT1
-----  ---  ----
10-01    3     3
10-02    3     4
10-03    3     5
10-04    2     5```
or
```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;```
569685
• Member Posts: 7,168
Nicely Done, Aketi Jyuuzou ...
• Member Posts: 984 Green Ribbon
edited December 2007
playing with model:
```SQL> with t as (
2  select '10-01' day1,'A' visitor from dual union all
3  select '10-01','A' from dual union all
4  select '10-01','B' from dual union all
5  select '10-01','B' from dual union all
6  select '10-01','C' from dual union all
7  select '10-01','C' from dual union all
8  select '10-02','A' from dual union all
9  select '10-02','C' from dual union all
10  select '10-02','C' from dual union all
11  select '10-02','D' from dual union all
12  select '10-02','D' from dual union all
13  select '10-03','B' from dual union all
14  select '10-03','B' from dual union all
15  select '10-03','B' from dual union all
16  select '10-03','A' from dual union all
17  select '10-03','A' from dual union all
18  select '10-03','F' from dual union all
19  select '10-04','A' from dual union all
20  select '10-04','A' from dual union all
21  select '10-04','F' from dual)
22  --
23  select d,cnt1,cnt2 from t
24   model
25    return updated rows
26    dimension by (day1 d, rownum rn)
27    measures(visitor, 0 cnt1, 0 cnt2)
28     (cnt1[for d in (select day1 from t), 0]=count(distinct visitor)[CV(),any],
29      cnt2[any,0]=count(distinct visitor)[d<=CV(),any])
30      order by 1
31  /

D           CNT1       CNT2
----- ---------- ----------
10-01          3          3
10-02          3          4
10-03          3          5
10-04          2          5

SQL> ```
Message was edited by:
Volder

or a better version with "upsert all":
```...
22  --
23  select d,cnt1,cnt2 from t
24   model
25    return updated rows
26    dimension by (day1 d, rownum rn)
27    measures(visitor, 0 cnt1, 0 cnt2)
28    rules upsert all
29     (cnt1[any,0]=count(distinct visitor)[CV(),any],
30      cnt2[any,0]=count(distinct visitor)[d<=CV(),any])
31      order by 1
32  /

D           CNT1       CNT2
----- ---------- ----------
10-01          3          3
10-02          3          4
10-03          3          5
10-04          2          5

SQL> ```
This discussion has been closed.