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.

How to do cumulative sum for this scenario?

672680Mar 30 2011 — edited Mar 30 2011
Hi all,

I want to do cumulative sum in scenario below. my DB version: 10.2.0
the table and data: 
CREATE TABLE T (DT DATE, USR VARCHAR2(3));

Insert into T (DT,USR) values (to_date('2011-03-26','YYYY-MM-DD'),'a');
Insert into T (DT,USR) values (to_date('2011-03-26','YYYY-MM-DD'),'b');
Insert into T (DT,USR) values (to_date('2011-03-27','YYYY-MM-DD'),'b');
Insert into T (DT,USR) values (to_date('2011-03-27','YYYY-MM-DD'),'c');
Insert into T (DT,USR) values (to_date('2011-03-28','YYYY-MM-DD'),'c');
Insert into T (DT,USR) values (to_date('2011-03-28','YYYY-MM-DD'),'d');
Insert into T (DT,USR) values (to_date('2011-03-29','YYYY-MM-DD'),'d');
Insert into T (DT,USR) values (to_date('2011-03-29','YYYY-MM-DD'),'e');
the result I want is : 
dt                        distinct user
-------------------------------------
2011-03-26  	2
2011-03-27  	3
2011-03-28  	4
2011-03-29  	5
the only query I came up with is as below and it looks clumsy.
So, I wonder if there is a better way to do it?
SELECT a.dt, COUNT(DISTINCT b.usr)
FROM
  (SELECT DISTINCT DT FROM T ) A,
  T B
WHERE A.DT>=b.dt
GROUP BY a.dt;
OVER() seems not work, because oracle doesn`t allow COUNT(DISTINCT) be used together with OVER(ORDER BY ).

correct me if I am wrong.

Thanks

Comments

Thierry H.
Hi PhoenixBai,

I am not sure i understood your problem correctly since you only need to add a ORDER BY at the end of your select to have the results you described.
SQL> with T as (
  2  select  to_date('2011-03-26','YYYY-MM-DD') as DT, 'a' as usr from dual union
  3  select to_date('2011-03-26','YYYY-MM-DD') , 'b'  from dual union
  4  select to_date('2011-03-27','YYYY-MM-DD') , 'b' from dual union
  5  select to_date('2011-03-27','YYYY-MM-DD') , 'c' from dual union
  6  select to_date('2011-03-28','YYYY-MM-DD') , 'c' from dual union
  7  select to_date('2011-03-28','YYYY-MM-DD') , 'd' from dual union
  8  select to_date('2011-03-29','YYYY-MM-DD') , 'd' from dual union
  9  select to_date('2011-03-29','YYYY-MM-DD') , 'e' from dual )
 10  SELECT a.dt, COUNT(DISTINCT b.usr)
 11  FROM
 12    (SELECT DISTINCT DT FROM T ) A,
 13    T B
 14  WHERE A.DT>=b.dt
 15  GROUP BY a.dt
 16  order by a.dt;

DT       COUNT(DISTINCTB.USR)
-------- --------------------
26-03-11                    2
27-03-11                    3
28-03-11                    4
29-03-11                    5
HTH,
Thierry
Aketi Jyuuzou 
with t(DT,USR) as(
select date '2011-03-26','a' from dual union all
select date '2011-03-26','b' from dual union all
select date '2011-03-27','b' from dual union all
select date '2011-03-27','c' from dual union all
select date '2011-03-28','c' from dual union all
select date '2011-03-28','d' from dual union all
select date '2011-03-29','d' from dual union all
select date '2011-03-29','e' from dual)
select DT,sum(sum(willSum)) over(order by DT) as "distinct user"
from (select DT,USR,
      case Row_Number() over(partition by USR order by DT)
      when 1 then 1 else 0 end as willSum
      from t)
group by DT
order by DT;

DT        distinct user
--------  -------------
11-03-26              2
11-03-27              3
11-03-28              4
11-03-29              5
My SQL articles of OTN-Japan B-)
http://www.oracle.com/technetwork/jp/articles/otnj-sql-image3-1-323602-ja.html
1 person found this helpful
RakeshD
How about this?
SELECT dt,
       (SELECT COUNT(DISTINCT(usr)) 
          FROM t t1 
         WHERE t1.dt <= t.dt) as distinct_users 
FROM t
GROUP BY dt
ORDER BY dt
Results...
DT        DISTINCT_USERS
--------- --------------
26-MAR-11              2
27-MAR-11              3
28-MAR-11              4
29-MAR-11              5
-Rakesh
1 person found this helpful
Frank Kulash
Hi,

This might be more efficient than a self-join: 
WITH	got_r_num	AS
(
	SELECT	dt
	,	ROW_NUMBER () OVER ( PARTITION BY usr
				     ORDER BY	  dt
				   )	AS r_num
	FROM	t
--	WHERE	...	-- If you need any filtering, put it here
)
SELECT DISTINCT
	  dt
,	  COUNT (*) OVER (ORDER BY dt)	AS distinct_user
FROM	  got_r_num
WHERE	  r_num	= 1
ORDER BY  dt
;
Unfortunately, you can't use both DISTINCT and ORDER BY in the same analytic COUNT function.
The query above gets distinct users by onlly counting the first dt when each usr occurs. That is "ROW_NUMBER () OVER (PARTITION BY usr ...)" will return 1 for exactly one row for each distinct value of usr.
1 person found this helpful
672680
Thank you all very much.
I will go on and try to figure out which one is more efficient :D
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 27 2011
Added on Mar 30 2011
5 comments
2,632 views