sqlserver开窗函数

国际太阳娱乐网站2138 1
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

 

分析函数是什么?
分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值。

  1. 排序函数 (Ranking Function) ;

  2. 聚合函数 (Aggregate Function) ;

  3. 国际太阳娱乐网站2138,分析函数 (Analytic Function) ;

  4. NEXT VALUE FOR Function, 这是给sequence专用的一个函数;

四、NEXT VALUE FOR 函数

分析函数和聚合函数的不同之处是什么?
普通的聚合函数用group by分组,每个分组返回一个统计值,而分析函数采用partition
by分组,并且每组每行都可以返回一个统计值。

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

 

 运行结果:

二. 聚合函数 (Aggregate
Function)

二、聚合开窗函数

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

SQL Server Windowing Functions: ROWS vs. RANGE

 

分析函数的形式
分析函数带有一个开窗函数over(),包含三个分析子句:分组(partition by),
排序(order by), 窗口(rows) ,他们的使用形式如下:over(partition by xxx
order by yyy rows between zzz)。
注:窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提

 

 

运行结果:

 

2. 示例

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;

假设有个门禁系统,在员工每次进门时写入一条记录,记录了“身份号码”,“进门时间”,“衣服颜色”,查询每个员工最后一次进门时的“衣服颜色”。

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

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;

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

Aggregate Window Functions

< OVER_CLAUSE > :: =

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

国际太阳娱乐网站2138 1

排序函数在语法上要求OVER子句里必须含ORDER
BY,否则语法不通过,对于不想排序的场景可以这样变通;

Ranking Window Functions

< OVER_CLAUSE > :: =

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

          <ORDER BY_Clause> )

评级函数

常见评级函数如下:

  • RANK():返回数据项在分组中的排名,在排名相等时会在名次中留下空位,造成排名不连续。
  • DENSE_RANK():同样返回数据项在分组中排名,不过在排名相等时不会留下名位空位。
  • CUME_DIST():返回特定值相对于一组值的位置,是累积分布(cumulative
    distribution)的简写。
  • PERCENT_RANK():返回某个值相对于一组值的百分比排名。
  • NTILE():返回n分片后的值,如三分片、四分片等。
  • ROW_NUMBER():为每一条分组记录返回一个数字,注意不同于rownum伪列。

 

 

 

 

  SQL Server 2012 为聚合函数提供了窗口排序和框架支持,可以将 OVER
子句与函数一起使用,以便计算各种聚合值,例如移动平均值、累积聚合、运行总计或每组结果的前
N 个结果。

 运行结果:

四. NEXT VALUE FOR Function

 

 

--移动平均,举个例子,就是求前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

 

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

 

 

示例目的:显示各部门员工的工资,并附带显示该部分的最高工资。

代码示例2:移动平均

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

示例目标:   当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总

SQL Server 2012开始,窗口聚合函数支持ORDER
BY,以及ROWS/RAGNE选项,原本需要子查询来实现的需求,如: 移动平均
(moving averages), 总计聚合 (cumulative aggregates), 累计求和 (running
totals) 等,变得更加方便;

1. 语法

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;
You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图