图片 2

T-SQL:开窗函数(十二)

1.基本概念

    学习目标

开窗函数分为两个部分分别是

        -了解分析函数作用和类型

1.聚合,排名,偏移,分布函数 。

        -使用分析函数产生报告

2.开窗分区,排序,框架。

 

下面举个例子

    分析函数

SELECT empid, ordermonth, val,
  SUM(val) OVER(PARTITION BY empid
                ORDER BY ordermonth
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) AS runval
FROM Sales.EmpOrders;

       
分析函数用于计算一些基于组的聚合值,它与聚合函数的区别在于,分析函数每组返回多行,聚合函数每组返回一行。

sum(val)  就是集合函数

 

over() 就是开窗     PARTITION BY empid  就是开窗分区(分组)   ORDER BY
ordermonth 开窗排序  

    一般分析函数

 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  开窗架构

        ROW_NUMBER() OVER(PARTITION BY … ORDER BY …) 
按分区或返回记录生成唯一编号

2.排名开窗函数

        RANK() OVER(PARTITION BY … ORDER BY …) 
按分区或返回记录排序,会跳号

 SQL SERVER 支持4个排名函数 ROW_NUMBER,RANK,DENSE_RANK ,NTLE 
 来看看它们分别的作用

        DENSE_RANK() OVER(PARTITION BY … ORDER BY
…)  按分区或返回记录排序,不跳号

SELECT orderid, custid, val,
ROW_NUMBER() OVER(ORDER BY val) AS rownum,
RANK() OVER(ORDER BY val) AS rank,
DENSE_RANK() OVER(ORDER BY val) AS dense_rank,
NTILE(10) OVER(ORDER BY val) AS ntile
FROM Sales.OrderValues
ORDER BY val;

        COUNT() OVER(PARTITION BY … ORDER BY
…) 按分区或返回记录进行计数

图片 1

        MAX() OVER(PARTITION BY … ORDER BY
…) 按分区或返回记录计算最大值 

可以看到 它们不同排序规则

        MIN() OVER(PARTITION BY … ORDER BY
…) 按分区或返回记录计算最小值  

ROW_NUMBER() 对排序字段行号进行排序  

        SUM() OVER(PARTITION BY … ORDER BY
…) 按分区或返回记录进行求和

RANK() 对数值进行排序 对相同数值有行号占用

        AVG() OVER(PARTITION BY … ORDER BY
…) 按分区或返回记录求出平均值

DENSE_RANK() 也是对数值排序 如果有相同数值 依旧会按照原先行号加

        FIRST_VALUE() OVER(PARTITION BY … ORDER BY
…) 按分区或返回记录的第一个值

NTILE 分区排序 为每一行分配一个区号 如果分10区 会对所有数据进行分区 
总数据/分区数  就是每多少数据为一区

        LAST_VALUE() OVER(PARTITION BY … ORDER BY
…) 按分区或返回记录的最后一个值

ROW_NUMBER()  默认在 DISTINCT 筛选重复项之前计算

        LAG() OVER(PARTITION BY … ORDER BY
…) 按分区或返回记录上偏移值

2.偏移开窗函数 

        LEAD() OVER(PARTITION BY … ORDER BY
…) 按分区或返回记录下偏移值

sql server 中总共有4个偏移函数 LAG 和 LEAD, FIRST_VALUE 和 LAST_VALUE 
 

 

LAG函数在当前行之前查找

        函数语法说明

LEAD函数在当前行之后查找

            PARITION BY 

LAST_VALUE    返回查找结果的最后一行

                Function_name(…) Over(partition by col_name)

FIRST_VALUE  返回查找结果的第一行

                用于分区,按列进行分区

 3.聚合开窗函数

            ORDER BY (默认窗口)

SUM() OVER()

                Function_name(…) Over(Order by col_name [rows |
range between n|unbounded preceding and n| unbounded following])

AVG() OVER()

                -rows:【行】前n行、后n行或当前行,用于计算

COUNT() OVER()

                -range:【范围】大于或小于当前值的n行,或使用前n行来计算

MAX() OVER() 等等

                -unbounded:【无界限】所有行都使用计算

 

 

        ROW_NUMBER() OVER(partition by … order by …)

                功能与rownum伪列类似,order
by子句中指定有序列,从1开始为分区中的每一行或查询返回的每一行分配一个唯一的编号。

                

                注意ROW_NUMBER(这里不能限定列名)

                

查询按部门代码求出薪水排名:
13:49:52 SQL> set pagesize 500
13:50:09 SQL> col last_name format a20
13:50:09 SQL> select last_name,department_id,salary,row_number() over(partition by department_id order by salary ) row_num
13:50:09   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    ROW_NUM
-------------------- ------------- ---------- ----------
Whalen                          10       4390          1
Fay                             20       5990          1
Hartstein                       20      12990          2
Colmenares                      30       2490          1
Himuro                          30       2590          2
Tobias                          30       2790          3
Baida                           30       2890          4
Khoo                            30       3090          5
Raphaely                        30      10990          6
Mavris                          40       6490          1

 

            RANK()   OVER(PARTITION BY … ORDER BY …)             

                为查询返回的每一行并列排序,相同排名后的排名会出现跳号

 

查询部门代码50,工资在3000~6000之间的排名情况
13:56:59 SQL> set pagesize 500
13:57:30 SQL> col last_name format a20
13:57:30 SQL> select last_name,department_id,salary,rank() over(partition by department_id order by salary ) row_num
13:57:30   2  from employees
13:57:30   3  where department_id =50
13:57:30   4  and salary between 3000 and 6000;
LAST_NAME            DEPARTMENT_ID     SALARY    ROW_NUM
-------------------- ------------- ---------- ----------
Fleaur                          50       3090          1
Walsh                           50       3090          1
Davies                          50       3090          1
Nayer                           50       3190          4--出现跳号
McCain                          50       3190          4
Taylor                          50       3190          4
Stiles                          50       3190          4
Bissot                          50       3290          8--出现跳号
Mallin                          50       3290          8
Dellinger                       50       3390         10--出现跳号
Rajs                            50       3490         11
Dilly                           50       3590         12
Ladwig                          50       3590         12
Chung                           50       3790         14--出现跳号
Everett                         50       3890         15
Bell                            50       3990         16
Bull                            50       4090         17
Sarchand                        50       4190         18
Mourgos                         50       5790         19
已選取 19 個資料列.

 

            DENSE_RANK() OVER(PARTITION BY … ORDER BY …)

                为查询返回的每一行并列排序,相同排名后的排名不会跳号

 

查询部门代码50,工资在3000~6000之间的排名情况
14:01:48 SQL> set pagesize 500
14:02:06 SQL> col last_name format a20
14:02:06 SQL> select last_name,department_id,salary,dense_rank() over(partition by department_id order by salary ) row_num
14:02:06   2  from employees
14:02:06   3  where department_id =50
14:02:06   4  and salary between 3000 and 6000;
LAST_NAME            DEPARTMENT_ID     SALARY    ROW_NUM
-------------------- ------------- ---------- ----------
Fleaur                          50       3090          1
Walsh                           50       3090          1
Davies                          50       3090          1
Nayer                           50       3190          2
McCain                          50       3190          2
Taylor                          50       3190          2
Stiles                          50       3190          2
Bissot                          50       3290          3
Mallin                          50       3290          3
Dellinger                       50       3390          4
Rajs                            50       3490          5
Dilly                           50       3590          6
Ladwig                          50       3590          6
Chung                           50       3790          7
Everett                         50       3890          8
Bell                            50       3990          9
Bull                            50       4090         10
Sarchand                        50       4190         11
Mourgos                         50       5790         12
已選取 19 個資料列.

 

            COUNT() OVER(PARTITION BY … ORDER BY …)

                返回查询记录或分区的计数值(次数)

 

14:11:48 SQL> set pagesize 500
14:12:54 SQL> col last_name format a20
14:12:54 SQL> select last_name,department_id,salary,count(salary) over( partition by department_id ) count_num
14:12:54   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    COUNT_NUM
-------------------- ------------- ---------- ----------
Whalen                          10       4390          1--部门10中出现了1次
Hartstein                       20      12990          2--部门20中出现了2次
Fay                             20       5990          2--部门20中出现了2次
Raphaely                        30      10990          6--部门30中出现了6次
Khoo                            30       3090          6
Baida                           30       2890          6
Tobias                          30       2790          6
Himuro                          30       2590          6
Colmenares                      30       2490          6
Mavris                          40       6490          1
Weiss                           50       7990         45
Fripp                           50       8190         45
Kaufling                        50       7890         45
Vollman                         50       6490         45

 

            MAX() OVER(PARTITION BY … ORDER BY …)

                按分区返回最大的值

 

14:12:55 SQL> set pagesize 500
14:15:59 SQL> col last_name format a20
14:15:59 SQL> select last_name,department_id,salary,max(salary) over( partition by department_id ) max_sal
14:15:59   2  from employees
14:15:59   3  ;
LAST_NAME            DEPARTMENT_ID     SALARY    MAX_SAL
-------------------- ------------- ---------- ----------
Whalen                          10       4390       4390--部门10薪水最高的
Hartstein                       20      12990      12990--部门20薪水最高的
Fay                             20       5990      12990--部门20薪水最高的
Raphaely                        30      10990      10990--部门30薪水最高的
Khoo                            30       3090      10990
Baida                           30       2890      10990
Tobias                          30       2790      10990
Himuro                          30       2590      10990
Colmenares                      30       2490      10990
Mavris                          40       6490       6490--部门40薪水最高的
Weiss                           50       7990       8190--部门50薪水最高的
Fripp                           50       8190       8190
Kaufling                        50       7890       8190
Vollman                         50       6490       8190
Mourgos                         50       5790       8190

 

            MIN() OVER(PARTITION BY … ORDER BY …)

                按分区返回最小的值

 

14:16:00 SQL> set pagesize 500
14:18:10 SQL> col last_name format a20
14:18:10 SQL> select last_name,department_id,salary,min(salary) over( partition by department_id ) min_sal
14:18:10   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    MIN_SAL
-------------------- ------------- ---------- ----------
Whalen                          10       4390       4390
Hartstein                       20      12990       5990
Fay                             20       5990       5990
Raphaely                        30      10990       2490
Khoo                            30       3090       2490
Baida                           30       2890       2490
Tobias                          30       2790       2490
Himuro                          30       2590       2490
Colmenares                      30       2490       2490
Mavris                          40       6490       6490
Weiss                           50       7990       2090
Fripp                           50       8190       2090
Kaufling                        50       7890       2090
Vollman                         50       6490       2090
Mourgos                         50       5790       2090

 

            SUM() OVER(PARTITION BY … ORDER BY …)

                按分区汇总求和

 

查询所有薪水累积相加
14:32:10 SQL> set pagesize 500
14:32:40 SQL> col last_name format a20
14:32:40 SQL> select last_name,department_id,salary,sum(salary) over( order   by salary ) sum_sal
14:32:40   2  from employees
14:32:40   3  where department_id=30;

LAST_NAME            DEPARTMENT_ID     SALARY    SUM_SAL
-------------------- ------------- ---------- ----------
Colmenares                      30       2490       2490
Himuro                          30       2590       5080
Tobias                          30       2790       7870
Baida                           30       2890      10760
Khoo                            30       3090      13850
Raphaely                        30      10990      24840
已選取 6 個資料列.

查询按部门分区进行求和
14:19:28 SQL> set pagesize 500
14:20:06 SQL> col last_name format a20
14:20:06 SQL> select last_name,department_id,salary,sum(salary) over( partition by department_id ) sum_sal
14:20:06   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    SUM_SAL
-------------------- ------------- ---------- ----------
Whalen                          10       4390       4390
Hartstein                       20      12990      18980
Fay                             20       5990      18980
Raphaely                        30      10990      24840
Khoo                            30       3090      24840
Baida                           30       2890      24840
Tobias                          30       2790      24840
Himuro                          30       2590      24840
Colmenares                      30       2490      24840
Mavris                          40       6490       6490
Weiss                           50       7990     155950
Fripp                           50       8190     155950
Kaufling                        50       7890     155950

查询按部门分区前后2笔进行求和 【窗口期】
14:24:33 SQL> set pagesize 500
14:24:59 SQL> col last_name format a20
14:24:59 SQL> select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary rows between 2 preceding and 2 following ) sum_sal
14:24:59   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    SUM_SAL
-------------------- ------------- ---------- ----------
Whalen                          10       4390       4390 --部门只有1行
Fay                             20       5990      18980 
Hartstein                       20      12990      18980
Colmenares                      30       2490       7870--部门30,前后2行求和,7870=2490+2590+2790
Himuro                          30       2590      10760--部门30,前后2行求和,10760 =2490+2590+2790+2890
Tobias                          30       2790      13850--部门30,前后2行求和,13850 =2490+2590+2790+2890+3090
Baida                           30       2890      22350--部门30,前后2行求和,22350 =2590+2790+2890+3090+10990
Khoo                            30       3090      19760
Raphaely                        30      10990      16970
Mavris                          40       6490       6490
Olson                           50       2090       6470
Philtanker                      50       2190       8860
Markle                          50       2190      11250
Gee                             50       2390      11650
Landry                          50       2390      11950
Patel                           50       2490      12250
Vargas                          50       2490      12350
Marlow                          50       2490      12450
Perkins                         50       2490      12550
Sullivan                        50       2490      12650
OConnell                        50       2590      12750

范围在200以内的【窗口期】
set pagesize 500
col last_name format a20
select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary range between 200 preceding and 200 following ) sum_sal
from employees;

范围无限制【窗口期】
set pagesize 500
col last_name format a20
select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary range between unbounded preceding and unbounded following ) sum_sal
from employees;

set pagesize 500
col last_name format a20
select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary rows between unbounded preceding and unbounded following ) sum_sal
from employees;

 

            AVG() OVER(PARTITION BY … ORDER BY …)

                按分区求出平均值

            

14:39:56 SQL> set pagesize 500
14:39:56 SQL> col last_name format a20
14:39:56 SQL> select last_name,department_id,salary,avg(salary) over( partition by department_id ) avg_sal
14:39:56   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    AVG_SAL
-------------------- ------------- ---------- ----------
Whalen                          10       4390       4390
Hartstein                       20      12990       9490
Fay                             20       5990       9490
Raphaely                        30      10990       4140
Khoo                            30       3090       4140
Baida                           30       2890       4140
Tobias                          30       2790       4140
Himuro                          30       2590       4140
Colmenares                      30       2490       4140
Mavris                          40       6490       6490
Weiss                           50       7990 3465.55556
Fripp                           50       8190 3465.55556
Kaufling                        50       7890 3465.55556
Vollman                         50       6490 3465.55556
Mourgos                         50       5790 3465.55556

             FIRST_VALUE() OVER(PARTITION BY … ORDER BY
….) 返回一组有序的值中的第一个值。
如果集合中的第一个值为空,则函数将返回null,除非指定IGNORE NULLS

            LAST_VALUE() OVER(PARTITION BY … ORDER BY
….)  返回一组有序的值中的最后一个值。
如果集合中的最后一个值为空,则函数将返回null,除非指定IGNORE NULLS。

 

14:49:16 SQL> set pagesize 500
14:50:48 SQL> col last_name format a20
14:50:48 SQL> select last_name,department_id,salary,first_value(salary) over( partition by department_id) first_sal,
14:50:48   2  last_value(salary) over( partition by department_id ) last_sal
14:50:48   3  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY  FIRST_SAL   LAST_SAL
-------------------- ------------- ---------- ---------- ----------
Whalen                          10       4390       4390       4390
Hartstein                       20      12990      12990       5990
Fay                             20       5990      12990       5990
Raphaely                        30      10990      10990       2490
Khoo                            30       3090      10990       2490
Baida                           30       2890      10990       2490
Tobias                          30       2790      10990       2490
Himuro                          30       2590      10990       2490
Colmenares                      30       2490      10990       2490
Mavris                          40       6490       6490       6490
Weiss                           50       7990       7990       2590
Fripp                           50       8190       7990       2590
Kaufling                        50       7890       7990       2590

 

            LAG(column,n,default) OVER(PARTITION BY … ORDER BY …) 
 上偏移位置的返回值,n表示偏移量(正整数),default指定默认值

            LEAD(column,n,default) OVER(PARTITION BY … ORDER BY …) 
下偏移位置的返回值,n表示偏移量(正整数) ,default指定默认值

            

14:58:21 SQL> set pagesize 500
14:58:33 SQL> col last_name format a20
14:58:33 SQL> select last_name,department_id,lag(salary,1,0) over( partition by department_id order by salary) lag_sal,
14:58:33   2  salary,
14:58:33   3  lead(salary,1,NULL) over( partition by department_id order by salary) lead_sal
14:58:33   4  from employees;
LAST_NAME            DEPARTMENT_ID    LAG_SAL     SALARY   LEAD_SAL
-------------------- ------------- ---------- ---------- ----------
Whalen                          10          0       4390
Fay                             20          0       5990      12990
Hartstein                       20       5990      12990
Colmenares                      30          0       2490       2590
Himuro                          30       2490       2590       2790
Tobias                          30       2590       2790       2890
Baida                           30       2790       2890       3090
Khoo                            30       2890       3090      10990
Raphaely                        30       3090      10990
Mavris                          40          0       6490
Olson                           50          0       2090       2190
Philtanker                      50       2090       2190       2190
Markle                          50       2190       2190       2390
Gee                             50       2190       2390       2390

图片 2

学习总结:

        1.了解分析函数和聚合(组)函数之间的差异

        2.一般分析函数大致分为4类

            伪列类:row_number() over()、rank() over()和dense_rank()
over()

            计算类:sum() over()、count() over()、max() over()、min()
over()和avg() over()

            返回值类:first_value() over()和last_value()  over()

            偏移类:lag() over()和lead() over