很多需求中都涉及到统计:均值、累计、范围均值、相邻记录比较等。
这些操作会统计多次，或有明确的统计范围，或返回的记录统计的数据集不同...
根据场景不同可分为如下几类:
1. 全统计
2. 滚动统计
3. 范围统计
4. (相邻)行比较
构建测试数据:
SQL> desc criss_sales;
Name Type Nullable Default Comments
---------- ----------- -------- ------- --------
DEPT_ID VARCHAR2(6) Y
SALE_DATE DATE Y
GOODS_TYPE VARCHAR2(4) Y
SALE_CNT NUMBER(10) Y
SQL> select * from criss_sales order by dept_id,sale_date desc;
DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT
------- ----------- ---------- -----------
D01 2014/5/4 G02 80
D01 2014/4/30 G03 800
D01 2014/4/8 G01 200
D01 2014/3/4 G00 700
D02 2014/5/2 G03 900
D02 2014/4/27 G01 300
D02 2014/4/8 G02 100
D02 2014/3/6 G00 500
一.全统计
最常用的全统计就是均值或求和,有时会要求同一行记录包含不同范围的全统计。
例:
为数据集统计部门销售总和，全公司销售总和，部门销售均值，全公司销售均值
SQL> select
2 dept_id
3 ,sale_date
4 ,goods_type
5 ,sale_cnt
6 ,sum(sale_cnt) over (partition by dept_id) dept_total
7 ,sum(sale_cnt) over() cmp_total
8 ,avg(sale_cnt) over (partition by dept_id) avg_dept
9 ,avg(sale_cnt) over() avg_cmp
10 from criss_sales
11 ;
DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT DEPT_TOTAL CMP_TOTAL AVG_DEPT AVG_CMP
------- ----------- ---------- ----------- ---------- ---------- ---------- ----------
D01 2014/5/4 G02 80 1780 3580 445 447.5
D01 2014/4/8 G01 200 1780 3580 445 447.5
D01 2014/4/30 G03 800 1780 3580 445 447.5
D01 2014/3/4 G00 700 1780 3580 445 447.5
D02 2014/5/2 G03 900 1800 3580 450 447.5
D02 2014/4/8 G02 100 1800 3580 450 447.5
D02 2014/3/6 G00 500 1800 3580 450 447.5
D02 2014/4/27 G01 300 1800 3580 450 447.5
这样在同一行记录，就得到了部门范围的全统计(均值/求和)和公司范围的全统计(均值/求和)。
二.滚动统计
滚动统计最常用的一个场景之一是累计。
例:
计算部门和全公司的销售树量累计值。
SQL> select
2 dept_id
3 ,sale_date
4 ,goods_type
5 ,sale_cnt
6 ,sum(sale_cnt) over(partition by dept_id order by dept_id,sale_date rows between unbounded preceding and current row) dept_cur_total
7 ,sum(sale_cnt) over(order by dept_id,sale_date rows between unbounded preceding and current row) cmp_cur_total
8 from criss_sales
9 ;
DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT DEPT_CUR_TOTAL CMP_CUR_TOTAL
------- ----------- ---------- ----------- -------------- -------------
D01 2014/3/4 G00 700 700 700
D01 2014/4/8 G01 200 900 900
D01 2014/4/30 G03 800 1700 1700
D01 2014/5/4 G02 80 1780 1780
D02 2014/3/6 G00 500 500 2280
D02 2014/4/8 G02 100 600 2380
D02 2014/4/27 G01 300 900 2680
D02 2014/5/2 G03 900 1800 3580
当然，滚动查询也可以计算当前平均值~这里就不在赘述了
三.范围统计
有时候，我们往往关注一定范围内的数据，例如时间范围(一周内的数据)，记录范围(前三条记录到当前记录)。
例:按日期排序，求相相邻三次销售记录的和
SQL> select
2 dept_id
3 ,sale_date
4 ,goods_type
5 ,sale_cnt
6 ,sum(sale_cnt) over(order by sale_date rows between 1 preceding and 1 following) CON_1_CNT
7 from criss_sales
8 ;
DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT CON_1_CNT
------- ----------- ---------- ----------- ----------
D01 2014/3/4 G00 700 1200
D02 2014/3/6 G00 500 1400
D01 2014/4/8 G01 200 800
D02 2014/4/8 G02 100 600
D02 2014/4/27 G01 300 1200
D01 2014/4/30 G03 800 2000
D02 2014/5/2 G03 900 1780
D01 2014/5/4 G02 80 980
时间范围例子:
按日期排序，求当前记录日期前三天到后天三的销售数量和
SQL> select
2 dept_id
3 ,sale_date
4 ,goods_type
5 ,sale_cnt
6 ,sum(sale_cnt) over(order by sale_date range
7 between interval '3' day preceding
8 and interval '3' day following) sum_7_days
9 from criss_sales
10 ;
DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT SUM_7_DAYS
------- ----------- ---------- ----------- ----------
D01 2014/3/4 G00 700 1200
D02 2014/3/6 G00 500 1200
D01 2014/4/8 G01 200 300
D02 2014/4/8 G02 100 300
D02 2014/4/27 G01 300 1100
D01 2014/4/30 G03 800 2000
D02 2014/5/2 G03 900 1780
D01 2014/5/4 G02 80 980
四.(相邻)行比较
其实用over(order by xxx rows between 1 preceding and 0 following)也能实现相邻行的对比。
但是，Oracle提供更方便的两个函数
lead() 与后面某一行对比
lag() 与前面一行对比
按时间排序，显示当前记录的数量以及前后相邻记录的销售数量
SQL> select
2 dept_id
3 ,sale_date
4 ,goods_type
5 ,sale_cnt
6 ,lag(sale_cnt,1) over(order by sale_date) lag_1
7 ,lead(sale_cnt,1) over(order by sale_date) lead_1
8 ,first_value(sale_cnt) over(order by sale_date rows between 1 preceding and 0 following)
9 from criss_sales
10 ;
DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT LAG_1 LEAD_1 FIRST_VALUE(SALE_CNT)OVER(ORDE
------- ----------- ---------- ----------- ---------- ---------- ------------------------------
D01 2014/3/4 G00 700 500 700
D02 2014/3/6 G00 500 700 200 700
D01 2014/4/8 G01 200 500 100 500
D02 2014/4/8 G02 100 200 300 200
D02 2014/4/27 G01 300 100 800 100
D01 2014/4/30 G03 800 300 900 300
D02 2014/5/2 G03 900 800 80 800
D01 2014/5/4 G02 80 900 900
最后一列是利用over(order by xxx rows between 1 preceding and 0 following)与 lag做对比。同样可以得到我们希望看到的结果