TSQL 滚动平均时间分组

Question

这是对以下内容的跟进:TSQL Group by N Seconds . (我得到了我所要求的，但没有要求正确的东西)

如何获得 count(*) 的 1 秒组的滚动平均值?

所以我想返回每秒计数，但我也希望能够在某些时间间隔内平滑它，比如 10 秒。

所以一种方法可能是每 10 秒取每秒的平均值，这可以在 TSQL 中完成吗？

理想情况下，时间字段将以 Unix 时间返回。

Answer 1

SQL Server在滚动/累积查询中不是特别好。

你可以使用这个:

WITH    q (unix_ts, cnt) AS
        (
        SELECT  DATEDIFF(s, '1970-01-01', ts), COUNT(*)
        FROM    record 
        GROUP BY
                DATEDIFF(s, '1970-01-01', ts)
        )
SELECT  *
FROM    q q1
CROSS APPLY
        (
        SELECT  AVG(cnt) AS smooth_cnt
        FROM    q q2
        WHERE   q2.unix_ts BETWEEN q1.unix_ts - 5 AND q1.unix_ts + 5
        ) q2

，然而，这可能不是很有效，因为它会一遍又一遍地计算相同的重叠间隔。

对于较大的 inverval，使用 CURSOR 可能会更好-基于解决方案，允许保留中间结果(尽管通常它们在性能方面比纯基于集合的解决方案更差)。
Oracle和 PostgreSQL支持这个条款:

WITH    q (unix_ts, cnt) AS
        (
        SELECT  TRUNC(ts, 'ss'), COUNT(*)
        FROM    record 
        GROUP BY
               TRUNC(ts, 'ss')
        )
SELECT  q.*,
        AVG(cnt) OVER (ORDER BY unix_ts RANGE BETWEEN INTERVAL '-5' SECOND AND INTERVAL '5' SECOND)
FROM    q

它保留了一个内部窗口缓冲区并且非常有效。
SQL Server不幸的是，不支持移动窗口。