16. 窗口函数 (Window Function) 的运用

SQL Server开始支持窗口函数 (Window,以及到SQL Server,开窗函数分别应用于每个分区,  在开窗函数出现之前存在着很多用 SQL,分析函数和聚合函数的不同之处是什么,by分组,和

金沙国际唯一官网网址 15

 

注意:OAMG ONEDESportage BY 子句钦定对相应 FROM
子句生成的行集举行分区所遵照的列。value_expression 只可以援引通过 FROM
子句可用的列。value_expression
无法援用选取列表中的表明式或外号。value_expression
能够是列表明式、标量子查询、标量函数或用户定义的变量。

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal
  FROM SCOTT.EMP;

上面是应和的查询结果:

帮衬文档里的代码示例很全。

四、NEXT VALUE FOR 函数

金沙国际唯一官网网址 1

金沙国际唯一官网网址 2SELECT SalesOrderID,CustomerID,RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RowNum
金沙国际唯一官网网址 3FROM Sales.SalesOrderHeader
金沙国际唯一官网网址 4

--移动平均,举个例子,就是求前N天的平均值,和股票市场的均线类似
drop table if exists test_moving_avg

create table test_moving_avg
(
ID    int, 
Value int,
DT    datetime
)

insert into test_moving_avg 
values
(1,10,GETDATE()-10),
(2,110,GETDATE()-9),
(3,100,GETDATE()-8),
(4,80,GETDATE()-7),
(5,60,GETDATE()-6),
(6,40,GETDATE()-5),
(7,30,GETDATE()-4),
(8,50,GETDATE()-3),
(9,20,GETDATE()-2),
(10,10,GETDATE()-1)

--1. 没有窗口函数时,用子查询
select *,
(select AVG(Value) from test_moving_avg a where a.DT >= DATEADD(DAY, -5, b.DT) AND a.DT < b.DT) AS avg_value_5days
from test_moving_avg b

--2. 从SQL Server 2012起,用窗口函数
--三个内置常量,第一行,最后一行,当前行:UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING, CURRENT ROW 
--在行间移动,用BETWEEN m preceding AND n following (m, n > 0)
SELECT *,
       sum(value) over (ORDER BY DT ROWS BETWEEN 5 preceding AND CURRENT ROW) moving_sum,
       avg(value) over (ORDER BY DT ROWS BETWEEN 4 preceding AND CURRENT ROW) moving_avg1,
       avg(value) over (ORDER BY DT ROWS BETWEEN 5 preceding AND 1 preceding) moving_avg2,
       avg(value) over (ORDER BY DT ROWS BETWEEN 3 preceding AND 1 following) moving_avg3
FROM  test_moving_avg
ORDER BY DT

 

亲自去做指标:当前行至最终一行的汇聚

金沙国际唯一官网网址 5

  1. 排序函数 (Ranking Function) ;

  2. 聚合函数 (Aggregate Function) ;

  3. 浅析函数 (Analytic Function) ;

  4. NEXT VALUE FO奔驰M级 Function, 那是给sequence专项使用的三个函数;

  通过将 OVEHighlander 子句应用于 NEXT VALUE FOWrangler 调用,NEXT VALUE FOENCORE函数帮忙生成排序的体系值。 通过利用 OVEEnclave子句,能够向用户保障重临的值是规行矩步 OVELAND 子句的 O本田CR-VDE索罗德 BY
子子句的逐条生成的。

分析函数的花样
解析函数带有三个开窗函数over(),包蕴八个分析子句:分组(partition by),
排序(order by), 窗口(rows) ,他们的运用方式如下:over(partition by xxx
order by yyy rows between zzz)。
注:窗口子句在此地作者只说rows方式的窗口,range情势和滑动窗口也不提

就来发出如下的询问结果:

 

一、排行开窗函数

 运营结果:

1.         
1.
ROW_NUMBER()

排序函数在语法上要求OVE奥德赛子句里必须含O凯雷德DER
BY,不然语法不经过,对于不想排序的场馆能够这么变化;

 

运维结果:

金沙国际唯一官网网址,三、           
PIVOT Operator

 

运作结果:

金沙国际唯一官网网址 6

drop table if exists test_first_last

create table test_first_last
(
EmployeeID             int,
EnterTime              datetime,
ColorOfClothes         varchar(20)
)

insert into test_first_last
values
(1001, GETDATE()-9, 'GREEN'),
(1001, GETDATE()-8, 'RED'),
(1001, GETDATE()-7, 'YELLOW'),
(1001, GETDATE()-6, 'BLUE'),
(1002, GETDATE()-5, 'BLACK'),
(1002, GETDATE()-4, 'WHITE')

--1. 用子查询
--LastColorOfColthes
select * from test_first_last a
where not exists(select 1 from test_first_last b where a.EmployeeID = b.EmployeeID and a.EnterTime < b.EnterTime)

--LastColorOfColthes
select *
from 
(select *, ROW_NUMBER() over(partition by EmployeeID order by EnterTime DESC) num
from test_first_last ) t
where t.num =1


--2. 用窗口函数
--用LAST_VALUE时,必须加上ROWS/RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING,否则结果不正确
select *, 
       FIRST_VALUE(ColorOfClothes) OVER (PARTITION BY EmployeeID ORDER BY EnterTime DESC) as LastColorOfClothes,
       FIRST_VALUE(ColorOfClothes) OVER (PARTITION BY EmployeeID ORDER BY EnterTime ASC) as FirstColorOfClothes,
       LAST_VALUE(ColorOfClothes) OVER (PARTITION BY EmployeeID ORDER BY EnterTime ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as LastColorOfClothes,
       LAST_VALUE(ColorOfClothes) OVER (PARTITION BY EmployeeID ORDER BY EnterTime DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as FirstColorOfClothes
from test_first_last

--对于显示表中所有行,并追加Last/First字段时用窗口函数方便些
--对于挑选表中某一行/多行时,用子查询更方便

SELECT SalesOrderID, ProductID, OrderQty

   ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Total’

   ,CAST(1.0 * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID)

       *100 AS DECIMAL(5,2))AS ‘Percent by ProductID’

FROM SalesOrderDetail

WHERE SalesOrderID IN(43659,43664);

PARTITION BY子句

当必要实行获得分组后各组内的排名,则要求动用partition
by子句。它分化于group
by的分组,这种分组不“合併聚合”,它相当于把值分组后计算,然后再一次每个值。

最广大的例证如:在table表中有name(姓名)、class(班级)和score(分数)三个字段,求每一种班级里前三名姓名、班级及分数,SQL语句为:

select name,class,score

from (select name,

class,

score,

rank() over(partition by class order by score desc) rank

from table)

where rank <= 3;

在SCOTT用户中测量试验,求每一个部门薪俸前3名的人姓名、部门、专门的学问和工资,如:

select *

from (select ename,

deptno,

job,

sal,

dense_rank() over(partition by deptno order by sal desc) rank

from emp)

where rank <= 3;

RANK()的选拔和ROW_NUMBE帕杰罗()类似。可是它与ROW_NUMBE宝马X3()所差别的是:对于被钦点为排序的字段,具备同样值得Row对应的重返值一样。举个例子:

drop table if exists test_ranking

create table test_ranking
( 
id int not null,
name varchar(20) not null,
value int not null
) 

insert test_ranking 
select 1,'name1',1 union all 
select 1,'name2',2 union all 
select 2,'name3',2 union all 
select 3,'name4',2

select id , name, ROW_NUMBER() over (PARTITION by id ORDER BY name) as num
from test_ranking

select id , name, ROW_NUMBER() over (PARTITION by id) as num
from test_ranking
/*
Msg 4112, Level 15, State 1, Line 1
The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.
*/

--ORDERY BY后面给一个和原表无关的派生列
select id , name, ROW_NUMBER() over (PARTITION by id ORDER BY GETDATE()) as num
from test_ranking

select id , name, ROW_NUMBER() over (PARTITION by id ORDER BY (select 0)) as num
from test_ranking

  可参考 

 运营结果:

DENSE_RANK()完结了二个总是的Ranking。比方上边包车型客车SQL:

 

1. 语法

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总 
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME ROWS 
                     BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

T-SQL Enhancement in SQL Server
2005:
[原创]T-SQL Enhancement in SQL
Server 2005 – Part
I
[原创]T-SQL Enhancement in SQL Server 2005 – Part
II

代码示例1:总括/小计/累计求和

 

测算中心平均值

计量当前月份前、后各二个月的销量移动平均值,SQL语句如下:

SELECT month,

SUM(amount) AS month_amount,

AVG(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING) AS moving_average

FROM all_sales

where year = 2003

GROUP BY month

ORDER BY month;

对核心平均值部分AVG(SUM(amount)) OVE福特Explorer (O酷威DE中华V BY month ROWS BETWEEN 1
PRECEDING AND 1 FOLLOWING)解析如下:

  • AVG(SUM(amount))内部的sum(amount)总计月销量和,外界的avg()总结平均值。
  • O途乐DE奥迪Q3 BY month
    按月度对查询读取的记录实行排序(那是必须的,因为独有排序后能力做积攒或左右求平均值)。
  • ROWS BETWEEN 1 PRECEDING AND 1
    FOLLOWING定义了窗口的起源是现阶段记下在此以前的那条记下,窗口的顶峰是近期记录之后的那条记下。

金沙国际唯一官网网址 7SELECT SalesOrderID,CustomerID,ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
金沙国际唯一官网网址 8FROM Sales.SalesOrderHeader
金沙国际唯一官网网址 9ORDER BY SalesOrderID
金沙国际唯一官网网址 10

排序函数中,ROW_NUMBE奥迪Q7()较为常用,可用于去重、分页、分组中选用数据,生成数字协助表等等;

  可参考 

亲自去做指标:展现各机关职工的薪资,并顺便彰显该部分的万丈工资。

大家来看多个事例:对Sales.SalesOrderHeader依照CustomerID进行排序,并显示每条记下的Row
Number。

drop table if exists test_analytic

create table test_analytic
(
SalesYear         varchar(10),
Revenue           int,
Offset            int
)

insert into test_analytic
values
(2013,1001,1),
(2014,1002,1),
(2015,1003,1),
(2016,1004,1),
(2017,1005,1),
(2018,1006,1)

--当年及去年的销售额
select *,lag(Revenue,1,null) over(order by SalesYear asc) as PreviousYearRevenue from test_analytic
select *,lag(Revenue,Offset,null) over(order by SalesYear asc) as PreviousYearRevenue from test_analytic
select *,lead(Revenue,1,null) over(order by SalesYear desc) as PreviousYearRevenue from test_analytic

--当年及下一年的销售额
select *,lead(Revenue,1,null) over(order by SalesYear asc) as NextYearRevenue from test_analytic
select *,lead(Revenue,Offset,null) over(order by SalesYear asc) as NextYearRevenue from test_analytic
select *,lag(Revenue,1,null) over(order by SalesYear desc) as NextYearRevenue from test_analytic

--可以根据offset调整跨度

三、剖判开窗函数

RANK()和DENSE_RANK()

rank()和dense_rank()函数都可用于总计数据项在分组中(在不选拔partition
by时以富有数据为贰个分组)的排名。它们的分别在于rank()在排名相等时,如:有3个第1名时,则下二个排名为第4名,未有2、3名;而dense_rank()则在有3个第1名时,下三个名次的榜单为第2名。即,rank()会合世排行间隔,而dense_rank()则不会现知名次间隔。

那三个函数多用于select子句中,在不开始展览分组的情形下,能够不利用partition
by子句。其利用举个例子如,寻找公司享有人薪资排名:

select ename,

rank() over (order by sal desc) rank,

dense_rank() over (order by sal desc) dense_rank

from emp;

从言语中得以看来,rank()函数需求有根本字over和order
by。何况rank()是三个单值函数,并非聚合函数。若要求寻觅各个专门的学问的参天薪水在颇具工作最高级程序员资中的排行:

select job,

rank() over (order by max(sal) desc) rank,

dense_rank() over (order by max(sal) desc) dense_rank

from emp

group by job;

在排行中,会油可是生NULL值在前在后的难题,能够在O途达DER
BY子句之后接纳主要字NULLS FIQashqaiST/LAST来支配。

其查询结果为:

 

开窗函数是在 ISO 典型中定义的。SQL Server
提供排名开窗函数和集结开窗函数。

评级函数

科学普及评级函数如下:

  • RANK():重临数据项在分组中的排名,在排行相等时会在排行中留给空位,产生排行不延续。
  • DENSE_RANK():同样再次来到数据项在分组中排行,可是在排名相等时不会留下名位空位。
  • CUME_DIST():再次来到特定值相对于一组值的地点,是储存分布(cumulative
    distribution)的简写。
  • PERCENT_RANK():再次来到有些值绝对于一组值的比重排行。
  • NTILE():再次来到n分片后的值,如七分片、四分片等。
  • ROW_NUMBESportage():为每一条分组记录重回三个数字,注意不一样于rownum伪列。

金沙国际唯一官网网址 11SELECT SalesOrderID,CustomerID,NTILE(3) OVER (ORDER BY CustomerID) AS RowNum
金沙国际唯一官网网址 12FROM Sales.SalesOrderHeader
金沙国际唯一官网网址 13WHERE CustomerID <3
金沙国际唯一官网网址 14

drop sequence if exists test_seq

create sequence test_seq
start with 1
increment by 1;

GO

drop table if exists test_next_value

create table test_next_value
(
ID         int,
Name       varchar(10)
)

insert into test_next_value(Name)
values
('AAA'),
('AAA'),
('BBB'),
('CCC')

--对于多行数据获取sequence的next value,是否使用窗口函数都会逐行计数
--窗口函数中ORDER BY用于控制不同列值的计数顺序
select *, NEXT VALUE FOR test_seq from test_next_value
select *, NEXT VALUE FOR test_seq OVER(ORDER BY Name DESC) from test_next_value

从 转

 

金沙国际唯一官网网址 15