Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

611012Nov 29 2007 — edited Dec 1 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..

Comments

598338
Sorry, deleted !
jeneesh 
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...
450441 
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...
611012
thanks a lot for quick response
Aketi Jyuuzou 
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;

similar thread
569685

jeneesh
Nicely Done, Aketi Jyuuzou ...
572471
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>
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 29 2007
Added on Nov 29 2007
7 comments
3,909 views