您的位置: 首页  >  IT文章  >  oracle分析函数与over()（转）

oracle分析函数与over()（转）

分类: IT文章 • 2022-05-04 14:31:12

文章参考：http://blog.csdn.net/haiross/article/details/15336313

-- Oracle分析函数入门
-- 分析函数是什么？ 分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值
-- 分析函数和聚合函数的不同之处是什么？ 普通的二居和函数用group by分组，每一个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值
-- 分析函数的形式
-- 分析函数带有一个开窗函数over(),包含三个分析子句:分组（partition by），排序（order by），窗口（rows），它们的使用形式如下：
-- over(paritition by xxx order by yyy rows between zzz)
-- 住：窗口子句在我这里只说rows方式的窗口，range方式和滑动窗口也不提
-- 分析函数的例子：

-- 显示各部门员工的工资，并附带显示该部分的最高工资。
-- unbound preceding and unbound following 针对当前记录行的前不受限制，后不受限制，就是所有表中的记录
-- unbounded:不受控制的，无限的
-- preceding：在...之前
-- following：在...之后
SELECT E.DEPTNO,E.EMPNO,E.ENAME
,E.SAL LAST_VALUE(E.SAL)
OVER(PARITITION BY E.DEPTNO
ORDER BY E.SAL
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) MAX_SAL
FROM EMP E;
-- 按照deptno分组，然后计算每组值的总和
SELECT EMPNO,EMPNAME,DEPTNO,SAL,
SUM(SAL) OVER(
PARITITY BY DEPTNO
ORDER BY ENAME) max_sal
FROM SOCCT.EMP;
-- 对各部门进行分组，并附带第一行至当前行的汇总
-- ROWS BETWEEN unbounded precrding and current row 是指第一行至当前行的汇总
SELECT EMPNO,ENAME,DEPTNO,SAL,
SUM(SAL) OVER(
PARITITY BY DEPTNO
ORDER BY ENAME
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWS
) max_sal
FROM SCOTT.EMP;
-- 当前行至最后一行的汇总
-- ROWS BETWEEN current row AND unbounded following指当前行到最后一行的汇总
SELECT EMPNO, ENAME, DEPTNO, SAL,
SUM(SAL) OVER(PARITITY BY DEPTNO
ORDER BY ENAME
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal
FROM SCOTT.EMP;

-- 当前行的上一行（rownum-1）到当前行的汇总
SELECT EMPNO,ENAME,DEPTNO,SAL,
SUM(SAL) OVER(PARTITION BY DEPTNO
ORDER BY ENAME ROWS
BETWEEN 1 PERCEDING AND CURRENT ROW
) max_sal
FROM SCOTT.EMP;
-- 当前行的上一行（rownum-1）到当前行的下两行（rownum+2）的汇总
SELECT EMPNO,ENAME,DEPTNO,SAL,
SUM(SAL) OVER(PARTITION BY DEPTNO
ORDER BY ENAME
ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING
) max_sal
FROM SCOTT.EMP;
/**
理解over()函数
1、两个order by的执行时机
分析函数（以及与其配合的开窗函数over()）是在整个sql查询结束后（sql语句中的order by的执行比较特殊）再进行的操作
，也就是说sql语句中的order by也会影响分析函数的执行结果：
a)两者一致：如果sql语句中的order by满足与分析函数配合的开窗函数over()分析时要求的排序，即sql语句中的order by子句里
的内容和开窗函数over（）椎间盘美好的order by子句里的内容一样，那么sql中的排序先执行，分析函数在分析时就不必再排序
b）两者不一致：如果sql语句中的order by不满足与分析函数配合的开窗函数over（）分析时要求的排序，即sql语句中的order by
子句里的内容和开窗函数order by子句里的内容不一样：那么sql语句中的排序将在最后在分析函数结束后执行排序。

2、开窗函数over（）中的分组、排序、窗口
开窗函数over（）包含三个分析子句：分组子句（partition by）、排序子句（order by），窗口子句（rows）
窗口就是分析函数分析时要处理的数据范围，就拿sum来说，它是sum窗口中的记录而不是整个分组中的记录，因此我们在想要得到某个
栏位的累计值的时候，我们需要把窗口置顶到该分组中的第一行数据到当前行，如果你置顶该窗口从该分组红的第一行到最后一行，那么sum的结果都一样。
窗口子句这里只说rows方式的窗口。
窗口子句中我们经常用到置顶第一行，当前行，最后一行这样的三个属性：
当前行是current row
第一行是unbounded precrding
最后一行是unbounded following
注意：当开窗函数over（）出现分组（paritition by）子句时，unbounded preceding和作用范围是表中的一个分组；当没有parition by时，作用范围是整个表

如果忽略分组，则将全部记录当成一个组
窗口子句不能单独出现，必须有order by子句时才能出现
/

分析函数练习：

-- 建表
CREATE TABLE T(
bill_month VARCHAR2(12), -- 月
area_code NUMBER, -- 地区
net_type VARCHAR2(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;

-- 取出每月通话话费最高和最低的两个地区
SELECT bill_month,area_code,SUM(local_fare) localfare,
first_value(SUM(local_fare)) over(partition by bill_month) "firstval",
last_value(SUM(local_fare)) over(partition by bill_month) "lastval"
FROM T
GROUP BY bill_month,area_code
ORDER BY localfare;
-- 这个有点难度，要注意开窗函数的作用对象是组，如果没分组作用的是整个表，注意order by语句的执行顺序，如果窗口中的order by与外边的order by 相同，窗口内的oreder by可以省略
-- 注意执行顺序，先执行外面的分组再执行开窗，开窗内的字段一定是基础字段或者基础字段组成的函数而不是别名

其他常见分析函数：

常见分析函数

first_value()与last_value():求最值对应的其他属性
要求：取出每月通话话费最高和最低的两个地区
SELECT bill_month,area_code,SUM(local_fare) localfare,
first_value(SUM(local_fare)) over(partition by bill_month) "firstval",
last_value(SUM(local_fare)) over(partition by bill_month) "lastval"
FROM T
GROUP BY bill_month,area_code
ORDER BY localfare;
解析：这个有点难度，要注意开窗函数的作用对象是组，如果没分组作用的是整个表，注意order by语句的执行顺序，如果窗口中的order by与外边的order by 相同，窗口内的oreder by可以省略注意执行顺序，先执行外面的分组再执行开窗，开窗内的字段一定是基础字段或者基础字段组成的函数而不是别名

rank(),dense_rank()与row_number():求排序
rank、dense_rank、row_number函数为每条记录产生一个从1开始至n的自然数，n的值可能小于等于记录的总数，这三个函数的唯一区别在于碰到相同数据时的排名策略：
row_number：rownumber函数返回一个唯一的值，当碰到相同的数据时，排名按照记录集中记录的顺序依次递增
dense_rank:dense_rank函数返回一个唯一的值，当碰到相同的数据时，此时所有的相同数据的排名都是一样的
rank：rank函数返回一个唯一的值，当碰到相同的数据时，此时所有相同数据的而排名是一样的，同事会在最后一条记录和下一条记录的之间空出排名。

-- 查询每一个部门的工资工资前4的员工的信息
SELECT * FROM (
SELECT DEPTNO,RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW,ENAME,SAL
FROM EMP)
WHERE RW<=4
rank()值相同时排名相同，其后排名跳跃不连续
dense_rank()值相同时排名相同，其后排名连续不跳跃
row_number()值相同时排名不相等，其后排名连续不跳跃

查询group by体验：
普通group by
group by rollup(A,B,C):先对A、B、C进行group by，再对a进行group by，再对b进行group by
groub by cube(A,B,C)：先对a、b、c进行分组再一次对（a、b）（a、c）、a、（b、c）、b、c

然后就设计max(),min()和avg了。

相关推荐