选读SQL经典实例笔记15_窗口函数
2023-08-01 本文已影响0人
躺柒
选读SQL经典实例笔记15_窗口函数.png
吐槽一下，图片上传失败一上午。。。。。。。
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 emp
DEPTNO 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 14
5.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,2
DEPTNO 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 14
7. 窗口函数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_rnk
TOP_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_research
DEPTNO ENAME
------ --------------------
100 HOPKINS
100 JONES
100 TONEY
200 MORALES
200 P.WHITAKER
200 MARCIANO
200 ROBINSON
300 LACY
300 WRIGHT
300 J.TAYLOR
select * from it_apps
DEPTNO 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 MARGARITO
8.2. sql
RESEARCH APPS
-------------------- ---------------
100 400
JONES MAYWEATHER
TONEY CASTILLO
HOPKINS MARQUEZ
200 MOSLEY
P.WHITAKER CORRALES
MARCIANO 500
ROBINSON CALZAGHE
MORALES GATTI
300 600
WRIGHT HAGLER
J.TAYLOR HEARNS
LACY FRAZIER
LAMOTTA
700
JUDAH
MARGARITO
GUINN
8.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
