sql滑动窗口-在间隔内找到最大值 [英] sql sliding window - finding max value over interval

问题描述

我有一个滑动窗口问题。具体来说，我不知道我的窗口应该在哪里开始，它应该在哪里结束。我确实知道我的间隔/窗口的大小。

i have a sliding window problem. specifically, i do not know where my window should start and where it should end. i do know the size of my interval/window.

我需要找到提供最佳（或最差）效果的窗口的开始/结束，具体取决于您的外观

i need to find the start/end of the window that delivers the best (or worst, depending on how you look at it) case scenario.

这是一个示例数据集：

 value  | tstamp
 100    | 2013-02-20 00:01:00
 200    | 2013-02-20 00:02:00
 300    | 2013-02-20 00:03:00
 400    | 2013-02-20 00:04:00
 500    | 2013-02-20 00:05:00
 600    | 2013-02-20 00:06:00
 500    | 2013-02-20 00:07:00
 400    | 2013-02-20 00:08:00
 300    | 2013-02-20 00:09:00
 200    | 2013-02-20 00:10:00
 100    | 2013-02-20 00:11:00

说我知道我的间隔需要为5分钟。因此，我需要知道5分钟间隔中包含的值和时间戳，其中值的总和最高。在我上面的示例中，从'2013-02-20 00:04:00'到'2013-02-20 00:08:00'的行总计为400 + 500 + 600 + 500 + 400 = 2400 ，这是该表中5分钟内的最高值。

let's say i know that my interval needs to be 5 minutes. so, i need to know the value and timestamps included in the 5 minute interval where the sum of 'value' is the highest. in my above example, the rows from '2013-02-20 00:04:00' to '2013-02-20 00:08:00' would give me a sum of 400+500+600+500+400 = 2400, which is the highest value over 5 minutes in that table.

我不反对在需要时使用多个表。但我正在尝试查找最佳情况间隔。只要间隔时间间隔，结果就可以任意选择。如果我在该时间间隔内获得所有数据点，它仍然可以工作。如果我得到了起点和终点，我也可以使用它们。

i'm not opposed to using multiple tables if needed. but i'm trying to find a "best case scenario" interval. results can go either way, as long as they net the interval. if i get all data points over that interval, it still works. if i get the start and end points, i can use those as well.

我发现了SQL的多个滑动窗口问题，但没有找到窗口大小是已知因素，起点是未知的。

i've found several sliding window problems for SQL, but haven't found any where the window size is the known factor, and the starting point is unknown.

推荐答案

SELECT  *,
        (
        SELECT  SUM(value)
        FROM    mytable mi
        WHERE   mi.tstamp BETWEEN m.tstamp - '2.5 minute'::INTERVAL AND m.tstamp + '2.5 minute'::INTERVAL
        ) AS maxvalue
FROM    mytable m
ORDER BY
        maxvalue DESC
LIMIT   1

这篇关于sql滑动窗口-在间隔内找到最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！