选读SQL经典实例笔记15_窗口函数

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

1. select deptno,
2.        job,
3.        count(*) over (partition by deptno) as emp_cnt,
4.        count(job) over (partition by deptno,job) as job_cnt,
5.        count(*) over () as total
6.   from emp
7. DEPTNO JOB          EMP_CNT    JOB_CNT      TOTAL
8. ------ --------- ---------- ---------- ----------
9.     10 CLERK              3          1         14
10.     10 MANAGER            3          1         14
11.     10 PRESIDENT          3          1         14
12.     20 ANALYST            5          2         14
13.     20 ANALYST            5          2         14
14.     20 CLERK              5          2         14
15.     20 CLERK              5          2         14
16.     20 MANAGER            5          1         14
17.     30 CLERK              6          1         14
18.     30 MANAGER            6          1         14
19.     30 SALESMAN           6          4         14
20.     30 SALESMAN           6          4         14
21.     30 SALESMAN           6          4         14
22.     30 SALESMAN           6          4         14

复制代码

5.1.1. 窗口函数的出现使得许多通常被认为单纯使用标准SQL 难以解决的问题变得较为容易了

6. 使用多个自连接和标量子查询

6.1. sql

1. select a.deptno, a.job,
2.        (select count(*) from emp b
3.          where b.deptno = a.deptno) as emp_cnt,
4.        (select count(*) from emp b
5.          where b.deptno = a.deptno and b.job = a.job) as job_cnt,
6.        (select count(*) from emp) as total
7.   from emp a
8. order by 1,2
9. DEPTNO JOB          EMP_CNT    JOB_CNT      TOTAL
10. ------ --------- ---------- ---------- ----------
11.     10 CLERK              3          1         14
12.     10 MANAGER            3          1         14
13.     10 PRESIDENT          3          1         14
14.     20 ANALYST            5          2         14
15.     20 ANALYST            5          2         14
16.     20 CLERK              5          2         14
17.     20 CLERK              5          2         14
18.     20 MANAGER            5          1         14
19.     30 CLERK              6          1         14
20.     30 MANAGER            6          1         14
21.     30 SALESMAN           6          4         14
22.     30 SALESMAN           6          4         14
23.     30 SALESMAN           6          4         14
24.     30 SALESMAN           6          4         14

复制代码

7. 窗口函数DENSE_RANK OVER

7.1. sql

1. select max(case grp when 1 then rpad(ename,6) ||
2.                     ' ('|| sal ||')' end) top_3,
3.        max(case grp when 2 then rpad(ename,6) ||
4.                     ' ('|| sal ||')' end) next_3,
5.        max(case grp when 3 then rpad(ename,6) ||
6.                     ' ('|| sal ||')' end) rest
7.   from (
8. select ename,
9.        sal,
10.        rnk,
11.        case when rnk ＜= 3 then 1
12.             when rnk ＜= 6 then 2
13.             else 3
14.        end grp,
15.        row_number()over (
16.          partition by case when rnk ＜= 3 then 1
17.                            when rnk ＜= 6 then 2
18.                            else 3
19.                        end
20.              order by sal desc, ename
21.        ) grp_rnk
22.   from (
23. select ename,
24.        sal,
25.        dense_rank()over(order by sal desc) rnk
26.   from emp
27.        ) x
28.        ) y
29. group by grp_rnk
30. TOP_3           NEXT_3          REST
31. --------------- --------------- -------------
32. KING   (5000)   BLAKE  (2850)   TURNER (1500)
33. FORD   (3000)   CLARK  (2450)   MILLER (1300)
34. SCOTT  (3000)   ALLEN  (1600)   MARTIN (1250)
35. JONES  (2975)                   WARD   (1250)
36.                                 ADAMS  (1100)
37.                                 JAMES  (950)
38.                                 SMITH  (800)

复制代码

7.2. 窗口函数最为引人注目的功能之一就是，只需访问一次原始数据就可以完成很多复杂的任务

7.3. 不需要自连接或临时表，只要准备好必要的基础数据集，剩下的工作交给窗口函数处理就行了

8. 为两次变换后的结果集增加列标题

8.1. sql

1. select * from it_research
2. DEPTNO ENAME
3. ------ --------------------
4.    100 HOPKINS
5.    100 JONES
6.    100 TONEY
7.    200 MORALES
8.    200 P.WHITAKER
9.    200 MARCIANO
10.    200 ROBINSON
11.    300 LACY
12.    300 WRIGHT
13.    300 J.TAYLOR
14. select * from it_apps
15. DEPTNO ENAME
16. ------ -----------------
17.    400 CORRALES
18.    400 MAYWEATHER
19.    400 CASTILLO
20.    400 MARQUEZ
21.    400 MOSLEY
22.    500 GATTI
23.    500 CALZAGHE
24.    600 LAMOTTA
25.    600 HAGLER
26.    600 HEARNS
27.    600 FRAZIER
28.    700 GUINN
29.    700 JUDAH
30.    700 MARGARITO

复制代码

8.2. sql

1. RESEARCH             APPS
2. -------------------- ---------------
3. 100                  400
4.   JONES                MAYWEATHER
5.   TONEY                CASTILLO
6.   HOPKINS              MARQUEZ
7. 200                    MOSLEY
8.   P.WHITAKER           CORRALES
9.   MARCIANO           500
10.   ROBINSON             CALZAGHE
11.   MORALES              GATTI
12. 300                  600
13.   WRIGHT               HAGLER
14.   J.TAYLOR             HEARNS
15.   LACY                 FRAZIER
16.                        LAMOTTA
17.                      700
18.                        JUDAH
19.                        MARGARITO
20.                        GUINN

复制代码

8.3. sql

1. select max(decode(flag2,0,it_dept)) research,
2.         max(decode(flag2,1,it_dept)) apps
3.    from (
4. select sum(flag1)over(partition by flag2
5.                            order by flag1,rownum) flag,
6.         it_dept, flag2
7.    from (
8. select 1 flag1, 0 flag2,
9.         decode(rn,1,to_char(deptno),'  '||ename) it_dept
10.    from (
11. select x.*, y.id,
12.         row_number()over(partition by x.deptno order by y.id) rn
13.    from (
14. select deptno,
15.         ename,
16.         count(*)over(partition by deptno) cnt
17.    from it_research
18.         ) x,
19.         (select level id from dual connect by level ＜= 2) y
20.         )
21.   where rn ＜= cnt+1
22. union all
23. select 1 flag1, 1 flag2,
24.         decode(rn,1,to_char(deptno),'  '||ename) it_dept
25.    from (
26. select x.*, y.id,
27.         row_number()over(partition by x.deptno order by y.id) rn
28.    from (
29. select deptno,
30.         ename,
31.         count(*)over(partition by deptno) cnt
32.    from it_apps
33.         ) x,
34.         (select level id from dual connect by level ＜= 2) y
35.         )
36.   where rn ＜= cnt+1
37.         ) tmp1
38.         ) tmp2
39.   group by flag

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！