sum()over()和count()over()分析函式

Sum()over()和count()over分析函式使用

建立測試表

create table test(sales_id varchar2(2),sales varchar2(10),dest varchar2(10),dept varchar2(10),revenue number);

插入測試資料

insert into test values('11','smith','hangzhou','市場',1000);

insert into test values('12','smith','wenzhou','市場',2000);

insert into test values('13','allen','wenzhou','渠道',3000);

insert into test values('14','allen','wenzhou','渠道',4000);

insert into test values('15','jekch','shanghai','渠道',2500);

insert into test values('11','smith','hangzhou','市場',1000);

insert into test values('12','smith','wenzhou','市場',2000);

commit;

SQL> select * from test;

SALES_ID SALES DEST DEPT REVENUE

-------- ---------- ---------- ---------- ----------

11 smith hangzhou 市場 1000

12 smith wenzhou 市場 2000

13 allen wenzhou 渠道 3000

14 allen wenzhou 渠道 4000

15 jekch shanghai 渠道 2500

11 smith hangzhou 市場 1000

12 smith wenzhou 市場 2000

SQL> select sales_id,

2 sales,

3 dest,

4 dept,

5 revenue,

6 sum(revenue) over() as 總銷售額

7 from test

8 ;

SALES_ID SALES DEST DEPT REVENUE 總銷售額

-------- ---------- ---------- ---------- ---------- ----------

11 smith hangzhou 市場 1000 15500

12 smith wenzhou 市場 2000 15500

13 allen wenzhou 渠道 3000 15500

14 allen wenzhou 渠道 4000 15500

15 jekch shanghai 渠道 2500 15500

11 smith hangzhou 市場 1000 15500

12 smith wenzhou 市場 2000 15500

按照sales_id order by排序計算遞加的銷售總額

SQL> select sales_id,sales,dest,dept,revenue,sum(revenue)over(order by sales)遞加銷售總額 from test;

SALES_ID SALES DEST DEPT REVENUE 遞加銷售總額

-------- ---------- ---------- ---------- ---------- ------------

14 allen wenzhou 渠道 4000 7000

13 allen wenzhou 渠道 3000 7000

15 jekch shanghai 渠道 2500 9500

11 smith hangzhou 市場 1000 15500

12 smith wenzhou 市場 2000 15500

12 smith wenzhou 市場 2000 15500

11 smith hangzhou 市場 1000 15500

7 rows selected

SQL> select sales_id,sales,dest,dept,revenue,sum(revenue)over(partition by sales_id) 分組銷售總額 from test;

SALES_ID SALES DEST DEPT REVENUE 分組銷售總額

-------- ---------- ---------- ---------- ---------- ------------

11 smith hangzhou 市場 1000 2000

11 smith hangzhou 市場 1000 2000

12 smith wenzhou 市場 2000 4000

12 smith wenzhou 市場 2000 4000

13 allen wenzhou 渠道 3000 3000

14 allen wenzhou 渠道 4000 4000

15 jekch shanghai 渠道 2500 2500

對sales_id進行分組，然後分組求sum

SQL> select sales_id,sales,dest,dept,revenue,sum(revenue)over(partition by sales order by sales_id) 分組遞加銷售總額 from test;

SALES_ID SALES DEST DEPT REVENUE 分組遞加銷售總額

-------- ---------- ---------- ---------- ---------- ----------------

13 allen wenzhou 渠道 3000 3000

14 allen wenzhou 渠道 4000 7000

15 jekch shanghai 渠道 2500 2500

11 smith hangzhou 市場 1000 2000

11 smith hangzhou 市場 1000 2000

12 smith wenzhou 市場 2000 6000

12 smith wenzhou 市場 2000 6000

對sales進行分組，然後分組內遞加sum。

看下來的count()over()分析函式的使用

SQL> select sales_id,sales,count(*)over()求總計數,

2 count(*)over(order by sales_id)遞加求計數,

3 count(*)over(partition by sales_id)分組求計數,

4 count(*)over(partition by sales_id order by sales)分組遞加求計數

5 from test

6 ;

SALES_ID SALES 求總計數 遞加求計數 分組求計數 分組遞加求計數

-------- ---------- ---------- ---------- ---------- --------------

11 smith 7 2 2 2

11 smith 7 2 2 2

12 smith 7 4 2 2

12 smith 7 4 2 2

13 allen 7 5 1 1

14 allen 7 6 1 1

15 jekch 7 7 1 1

[@more@]