sqlserver开窗函数

2019-04-02 12:16栏目:程序人生

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

 

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

三、            PIVOT Operator

SQL Server 二〇一三发端,窗口聚合函数帮衬ORubiconDER BY,以及ROWS/RAGNE选项,原本要求子查询来达成的须要,如: 移动平均 (moving averages), 总括聚合 (cumulative aggregates), 累计求和 (running totals) 等,变得更为惠及;

 

RANK()和DENSE_RANK()

rank()和dense_rank()函数都可用于总计数据项在分组中(在不应用partition by时以全体数据为三个分组)的排行。它们的界别在于rank()在排行相等时,如:有二个第二名时,则下三个排行榜为第陆名,未有二、叁名;而dense_rank()则在有二个第三名时,下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()是1个单值函数,而不是聚合函数。若须求找出种种工作的万丈工资在有着工作最高级工程师资中的排行:

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 FILX570ST/LAST来控制。

四、            Ranking

 

 

          

从上海体育场地中可以见见,最后的结果以O景逸SUVDELAND BY中钦命的SalesOrderID进行排序,不过ROW_NUMBEOdyssey()展现的值却是基于CustmerID排序的。

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

三. SQL Server 二零一一 增添效果

 

DENSE_RANK()达成了三个连接的Ranking。比如上面包车型客车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用户中测试,求每个机关薪给前三名的人姓名、部门、工作和报酬,如:

select *

from (select ename,

deptno,

job,

sal,

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

from emp)

where rank <= 3;

ca888会员登录 1

SELECT - OVER Clause (Transact-SQL)

 

示范目标:对各机关开始展览分组,并顺便显示第3行至当前行的汇集

ca888会员登录 2SELECT SalesOrderID,CustomerID,NTILE(3) OVER (ORDER BY CustomerID) AS RowNum
ca888会员登录 3FROM Sales.SalesOrderHeader
ca888会员登录 4WHERE CustomerID <3
ca888会员登录 5

 

 

 运转结果:

ca888会员登录 6

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

  可参考 

计量移动平均值

计量本月与前5个月以国内销售量的运动平均值,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)) OVERAV4 (OMuranoDELX570 BY month ROWS BETWEEN 三PRECEDING AND CU大切诺基RENT ROW)解析如下:

  • AVG(SUM(amount))内部的sum(amount)总括月销量和,外部的avg()总计平均值。
  • O福睿斯DE大切诺基 BY month 按月度对查询读取的记录举办排序(那是必须的,因为唯有排序后才能做累积或左右求平均值)。
  • ROWS BETWEEN 三 PRECEDING AND CURAV4RENT ROW定义了窗口的起源为眼下记下的前三条记下,窗口的终端为当下记下。

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

 

2. 示例

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

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

二. 聚合函数 (Aggregate Function)

三、分析开窗函数

运转结果:

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

SQL Server 二零零六中,窗口聚合函数仅援助PALX570TITION BY,也便是说仅能对分组的多少完全做聚合运算;

 

窗口第贰条和最后一条记下

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

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;

里面,窗口定义了源点为前7个月终点为后三个月,故而first_value(sum(amount))为前3个月销量而last_value()为后二个月销量。

 

询问得到的结果是:

 

 

示范指标:   当前行的上一行(rownum-一)到当下行的下辆行(rownum 贰)的汇总

ca888会员登录 7SELECT SalesOrderID,CustomerID,ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
ca888会员登录 8FROM Sales.SalesOrderHeader
ca888会员登录 9

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

由于ROW_NUMBERubicon()浮现是依据有个别鲜明的字段进行排序后某些DataRow所处的地方,所以它不可能一直选拔到Aggregate的Column中。比如上边包车型地铁SQL是违法的:

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

四、NEXT VALUE FOR 函数

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;

对应的询问结果:

 

 

ca888会员登录 10ca888会员登录,

版权声明:本文由ca888发布于程序人生,转载请注明出处:sqlserver开窗函数