SQL SERVER 2012 T-SQL新增聚合函数

SQL SERVER 2012 T-SQL新增几个聚合函数:

FIRST_VALUE

LAST_VALUE

LEAD

LAG

下面练习一下.

SELECT NAME,date,amount,
first_value(amount) OVER (PARTITION BY NAME ORDER BY date) AS firstvalue,
last_value(amount) OVER (PARTITION BY NAME ORDER BY date) AS lastvalue1,
last_value(amount) OVER (PARTITION BY NAME ORDER BY date RANGE BETWEEN unbounded preceding AND unbounded following) AS lastvalue2,
lead(amount) OVER (PARTITION BY NAME ORDER BY date) AS lead,
lag(amount) OVER (PARTITION BY NAME ORDER BY date) AS lag
FROM test

需要指出的是:

LAST_VALUE,默认是当前行,因此, 要想返回预期的结果,必须使用窗口函数加以限制.

上图第一个返回的,显然不是我们想要的,第二个用了窗口函数,就正确了.