常見分析函數詳解

三、常見分析函數詳解

為了方便進行實踐，特將演示表和數據羅列如下：

一、創建表

create table t( 
   bill_month varchar2(12) , 
   area_code number, 
   net_type varchar(2), 
   local_fare number 
);

二、插入數據

insert into t values('200405',5761,'G', 7393344.04); 
insert into t values('200405',5761,'J', 5667089.85); 
insert into t values('200405',5762,'G', 6315075.96); 
insert into t values('200405',5762,'J', 6328716.15); 
insert into t values('200405',5763,'G', 8861742.59); 
insert into t values('200405',5763,'J', 7788036.32); 
insert into t values('200405',5764,'G', 6028670.45); 
insert into t values('200405',5764,'J', 6459121.49); 
insert into t values('200405',5765,'G', 13156065.77); 
insert into t values('200405',5765,'J', 11901671.70); 
insert into t values('200406',5761,'G', 7614587.96); 
insert into t values('200406',5761,'J', 5704343.05); 
insert into t values('200406',5762,'G', 6556992.60); 
insert into t values('200406',5762,'J', 6238068.05); 
insert into t values('200406',5763,'G', 9130055.46); 
insert into t values('200406',5763,'J', 7990460.25); 
insert into t values('200406',5764,'G', 6387706.01); 
insert into t values('200406',5764,'J', 6907481.66); 
insert into t values('200406',5765,'G', 13562968.81); 
insert into t values('200406',5765,'J', 12495492.50); 
insert into t values('200407',5761,'G', 7987050.65); 
insert into t values('200407',5761,'J', 5723215.28); 
insert into t values('200407',5762,'G', 6833096.68); 
insert into t values('200407',5762,'J', 6391201.44); 
insert into t values('200407',5763,'G', 9410815.91); 
insert into t values('200407',5763,'J', 8076677.41); 
insert into t values('200407',5764,'G', 6456433.23); 
insert into t values('200407',5764,'J', 6987660.53); 
insert into t values('200407',5765,'G', 14000101.20); 
insert into t values('200407',5765,'J', 12301780.20); 
insert into t values('200408',5761,'G', 8085170.84); 
insert into t values('200408',5761,'J', 6050611.37); 
insert into t values('200408',5762,'G', 6854584.22); 
insert into t values('200408',5762,'J', 6521884.50); 
insert into t values('200408',5763,'G', 9468707.65); 
insert into t values('200408',5763,'J', 8460049.43); 
insert into t values('200408',5764,'G', 6587559.23); 
insert into t values('200408',5764,'J', 7342135.86); 
insert into t values('200408',5765,'G', 14450586.63); 
insert into t values('200408',5765,'J', 12680052.38); 
commit;

三、first_value()與last_value()：求最值對應的其他屬性
問題、取出每月通話費最高和最低的兩個地區。

SELECT BILL_MONTH, 
       AREA_CODE,
       SUM(LOCAL_FARE) LOCAL_FARE, 
       FIRST_VALUE(AREA_CODE) 
       OVER(PARTITION BY BILL_MONTH 
            ORDER BY SUM(LOCAL_FARE) DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FIRSTVAL, 
       LAST_VALUE(AREA_CODE) 
       OVER(PARTITION BY BILL_MONTH 
            ORDER BY SUM(LOCAL_FARE) DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LASTVAL 
  FROM T 
 GROUP BY BILL_MONTH, AREA_CODE 
 ORDER BY BILL_MONTH

運行結果：

四、rank(),dense_rank()與row_number()：求排序

rank，dense_rank，row_number函數為每條記錄產生一個從1開始至n的自然數，n的值可能小于等于記錄的總數。這3個函數的唯一區別在于當碰到相同數據時的排名策略。
①row_number：
row_number函數返回一個唯一的值，當碰到相同數據時，排名按照記錄集中記錄的順序依次遞增。
②dense_rank：
dense_rank函數返回一個唯一的值，當碰到相同數據時，此時所有相同數據的排名都是一樣的。
③rank：
rank函數返回一個唯一的值，當碰到相同的數據時，此時所有相同數據的排名是一樣的，同時會在最后一條相同記錄和下一條不同記錄的排名之間空出排名。

演示數據在Oracle自帶的scott用戶下：
1、rank()值相同時排名相同，其后排名跳躍不連續

SELECT * 
  FROM (SELECT DEPTNO, 
               RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW, 
               ENAME,
               SAL
          FROM SCOTT.EMP) 
 WHERE RW <= 4;

運行結果：

2、dense_rank()值相同時排名相同，其后排名連續不跳躍

SELECT * 
  FROM (SELECT DEPTNO, 
               DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW, 
               ENAME,
               SAL
          FROM SCOTT.EMP) 
 WHERE RW <= 4;

運行結果：

3、row_number()值相同時排名不相等，其后排名連續不跳躍

SELECT * 
  FROM (SELECT DEPTNO, 
               ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW, 
               ENAME,
               SAL
          FROM SCOTT.EMP) 
 WHERE RW <= 4;

運行結果：

五、lag()與lead()：求之前或之后的第N行
lag和lead函數可以在一次查詢中取出同一字段的前n行的數據和后n行的值。這種操作可以使用對相同表的表連接來實現，不過使用lag和lead有更高的效率。
lag(arg1,arg2,arg3)
第一個參數是列名，
第二個參數是偏移的offset，
第三個參數是超出記錄窗口時的默認值。

舉例如下：
SQL> select * from kkk;

        ID NAME
---------- --------------------
         1 1name
         2 2name
         3 3name
         4 4name
         5 5name
SQL> select id,name,lag(name,1,0) over(order by id) from kkk;

        ID NAME                 LAG(NAME,1,0)OVER(ORDERBYID)
---------- -------------------- ----------------------------
         1 1name                0
         2 2name                1name
         3 3name                2name
         4 4name                3name
         5 5name                4name

SQL> select id,name,lead(name,1,0) over(order by id) from kkk;

        ID NAME                 LEAD(NAME,1,0)OVER(ORDERBYID)
---------- -------------------- -----------------------------
         1 1name                2name
         2 2name                3name
         3 3name                4name
         4 4name                5name
         5 5name                0

SQL> select id,name,lead(name,2,0) over(order by id) from kkk;
        ID NAME                 LEAD(NAME,2,0)OVER(ORDERBYID)
---------- -------------------- -----------------------------
         1 1name                3name
         2 2name                4name
         3 3name                5name
         4 4name                0
         5 5name                0
SQL> select id,name,lead(name,1,'linjiqin') over(order by id) from kkk;

        ID NAME                 LEAD(NAME,1,'ALSDFJLASDJFSAF')
---------- -------------------- ------------------------------
         1 1name                2name
         2 2name                3name
         3 3name                4name
         4 4name                5name
         5 5name                linjiqin

---------------------------------------------------------------------------------------

六、rollup()與cube()：排列組合分組
1)、group by rollup(a, b, c)：
首先會對(a、b、c)進行group by，
然后再對(a、b)進行group by，
其后再對(a)進行group by，
最后對全表進行匯總操作。

2)、group by cube(a, b, c)：
則首先會對(a、b、c)進行group by，
然后依次是(a、b)，(a、c)，(a)，(b、c)，(b)，(c)，
最后對全表進行匯總操作。

1、生成演示數據：
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ds_trade

SQL> conn system/oracle as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as SYS

SQL> create table scott.t as select * from dba_indexes;

Table created

SQL> connect scott/oracle
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as scott

SQL>

2、普通group by體驗
sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by owner, index_type, status;

3、group by rollup(A,B,C)
GROUP BY ROLLUP(A, B, C)：
首先會對(A、B、C)進行GROUP BY，
然后再對(A、B)進行GROUP BY，
其后再對(A)進行GROUP BY，
最后對全表進行匯總操作。
sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by ROLLUP(owner, index_type, status);

4、group by cube(A,B,C)
GROUP BY CUBE(A, B, C)：
則首先會對(A、B、C)進行GROUP BY，
然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，
最后對全表進行匯總操作。

sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by cube(owner, index_type, status);

七、max(),min(),sun()與avg()：求移動的最值總和與平均值
問題：計算出各個地區連續3個月的通話費用的平均數(移動平均值)

SELECT AREA_CODE, 
       BILL_MONTH,
       LOCAL_FARE,
       SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_sum", 
       AVG(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_avg", 
       MAX(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_max", 
       MIN(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY TO_NUMBER(BILL_MONTH) 
                            RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_min" 
  FROM (SELECT T.AREA_CODE, T.BILL_MONTH, SUM(T.LOCAL_FARE) LOCAL_FARE 
          FROM T 
         GROUP BY T.AREA_CODE, T.BILL_MONTH)

運行結果：

問題：求各地區按月份累加的通話費

SELECT AREA_CODE, 
       BILL_MONTH,
       LOCAL_FARE,
       SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE 
                            ORDER BY BILL_MONTH ASC) "last_sum_value" 
  FROM (SELECT T.AREA_CODE, T.BILL_MONTH, SUM(T.LOCAL_FARE) LOCAL_FARE 
          FROM T 
         GROUP BY T.AREA_CODE, T.BILL_MONTH) 
 ORDER BY AREA_CODE, BILL_MONTH

運行結果：

--------------------------------------------------------------------------
Blog：http://www.cnblogs.com/linjiqin/
J2EE、Android、Linux、Oracle QQ交流群：142463980、158560018(滿)

posted on 2015-05-13 00:46 奮斗成就男人閱讀(409) 評論(0) 編輯收藏所屬分類: SQL

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: Oracle分析函數入門常見分析函數詳解 ORACLE 常用函數 linux下導入、導出mysql數據庫命令 MySQL 請選擇合適的列深入理解where 1=1的用處按某一字段分組取最大(小)值所在行的數據通過存儲過程對SQLSERVER2005分頁 SQL分頁語句 SQLSERVERl時間函數

yxhxj2006

導航

留言簿(7)

隨筆分類

文章分類

隨筆檔案

閱讀排行榜

評論排行榜

常用鏈接

統計

最新評論

常見分析函數詳解

三、常見分析函數詳解