MS SQL窗口函数5秒内完成LAST_VALUE
MS SQL窗口函数5秒内完成LAST_VALUE
提问于 2015-12-01 21:28:26
我已经得到了下一个数据Id，已接收和余额
现在，我需要计算每一段时间的最后一列，因此最后一列应该是balance列在接下来的五秒内的最后一个值。
Id Received Balance LastPerFiveSeconds
171 2015-11-25 10:05:50.630 13548830.86000000 15026517.59000000
172 2015-11-25 10:05:50.760 13590759.43000000 15026517.59000000
173 2015-11-25 10:05:50.790 13632688.00000000 15026517.59000000
174 2015-11-25 10:05:50.850 13662038.00000000 15026517.59000000
175 2015-11-25 10:05:50.910 13687195.15000000 15026517.59000000
176 2015-11-25 10:05:50.940 13703966.58000000 15026517.59000000
177 2015-11-25 10:05:50.950 13720738.01000000 15026517.59000000
178 2015-11-25 10:05:50.990 13762666.59000000 15026517.59000000
179 2015-11-25 10:05:51.060 13787823.73000000 15026517.68000000
180 2015-11-25 10:05:51.200 14987823.73000000 15026517.68000000
181 2015-11-25 10:05:51.360 15026517.68000000 15026517.68000000
182 2015-11-25 10:05:56.630 15034903.39000000 15034903.39000000查询看起来像这样
SELECT id, Received, Balance, LAST_VALUE(Balance) OVER (PARTITION BY DATEADD(S, 5, [Received]) ORDER BY Received RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM Data回答 1
Stack Overflow用户
发布于 2015-12-01 21:48:52
您可以使用CROSS APPLY来实现您想要的功能。它将查看未来5分钟内的余额，并选择最新的余额：
SELECT do.Id ,
do.Received ,
do.Balance ,
ISNULL(details.Balance,do.Balance) AS LastPerFiveMinute
FROM dbo.data do
OUTER APPLY ( SELECT TOP 1
Balance
FROM dbo.Data di
WHERE di.Received <= DATEADD(MINUTE, 5,
do.Received)
AND di.Received >= do.Received
ORDER BY Received DESC
) details
ORDER BY Received ASC 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/34021622
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号