澳门新萄京:16. 窗口函数 (Window Function) 的采用

澳门新萄京:16. 窗口函数 (Window Function) 的采用

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

  例如:

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

澳门新萄京 1SELECT SalesPersonID, [1] AS JAN,[2] AS FEB, [3] AS MAR, [4] AS APR
澳门新萄京 2FROM 
澳门新萄京 3(
澳门新萄京 4    SELECT SalesPersonID, DATEPART(MM,OrderDate) AS MON
澳门新萄京 5    FROM Sales.SalesOrderHeader
澳门新萄京 6    WHERE DATEPART(yyyy,OrderDate) = 2002
澳门新萄京 7) S
澳门新萄京 8
澳门新萄京 9PIVOT 
澳门新萄京 10(
澳门新萄京 11    COUNT(MON) FOR MON IN ([1],[2],[3],[4])
澳门新萄京 12)
澳门新萄京 13AS P
澳门新萄京 14

 

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

   FirstName, LastName

FROM Person.Contact ;

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

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

经过上边包车型地铁PIVOT(COUNT(MON)是大家须要总结的数目,FOR
MON IN ([1],[2],[3],[4]是总结的限制)就成了笔者们最后输出的协会了。
 

帮衬文书档案里的代码示例很全。

 

澳门新萄京 15

咱俩来看多少个例证:对Sales.SalesOrderHeader根据CustomerID进行排序,并出示每条记下的Row
Number。

 

 

澳门新萄京 16

3.         
3.
DENSE_RANK()

从SQL Server 二〇〇七起,SQL Server开首帮忙窗口函数 (Window
Function),以及到SQL Server
二零一二,窗口函数功用加强,最近停止帮助以下三种窗口函数:

 

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;

澳门新萄京 17

SQL Server 贰零零陆中,窗口聚合函数仅支持PARTITION
BY,也便是说仅能对分组的数目总体做聚合运算;

  详细情况请仿效 

 

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

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

一、排名开窗函数

计算累计和

查询从2001年七月到四月的一同销量,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帕杰罗RENT ROW)剖析如下:

  • SUM(SUM(amount))中内部的SUM(amount)用于总计月销量总和,外界的SUM()用于总结累计划贩卖量。
  • OTiggoDE途乐 BY month 按月度对查询读取的记录实行排序。
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CU福特ExplorerRENT
    ROW定义了窗口的源点和终端,起源为UNBOUNDED
    PRECEDING,意味着起源为固定的询问结果集的率先行;终点为CU奥德赛RENT
    ROW表示终点为管理结果集的近期行。当外界SUM函数计算再次回到当前的计算划出卖量后,窗口的极端便向下移动一行。PRECEDING表示发展累计数,若将UNBOUNDED换来数字如1,则表示跟此前一条记下做储存;同一时间还是可以向后,使用首要字FOLLOWING,内定向后积攒数只须求在该重大字前加数字就能够,该数字为向后积累的行数(从那边也足以看出排序的主要)。

如:

若要总计钦命月份如七月到11月的积聚销量,则只须求在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

从上航海用教室中可以看来,最后的结果以OWranglerDEXC90 BY中钦定的SalesOrderID实行排序,不过ROW_NUMBESportage()突显的值却是基于CustmerID排序的。

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

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

澳门新萄京 18

澳门新萄京 19

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

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

分析函数的花样
深入分析函数带有二个开窗函数over(),富含四个深入分析子句:分组(partition by),
排序(order by), 窗口(rows) ,他们的选取情势如下:over(partition by xxx
order by yyy rows between zzz)。
注:窗口子句在此地笔者只说rows方式的窗口,range格局和滑动窗口也不提

澳门新萄京 20SELECT SalesOrderID,CustomerID,NTILE(3) OVER (ORDER BY CustomerID) AS RowNum
澳门新萄京 21FROM Sales.SalesOrderHeader
澳门新萄京 22WHERE CustomerID <3
澳门新萄京 23

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

  下例将基于 SalesOrderID
实行分区,然后为每一个分区分别总计SUM、AVG、COUNT、MIN、MAX。

 

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

 

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

 运维结果:

澳门新萄京 24

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

从 转

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;

1.         
1.
ROW_NUMBER()

二. 聚合函数 (Aggregate
Function)

1. 语法

              

澳门新萄京 25SELECT SalesOrderID,CustomerID,ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
澳门新萄京 26FROM Sales.SalesOrderHeader
澳门新萄京 27

 

 

          

PIVOT的粤语意思是“在枢轴上旋转”,比如对于贰个2维坐标,将横坐标变成纵坐标,将纵坐标形成横坐标。反映在三个Relational Table上的情趣就是:产生为列,变列为行。相信咱们在拓展报表设计的时候都超越过类似于这样的供给:总括2000年内有个别贩卖职员第一季度每一个月管理的订单数。在AdventureWorks
萨姆ple Databse中,Sales
Order存款和储蓄于SaleOrderHeader那张表中,它的结果如下:

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

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

亲自过问目标:对各机构开始展览分组,并顺便展现第一行至当前行的聚焦

澳门新萄京 28SELECT SalesOrderID,CustomerID,DENSE_RANK() OVER (ORDER BY CustomerID) AS RowNum
澳门新萄京 29FROM Sales.SalesOrderHeader
澳门新萄京 30

发表评论

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

网站地图xml地图