分析函式——統計
很多需求中都涉及到統計:均值、累計、範圍均值、相鄰記錄比較等。
這些操作會統計多次，或有明確的統計範圍，或返回的記錄統計的資料集不同...
根據場景不同可分為如下幾類:
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做對比。同樣可以得到我們希望看到的結果
這些操作會統計多次，或有明確的統計範圍，或返回的記錄統計的資料集不同...
根據場景不同可分為如下幾類:
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做對比。同樣可以得到我們希望看到的結果
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28929558/viewspace-1180990/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 統計分析函式Oracle函式
- 分析函式之排名統計函式
- 不使用分析函式的累計統計查詢函式
- Oracle分析函式、多維函式和Model函式簡要說明，主要針對BI報表統計Oracle函式
- Oracle分析函式七——分析函式案例Oracle函式
- Oracle統計分析函式集,over(partition by..) 的運用Oracle函式
- Oracle聚合函式/分析函式Oracle函式
- 分析函式函式
- 【筆記】oracle 統計函式筆記Oracle函式
- oracle中LAG()和LEAD()等分析統計函式的用法(統計月增長率)Oracle函式
- 【分析函式】Oracle分析函式之LAG和LEAD函式Oracle
- 分析函式概述函式
- 分析函式 over函式
- Oracle 分析函式Oracle函式
- 分析函式 - LAG函式
- Oracle分析函式Oracle函式
- Lesson17——NumPy 統計函式函式
- Oracle分析函式與視窗函式Oracle函式
- 【Analytic】分析函式之MIN函式函式
- 【Analytic】分析函式之MAX函式函式
- 【Analytic】分析函式之AVG函式函式
- 【Analytic】分析函式之RANK函式函式
- 【Analytic】分析函式之COUNT函式函式
- oracle 10g函式大全--分析函式Oracle 10g函式
- sql優化用group by 函式代替分析函式SQL優化函式
- MySQL函式大全(字串函式，數學函式，日期函式，系統級函式，聚合函式)MySql函式字串
- DAX 第六篇：統計函式函式
- golang count 單字元 字串 統計函式Golang字元字串函式
- 使用LAG和LEAD函式統計函式
- Hive之分析函式Hive函式
- 分析函式——NTILE(n)函式
- oracle 分析函式(轉)Oracle函式
- oracle分析函式（一）Oracle函式
- Oracle常用分析函式Oracle函式
- Oracle分析函式{轉}Oracle函式
- 多維分析函式函式
- Oracle分析函式-6Oracle函式
- Oracle分析函式-5Oracle函式