更新
正如@BenThul 指出的那样,LAG
有一个可选的默认值,可用于消除使用的需要COALESCE
:
SELECT ACCOUNT_NO,
[Date],
Amount,
LAG(Amount, 1, 0) OVER(PARTITION BY ACCOUNT_NO ORDER BY [Date]) AS "Yesterday Amount"
FROM test
原答案
您可以使用LAG
, withCOALESCE
将NULL
值转换为 0(前一天不存在值时):
SELECT ACCOUNT_NO,
[Date],
Amount,
COALESCE(LAG(Amount) OVER(PARTITION BY ACCOUNT_NO ORDER BY [Date]), 0) AS "Yesterday Amount"
FROM test
输出:
ACCOUNT_NO Date Amount Yesterday Amount
1 23/03/2014 00:00:00 100 0
1 24/03/2014 00:00:00 200 100
1 25/03/2014 00:00:00 50 200
2 23/03/2014 00:00:00 1111 0
3 24/03/2014 00:00:00 1200 0
3 25/03/2014 00:00:00 1300 1200
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句