金沙国际唯一官网-奥门金沙手机娱乐网址

热门关键词: 金沙国际唯一官网,奥门金沙手机娱乐网址
金沙国际唯一官网 > 业界动态 >   在开窗函数出现此前存在重视重用 SQL,SQL

原标题:  在开窗函数出现此前存在重视重用 SQL,SQL

浏览次数:87 时间:2019-10-09

从SQL Server 2006起,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_NUMBECRUISER()较为常用,可用于去重、分页、分组中挑选数据,生成数字支持表等等;

  窗口是顾客钦点的一组行。开窗函数总结从窗口派生的结果集中各行的值。开窗函数分别接纳于每种分区,并为每种分区重新启航总计。

排序函数在语法上须要OVE汉兰达子句里必需含OENVISIONDER BY,不然语法不经过,对于不想排序的情景能够如此变化;

  OVELAND子句用于分明在使用关联的开窗函数在此之前,行集的分区和排序。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 2007中,窗口聚合函数仅帮忙PARTITION BY,也正是说仅能对分组的数据完全做聚合运算;

Ranking Window Functions

< OVER_CLAUSE > :: =

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

          <ORDER BY_Clause> )

SQL Server 二零一一早先,窗口聚合函数支持OPRADODER BY,乃至ROWS/RAGNE选项,原来须要子查询来贯彻的急需,如: 移动平均 (moving averages), 总结聚合 (cumulative aggregates), 累计求和 (running totals) 等,变得尤其有利;

 

 

瞩目:O奥迪Q7DE福睿斯 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调整跨度

 

 

2. 示例

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

  下例将依据 SalesOrderID 实行分区,然后为各类分区分别总括SUM、AVG、COUNT、MIN、MAX。

假使有个门禁系统,在员工每一遍进门时写入一条记下,记录了“身份号码”,“进门时间”,“服装颜色",查询每一个职工最终一回进门时的“服装颜色”。

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

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

 

 

  下例首先由 SalesOrderID 分区举行联谊,并为每一个 SalesOrderID 的每一行总括 ProductID 的百分比)。

四. NEXT VALUE FOR Function

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

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

 

 

3. SQL Server 二〇一三 扩大效果

参考:

  SQL Server 二零一三 为聚合函数提供了窗口排序和框架扶助,能够将 OVE途乐子句与函数一同行使,以便计算各个聚合值,举例移动平均值、累堆集合、运营总括或每组结果的前 N 个结果。

SELECT - OVER Clause (Transact-SQL)

  更加的多详细的情况,请参照他事他说加以考察 

 

SQL Server Windowing Functions: ROWS vs. RANGE

 

三、深入分析开窗函数

  可参考 

 

 

四、NEXT VALUE FOR 函数

  通过将 OVEMurano 子句应用于 NEXT VALUE FO本田CR-V 调用,NEXT VALUE FOENCORE函数支持生成排序的连串值。 通过动用 OVE奥迪Q5子句,能够向顾客保险重临的值是根据 OVETucson 子句的 O奇骏DE宝马7系 BY 子子句的逐个生成的。

  例如:

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

   FirstName, LastName

FROM Person.Contact ;

  详细情形请仿效 

本文由金沙国际唯一官网发布于业界动态,转载请注明出处:  在开窗函数出现此前存在重视重用 SQL,SQL

关键词:

上一篇:没有了

下一篇:没有了