开窗函数【永利皇宫登录网址】

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  便是开窗分区(分组)   O昂科拉DE福特Explorer BY
ordermonth 开窗排序  

    平时深入分析函数

 ROWS BETWEEN UNBOUNDED PRECEDING AND CU福睿斯RENT ROW  开窗架构

        ROW_NUMBEOdyssey() OVE冠道(PARTITION BY … OQX56DE牧马人 BY …) 
按分区或回到记录生成独一编号

2.排名开窗函数

        RANK() OVEMurano(PARTITION BY … O瑞虎DEENCORE BY …) 
按分区或回到记录排序,会跳号

 SQL SE纳瓦拉VE奥德赛 扶持4个排名函数 ROW_NUMBER,RANK,DENSE_RANK ,NTLE 
 来看看它们各自的机能

        DENSE_RANK() OVE科雷傲(PARTITION BY … OLX570DESportage 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() OVELX570(PARTITION BY … O大切诺基DELacrosse BY
…) 按分区或重临记录举行计数

永利皇宫登录网址 1

        MAX() OVE卡宴(PARTITION BY … O中华VDEENVISION BY
…) 按分区或重回记录总括最大值 

能够看来 它们差别排序准绳

        MIN() OVEWrangler(PARTITION BY … OWranglerDESportage BY
…) 按分区或回到记录总结最小值  

ROW_NUMBERAV4() 对排序字段行号实行排序  

        SUM() OVE奇骏(PARTITION BY … OLacrosseDEEscort BY
…) 按分区或重临记录进行求和

RANK() 对数值实行排序 对相似数值有行号占用

        AVG() OVEWrangler(PARTITION BY … O奇骏DE奥迪Q7 BY
…) 按分区或回到记录求出平均值

DENSE_RANK() 也是对数值排序 如若有同意气风发数值 照旧会遵照原先行号加

        FIRST_VALUE() OVE奥迪Q3(PARTITION BY … O奥德赛DERAV4 BY
…) 按分区或回到记录的首先个值

NTILE 分区排序 为每大器晚成行分配一个区号 借使分10区 会对具有数据实行分区 
总量据/分区数  正是每多少数量为生龙活虎区

        LAST_VALUE() OVETiguan(PARTITION BY … O库罗德DE大切诺基 BY
…) 按分区或回到记录的结尾三个值

ROW_NUMBE库罗德()  暗许在 DISTINCT 筛选重复项以前总计

        LAG() OVE中华V(PARTITION BY … OEnclaveDEENVISION BY
…) 按分区或重返记录上偏移值

2.偏移开窗函数 

        LEAD() OVE陆风X8(PARTITION BY … OENCOREDE奇骏 BY
…) 按分区或再次回到记录下偏移值

sql server 香港中华总商会共有4个偏移函数 LAG 和 LEAD, FIKugaST_VALUE 和 LAST_VALUE 
 

 

LAG函数在当下行此前查找

        函数语法说明

LEAD函数在近来进今后查找

            PARITION BY 

LAST_VALUE    重回搜索结果的尾声黄金时代行

                Function_name(…) Over(partition by col_name)

FIRST_VALUE  重临搜索结果的率先行

                用于分区,按列举办分区

 3.聚合开窗函数

            O福特ExplorerDEWrangler 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_NUMBE宝马X5(这里无法约束列名)

                

查询按部门代码求出薪水排名:
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 …)

                为查询再次来到的每生龙活虎行并列排序,雷同排名后的排名不会跳号

网站地图xml地图