SQL Server / T-SQL：选择特定间隔（分组依据）

Question

我想编写一个聚合数据的选择（其中有一个 DATETIME 列作为 ID），理论上可以有任何间隔（例如 1 小时、1 小时和 22 秒、1 年和 3 分钟等）。

这个选择应该能够聚合 1 小时、12 分钟、14 秒，并且应该返回 3 行，

SELECT  DATEPART(YEAR,id) as year, 
        DATEPART(MONTH,id) as month,
        DATEPART(DAY,id) as day,
        DATEPART(HOUR,id) as hour,
        DATEPART(MINUTE,id) as minute,
        AVG([Open]),
        AVG([Close]),
        AVG([Min]),
        AVG([Max])
FROM QuoteHistory
where id between '2000-02-06 17:00:00.000' and '2000-02-06 20:36:42.000'
GROUP BY 
    DATEPART(YEAR,id), 
    DATEPART(MONTH,id),
    DATEPART(DAY,id),
    DATEPART(HOUR,id),
    DATEPART(MINUTE,id)
ORDER BY 1,2,3,4,5;

我有点被困在这里，无法解决这个问题。对于像“30 分钟”这样的“简单间隔”，我可以添加一个模数

DATEPART(MINUTE,id)%2

，但是当间隔“触及”超过日期的 1 部分时，我陷入困境。

任何帮助表示赞赏，谢谢！

Answer 1

这里假设一些参数：

;WITH Date_Ranges AS (
    SELECT
        @min_datetime AS start_datetime,
        DATEADD(SECOND, @seconds,
            DATEADD(MINUTE, @minutes,
            DATEADD(HOUR, @hours,
            DATEADD(DAY, @days,
            DATEADD(WEEK, @weeks,
            DATEADD(MONTH, @months,
            DATEADD(YEAR, @years, @min_datetime))))))) AS end_datetime
    UNION ALL
    SELECT
        DATEADD(SECOND, 1, end_datetime),
        DATEADD(SECOND, @seconds,
            DATEADD(MINUTE, @minutes,
            DATEADD(HOUR, @hours,
            DATEADD(DAY, @days,
            DATEADD(WEEK, @weeks,
            DATEADD(MONTH, @months,
            DATEADD(YEAR, @years, end_datetime)))))))
    FROM
        Date_Ranges
    WHERE
        DATEADD(SECOND, 1, end_datetime) < @max_datetime
)
SELECT
    DR.min_datetime,
    DR.max_datetime,
    AVG([Open]),
    AVG([Close]),
    AVG([Min]),
    AVG([Max])
FROM
    Date_Ranges DR
LEFT OUTER JOIN Quote_History QH ON
    QH.id BETWEEN DR.min_datetime AND DR.max_datetime
GROUP BY
    DR.min_datetime,
    DR.max_datetime
ORDER BY
    DR.min_datetime,
    DR.max_datetime

您可能需要摆弄如何处理边缘情况（日期范围之间的 1 秒范围可能是一个问题，具体取决于您的数据）。不过，这应该会为您指明正确的方向。