為了方便進(jìn)行實(shí)踐，特將演示表和數(shù)據(jù)羅列如下：
一、創(chuàng)建表
create table t(
bill_month varchar2(12) ,
area_code number,
net_type varchar(2),
local_fare number
);
二、插入數(shù)據(jù)
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()：求最值對(duì)應(yīng)的其他屬性
問題、取出每月通話費(fèi)最高和最低的兩個(gè)地區(qū)。
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
運(yùn)行結(jié)果：
四、rank(),dense_rank()與row_number()：求排序
rank，dense_rank，row_number函數(shù)為每條記錄產(chǎn)生一個(gè)從1開始至n的自然數(shù)，n的值可能小于等于記錄的總數(shù)。這3個(gè)函數(shù)的唯一區(qū)別在于當(dāng)碰到相同數(shù)據(jù)時(shí)的排名策略。
①row_number：
row_number函數(shù)返回一個(gè)唯一的值，當(dāng)碰到相同數(shù)據(jù)時(shí)，排名按照記錄集中記錄的順序依次遞增。
②dense_rank：
dense_rank函數(shù)返回一個(gè)唯一的值，當(dāng)碰到相同數(shù)據(jù)時(shí)，此時(shí)所有相同數(shù)據(jù)的排名都是一樣的。
③rank：
rank函數(shù)返回一個(gè)唯一的值，當(dāng)碰到相同的數(shù)據(jù)時(shí)，此時(shí)所有相同數(shù)據(jù)的排名是一樣的，同時(shí)會(huì)在最后一條相同記錄和下一條不同記錄的排名之間空出排名。
演示數(shù)據(jù)在Oracle自帶的scott用戶下：
1、rank()值相同時(shí)排名相同，其后排名跳躍不連續(xù)
SELECT *
FROM (SELECT DEPTNO,
RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW,
ENAME,
SAL
FROM SCOTT.EMP)
WHERE RW <= 4;
運(yùn)行結(jié)果：
2、dense_rank()值相同時(shí)排名相同，其后排名連續(xù)不跳躍
SELECT *
FROM (SELECT DEPTNO,
DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW,
ENAME,
SAL
FROM SCOTT.EMP)
WHERE RW <= 4;
運(yùn)行結(jié)果：
3、row_number()值相同時(shí)排名不相等，其后排名連續(xù)不跳躍
SELECT *
FROM (SELECT DEPTNO,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW,
ENAME,
SAL
FROM SCOTT.EMP)
WHERE RW <= 4;
運(yùn)行結(jié)果：
五、lag()與lead()：求之前或之后的第N行
lag和lead函數(shù)可以在一次查詢中取出同一字段的前n行的數(shù)據(jù)和后n行的值。這種操作可以使用對(duì)相同表的表連接來實(shí)現(xiàn)，不過使用lag和lead有更高的效率。
lag(arg1,arg2,arg3)
第一個(gè)參數(shù)是列名，
第二個(gè)參數(shù)是偏移的offset，
第三個(gè)參數(shù)是超出記錄窗口時(shí)的默認(rèn)值。
舉例如下：
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)：
首先會(huì)對(duì)(a、b、c)進(jìn)行g(shù)roup by，
然后再對(duì)(a、b)進(jìn)行g(shù)roup by，
其后再對(duì)(a)進(jìn)行g(shù)roup by，
最后對(duì)全表進(jìn)行匯總操作。
2)、group by cube(a, b, c)：
則首先會(huì)對(duì)(a、b、c)進(jìn)行g(shù)roup by，
然后依次是(a、b)，(a、c)，(a)，(b、c)，(b)，(c)，
最后對(duì)全表進(jìn)行匯總操作。
1、生成演示數(shù)據(jù)：
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體驗(yàn)
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)：
首先會(huì)對(duì)(A、B、C)進(jìn)行GROUP BY，
然后再對(duì)(A、B)進(jìn)行GROUP BY，
其后再對(duì)(A)進(jìn)行GROUP BY，
最后對(duì)全表進(jìn)行匯總操作。
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)：
則首先會(huì)對(duì)(A、B、C)進(jìn)行GROUP BY，
然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，
最后對(duì)全表進(jìn)行匯總操作。
sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by cube(owner, index_type, status);
七、max(),min(),sun()與avg()：求移動(dòng)的最值總和與平均值
問題：計(jì)算出各個(gè)地區(qū)連續(xù)3個(gè)月的通話費(fèi)用的平均數(shù)(移動(dòng)平均值)
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)
運(yùn)行結(jié)果：
問題：求各地區(qū)按月份累加的通話費(fèi)
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
運(yùn)行結(jié)果：
--------------------------------------------------------------------------
Blog：http://www.cnblogs.com/linjiqin/
J2EE、Android、Linux、Oracle QQ交流群：142463980、158560018(滿)