Window offset 函数包括两类,一类是计算相对于当前行的偏移位置,一类则是相对于window frame的起点或是终点。
第一类包括 LAG和LEAD这两个函数,第二类包括FIRST_VALUE, LAST_VALUE和 NTH_VALUE,在SQL server 2012中没有实现NTH_VALUE。
Example for LAG and LEAD:
SELECT custid, orderdate, orderid, val,
LAG(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS prevval,
LEAD(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid) AS nextval
FROM Sales.OrderValues;
ORDER BY orderdate, orderid) AS nextval
FROM Sales.OrderValues;
Result:
example for FIRST_VALUE, LAST_VALUE:
SELECT custid, orderdate, orderid, val,
FIRST_VALUE(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid) AS val_firstorder,
LAST_VALUE(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) AS val_lastorder
FROM Sales.OrderValues
order by custid,val
FIRST_VALUE(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid) AS val_firstorder,
LAST_VALUE(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) AS val_lastorder
FROM Sales.OrderValues
order by custid,val
Result:
这个结果很好体会,因此不赘述
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/101629/viewspace-748588/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/101629/viewspace-748588/