菜单

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

2019年8月2日 - 数据网络
小学生之Oracle分析函数,窗口函数

 

  下例将根据 SalesOrderID
进行分区,然后为每个分区分别统计SUM、AVG、COUNT、MIN、MAX。

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

三、分析开窗函数

 

 

  可参考 

 

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

 

运行结果:

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

四、NEXT VALUE FOR 函数

 

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 增加功能

 

1. 语法

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

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

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

窗口第一条和最后一条记录

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()为后一个月销量。

 

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

  更多详情,请参考 

运行结果:

  1. 排序函数 (Ranking Function) ;

  2. 聚合函数 (Aggregate Function) ;

  3. 分析函数 (Analytic Function) ;

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

  OVER
子句用于确定在应用关联的开窗函数之前,行集的分区和排序。PARTITION BY
将结果集分为多个分区。

图片 1

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

 

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;

二. 聚合函数 (Aggregate
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);

 

SELECT – OVER Clause (Transact-SQL)

 

图片 2

从SQL Server 2005起,SQL Server开始支持窗口函数 (Window
Function),以及到SQL Server
2012,窗口函数功能增强,目前为止支持以下几种窗口函数:

 

计算累计和

查询从2003年1月到12月的累计销量,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)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW)解析如下:

如:

若要计算指定月份如6月到12月的累积销量,则只需要在where子句中再增加条件month
between 6 and 12即可。

计算当月跟前3个月累积销量,窗口语句:

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

一. 排序函数(Ranking
Function)

 

计算移动平均值

计算本月与前3个月之间销量的移动平均值,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)) OVER (ORDER BY month ROWS BETWEEN 3
PRECEDING AND CURRENT ROW)解析如下:

代码示例2:移动平均

 

图片 3

 

从 转

示例目的:按照deptno分组,然后计算每组值的总和

相关文章

发表评论

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

网站地图xml地图