选读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

 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

热门相关:亿万盛宠只为你   她们的果汁   爸爸,女儿和女儿朋友   富贵不能吟   大妆