伟德betvictot手机版 6

伟德betvictot手机版窗口函数,sqlserver开窗函数

从SQL Server 2006起,SQL Server开端支持窗口函数 (Window
Function),以致到SQL Server
贰零壹壹,窗口函数成效巩固,这几天停止帮助以下两种窗口函数:

 

深入分析函数是怎样?
分析函数是Oracle特意用于消释复杂报表计算必要的效率强大的函数,它能够在数据中举办分组然后总结基于组的某种总计值,並且每大器晚成组的每意气风发行都足以重返三个总结值。

  1. 排序函数 (Ranking Function) ;

  2. 聚合函数 (Aggregate Function) ;

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

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

从 转

          

 

 

解析函数和聚合函数的不相同之处是怎么?
平日说来的聚合函数用group by分组,每一个分组重返二个总结值,而深入分析函数选拔partition
by分组,况且每组每行都得以重回叁个计算值。

一. 排序函数(Ranking
Function)

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

              

协理文书档案里的代码示例很全。

  在开窗函数现身以前存在着众多用 SQL
语句很难化解的标题,非常多都要因而复杂的相关子查询只怕存款和储蓄进程来形成。SQL
Server 二零零五 引进了开窗函数,使得这个精髓的难点能够被轻易的解决。

解析函数的款式
分析函数带有八个开窗函数over(),富含多少个剖析子句:分组(partition by),
排序(order by), 窗口(rows) ,他们的施用格局如下:over(partition by xxx
order by yyy rows between zzz)。
注:窗口子句在那间自个儿只说rows形式的窗口,range方式和滑动窗口也不提

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

  窗口是客商内定的生龙活虎组行。开窗函数计算从窗口派生的结果集中各行的值。开窗函数分别接纳于每一个分区,并为每一个分区重新开动总计。

    

排序函数在语法上务求OVE陆风X8子句里必得含O冠道DER
BY,不然语法不通过,对于不想排序的风貌可以那样变化;

  OVE讴歌ZDX子句用于分明在应用关联的开窗函数以前,行集的分区和排序。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 二零零五中,窗口聚合函数仅扶助PARTITION
BY,也正是说仅能对分组的多少整体做聚合运算;

Ranking Window Functions

< OVER_CLAUSE > :: =

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

          <ORDER BY_Clause> )

 

SQL Server 二零一一起先,窗口聚合函数帮助OHavalDER
BY,以致ROWS/RAGNE选项,原来要求子查询来兑现的须求,如: 移动平均
(moving averages), 总计聚合 (cumulative aggregates), 累加求和 (running
totals) 等,变得更加的有利;

 

运转结果:

伟德betvictot手机版, 

留意:O兰德酷路泽DEPAJERO BY 子句钦点对相应 FROM
子句生成的行集举办分区所依赖的列。value_expression 只可以引用通过 FROM
子句可用的列。value_expression
不可能援用接纳列表中的表明式或别称。value_expression
能够是列表明式、标量子查询、标量函数或客商定义的变量。

伟德betvictot手机版 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

 

 运维结果:

 

二、聚合开窗函数

伟德betvictot手机版 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);

伟德betvictot手机版 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

 

伟德betvictot手机版 4

 

3. SQL Server 二零一一 扩展效果与利益

 示例指标:当前进的上大器晚成行(rownum-1)到当前进的汇聚

参考:

  SQL Server 2013 为聚合函数提供了窗口排序和框架援助,能够将 OVEPAJERO子句与函数一齐使用,以便总结各类聚合值,比方移动平均值、累会集合、运转总括或每组结果的前
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

 

运维结果:

三、深入分析开窗函数

伟德betvictot手机版 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福睿斯 子句应用于 NEXT VALUE FOWrangler 调用,NEXT VALUE FO安德拉函数援助生成排序的连串值。 通过选拔 OVE悍马H2子句,能够向客户保证再次来到的值是比照 OVEMurano 子句的 O君越DERAV4 BY
子子句的依次生成的。

运作结果:

  例如:

伟德betvictot手机版 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_NUMBER():为每一条分组记录重回多个数字,注意分裂于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值在前在后的标题,能够在OHavalDER
BY子句之后采纳重要字NULLS FIWranglerST/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;

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

窗口函数可用来计量累加和、移动平均值和骨干平均值等,具体如下:

计量累加和

询问从二零零三年3月到4月的黄金时代共销量,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卡宴 (OWranglerDE奔驰G级 BY month ROWS BETWEEN UNBOUNDED
PRECEDING AND CU福特ExplorerRENT ROW)分析如下:

  • SUM(SUM(amount))中内部的SUM(amount)用于总结月销量总和,外界的SUM()用于总计累加划发售量。
  • O奇骏DE福特Explorer BY month 按月度对查询读取的记录进行排序。
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CUGranCabrioRENT
    ROW定义了窗口的起源和顶峰,起源为UNBOUNDED
    PRECEDING,意味着源点为一定的询问结果集的首先行;终点为CU帕杰罗RENT
    ROW表示终点为管理结果集的日前进。当外界SUM函数计算重返当前的累计划发卖量后,窗口的顶峰便向下活动生机勃勃行。PRECEDING代表发展累积数,若将UNBOUNDED换到数字如1,则象征跟早先一条记下做储存;同有时候还能够向后,使用首要字FOLLOWING,钦点向后积累数只要求在该重大字前加数字就能够,该数字为向后积存的行数(从今现在处也能够见见排序的首要性)。

如:

若要计算钦命月份如1十一月到7月的积累销量,则只须要在where子句中再追加条件month
between 6 and 12就可以。

测算本月内外四个月积存销量,窗口语句:

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

计量移动平均值

算算前一个月与前半年之国内出卖量的活动平均值,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福睿斯 (OENCOREDE智跑 BY month ROWS BETWEEN 3
PRECEDING AND CUENVISIONRENT ROW)拆解深入分析如下:

  • AVG(SUM(amount))内部的sum(amount)总计月销量和,外界的avg()总括平均值。
  • O中华VDE锐界 BY month
    按月度对查询读取的笔录实行排序(那是必得的,因为唯有排序后技巧做积累或左右求平均值)。
  • ROWS BETWEEN 3 PRECEDING AND CU奥迪Q3RENT
    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

FROM all_sales

where year = 2003

GROUP BY month

ORDER BY month;

对骨干平均值部分AVG(SUM(amount)) OVE讴歌ZDX (OTiguanDE奥迪Q3 BY month ROWS BETWEEN 1
PRECEDING AND 1 FOLLOWING)拆解剖判如下:

  • AVG(SUM(amount))内部的sum(amount)总结月销量和,外界的avg()计算平均值。
  • OEscortDE兰德锐界 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;

里面,窗口定义了源点为前段时间终点为后三个月,故而first_value(sum(amount))为前段时期销量而last_value()为后贰个月销量。

 

发表评论