Oracle数据库中分析函数的使用过程

Oracle数据库中分析函数的使用过程
–创建测试数据库表
/*CREATE TABLE EARNINGS – 打工赚钱表
(
EARNMONTH VARCHAR2(6), – 打工月份
AREA VARCHAR2(20), – 打工地区
SNO VARCHAR2(10), – 打工者编号
SNAME VARCHAR2(20), – 打工者姓名
TIMES INT, – 本月打工次数
SINGLEINCOME NUMBER(10,2), – 每次赚多少钱
PERSONINCOME NUMBER(10,2) – 当月总收入
)*/

–插入测试数据表信息

/*INSERT INTO EARNINGS VALUES(‘200912’,’北平’,’511601’,’大魁’,11,30,11*30);
INSERT INTO EARNINGS VALUES(‘200912’,’北平’,’511602’,’大凯’,8,25,8*25);
INSERT INTO EARNINGS VALUES(‘200912’,’北平’,’511603’,’小东’,30,6.25,30*6.25);
INSERT INTO EARNINGS VALUES(‘200912’,’北平’,’511604’,’大亮’,16,8.25,16*8.25);
INSERT INTO EARNINGS VALUES(‘200912’,’北平’,’511605’,’贱敬’,30,11,30*11);

INSERT INTO EARNINGS VALUES(‘200912’,’金陵’,’511301’,’小玉’,15,12.25,15*12.25);
INSERT INTO EARNINGS VALUES(‘200912’,’金陵’,’511302’,’小凡’,27,16.67,27*16.67);
INSERT INTO EARNINGS VALUES(‘200912’,’金陵’,’511303’,’小妮’,7,33.33,7*33.33);
INSERT INTO EARNINGS VALUES(‘200912’,’金陵’,’511304’,’小俐’,0,18,0);
INSERT INTO EARNINGS VALUES(‘200912’,’金陵’,’511305’,’雪儿’,11,9.88,11*9.88);

INSERT INTO EARNINGS VALUES(‘201001’,’北平’,’511601’,’大魁’,0,30,0);
INSERT INTO EARNINGS VALUES(‘201001’,’北平’,’511602’,’大凯’,14,25,14*25);
INSERT INTO EARNINGS VALUES(‘201001’,’北平’,’511603’,’小东’,19,6.25,19*6.25);
INSERT INTO EARNINGS VALUES(‘201001’,’北平’,’511604’,’大亮’,7,8.25,7*8.25);
INSERT INTO EARNINGS VALUES(‘201001’,’北平’,’511605’,’贱敬’,21,11,21*11);

INSERT INTO EARNINGS VALUES(‘201001’,’金陵’,’511301’,’小玉’,6,12.25,6*12.25);
INSERT INTO EARNINGS VALUES(‘201001’,’金陵’,’511302’,’小凡’,17,16.67,17*16.67);
INSERT INTO EARNINGS VALUES(‘201001’,’金陵’,’511303’,’小妮’,27,33.33,27*33.33);
INSERT INTO EARNINGS VALUES(‘201001’,’金陵’,’511304’,’小俐’,16,18,16*18);
INSERT INTO EARNINGS VALUES(‘201001’,’金陵’,’511305’,’雪儿’,11,9.88,11*9.88);*/

SELECT * FROM EARNINGS;

–根据月份统计每个地区的总收入
SELECT E.EARNMONTH, E.AREA, SUM(E.PERSONINCOME)
FROM EARNINGS E
GROUP BY E.EARNMONTH, E.AREA;

–按照月份地区统计收入
SELECT E.EARNMONTH,
NVL(E.AREA, E.EARNMONTH || ‘地区总收入’),
SUM(E.PERSONINCOME)
FROM EARNINGS E
GROUP BY ROLLUP(E.EARNMONTH, E.AREA);

–按照月份地区进行收入总汇总
SELECT E.EARNMONTH, E.AREA, SUM(E.PERSONINCOME)
FROM EARNINGS E
GROUP BY CUBE(E.EARNMONTH, E.AREA)
ORDER BY E.EARNMONTH, E.AREA;

–测试使用grouping函数
–是用rollup和cube函数都会对结果集产生null，这时候可用grouping函数来确认
–该记录是由哪个字段得出来的
–grouping函数用法，带一个参数，参数为字段名，结果是根据该字段得出来的就返回1，反之返回0
SELECT DECODE(GROUPING(E.EARNMONTH), 1, ‘所有月份’, E.EARNMONTH),
DECODE(GROUPING(E.AREA), 1, ‘所有地区’, E.AREA),
SUM(E.PERSONINCOME)
FROM EARNINGS E
GROUP BY CUBE(E.EARNMONTH, E.AREA)
ORDER BY E.EARNMONTH, E.AREA NULLS LAST;

–使用rank() over开窗函数 按照月份、地区、求打工收入排序
SELECT E.EARNMONTH,
E.AREA,
E.SNO,
E.SNAME,
E.PERSONINCOME,
RANK() OVER(PARTITION BY E.EARNMONTH, E.AREA ORDER BY E.PERSONINCOME DESC) AS ORDERK –rank跳跃排序
FROM EARNINGS E
–查询地区中月份收入排名第一个的人员
SELECT *
FROM (SELECT E.EARNMONTH,
E.AREA,
E.SNO,
E.SNAME,
E.PERSONINCOME,
RANK() OVER(PARTITION BY E.EARNMONTH, E.AREA ORDER BY E.PERSONINCOME DESC) AS ORDERK –rank跳跃排序
FROM EARNINGS E) TEMP
WHERE TEMP.ORDERK = 1

–使用dense_rank() over开窗函数 按照月份、地区，求打工收入排序 dense_rank
SELECT E.EARNMONTH,
E.AREA,
E.SNO,
E.SNAME,
E.PERSONINCOME,
DENSE_RANK() OVER(PARTITION BY E.EARNMONTH, E.AREA ORDER BY E.PERSONINCOME DESC) AS ORDERK –顺序排序
FROM EARNINGS E;

–使用row_number() over开窗函数 按照月份、地区，求打工收入排序
SELECT E.EARNMONTH,
E.AREA,
E.SNO,
E.SNAME,
E.PERSONINCOME,
ROW_NUMBER() OVER(PARTITION BY E.EARNMONTH, E.AREA ORDER BY E.PERSONINCOME DESC)
FROM EARNINGS E
–发现rank,dense_rank,row_number的区别：
–1>结果集中如果出现两个相同的数据，那么rank会进行跳跃式的排名，
–2>比如两个第二，那么没有第三接下来就是第四；
–3>但是dense_rank不会跳跃式的排名，两个第二接下来还是第三；
–4>row_number最牛，即使两个数据相同，排名也不一样。

–根据月份求出各个打工者收入总和，按照收入由少到多排序
SELECT E.EARNMONTH,
E.AREA,
E.SNO,
E.SNAME,
SUM(E.PERSONINCOME) OVER(PARTITION BY E.EARNMONTH, E.AREA ORDER BY E.PERSONINCOME)
FROM EARNINGS E;

– max，min，avg和sum函数综合运用 按照月份和地区求打工收入最高值，最低值，平均值和总额
SELECT DISTINCT E.EARNMONTH,
E.AREA,
E.SNAME,
MAX(E.PERSONINCOME) OVER(PARTITION BY E.EARNMONTH, E.AREA) AS MAX_,
MIN(E.PERSONINCOME) OVER(PARTITION BY E.EARNMONTH, E.AREA) AS MIN_,
AVG(E.PERSONINCOME) OVER(PARTITION BY E.EARNMONTH, E.AREA) AS AVG_,
SUM(E.PERSONINCOME) OVER(PARTITION BY E.EARNMONTH, E.AREA) AS SUM_
FROM EARNINGS E;