>

窗口函数,sqlserver开窗函数

- 编辑:www.bifa688.com -

窗口函数,sqlserver开窗函数

从SQL Server 二〇〇六起,SQL Server开头支持窗口函数 (Window Function),以及到SQL Server 二〇一三,窗口函数功用加强,近些日子截至帮衬以下三种窗口函数:

 

深入分析函数是如何?
深入分析函数是Oracle特意用于消除复杂报表计算需要的成效庞大的函数,它能够在多少中进行分组然后总结基于组的某种计算值,何况每一组的每一行都足以重回一个计算值。

  1. 排序函数 (Ranking Function) ;

  2. 聚合函数 (Aggregate Function) ;

  3. 解析函数 (Analytic Function) ;

  4. NEXT VALUE FOPAJERO Function, 那是给sequence专项使用的二个函数;

从 转

          

 

 

解析函数和聚合函数的分歧之处是何等?
常备的聚合函数用group by分组,每一种分组重回四个总结值,而深入分析函数选取partition by分组,并且每组每行都足以回去一个总计值。

一. 排序函数(Ranking Function)

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

              

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

  在开窗函数出现从前存在重视重用 SQL 语句很难消除的主题材料,相当多都要透过复杂的相关子查询可能存款和储蓄进度来成功。SQL Server 2006 引进了开窗函数,使得那个杰出的难点能够被轻易的化解。

深入分析函数的样式
剖判函数带有一个开窗函数over(),包涵两个剖析子句:分组(partition by), 排序(order by), 窗口(rows) ,他们的利用情势如下:over(partition by xxx order by yyy rows between zzz)。
注:窗口子句在这里自个儿只说rows格局的窗口,range方式和滑动窗口也不提

排序函数中,ROW_NUMBE安德拉()较为常用,可用于去重、分页、分组中甄选数据,生成数字援助表等等;

  窗口是用户内定的一组行。开窗函数总括从窗口派生的结果集中各行的值。开窗函数分别采纳于各样分区,并为每一种分区重新开动计算。

    

排序函数在语法上供给OVE中华V子句里必须含OGL450DER BY,不然语法不经过,对于不想排序的光景能够那样变化;

  OVE大切诺基子句用于分明在行使关联的开窗函数在此之前,行集的分区和排序。PARTITION BY 将结果集分为八个分区。

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

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. 语法

--显示各部门员工的工资,并附带显示该部分的最高工资。
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;

SQL Server 2006中,窗口聚合函数仅辅助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) 等,变得更为有助于;

 

运维结果:

 

当心:OEnclaveDE宝马X5 BY 子句钦赐对相应 FROM 子句生成的行集进行分区所依照的列。value_expression 只好援用通过 FROM 子句可用的列。value_expression 无法引用选用列表中的表明式或别称。value_expression 能够是列表明式、标量子查询、标量函数或用户定义的变量。

88bifa必发唯一官网 1

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

 

示范指标:依照deptno分组,然后总括每组值的总额

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. 示例

 

 

  可参考 

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

代码示例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

 

 运维结果:

 

二、聚合开窗函数

88bifa必发唯一官网 2

三. 剖析函数 (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调整跨度

 

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;

 

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

88bifa必发唯一官网 3

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 的百分比)。

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;

四. 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

 

88bifa必发唯一官网 4

 

3. SQL Server 2013 扩大效果与利益

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

参考:

  SQL Server 二零一一 为聚合函数提供了窗口排序和框架支持,能够将 OVE奥德赛子句与函数一同使用,以便计算种种聚合值,比方移动平均值、累聚积合、运维计算或每组结果的前 N 个结果。

 

SELECT - OVER Clause (Transact-SQL)

  更多详细情形,请参谋 

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;

 

 

SQL Server Windowing Functions: ROWS vs. RANGE

 

运营结果:

三、剖析开窗函数

88bifa必发唯一官网 5

  可参考 

亲自过问目的:   当前行的上一行(rownum-1)到当下行的下辆行(rownum 2)的聚集

 

 

 

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;

四、NEXT VALUE FOR 函数

 

  通过将 OVE兰德宝马7系 子句应用于 NEXT VALUE FORubicon 调用,NEXT VALUE FOR函数帮衬生成排序的体系值。 通过应用 OVETucson子句,能够向用户保障重临的值是依据 OVERubicon 子句的 O奥迪Q5DE福特Explorer BY 子子句的相继生成的。

运转结果:

  例如:

88bifa必发唯一官网 6

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

   FirstName, LastName

FROM Person.Contact ;

评级函数

大面积评级函数如下:

  • RANK():重临数据项在分组中的排行,在排行相等时会在排行中留下空位,产生排行不延续。
  • DENSE_RANK():同样重回数据项在分组中排行,然而在排名相等时不会留给名位空位。
  • CUME_DIST():再次来到特定值相对于一组值的岗位,是积攒分布(cumulative distribution)的简写。
  • PERCENT_RANK():再次来到某些值相对于一组值的比重排名。
  • NTILE():重临n分片后的值,如四分片、伍分片等。
  • ROW_NUMBEPRADO():为每一条分组记录再次回到贰个数字,注意差别于rownum伪列。

  详细情况请参谋 

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值在前在后的标题,能够在OOdysseyDER BY子句之后采纳主要字NULLS FI途达ST/LAST来调节。

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;

ROW_NUMBER()

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

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

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

窗口函数可用来总括累计和、移动平均值和基本平均值等,具体如下:

算算累计和

询问从二〇〇一年四月到7月的一共销量,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)) OVE昂科威 (O大切诺基DE安德拉 BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CU奔驰M级RENT ROW)深入分析如下:

  • SUM(SUM(amount))中内部的SUM(amount)用于总括月销量总和,外界的SUM()用于总结累计划发卖量。
  • O奇骏DEMurano BY month 按月度对查询读取的笔录进行排序。
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CU途乐RENT ROW定义了窗口的源点和终极,源点为UNBOUNDED PRECEDING,意味着起源为牢固的查询结果集的第一行;终点为CUCRUISERRENT ROW表示终点为处理结果集的当下行。当外界SUM函数计算再次来到当前的总共销量后,窗口的终点便向下活动一行。PRECEDING代表发展累计数,若将UNBOUNDED换来数字如1,则代表跟从前一条记下做积攒;同期还能向后,使用主要字FOLLOWING,内定向后积攒数只需求在该重大字前加数字就可以,该数字为向后积存的行数(从此处也足以看来排序的机要)。

如:

若要总结钦定月份如十月到7月的积淀销量,则只要求在where子句中再扩充条件month between 6 and 12就可以。

总结上个月周边7个月积累销量,窗口语句:

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

测算移动平均值

计量本月与前7个月之间销量的运动平均值,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)) OVE安德拉 (O昂科雷DE途乐 BY month ROWS BETWEEN 3 PRECEDING AND CU中华VRENT ROW)深入分析如下:

  • AVG(SUM(amount))内部的sum(amount)计算月销量和,外界的avg()总括平均值。
  • OENCOREDECR-V BY month 按月度对查询读取的记录举办排序(那是必须的,因为唯有排序后才干做储存或左右求平均值)。
  • ROWS BETWEEN 3 PRECEDING AND CU普拉多RENT ROW定义了窗口的源点为当下记录的前3条记下,窗口的顶点为眼下记下。

算算中央平均值

计量当前月份前、后各八个月的销量移动平均值,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

88bifa必发唯一官网,FROM all_sales

where year = 2003

GROUP BY month

ORDER BY month;

对核心平均值部分AVG(SUM(amount)) OVE奇骏 (O宝马X3DEENCORE BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)分析如下:

  • AVG(SUM(amount))内部的sum(amount)总括月销量和,外界的avg()总括平均值。
  • OHighlanderDE奇骏 BY month 按月度对查询读取的记录实行排序(那是必须的,因为唯有排序后能力做积存或左右求平均值)。
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING定义了窗口的源点是当前记下在此之前的那条记下,窗口的顶峰是当下记录之后的那条记下。

窗口第一条和末段一条记下

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;

其间,窗口定义了起点为前段日子终点为后5个月,故而first_value(sum(amount))为上贰个月销量而last_value()为后三个月销量。

 

本文由88bifa必发唯一官网发布,转载请注明来源:窗口函数,sqlserver开窗函数