Forum Stats

  • 3,741,231 Users
  • 2,248,397 Discussions
  • 7,861,693 Comments

Discussions

Rolling unique person count by month over a time period

600493
600493 Member Posts: 1
edited Sep 29, 2007 9:06AM in SQL & PL/SQL
Anyone have any idea how I could get a rolling count of the unique number of people that have purchased from a single query?

Month Person
Jan-07 A
Jan-07 B
Feb-07 A
Feb-07 C
Mar-07 D

Desired Output when run for Jan-March

Month Count
Jan-07 2
Feb-07 3
Mar-07 4

Comments

  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    The count distinct analytic function is restricted in its use:

    If you specify DISTINCT, then you can specify only the query_partition_clause of the analytic_clause. The order_by_clause and windowing_clause are not allowed.

    (from the [url http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions032.htm#i82697]documentation)

    It is possible, unfortunately by accessing the table twice, but with the expected results:
    SQL> create table mytable
    2 as
    3 select date '2007-01-01' month, 'A' person from dual union all
    4 select date '2007-01-01', 'B' from dual union all
    5 select date '2007-02-01', 'A' from dual union all
    6 select date '2007-02-01', 'C' from dual union all
    7 select date '2007-03-01', 'D' from dual
    8 /

    Tabel is aangemaakt.

    SQL> with months as
    2 ( select distinct month
    3 from mytable
    4 )
    5 select m.month "Month"
    6 , count(distinct t.person) "Count"
    7 from months m
    8 , mytable t
    9 where t.month <= m.month
    10 group by m.month
    11 order by m.month
    12 /

    Month Count
    ------------------- ----------
    01-01-2007 00:00:00 2
    01-02-2007 00:00:00 3
    01-03-2007 00:00:00 4

    3 rijen zijn geselecteerd.
    Regards,
    Rob.
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Sep 29, 2007 9:06AM
    Or
    emulate count function of OLAP with distinct option and order by clause.
    2055381

    Because on math,
    "X+0 = X" is correct.
    create table myTable as
    select to_date('2007-01-01','yyyy-mm-dd') month,'A' person from dual union
    select to_date('2007-01-01','yyyy-mm-dd'),'B' from dual union
    select to_date('2007-02-01','yyyy-mm-dd'),'A' from dual union
    select to_date('2007-02-01','yyyy-mm-dd'),'C' from dual union
    select to_date('2007-03-01','yyyy-mm-dd'),'D' from dual;
    select distinct month,sum(WillSum) over(order by month) as "Count"
    from (select month,
    case Row_Number() over(partition by person order by month)
    when 1 then 1 else 0 end as WillSum
    from myTable)
    order by month;
    MONTH     Count
    -------- -----
    07-01-01 2
    07-02-01 3
    07-03-01 4
This discussion has been closed.