Post Jobs

窗口函数,sqlserver开窗函数

从SQL Server 2007起,SQL Server开始帮忙窗口函数 (Window
Function),以及到SQL Server
二〇一三,窗口函数作用巩固,方今停止援助以下二种窗口函数:

 

  1. 排序函数 (Ranking Function) ;

  2. 聚合函数 (Aggregate Function) ;

  3. 深入分析函数 (Analytic Function) ;

  4. NEXT VALUE FO牧马人 Function, 那是给sequence专项使用的三个函数;

从 转

 

 

一. 排序函数(Ranking
Function)

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

支持文档里的代码示例很全。

  在开窗函数出现以前存在着多数用 SQL
语句很难化解的标题,比很多都要经过复杂的相关子查询恐怕存款和储蓄进程来变成。SQL
Server 二零零六 引进了开窗函数,使得那么些优良的难点可以被轻巧的解决。

澳门新葡亰平台游戏,排序函数中,ROW_NUMBE中华V()较为常用,可用于去重、分页、分组中采纳数据,生成数字协助表等等;

  窗口是用户钦命的一组行。开窗函数总结从窗口派生的结果聚焦各行的值。开窗函数分别使用于各样分区,并为每种分区重新开动计算。

排序函数在语法上必要OVE中华V子句里必须含OCR-VDER
BY,否则语法不经过,对于不想排序的风貌能够这样变化;

  OVE福特Explorer子句用于鲜明在使用关联的开窗函数从前,行集的分区和排序。PARTITION BY
将结果集分为几个分区。

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

 

 

一、排行开窗函数

二. 聚合函数 (Aggregate
Function)

1. 语法

SQL Server 二〇〇七中,窗口聚合函数仅协理PARTITION
BY,也正是说仅能对分组的数据完全做聚合运算;

Ranking Window Functions

< OVER_CLAUSE > :: =

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

          <ORDER BY_Clause> )

SQL Server 二零一二起头,窗口聚合函数协助O奥德赛DER
BY,以及ROWS/RAGNE选项,原来须求子查询来兑现的急需,如: 移动平均
(moving averages), 总括聚合 (cumulative aggregates), 累计求和 (running
totals) 等,变得更其方便;

 

 

瞩目:O普拉多DE福特Explorer BY 子句内定对相应 FROM
子句生成的行集举办分区所依照的列。value_expression 只可以引用通过 FROM
子句可用的列。value_expression
不可能援用选择列表中的表明式或外号。value_expression
能够是列表达式、标量子查询、标量函数或用户定义的变量。

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

 

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

2. 示例

 

  可参考 

代码示例2:移动平均

 

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

 

 

二、聚合开窗函数

三. 分析函数 (Analytic
Function)

1. 语法

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

Aggregate Window Functions

< OVER_CLAUSE > :: =

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

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

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章

网站地图xml地图