选读SQL经典实例笔记15_窗口函数
2023-08-03 07:48:09 来源: 博客园
(资料图片)
1.分组
1.1.把相似的行数据聚集在一起
2.SQL分组的定义
2.1.数学上的“群”（group）定义为 (G, •,e)，其中G是一个集合，• 表示G的二进制运算，而e则是G中的成员
2.2.一个SQL 分组须满足的两个定理
2.2.1.对于G的每一个成员e，e具有唯一性，并且存在一个或者多个e的实例
2.2.1.1.分组不为空
2.2.1.1.1.一个分组至少要拥有一个成员（行
2.2.1.1.2.无法从一个空表中生成任何分组
2.2.1.2.分组具有唯一性
2.2.1.2.1.如果查询语句使用了GROUP BY子句，那么通常而言SELECT列表里就不再需要使用DISTINCT关键字了
2.2.2.对于G的每一个成员e，聚合函数COUNT的返回值大于0
2.2.2.1.COUNT永远大于0
2.2.2.1.1.无法从一个空表里生成分组，因此一个分组至少会含有1行数据
2.2.2.1.2.既然至少有1行数据，那么COUNT查询的结果自然至少等于1
2.2.2.1.3.同时使用COUNT和GROUP BY的状况
2.2.2.1.4.如果不要GROUP BY子句，针对一个空表执行COUNT查询当然会得到0
2.2.2.1.4.1.只使用COUNT的状况
2.3.SQL 分组的概念依存于SQL 查询，没有SQL 查询就不会有SQL 分组
2.4.SQL 分组在技术上指的就是由行数据构成的结果集
3.PARTITION BY子句
3.1.针对行数据进行分区（partition）或者分组（group），并根据其结果执行聚合运算
3.2.“动态的GROUP BY”
3.2.1.在最终的结果集中允许出现多种由PARTITION BY生成的分区
3.3.在同一个SELECT语句里我们可以按照不同的列进行分区，而且不同的窗口函数调用之间互不影响
4.Null的影响
4.1.所有的Null归入同一个分区或者分组
4.2.COUNT(column)会忽略Null
4.3.如果希望把NULL值一并计入，则应该使用COUNT(*)
5.使用窗口函数的代码显得短小精悍
5.1.sql
select deptno, job, count(*) over (partition by deptno) as emp_cnt, count(job) over (partition by deptno,job) as job_cnt, count(*) over () as total from empDEPTNO JOB EMP_CNT JOB_CNT TOTAL------ --------- ---------- ---------- ---------- 10 CLERK 3 1 14 10 MANAGER 3 1 14 10 PRESIDENT 3 1 14 20 ANALYST 5 2 14 20 ANALYST 5 2 14 20 CLERK 5 2 14 20 CLERK 5 2 14 20 MANAGER 5 1 14 30 CLERK 6 1 14 30 MANAGER 6 1 14 30 SALESMAN 6 4 14 30 SALESMAN 6 4 14 30 SALESMAN 6 4 14 30 SALESMAN 6 4 145.1.1.窗口函数的出现使得许多通常被认为单纯使用标准SQL 难以解决的问题变得较为容易了
6.使用多个自连接和标量子查询
6.1.sql
select a.deptno, a.job, (select count(*) from emp b where b.deptno = a.deptno) as emp_cnt, (select count(*) from emp b where b.deptno = a.deptno and b.job = a.job) as job_cnt, (select count(*) from emp) as total from emp a order by 1,2DEPTNO JOB EMP_CNT JOB_CNT TOTAL------ --------- ---------- ---------- ---------- 10 CLERK 3 1 14 10 MANAGER 3 1 14 10 PRESIDENT 3 1 14 20 ANALYST 5 2 14 20 ANALYST 5 2 14 20 CLERK 5 2 14 20 CLERK 5 2 14 20 MANAGER 5 1 14 30 CLERK 6 1 14 30 MANAGER 6 1 14 30 SALESMAN 6 4 14 30 SALESMAN 6 4 14 30 SALESMAN 6 4 14 30 SALESMAN 6 4 147.窗口函数DENSE_RANK OVER
7.1.sql
select max(case grp when 1 then rpad(ename,6) || " ("|| sal ||")" end) top_3, max(case grp when 2 then rpad(ename,6) || " ("|| sal ||")" end) next_3, max(case grp when 3 then rpad(ename,6) || " ("|| sal ||")" end) rest from (select ename, sal, rnk, case when rnk ＜= 3 then 1 when rnk ＜= 6 then 2 else 3 end grp, row_number()over ( partition by case when rnk ＜= 3 then 1 when rnk ＜= 6 then 2 else 3 end order by sal desc, ename ) grp_rnk from (select ename, sal, dense_rank()over(order by sal desc) rnk from emp ) x ) y group by grp_rnkTOP_3 NEXT_3 REST--------------- --------------- -------------KING (5000) BLAKE (2850) TURNER (1500)FORD (3000) CLARK (2450) MILLER (1300)SCOTT (3000) ALLEN (1600) MARTIN (1250)JONES (2975) WARD (1250) ADAMS (1100) JAMES (950) SMITH (800)7.2.窗口函数最为引人注目的功能之一就是，只需访问一次原始数据就可以完成很多复杂的任务
7.3.不需要自连接或临时表，只要准备好必要的基础数据集，剩下的工作交给窗口函数处理就行了
8.为两次变换后的结果集增加列标题
8.1.sql
select * from it_researchDEPTNO ENAME------ -------------------- 100 HOPKINS 100 JONES 100 TONEY 200 MORALES 200 P.WHITAKER 200 MARCIANO 200 ROBINSON 300 LACY 300 WRIGHT 300 J.TAYLORselect * from it_appsDEPTNO ENAME------ ----------------- 400 CORRALES 400 MAYWEATHER 400 CASTILLO 400 MARQUEZ 400 MOSLEY 500 GATTI 500 CALZAGHE 600 LAMOTTA 600 HAGLER 600 HEARNS 600 FRAZIER 700 GUINN 700 JUDAH 700 MARGARITO8.2.sql
RESEARCH APPS-------------------- ---------------100 400 JONES MAYWEATHER TONEY CASTILLO HOPKINS MARQUEZ200 MOSLEY P.WHITAKER CORRALES MARCIANO 500 ROBINSON CALZAGHE MORALES GATTI300 600 WRIGHT HAGLER J.TAYLOR HEARNS LACY FRAZIER LAMOTTA 700 JUDAH MARGARITO GUINN8.3.sql
select max(decode(flag2,0,it_dept)) research, max(decode(flag2,1,it_dept)) apps from ( select sum(flag1)over(partition by flag2 order by flag1,rownum) flag, it_dept, flag2 from ( select 1 flag1, 0 flag2, decode(rn,1,to_char(deptno)," "||ename) it_dept from ( select x.*, y.id, row_number()over(partition by x.deptno order by y.id) rn from ( select deptno, ename, count(*)over(partition by deptno) cnt from it_research ) x, (select level id from dual connect by level ＜= 2) y ) where rn ＜= cnt+1 union all select 1 flag1, 1 flag2, decode(rn,1,to_char(deptno)," "||ename) it_dept from ( select x.*, y.id, row_number()over(partition by x.deptno order by y.id) rn from ( select deptno, ename, count(*)over(partition by deptno) cnt from it_apps ) x, (select level id from dual connect by level ＜= 2) y ) where rn ＜= cnt+1 ) tmp1 ) tmp2 group by flag