SQL Server 2012 开发新特性：分析函数

一、概述

　　分析函数（或称解析函数）基于一组行计算聚合值。不过，与聚合函数不同，它们可能针对每个组返回多行。可以使用分析函数来计算移动平均线、运行总计、百分比或一个组内的前 N 个结果。

　　官网 http://msdn.microsoft.com/zh-cn/library/hh213234.aspx

　　SQL Server 2012 支持以下 8 个分析函数：

1、CUME_DIST

2、LEAD

　　访问相同结果集的后续行中的数据，而不使用 SQL Server 2012 中的自联接。 LEAD 以当前行之后的给定物理偏移量来提供对行的访问。 在 SELECT 语句中使用此分析函数可将当前行中的值与后续行中的值进行比较。　　

3、FIRST_VALUE

4、PERCENTILE_CONT

5、LAG

　　访问相同结果集的先前行中的数据，而不使用 SQL Server 2012 中的自联接。 LAG 以当前行之前的给定物理偏移量来提供对行的访问。 在 SELECT 语句中使用此分析函数可将当前行中的值与先前行中的值进行比较。　　 　　

6、PERCENTILE_DISC

7、LAST_VALUE

8、PERCENT_RANK

二、构造测试环境

CREATE TABLE [dbo].[Profits](
[Territory] [varchar](2) NOT NULL,
[FiscalYear] [smallint] NOT NULL,
[Profit] [int] NOT NULL,
CONSTRAINT [PK_Profits] PRIMARY KEY CLUSTERED
([Territory] ASC, [FiscalYear] ASC) ON [PRIMARY]
) ON [PRIMARY]

declare @x smallint,@y tinyint
set @x=2011
while @x<=2012
begin
set @y=0
while @y<3
begin
insert into dbo.Profits
values
( substring('CNUSEU' , @y *2+1 , 2),
@x,
cast(floor(rand()*1000) as int))
set @y = @y + 1
end
set @x = @x + 1
end

三、示例

1、查询某个区域在后续年度的销售业绩的差异。

SELECT Territory, FiscalYear, Profit AS CurrentProfit,
LEAD(Profit, 1, 0) OVER(ORDER BY Territory) AS NextProfit
FROM Profits
where Territory ='CN'

　　结果：

Territory FiscalYear CurrentProfit NextProfit
CN 2011 551 925
CN 2012 925 0

2、查询每个区域在后续年度的销售业绩的差异。

SELECT Territory, FiscalYear, Profit AS CurrentProfit,
LEAD(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY FiscalYear) AS NextProfit
FROM Profits
where FiscalYear in (2011,2012)

　　结果：

Territory FiscalYear CurrentProfit NextProfit
CN 2011 551 925
CN 2012 925 0
EU 2011 227 50
EU 2012 50 0
US 2011 87 595
US 2012 595 0

3、查询每个区域在先前年度的销售业绩的差异。

SELECT Territory, FiscalYear, Profit AS CurrentProfit,
LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY FiscalYear) AS PrevProfit
FROM Profits
where FiscalYear in (2011,2012)

　　结果：

Territory FiscalYear CurrentProfit PrevProfit
CN 2011 551 0
CN 2012 925 551
EU 2011 227 0
EU 2012 50 227
US 2011 87 0
US 2012 595 87

4. 连续3年的“行列转换”

select * from ( SELECT Territory, FiscalYear, Profit AS FY11, LEAD(Profit, 1, 0) OVER(ORDER BY Territory) AS Fy12 ,LEAD(Profit, 2, 0) OVER(ORDER BY Territory) AS Fy13 FROM Profits ) tmp where FiscalYear=2011