SQL 获取接下来 n 行的最大值

发布于 2025-01-11 12:36:19 字数 1088 浏览 0 评论 0原文

假设我有一个包含两列的表：时间和值。我希望能够获得一张桌子：每次获取接下来的 n 秒的最大值。

如果我想要每隔 3 秒的最大值，则下表：

时间	值
1	6
2	1
3	4
4	2
5	5
6	1
7	1
8	3
9	7

应该返回：

时间	值	最大值
1	6	6
2	1	4
3	4	5
4	2	5
5	5	5
6	1	3
7	1	7
8	3	NULL
9	7	NULL

有没有有没有办法直接用sql查询来做到这一点？

原文

Say I have a table with two columns: the time and the value. I want to be able to get a table with :
for each time get the max values of every next n seconds.

If I want the max value of every next 3 seconds, the following table:

time	value
1	6
2	1
3	4
4	2
5	5
6	1
7	1
8	3
9	7

Should return:

time	value	max
1	6	6
2	1	4
3	4	5
4	2	5
5	5	5
6	1	3
7	1	7
8	3	NULL
9	7	NULL

Is there a way to do this directly with an sql query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

放赐 2025-01-18 12:36:19

您可以使用 max 窗口函数：

select *,
case 
when row_number() over(order by time desc) > 2 then
max(value) over(order by time rows between current row and 2 following)
end as max
from table_name;

Fiddle

case 表达式检查当前行之后是否有超过 2 行来计算最大值，否则返回 null （对于按时间排序的最后 2 行）。

You can use the max window function:

select *,
case 
when row_number() over(order by time desc) > 2 then
max(value) over(order by time rows between current row and 2 following)
end as max
from table_name;

Fiddle

The case expression checks that there are more than 2 rows after the current row to calculate the max, otherwise null is returned (for the last 2 rows ordered by time).

回复收藏 0 原文

烟雨凡馨 2025-01-18 12:36:19

与 Zakaria 类似的版本，但此解决方案使用的 CPU 资源减少了约 40%（缩放至 3M 行进行基准测试），因为窗口函数都使用完全相同的 OVER 子句，因此 SQL 可以更好地优化查询。

3行滚动窗口的优化最大值

SELECT *,
    MaxValueIn3SecondWindow =   CASE 
                                    /*Check 3 rows exists to compare. If 3 rows exists, then calculate max value*/
                                    WHEN 3 =   COUNT(*) OVER (ORDER BY [Time] ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
                                    /*Returns max [Value] between the current row and the next 2 rows*/
                                    THEN MAX(A.[Value]) OVER (ORDER BY [Time] ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
                                END
FROM #YourTable AS A

Similar Version to Zakaria, but this solution uses about 40% less CPU resources (scaled to 3M rows for benchmark) as the window functions both use the same exact OVER clause so SQL can better optimize the query.

Optimized Max Value of Rolling Window of 3 Rows

SELECT *,
    MaxValueIn3SecondWindow =   CASE 
                                    /*Check 3 rows exists to compare. If 3 rows exists, then calculate max value*/
                                    WHEN 3 =   COUNT(*) OVER (ORDER BY [Time] ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
                                    /*Returns max [Value] between the current row and the next 2 rows*/
                                    THEN MAX(A.[Value]) OVER (ORDER BY [Time] ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
                                END
FROM #YourTable AS A

回复收藏 0 原文

~没有更多了~