Oracle分析函数-统计(sum、avg、max、min)

时间:2022-03-02 03:46:22

很多需求中都涉及到统计:均值、累计、范围均值、相邻记录比较等。这些操作会统计多次,或有明确的统计范围,或返回的记录统计的数据集不同...

根据场景不同可分为如下几类:
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

一.全统计
最常用的全统计就是均值或求和,有时会要求同一行记录包含不同范围的全统计。
例:
为数据集统计部门销售总和,全公司销售总和,部门销售均值,全公司销售均值

select
dept_id
,sale_date
,goods_type
,sale_cnt
,sum(sale_cnt) over (partition by dept_id) dept_total
,sum(sale_cnt) over() cmp_total
,avg(sale_cnt) over (partition by dept_id) avg_dept
,avg(sale_cnt) over() avg_cmp
from criss_sales;

Oracle分析函数-统计(sum、avg、max、min)

这样在同一行记录,就得到了部门范围的全统计(均值/求和)和公司范围的全统计(均值/求和)。

二.滚动统计
滚动统计最常用的一个场景之一是累计。

例:
计算部门和全公司的销售树量累计值。

select
dept_id
,sale_date
,goods_type
,sale_cnt
,sum(sale_cnt) over(partition by dept_id order by dept_id,sale_date rows between unbounded preceding and current row) dept_cur_total
,sum(sale_cnt) over(order by dept_id,sale_date rows between unbounded preceding and current row) cmp_cur_total
from criss_sales;

Oracle分析函数-统计(sum、avg、max、min)

当然,滚动查询也可以计算当前平均值~这里就不在赘述了

三.范围统计
有时候,我们往往关注一定范围内的数据,例如时间范围(一周内的数据),记录范围(前三条记录到当前记录)。

例:按日期排序,求相相邻三次销售记录的和

select
dept_id
,sale_date
,goods_type
,sale_cnt
,sum(sale_cnt) over(order by sale_date rows between 1 preceding and 1 following) CON_1_CNT
from criss_sales;

Oracle分析函数-统计(sum、avg、max、min)

时间范围例子:
按日期排序,求当前记录日期前三天到后天三的销售数量和

select
dept_id
,sale_date
,goods_type
,sale_cnt
,sum(sale_cnt) over(order by sale_date range between interval '' day preceding and interval '' day following) sum_7_days
from criss_sales;

Oracle分析函数-统计(sum、avg、max、min)

四.(相邻)行比较
其实用over(order by xxx rows between 1 preceding and 0 following)也能实现相邻行的对比。
但是,Oracle提供更方便的两个函数 
lead() 与后面某一行对比
lag()  与前面一行对比

按时间排序,显示当前记录的数量以及前后相邻记录的销售数量

select
dept_id
,sale_date
,goods_type
,sale_cnt
,lag(sale_cnt,1) over(order by sale_date) lag_1
,lead(sale_cnt,1) over(order by sale_date) lead_1
,first_value(sale_cnt) over(order by sale_date rows between 1 preceding and 0 following)
from criss_sales;

Oracle分析函数-统计(sum、avg、max、min)

最后一列是利用over(order by xxx rows between 1 preceding and 0 following)与 lag做对比。同样可以得到我们希望看到的结果。