很多需求中都涉及到統(tǒng)計:均值、累計、范圍均值、相鄰記錄比較等。
這些操作會統(tǒng)計多次，或有明確的統(tǒng)計范圍，或返回的記錄統(tǒng)計的數(shù)據(jù)集不同...

根據(jù)場景不同可分為如下幾類:   
    1. 全統(tǒng)計
    2. 滾動統(tǒng)計
    3. 范圍統(tǒng)計
    4. (相鄰)行比較


構建測試數(shù)據(jù):
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


一.全統(tǒng)計
最常用的全統(tǒng)計就是均值或求和,有時會要求同一行記錄包含不同范圍的全統(tǒng)計。

例:
為數(shù)據(jù)集統(tǒng)計部門銷售總和，全公司銷售總和，部門銷售均值，全公司銷售均值

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 


這樣在同一行記錄，就得到了部門范圍的全統(tǒng)計(均值/求和)和公司范圍的全統(tǒng)計(均值/求和)。


二.滾動統(tǒng)計
滾動統(tǒng)計最常用的一個場景之一是累計。


例:
   計算部門和全公司的銷售樹量累計值。


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

當然，滾動查詢也可以計算當前平均值~這里就不在贅述了 


三.范圍統(tǒng)計
有時候，我們往往關注一定范圍內(nèi)的數(shù)據(jù)，例如時間范圍(一周內(nèi)的數(shù)據(jù))，記錄范圍(前三條記錄到當前記錄)。

例:按日期排序，求相相鄰三次銷售記錄的和
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


時間范圍例子:
按日期排序，求當前記錄日期前三天到后天三的銷售數(shù)量和


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)也能實現(xiàn)相鄰行的對比。
但是，Oracle提供更方便的兩個函數(shù) 
lead() 與后面某一行對比
lag()  與前面一行對比

按時間排序，顯示當前記錄的數(shù)量以及前后相鄰記錄的銷售數(shù)量

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做對比。同樣可以得到我們希望看到的結(jié)果

成都創(chuàng)新互聯(lián)長期為上千余家客戶提供的網(wǎng)站建設服務，團隊從業(yè)經(jīng)驗10年，關注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為岳麓企業(yè)提供專業(yè)的網(wǎng)站設計、網(wǎng)站建設，岳麓網(wǎng)站改版等技術服務。擁有十年豐富建站經(jīng)驗和眾多成功案例,為您定制開發(fā)。

當前名稱：分析函數(shù)——統(tǒng)計
文章路徑：http://chinadenli.net/article2/jpcioc.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站建設、營銷型網(wǎng)站建設、品牌網(wǎng)站設計、企業(yè)網(wǎng)站制作、虛擬主機、靜態(tài)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)