云顶娱乐平台 65

云顶娱乐平台:16. 窗口函数 (Window Function) 的使用

 

SELECT NEXT VALUE FOR Test.CountBy1 OVER (ORDER BY LastName) AS ListNumber,

   FirstName, LastName

FROM Person.Contact ;

 示例目标:当前行的上一行(rownum-1)到当前行的汇总

从上图中可以看到,最终的结果以ORDER BY中指定的SalesOrderID进行排序,但是ROW_NUMBER()体现的值却是基于CustmerID排序的。

 

  详情请参考 

 运行结果:

在第一部分中,我们讨论了APPLYCTE这两个T-SQL
Enhancement。APPLY实现了Table和TVF的Join,CTE通过创建“临时的View”的方式使问题化繁为简。现在我们接着来讨论另外两个重要的T-SQL
Enhancement Items:PIVOTRanking。 

参考:

  OVER
子句用于确定在应用关联的开窗函数之前,行集的分区和排序。PARTITION BY
将结果集分为多个分区。

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值在前在后的问题,可以在ORDER
BY子句之后使用关键字NULLS FIRST/LAST来控制。

上面提到的所有Ranking都是基于真个结果基的。而有的时候我们需要将真个结果集按照某个Column
进行分组,进行基于组的Ranking。这就需要PARTITION BY了。PARTITION BY置于OVER Clause中,和ORDER BY 平级。

排序函数在语法上要求OVER子句里必须含ORDER
BY,否则语法不通过,对于不想排序的场景可以这样变通;

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

计算累计和

查询从2003年1月到12月的累计销量,SQL语句如下:

SELECT month,

SUM(amount) AS month_amount,

SUM(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS cumulative_amount

FROM all_sales

where year = 2003

GROUP BY month

ORDER BY month;

对于累计部分SUM(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW)解析如下:

  • SUM(SUM(amount))中内部的SUM(amount)用于计算月销量总和,外部的SUM()用于计算累计销量。
  • ORDER BY month 按月份对查询读取的记录进行排序。
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT
    ROW定义了窗口的起点和终点,起点为UNBOUNDED
    PRECEDING,意味着起点为固定的查询结果集的第一行;终点为CURRENT
    ROW表示终点为处理结果集的当前行。当外部SUM函数计算返回当前的累计销量后,窗口的终点便向下移动一行。PRECEDING表示向上累计数,若将UNBOUNDED换成数字如1,则表示跟之前一条记录做累积;同时还可以向后,使用关键字FOLLOWING,指定向后累积数只需要在该关键字前加数字即可,该数字为向后累积的行数(从这里也可以看出排序的重要性)。

如:

若要计算指定月份如6月到12月的累积销量,则只需要在where子句中再增加条件month
between 6 and 12即可。

计算当月跟前3个月累积销量,窗口语句:

SUM(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND
CURRENT ROW) AS cumulative_amount

计算前一个月和后一个月累积销量,窗口语句:

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

下面是查询结果:

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

  在开窗函数出现之前存在着很多用 SQL
语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。SQL
Server 2005 引入了开窗函数,使得这些经典的难题可以被轻松的解决。

示例目的:显示各部门员工的工资,并附带显示该部分的最高工资。

云顶娱乐平台 1

 

  下例首先由 SalesOrderID 分区进行聚合,并为每个 SalesOrderID
的每一行计算 ProductID 的百分比)。

评级函数

常见评级函数如下:

  • RANK():返回数据项在分组中的排名,在排名相等时会在名次中留下空位,造成排名不连续。
  • DENSE_RANK():同样返回数据项在分组中排名,不过在排名相等时不会留下名位空位。
  • CUME_DIST():返回特定值相对于一组值的位置,是累积分布(cumulative
    distribution)的简写。
  • PERCENT_RANK():返回某个值相对于一组值的百分比排名。
  • NTILE():返回n分片后的值,如三分片、四分片等。
  • ROW_NUMBER():为每一条分组记录返回一个数字,注意不同于rownum伪列。

其查询结果为:

 

  更多详情,请参考 

运行结果:

云顶娱乐平台 2WITH CTE_Order(CustomerID,OrderCount)
云顶娱乐平台 3AS
云顶娱乐平台 4(
云顶娱乐平台 5SELECT CustomerID,COUNT(*) AS OrderCount
云顶娱乐平台 6FROM Sales.SalesOrderHeader
云顶娱乐平台 7GROUP BY CustomerID
云顶娱乐平台 8)
云顶娱乐平台 9
云顶娱乐平台 10SELECT CustomerID,OrderCount,ROW_NUMBER() OVER (ORDER BY OrderCount)
云顶娱乐平台 11FROM CTE_Order
云顶娱乐平台 12

--移动平均,举个例子,就是求前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

 

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

云顶娱乐平台:16. 窗口函数 (Window Function) 的使用。3.         
3.
DENSE_RANK()

代码示例1:取当前行某列的前一个/下一个值

2. 示例

云顶娱乐平台 13

1.         
1.
ROW_NUMBER()

SQL Server Windowing Functions: ROWS vs. RANGE

 

 

我们发现最终的结果按照CutomerID进行排序,RowNum从1开始以此递增,每条记录(不管是否具有相同的CustomerID)拥有不同的RowNum。

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调整跨度

2. 示例

 

云顶娱乐平台 14SELECT SalesPersonID,
云顶娱乐平台 15SUM(CASE DATEPART(MM,OrderDate)WHEN 1 THEN 1 ELSE 0 END) AS JAN,
云顶娱乐平台 16SUM(CASE DATEPART(MM,OrderDate)WHEN 2 THEN 1 ELSE 0 END) AS FEB,
云顶娱乐平台 17SUM(CASE DATEPART(MM,OrderDate)WHEN 3 THEN 1 ELSE 0 END) AS MAR,
云顶娱乐平台 18SUM(CASE DATEPART(MM,OrderDate)WHEN 4 THEN 1 ELSE 0 END) AS APR
云顶娱乐平台 19FROM Sales.SalesOrderHeader
云顶娱乐平台 20WHERE DATEPART(yyyy,OrderDate) = 2002
云顶娱乐平台 21GROUP BY SalesPersonID
云顶娱乐平台 22

四. NEXT VALUE FOR Function

一、排名开窗函数

          

排序与排名是我们最为常用的统计方式,比如对班级的学生根据成员进行排名,或者按照成绩高低把学生划分成若干梯队:比如最好成绩的10名学生属于第一梯队,后10名又划分为第二梯队,以此类推。Ranking设计的Key Words包括:ROW_NUMBER(),RANK(),DENSE_RANK(),NTILE()**。**我们现在就来介绍一下他们的用法和相互之间的差异。

一. 排序函数(Ranking
Function)

 

 

云顶娱乐平台 23SELECT SalesPersonID, [1] AS JAN,[2] AS FEB, [3] AS MAR, [4] AS APR
云顶娱乐平台 24FROM 
云顶娱乐平台 25(
云顶娱乐平台 26    SELECT SalesPersonID, DATEPART(MM,OrderDate) AS MON
云顶娱乐平台 27    FROM Sales.SalesOrderHeader
云顶娱乐平台 28    WHERE DATEPART(yyyy,OrderDate) = 2002
云顶娱乐平台 29) S
云顶娱乐平台 30
云顶娱乐平台 31PIVOT 
云顶娱乐平台 32(
云顶娱乐平台 33    COUNT(MON) FOR MON IN ([1],[2],[3],[4])
云顶娱乐平台 34)
云顶娱乐平台 35AS P
云顶娱乐平台 36

 

注意:ORDER BY 子句指定对相应 FROM
子句生成的行集进行分区所依据的列。value_expression 只能引用通过 FROM
子句可用的列。value_expression
不能引用选择列表中的表达式或别名。value_expression
可以是列表达式、标量子查询、标量函数或用户定义的变量。

窗口函数(累计和、移动平均值等)

窗口函数可用来计算累计和、移动平均值和中心平均值等,具体如下:

云顶娱乐平台 37

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字段时用窗口函数方便些
--对于挑选表中某一行/多行时,用子查询更方便

Ranking Window Functions

< OVER_CLAUSE > :: =

   OVER ( [ PARTITION BY value_expression , … [ n ] ]

          <ORDER BY_Clause> )

 

我们来看一个例子:对Sales.SalesOrderHeader按照CustomerID进行排序,并显示每条记录的Row
Number。

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

3. SQL Server 2012 增加功能

示例目标:当前行至最后一行的汇总

云顶娱乐平台 38

 

分析函数和聚合函数的不同之处是什么?
普通的聚合函数用group by分组,每个分组返回一个统计值,而分析函数采用partition
by分组,并且每组每行都可以返回一个统计值。

由于ROW_NUMBER()体现是基于某个确定的字段进行排序后某个DataRow所处的位置,所以它不能直接使用到Aggregate的Column中。比如下面的SQL是不合法的:

代码示例2:移动平均

Aggregate Window Functions

< OVER_CLAUSE > :: =

   OVER ( [ PARTITION BY value_expression , … [ n ] ] )

 运行结果:

通过数据在原表的结构和我们最终获得的结果进行比较,我们发现就像是“旋转”了90度,原来的OrderDate是存储在每行的基于Order的一个属性(行),现在我们要把Order
Date按照不同月份统计,这样行变成了列。

从SQL Server 2005起,SQL Server开始支持窗口函数 (Window
Function),以及到SQL Server
2012,窗口函数功能增强,目前为止支持以下几种窗口函数:

  窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值。开窗函数分别应用于每个分区,并为每个分区重新启动计算。

计算中心平均值

计算当前月份前、后各一个月的销量移动平均值,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)) OVER (ORDER BY month ROWS BETWEEN 1
PRECEDING AND 1 FOLLOWING)解析如下:

  • AVG(SUM(amount))内部的sum(amount)计算月销量和,外部的avg()计算平均值。
  • ORDER BY month
    按月份对查询读取的记录进行排序(这是必须的,因为只有排序后才能做累积或前后求平均值)。
  • ROWS BETWEEN 1 PRECEDING AND 1
    FOLLOWING定义了窗口的起点是当前记录之前的那条记录,窗口的终点是当前记录之后的那条记录。

云顶娱乐平台 39SELECT SalesOrderID,CustomerID,DENSE_RANK() OVER (ORDER BY CustomerID) AS RowNum
云顶娱乐平台 40FROM Sales.SalesOrderHeader
云顶娱乐平台 41

 

四、NEXT VALUE FOR 函数

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

提到排序,我们就不得不提到Order
BY,如果我们在后面加上ORDER
BY,并指定不同的排序字段,会出现怎样的结果呢?

 

 

云顶娱乐平台 42

如果你第一次见到PIVOT,可以不能一下明白它的实现,但是只要你是使用了一两次,相信就会很容易地掌握它。与PIVOT对应的还以一个操作符UNPIVOT,它完成PIVOT的逆操作,在这里就不介绍了,如果有兴趣的话,可以参考SQL
Server Books Online。

drop table if exists test_aggregate;

create table test_aggregate
(
event_id      varchar(100),
rk            int,
price         int
)

insert into test_aggregate
values
('a',1,10),
('a',2,10),
('a',3,50),
('b',1,10),
('b',2,20),
('b',3,30)


--1. 没有窗口函数时,用子查询
select a.event_id, 
       a.rk,  --build ranking column if needed
       a.price, 
     (select sum(price) from test_aggregate b where b.event_id = a.event_id and b.rk <= a.rk) as totalprice 
  from test_aggregate a


--2. 从SQL Server 2012起,用窗口函数
--2.1 
--没有PARTITION BY, 没有ORDER BY,为全部总计;
--只有PARTITION BY, 没有ORDER BY,为分组小计;
--只有ORDER BY,没有PARTITION BY,为全部累计求和(RANGE选项,见2.2)
select *,
     sum(price) over() as TotalPrice,
     sum(price) over(partition by event_id) as SubTotalPrice,
       sum(price) over(order by rk) as RunningTotalPrice
  from test_aggregate a

--2.2 注意ORDER BY列的选择,可能会带来不同结果
select *,
     sum(price) over(partition by event_id order by rk) as totalprice 
  from test_aggregate a
/*
event_id    rk    price    totalprice
a    1    10    10
a    2    10    20
a    3    50    70
b    1    10    10
b    2    20    30
b    3    30    60
*/

select *,
     sum(price) over(partition by event_id order by price) as totalprice 
  from test_aggregate a
/*
event_id    rk    price    totalprice
a    1    10    20
a    2    10    20
a    3    50    70
b    1    10    10
b    2    20    30
b    3    30    60
*/

--因为ORDER BY还有个子选项ROWS/RANGE,不指定的情况下默认为RANGE UNBOUNDED PRECEDING AND CURRENT ROW 
--RANGE按照ORDER BY中的列值,将相同的值的行均视为当前同一行
select  *,sum(price) over(partition by event_id order by price) as totalprice from test_aggregate a
select  *,sum(price) over(partition by event_id order by price range between unbounded preceding and current row) as totalprice from test_aggregate a

--如果ORDER BY中的列值有重复值,手动改用ROWS选项即可实现逐行累计求和
select  *,sum(price) over(partition by event_id order by price rows between unbounded preceding and current row) as totalprice from test_aggregate a

 

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal
  FROM SCOTT.EMP;

在上面的例子中,同过下面的SELECT语句筛选出来的是为经过PIVOT的数据。

SQL Server 2005中,窗口聚合函数仅支持PARTITION
BY,也就是说仅能对分组的数据整体做聚合运算;

 

 运行结果:

DENSE_RANK()实现了一个连续的Ranking。比如下面的SQL:

代码示例2:分组中某列最大/最小值,对应的其他列值

  例如:

云顶娱乐平台 43

看到ROW_NUMBER(),我想绝大多数人会像想到Oracle的ROWNUM。他们的作用相似,都是表示某条记录所处的Index。ROW_NUMBER()比Oracle的ROWNUM更加强大的是,它可以通过OVER语句指定一个进行排序的Column,比如:ROW_NUMBER() OVER (ORDER BY
CustomerID)。

假设有个门禁系统,在员工每次进门时写入一条记录,记录了“身份号码”,“进门时间”,“衣服颜色”,查询每个员工最后一次进门时的“衣服颜色”。

 

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

云顶娱乐平台 44SELECT SalesOrderID,CustomerID,RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RowNum
云顶娱乐平台 45FROM Sales.SalesOrderHeader
云顶娱乐平台 46

二. 聚合函数 (Aggregate
Function)

开窗函数是在 ISO 标准中定义的。SQL Server
提供排名开窗函数和聚合开窗函数。

 

就来产生如下的查询结果:

三.云顶娱乐平台分析函数 (Analytic
Function)

  通过将 OVER 子句应用于 NEXT VALUE FOR 调用,NEXT VALUE FOR
函数支持生成排序的序列值。 通过使用 OVER
子句,可以向用户保证返回的值是按照 OVER 子句的 ORDER BY
子子句的顺序生成的。

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;

三、           
PIVOT Operator

SQL Server 2012开始,窗口聚合函数支持ORDER
BY,以及ROWS/RAGNE选项,原本需要子查询来实现的需求,如: 移动平均
(moving averages), 总计聚合 (cumulative aggregates), 累计求和 (running
totals) 等,变得更加方便;

 

              

云顶娱乐平台 47

  1. 排序函数 (Ranking Function) ;

  2. 聚合函数 (Aggregate Function) ;

  3. 分析函数 (Analytic Function) ;

  4. NEXT VALUE FOR Function, 这是给sequence专用的一个函数;

  SQL Server 2012 为聚合函数提供了窗口排序和框架支持,可以将 OVER
子句与函数一起使用,以便计算各种聚合值,例如移动平均值、累积聚合、运行总计或每组结果的前
N 个结果。

分析函数是什么?
分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值。

Reference: 《Programming Microsoft
SQL Server 2005》 By Andrew J. Brust & Stephen Forte

 

 

示例目标:   当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总

云顶娱乐平台 48

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

 

 

PIVOT的中文意思是“在枢轴上转动”,比如对于一个2维坐标,将横坐标变成纵坐标,将纵坐标变成横坐标。反映在一个Relational Table上的意思就是:变成为列,变列为行。相信大家在进行报表设计的时候都遇到过类似于这样的需求:统计2002年内某个销售人员第一季度每个月处理的订单数。在AdventureWorks
Sample Databse中,Sales
Order存储于SaleOrderHeader这张表中,它的结果如下:

  可参考 

示例目的:对各部门进行分组,并附带显示第一行至当前行的汇总

四、           
Ranking

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

 

云顶娱乐平台 49

通过下面的PIVOT(COUNT(MON)是我们需要统计的数据,FOR
MON IN ([1],[2],[3],[4]是统计的范围)就成了我们最终输出的结构了。
 

排序函数中,ROW_NUMBER()较为常用,可用于去重、分页、分组中选择数据,生成数字辅助表等等;

二、聚合开窗函数

 

云顶娱乐平台 50SELECT CustomerID,COUNT(*) AS OrderCount,ROW_NUMBER() OVER (ORDER BY OrderCount)
云顶娱乐平台 51FROM Sales.SalesOrderHeader
云顶娱乐平台 52GROUP BY CustomerID
云顶娱乐平台 53

SELECT – OVER Clause (Transact-SQL)

 

运行结果:

我们一般情况下通过下面的SQL实现我们提出的统计功能:

 

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;

云顶娱乐平台 54SELECT SalesOrderID,CustomerID,ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
云顶娱乐平台 55FROM Sales.SalesOrderHeader
云顶娱乐平台 56ORDER BY SalesOrderID
云顶娱乐平台 57

  下例将根据 SalesOrderID
进行分区,然后为每个分区分别统计SUM、AVG、COUNT、MIN、MAX。

 

云顶娱乐平台 58

1. 语法

运行结果:

云顶娱乐平台 59

SELECT SalesOrderID, ProductID, OrderQty

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

   ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Avg’

   ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Count’

   ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Min’

   ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Max’

FROM SalesOrderDetail

WHERE SalesOrderID IN(43659,43664);

计算移动平均值

计算本月与前3个月之间销量的移动平均值,SQL语句如下:

SELECT month,

SUM(amount) AS month_amount,

AVG(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND
CURRENT ROW) AS moving_average

FROM all_sales

where year = 2003

GROUP BY month

ORDER BY month;

对移动平均值部分AVG(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3
PRECEDING AND CURRENT ROW)解析如下:

  • AVG(SUM(amount))内部的sum(amount)计算月销量和,外部的avg()计算平均值。
  • ORDER BY month
    按月份对查询读取的记录进行排序(这是必须的,因为只有排序后才能做累积或前后求平均值)。
  • ROWS BETWEEN 3 PRECEDING AND CURRENT
    ROW定义了窗口的起点为当前记录的前3条记录,窗口的终点为当前记录。

云顶娱乐平台 60SELECT SalesOrderID,CustomerID,ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
云顶娱乐平台 61FROM Sales.SalesOrderHeader
云顶娱乐平台 62

1. 语法

云顶娱乐平台 63

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

从 转

分析函数的形式
分析函数带有一个开窗函数over(),包含三个分析子句:分组(partition by),
排序(order by), 窗口(rows) ,他们的使用形式如下:over(partition by xxx
order by yyy rows between zzz)。
注:窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提

比如下面的SQL将Order记录按照CustomerID进行分组,在每组中输出排名(安OrderDate排序):

三、分析开窗函数

窗口第一条和最后一条记录

FIRST_VALUE()和LAST_VALUE()函数可用于获取窗口中的第一行和最后一行数据,如,可用于获取当前月前一个月和后一个月的销量:

SELECT month,

SUM(amount) AS month_amount,

FIRST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING
AND 1 FOLLOWING) AS pre_month_amount,

LAST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING
AND 1 FOLLOWING) AS next_month_amount

FROM all_sales

where year = 2003

GROUP BY month

ORDER BY month;

其中,窗口定义了起点为前一个月终点为后一个月,故而first_value(sum(amount))为前一个月销量而last_value()为后一个月销量。

 

4. NTILE()

  可参考 

 

于是我们得到了这样的统计数据:

    

2.         
RANK()

分析函数例子(在scott用户下模拟)

云顶娱乐平台 64

--显示各部门员工的工资,并附带显示该部分的最高工资。
SELECT E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       LAST_VALUE(E.SAL) 
       OVER(PARTITION BY E.DEPTNO 
            ORDER BY E.SAL ROWS 
            --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录,也就是表中的所有记录
            --unbounded:不受控制的,无限的
            --preceding:在...之前
            --following:在...之后
            BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP E;

要是想按照OrderCount,可以使用第一部分介绍的CTE:

云顶娱乐平台 65

对于RANK(),还有一点需要说明的是,它的回返值不是连续的,
比如第五条记录的Row_Num是5而不是2。如果想实现这样需求,就需要用下面一个Function:DENSE_RANK()。

示例目的:按照deptno分组,然后计算每组值的总和

云顶娱乐平台 66SELECT SalesOrderID,CustomerID,NTILE(3) OVER (ORDER BY CustomerID) AS RowNum
云顶娱乐平台 67FROM Sales.SalesOrderHeader
云顶娱乐平台 68WHERE CustomerID <3
云顶娱乐平台 69

ROW_NUMBER()

row_number为每一行返回一个数字,在分组中较常用(rownum在非分组中常用)。如,给emp表中每种工作工资由高到低进行排序:

select ename,job,sal,row_number() over (partition by job order by sal
desc) from emp;

像这样的需求,我们都可以可以通过PIVOT这个操作符来实现,下面就是基于PIVOT的SQL:

 

上面我们说到划分梯队的问题,这样的问题可以通过NTILE()
Function来实现。比如我们现在按照CustomerID排序,把CustomerID为1和2的划分到3梯队中:

云顶娱乐平台 70SELECT SalesOrderID,CustomerID,RANK() OVER (ORDER BY CustomerID) AS RowNum
云顶娱乐平台 71FROM Sales.SalesOrderHeader
云顶娱乐平台 72

云顶娱乐平台 73SELECT SalesPersonID, DATEPART(MM,OrderDate) AS MON
云顶娱乐平台 74    FROM Sales.SalesOrderHeader
云顶娱乐平台 75    WHERE DATEPART(yyyy,OrderDate) = 2002
云顶娱乐平台 76

5.         
5.PARTITION BY

云顶娱乐平台 77PIVOT 
云顶娱乐平台 78(
云顶娱乐平台 79    COUNT(MON) FOR MON IN ([1],[2],[3],[4])
云顶娱乐平台 80)
云顶娱乐平台 81

RANK()的使用和ROW_NUMBER()类似。不过它与ROW_NUMBER()所不同的是:对于被指定为排序的字段,具有相同值得Row对应的返回值相同。比如:

下面是相应的查询结果:

查询获得的结果是:

我们可以看到,一共12条记录,划分为3组,平均下来每组4条记录。

相应的查询结果: