Forum Stats

  • 3,733,254 Users
  • 2,246,738 Discussions
  • 7,856,636 Comments

Discussions

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

611012
611012 Member Posts: 2
edited December 2007 in SQL & PL/SQL
[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
    598338 Member Posts: 345
    edited November 2007
    Sorry, deleted !
  • jeneesh
    jeneesh 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...
  • 450441
    450441 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...
  • 611012
    611012 Member Posts: 2
    thanks a lot for quick response
  • Aketi Jyuuzou
    Aketi Jyuuzou 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;
    similar thread
    569685
  • jeneesh
    jeneesh Member Posts: 7,168
    Nicely Done, Aketi Jyuuzou ...
  • 572471
    572471 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.