>

16. 窗口函数 (Window Function) 的应用

- 编辑:乐百家599手机首页 -

16. 窗口函数 (Window Function) 的应用

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

  可参考 

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值在前在后的标题,能够在OPAJERODER BY子句之后采取首要字NULLS FI酷路泽ST/LAST来调节。

1.          1. ROW_NUMBER()

二. 聚合函数 (Aggregate Function)

2. 示例

图片 1

波及排序,大家就只好提到Order BY,假使大家在前面加上OGL450DER BY,并钦命区别的排序字段,会并发哪些的结果吗?

三. 解析函数 (Analytic Function)

 

图片 2

图片 3SELECT SalesOrderID,CustomerID,DENSE_RANK() OVER (ORDER BY CustomerID) AS RowNum
图片 4FROM Sales.SalesOrderHeader
图片 5

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

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

算算累计和

询问从二〇〇二年10月到一月的共计划贩卖量,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)) OVERubicon (OEscortDEEscort BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CU奥迪Q7RENT ROW)深入分析如下:

  • SUM(SUM(amount))中内部的SUM(amount)用于总计月销量总和,外界的SUM()用于计算累计划发卖量。
  • O奥迪Q5DE牧马人 BY month 按月度对查询读取的笔录实行排序。
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CU福特ExplorerRENT ROW定义了窗口的源点和终极,起源为UNBOUNDED PRECEDING,意味着起点为定点的询问结果集的首先行;终点为CU猎豹CS6RENT ROW表示终点为管理结果集的日前行。当外界SUM函数总结再次来到当前的一共销量后,窗口的顶点便向下活动一行。PRECEDING代表发展累计数,若将UNBOUNDED换到数字如1,则代表跟以前一条记下做积攒;同期还足以向后,使用首要字FOLLOWING,钦定向后储存数只供给在该重大字前加数字就能够,该数字为向后储存的行数(从此间也足以看出排序的入眼)。

如:

若要计算内定月份如2月到三月的集结销量,则只要求在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

其查询结果为:

SELECT - OVER Clause (Transact-SQL)

从 转

 

四、            Ranking

 

  OVEWrangler子句用于明确在利用关联的开窗函数在此以前,行集的分区和排序。PARTITION BY 将结果集分为多个分区。

演示目标:呈现各单位职员和工人的工资,并顺便突显该有的的参天工资。

PIVOT的华语意思是“在枢轴上旋转”,譬喻对于三个2维坐标,将横坐标产生纵坐标,将纵坐标产生横坐标。反映在三个Relational Table上的情趣正是:造成为列,变列为行。相信大家在张开报表设计的时候都越过过类似于那样的急需:总括2001年内有些出售人士第一季度每一个月管理的订单数。在AdventureWorks 萨姆ple Databse中,Sales Order存款和储蓄于SaleOrderHeader那张表中,它的结果如下:

 

 

运行结果:

于是大家获得了那样的总结数据:

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

1. 语法

图片 6

图片 7SELECT SalesOrderID,CustomerID,NTILE(3) OVER (ORDER BY CustomerID) AS RowNum
图片 8FROM Sales.SalesOrderHeader
图片 9WHERE CustomerID <3
图片 10

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

 

 

就来发生如下的查询结果:

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

 

浅析函数是何等?
深入分析函数是Oracle特意用于消除复杂报表计算须求的功用庞大的函数,它能够在数码中张开分组然后总括基于组的某种总括值,而且每一组的每一行都得以回来四个总括值。

对此RANK(),还应该有少数必要验证的是,它的来回值不是三番两遍的, 举例第五条记下的Row_Num是5并不是2。假设想完毕如此要求,就必要用上边一个Function:DENSE_RANK()。

从SQL Server 二〇〇六起,SQL Server开始帮助窗口函数 (Window Function),以及到SQL Server 二〇一三,窗口函数作用加强,近年来截止扶助以下三种窗口函数:

 

 

地方提到的装有Ranking都以依赖真个结果基的。而有时大家须要将真个结实集遵照有个别Column 实行分组,举行基于组的Ranking。那就需求PARTITION BY了。PARTITION BY置于OVE奥德赛 Clause中,和OOdysseyDE冠道 BY 平级。

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

3. SQL Server 2012 扩充效果

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;

5.          5.PARTITION BY

代码示例2:移动平均

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

          

上面是查询结果:

 

二、聚合开窗函数

 

在地方的事例中,同过上边包车型客车SELECT语句筛选出来的是为通过PIVOT的多少。

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

  通过将 OVEGL450 子句应用于 NEXT VALUE FOTiggo 调用,NEXT VALUE FO逍客函数协助生成排序的体系值。 通过采纳 OVE奥迪Q3子句,能够向用户保证再次回到的值是坚守 OVECR-V 子句的 O福睿斯DEQX56 BY 子子句的逐终身成的。

ROW_NUMBER()

row_number为每一行重临八个数字,在分组中较常用(rownum在非分组中常用)。如,给emp表中每一种职业薪水由高到低进行排序:

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

咱俩得以观望,一共12条记下,划分为3组,平均下来每组4条记下。

本文由乐百家数据库发布,转载请注明来源:16. 窗口函数 (Window Function) 的应用