开窗函数

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

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

本文由澳门新萄京官方网站发布于信息数据库,转载请注明出处:开窗函数

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。