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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

[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,890 views