频道栏目
首页 > 数据库 > SQL Server > 正文
锋利的SQL2014:基于窗口的偏移计算
2015-02-14 09:53:36         来源:张洪举  
收藏   我要投稿

SQL Server 2012引入了四个偏移函数:LAG和LEAD、FIRST_VALUE和LAST_VALUE,用于从当前行的某个偏移量、或是一个窗口框架的开头或结尾的行返回一个元素。

LAG和LEAD支持窗口分区和窗口排序子句,FIRST_VALUE和LAST_VALUE在支持窗口分区和窗口排序子句的基础上,还支持窗口框架子句。

9.5.1 LAG和LEAD函数

LAG函数用于在当前行之前查找,LEAD函数在之后查找。函数的第一个参数(必选)指定要返回值的列,第二个参数(可选)是偏移量(如果未指定,默认为1),第三个参数(可选)是在请求的偏移量没有行返回的情况下的默认值(如果未指定,默认为NULL)。

下面的示例使用在9.2节创建的Orders表,用于查询雇员上一季度和下一季度的销售额。其中,LAG函数返回上一季度销售额,LEAD函数返回下一季度销售值。查询结果如表9-18所示。

SELECT EmpID, SalesYear, SalesQuarter,

LAG(SubTotal) OVER(PARTITION BYEmpID

ORDER BY EmpID, SalesYear, SalesQuarter) AS PrevVal,

SubTotalAS CurVal,

LEAD(SubTotal) OVER(PARTITION BY EmpID

ORDER BY EmpID, SalesYear, SalesQuarter) AS NextVal

FROM dbo.Orders;

 

\

由于没有指定偏移量,函数便假定为默认值1。并且没有指定第三个参数,当没有前一行或下一行时,便假定为默认值NULL。下面的查询用于获取雇员前面第二个季度和后面第二个季度的销售额,表达式LAG(SubTotal, 2, 0)从前面第二行中获取值,并且如果找不到行,将返回0。查询结果如表9-19所示。

SELECT EmpID, SalesYear, SalesQuarter,

LAG(SubTotal, 2, 0) OVER(PARTITION BY EmpID

ORDER BY EmpID,SalesYear, SalesQuarter) AS PrevVal,

SubTotalAS CurVal,

LEAD(SubTotal, 2, 0) OVER(PARTITION BY EmpID

ORDER BY EmpID,SalesYear, SalesQuarter) AS NextVal

FROM dbo.Orders;

表9-19 获取雇员前面第二个季度和后面第二个季度的销售额

EmpID

SalesYear

SalesQuarter

PrevVal

CurVal

NextVal

1

2013

1

0

100

300

1

2013

2

0

200

400

1

2013

3

100

300

200

1

2013

4

200

400

200

1

2014

1

300

200

100

1

2014

2

400

200

100

1

2014

3

200

100

0

1

2014

4

200

100

0

2

2013

1

0

150

350

2

2013

2

0

250

450

2

2013

3

150

350

250

2

2013

4

250

450

250

2

2014

1

350

250

150

2

2014

2

450

250

150

2

2014

3

250

150

0

2

2014

4

250

150

0

9.5.2 FIRST_VALUE和LAST_VALUE函数

FIRST_VALUE和LAST_VALUE函数分别允许从窗口框架的第一行和最后一行返回一个元素。因此,这些函数支持窗口分区、排序和框架子句。

如果希望元素来自窗口分区的第一行,应使用带有窗口框架范围“ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”的FIRST_VALUE。由于该框架是默认的,故而也可以省略。

如果希望元素来自窗口分区的最后一行,应使用带有窗口框架范围“ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING”的LAST_VALUE。需要注意的是,该框架不能省略,因为在未指定该框架的情况下会使用默认框架“ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”,这样会导致最后一行就是当前行,即CURRENT ROW,所以即使使用了LAST_VALUE函数,也不会得到窗口的最后一行。

下面通过一个示例来说明这两个函数的使用方法。下面的查询使用FIRST_VALUE函数返回雇员当年第一个季度的销售额,使用LAST_VALUE函数返回当年最后一个季度的销售额。查询结果如表9-20所示。

SELECT EmpID, SalesYear, SalesQuarter,

FIRST_VALUE(SubTotal) OVER(PARTITION BY EmpID, SalesYear

ORDER BY EmpID,SalesYear, SalesQuarter

ROWS BETWEEN UNBOUNDEDPRECEDING

AND CURRENT ROW) AS FirstVal,

SubTotalAS CurVal,

LAST_VALUE(SubTotal) OVER(PARTITION BY EmpID, SalesYear

ORDER BY EmpID,SalesYear, SalesQuarter

ROWS BETWEEN CURRENT ROW

AND UNBOUNDED FOLLOWING) AS LastVal

FROM dbo.Orders

ORDER BY EmpID, SalesYear, SalesQuarter;

\

点击复制链接 与好友分享!回本站首页
相关TAG标签
上一篇:02_NoSQL数据库之Redis数据库:string类型和hash类型
下一篇:SQL优化--逻辑优化--数据库的约束规则与语义优化
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站