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!
We've just released cx_Oracle 7.0
Check out the release blog post for details on what's new and changed: https://blogs.oracle.com/opal/python-cx_oracle-7-introduces-soda-document-storage
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...
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
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
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>
... 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>