Sum()over()和count()over分析函数使用
创建测试表
createtabletest(sales_idvarchar2(2),salesvarchar2(10),destvarchar2(10),deptvarchar2(10),revenuenumber);
插入测试数据
insertintotestvalues('11','smith','hangzhou','市场',1000);
insertintotestvalues('12','smith','wenzhou','市场',2000);
insertintotestvalues('13','allen','wenzhou','渠道',3000);
insertintotestvalues('14','allen','wenzhou','渠道',4000);
insertintotestvalues('15','jekch','shanghai','渠道',2500);
insertintotestvalues('11','smith','hangzhou','市场',1000);
insertintotestvalues('12','smith','wenzhou','市场',2000);
commit;
SQL> select * from test;
SALES_ID SALESDESTDEPTREVENUE
-------- ---------- ---------- ---------- ----------
11smithhangzhou市场1000
12 smithwenzhou市场2000
13allenwenzhou渠道3000
14allenwenzhou渠道4000
15jekchshanghai渠道2500
11smithhangzhou市场1000
12smithwenzhou市场2000
SQL> select sales_id,
2sales,
3dest,
4dept,
5revenue,
6sum(revenue) over() as总销售额
7from test
8;
SALES_ID SALESDESTDEPTREVENUE总销售额
-------- ---------- ---------- ---------- ---------- ----------
11smithhangzhou市场100015500
12smithwenzhou市场200015500
13allenwenzhou渠道300015500
14allenwenzhou渠道400015500
15jekchshanghai渠道250015500
11smithhangzhou市场100015500
12smithwenzhou市场200015500
按照sales_id order by排序计算递加的销售总额
SQL> select sales_id,sales,dest,dept,revenue,sum(revenue)over(order by sales)递加销售总额from test;
SALES_ID SALESDESTDEPTREVENUE递加销售总额
-------- ---------- ---------- ---------- ---------- ------------
14allenwenzhou渠道40007000
13allenwenzhou渠道30007000
15jekchshanghai渠道25009500
11smithhangzhou市场100015500
12smithwenzhou市场200015500
12smithwenzhou市场200015500
11smithhangzhou市场100015500
7 rows selected
SQL> select sales_id,sales,dest,dept,revenue,sum(revenue)over(partition by sales_id)分组销售总额from test;
SALES_ID SALESDESTDEPTREVENUE分组销售总额
-------- ---------- ---------- ---------- ---------- ------------
11smithhangzhou市场10002000
11smithhangzhou市场10002000
12smithwenzhou市场20004000
12smithwenzhou市场20004000
13allenwenzhou渠道30003000
14allenwenzhou渠道40004000
15jekchshanghai渠道25002500
对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 SALESDESTDEPTREVENUE分组递加销售总额
-------- ---------- ---------- ---------- ---------- ----------------
13allenwenzhou渠道30003000
14allenwenzhou渠道40007000
15jekchshanghai渠道25002500
11smithhangzhou市场10002000
11smithhangzhou市场10002000
12smithwenzhou市场20006000
12smithwenzhou市场20006000
对sales进行分组,然后分组内递加sum。
看下来的count()over()分析函数的使用
SQL> select sales_id,sales,count(*)over()求总计数,
2count(*)over(order by sales_id)递加求计数,
3count(*)over(partition by sales_id)分组求计数,
4count(*)over(partition by sales_id order by sales)分组递加求计数
5from test
6;
SALES_ID SALES求总计数递加求计数分组求计数分组递加求计数
-------- ---------- ---------- ---------- ---------- --------------
11smith7222
11smith7222
12smith7422
12smith7422
13allen7511
14allen7611
15jekch7711
[@more@]