最新要闻

广告

手机

最新！男子38万元劳力士寄丢，顺丰通报：手表找回了，快递员没责任，初判系外部人员所为

最新！男子38万元劳力士寄丢，顺丰通报：手表找回了，快递员没责任，初判系外部人员所为

 机构：三季度手机面板价格总体将逐步趋于稳定

机构：三季度手机面板价格总体将逐步趋于稳定

家电

  1. 首页
  2. 移动通信
  3. 内容

选读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 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         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,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         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_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 MARGARITO

8.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                       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

关键词：