為了方便進行實踐，特將演示表和數據羅列如下：
一、創建表
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(滿)