一、概述

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

　　官网

　　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=2011while @x<=2012begin 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 + 1end

三、示例

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

SELECT Territory, FiscalYear, Profit AS CurrentProfit,

LEAD(Profit, 1, 0) OVER(ORDER BY Territory) AS NextProfit FROM Profitswhere Territory ='CN'

　　结果：

Territory FiscalYear CurrentProfit NextProfit

CN 2011 551 925CN 2012 925 0

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

SELECT Territory, FiscalYear, Profit AS CurrentProfit,

LEAD(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY FiscalYear) AS NextProfit FROM Profitswhere FiscalYear in (2011,2012)

　　结果：

Territory FiscalYear CurrentProfit NextProfit

CN 2011 551 925CN 2012 925 0EU 2011 227 50EU 2012 50 0US 2011 87 595US 2012 595 0

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

SELECT Territory, FiscalYear, Profit AS CurrentProfit,

LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY FiscalYear) AS PrevProfit FROM Profitswhere FiscalYear in (2011,2012)

　　结果：

Territory FiscalYear CurrentProfit PrevProfit

CN 2011 551 0CN 2012 925 551EU 2011 227 0EU 2012 50 227US 2011 87 0US 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