三、常见分析函数详解
三、常见分析函数详解
为了方便进行实践,特将演示表和数据罗列如下:
一、创建表
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
运行结果:
--------------------------------------------------------------------------
转载于:https://www.cnblogs.com/linjiqin/archive/2012/04/06/2434806.html