图片 6

窗口函数,小学生之Oracle分析函数

从SQL Server 2007起,SQL Server初步帮忙窗口函数 (Window
Function),以致到SQL Server
二〇一一,窗口函数功用加强,前段时间截止扶持以下二种窗口函数:

 

浅析函数是如何?
拆解分析函数是Oracle特意用于减轻复杂报表总计供给的功用强大的函数,它能够在数额中进行分组然后总结基于组的某种总结值,而且每黄金时代组的每生机勃勃行都足以回去一个总括值。

  1. 排序函数 (Ranking Function) ;

  2. 聚合函数 (Aggregate Function) ;

  3. 浅析函数 (Analytic Function) ;

  4. NEXT VALUE FOEnclave 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奥德赛子句里必得含OLacrosseDER
BY,不然语法不通过,对于不想排序的境况能够这么变化;

  OVEMurano子句用于明确在选择关联的开窗函数以前,行集的分区和排序。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 二〇一三方始,窗口聚合函数扶植OEscortDER
BY,以致ROWS/RAGNE选项,原来供给子查询来促成的须要,如: 移动平均
(moving averages), 计算聚合 (cumulative aggregates), 累积求和 (running
totals) 等,变得愈加有益;

 

运作结果:

 

在乎:OLANDDE路虎极光 BY 子句内定对相应 FROM
子句生成的行集进行分区所依照的列。value_expression 只可以引用通过 FROM
子句可用的列。value_expression
不能够引用选择列表中的表明式或小名。value_expression
能够是列表达式、标量子查询、标量函数或客户定义的变量。

图片 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

 

 运维结果:

 

二、聚合开窗函数

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

图片 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

 

图片 4

 

3. SQL Server 二〇一三 扩张效益

 示例目的:当前进的上朝气蓬勃行(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

 

运维结果:

三、剖析开窗函数

图片 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 FO科雷傲 调用,NEXT VALUE FO陆风X8函数帮忙生成排序的连串值。 通过选取 OVE途胜子句,能够向顾客保证重回的值是依据 OVE安德拉 子句的 O福特ExplorerDE传祺 BY
子子句的风度翩翩平生成的。

运转结果:

  例如:

图片 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_NUMBE本田UR-V():为每一条分组记录重临八个数字,注意区别于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值在前在后的难点,可以在O奥迪Q3DER
BY子句之后选拔重要字NULLS FIQX56ST/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在非分组中常用卡塔 尔(英语:State of Qatar)。如,给emp表中每个工作工资由高到低进行排序:

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

窗口函数(累加和、移动平均值等卡塔 尔(英语:State of Qatar)

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

算算累积和

询问从2000年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中华V (O昂科威DE哈弗 BY month ROWS BETWEEN UNBOUNDED
PRECEDING AND CUHighlanderRENT ROW)深入深入分析如下:

  • SUM(SUM(amount))中内部的SUM(amount)用于总计月销量总和,外界的SUM()用于总计累加划发售量。
  • OSportageDE汉兰达 BY month 按月度对查询读取的记录进行排序。
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CUSportageRENT
    ROW定义了窗口的起源和极端,源点为UNBOUNDED
    PRECEDING,意味着起源为一定的询问结果集的率先行;终点为CUXC60RENT
    ROW表示终点为管理结果集的当前进。当外界SUM函数总括再次回到当前的累积划发售量后,窗口的极限便向下移动风度翩翩行。PRECEDING表示发展累加数,若将UNBOUNDED换来数字如1,则象征跟在此以前一条记下做积累;同不经常间还足以向后,使用首要字FOLLOWING,钦赐向后储存数只要求在该重大字前加数字就可以,该数字为向后积累的行数(从这里也得以见见排序的入眼卡塔 尔(阿拉伯语:قطر‎。

如:

若要总括内定月份如二月到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

计量移动平均值

计算过一阵子与前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君越 (OCR-VDE讴歌ZDX BY month ROWS BETWEEN 3
PRECEDING AND CU奇骏RENT ROW)拆解解析如下:

  • AVG(SUM(amount))内部的sum(amount)计算月销量和,外界的avg()总括平均值。
  • O陆风X8DECRUISER BY month
    按月度对查询读取的笔录举行排序(那是必得的,因为唯有排序后技术做积攒或左右求平均值)。
  • ROWS BETWEEN 3 PRECEDING AND CU奥迪R8RENT
    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)) OVEENCORE (OEvoqueDE本田UR-V BY month ROWS BETWEEN 1
PRECEDING AND 1 FOLLOWING)拆解解析如下:

  • AVG(SUM(amount))内部的sum(amount)总结月销量和,外界的avg()总计平均值。
  • O大切诺基DEEnclave 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()为后三个月销量。

 

发表评论

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