[Oracle]分析函数详述(四)
3330
S00 2013-04 300 3630
S01 2013-03 4430 4430
S01 2013-04 2700 7130
S02 2013-03 1370 1370
S02 2013-04 1800 3170
已选择6行。
例2：求出每个部门按售出货物类别的累计销售额（按货物类别代码正序排列）以及每个货物按部门的累计销售额（按部门编号正序排列）
[sql]
SQL> with t as (
2 select dept_id, goods_type, sum(sale_cnt) goods_sale_cnt from lw_sales group by dept_id, goods_type
3 )
4 select dept_id, goods_type, goods_sale_cnt,
5 sum(goods_sale_cnt)over(partition by dept_id order by goods_type) cum_gsc_goods,
6 sum(goods_sale_cnt)over(partition by goods_type order by dept_id) cum_gsc_dept
7 from t;
DEPT_I GOOD GOODS_SALE_CNT CUM_GSC_GOODS CUM_GSC_DEPT
------ ---- -------------- ------------- ------------
S00 G00 400 400 400
S01 G00 1600 1600 2000
S02 G00 400 400 2400
S00 G01 330 730 330
S01 G01 800 2400 1130
S02 G01 270 670 1400
S01 G02 1400 3800 1400
S02 G02 900 1570 2300
S00 G03 1000 1730 1000
S01 G03 800 4600 1800
S02 G03 1100 2670 2900
S00 G04 1000 2730 1000
S01 G04 2530 7130 3530
S02 G04 300 2970 3830
S00 G05 900 3630 900
S02 G05 200 3170 1100
已选择16行。
相关统计分析函数--AVG
例1：求出每个部门每种货物的销售额与该货物在公司各部门平均销售额之间的差值
[sql]
SQL> with t as (
2 select dept_id, goods_type, sum(sale_cnt) goods_sale_cnt
3 from lw_sales group by dept_id, goods_type)
4 select dept_id, goods_type, goods_sale_cnt,
5 round(AVG(goods_sale_cnt)over(partition by goods_type),2) avg_goods_sale_cnt
6 , goods_sale_cnt-round(AVG(goods_sale_cnt) over(partition by goods_type),2) dv_goods_sale_cnt
7 from t;
DEPT_I GOOD GOODS_SALE_CNT AVG_GOODS_SALE_CNT DV_GOODS_SALE_CNT
------ ---- -------------- ------------------ -----------------
S00 G00 400 800 -400
S01 G00 1600 800 800
S02 G00 400 800 -400
S00 G01 330 466.67 -136.67
S01 G01 800 466.67 333.33
S02 G01 270 466.67 -196.67
S01 G02 1400 1150 250
S02 G02 900 1150 -250
S00 G03 1000 966.67 33.33
S01 G03 800 966.67 -166.67
S02 G03 1100 966.67 133.33
S00 G04 1000 1276.67 -276.67
S01 G04 2530 1276.67 1253.33
S02 G04 300 1276.67 -976.67
S00 G05 900 550 350
S02 G05 200 550 -350
已选择16行。
相关统计分析函数--MAX/MIN
例1：货物G01每月的最高和最低销售额对应的部门（如有多个部门按部门ID列出最小的一个，如某部门某月无销售额则不做统计）
[sql]
SQL> with t as (
2 select dept_id , to_char(sale_date,'YYYY-MM') sale_month,
3 sum(sale_cnt) goods_sale_cnt，
4 max(sum(sale_cnt))over(partition by to_char(sale_date,'YYYY-MM')) max_gsc,
5 min(sum(sale_cnt))over(partition by to_char(sale_date,'YYYY-MM')) min