每小时半点（那个小时）的值总和

给定数据：

CREATE TABLE #Demo 
(
    Val integer NOT NULL, 
    TS datetime2(3) NOT NULL
);

INSERT #Demo 
    (Val, TS)
VALUES
(1, '2019-08-12 00:00:00.013'),
(3, '2019-08-12 00:10:00.013'),
(2, '2019-08-12 00:20:00.013'),
(2, '2019-08-12 00:30:00.013'),
(0, '2019-08-12 00:40:00.013'),
(0, '2019-08-12 00:50:00.013'),
(1, '2019-08-12 01:00:00.013'),
(7, '2019-08-12 01:10:00.013'),
(0, '2019-08-12 01:20:00.013'),
(1, '2019-08-12 01:30:00.013'),
(2, '2019-08-12 01:40:00.013'),
(0, '2019-08-12 01:50:00.013'),
(2, '2019-08-12 02:00:00.013'),
(0, '2019-08-12 02:10:00.013'),
(0, '2019-08-12 02:20:00.013'),
(0, '2019-08-12 02:30:00.013'),
(1, '2019-08-12 02:40:00.013'),
(0, '2019-08-12 02:50:00.013'),
(4, '2019-08-12 03:00:00.013'),
(3, '2019-08-12 03:20:00.013'),
(1, '2019-08-12 03:30:00.013');

一种方法是：

• 计算从固定日期开始的分钟数（没有时间分量）
• 每次增加 30 分钟以将半小时时段与小时对齐
• 四舍五入到小时（使用整数数学除以分钟，然后乘以 60）
• 减去 30 分钟
• 将分钟添加到相同的固定日期

这显示在下面的代码中：

SELECT
    TS = CONVERT(char(19), Timeslot.TS, 120),
    Val = SUM(D.Val)
FROM #Demo AS D
CROSS JOIN (VALUES(CONVERT(datetime2(3), '20190101', 112))) AS Base(FixedDate)
CROSS APPLY(VALUES((DATEDIFF(MINUTE, Base.FixedDate, D.TS) + 30) / 60 * 60)) AS Slot(InMins)
CROSS APPLY(VALUES(DATEADD(MINUTE, Slot.InMins - 30, Base.FixedDate))) AS Timeslot(TS)
GROUP BY
    Timeslot.TS
ORDER BY
    Timeslot.TS;

输出：

╔═════════════════════╦═════╗
║         TS          ║ Val ║
╠═════════════════════╬═════╣
║ 2019-08-11 23:30:00 ║   6 ║
║ 2019-08-12 00:30:00 ║  10 ║
║ 2019-08-12 01:30:00 ║   5 ║
║ 2019-08-12 02:30:00 ║   8 ║
║ 2019-08-12 03:30:00 ║   1 ║
╚═════════════════════╩═════╝

db<>小提琴演示

与问题中显示的输出相比，这会产生几个额外的时间段，但它对我来说仍然是正确的。

如果您更喜欢（有点慢）FORMAT功能，则以下是等效的：

SELECT
    TS = Slot.TS,
    Val = SUM(D.Val)
FROM #Demo AS D
CROSS APPLY
(
    VALUES
    (
        -- Remove 30 minutes
        DATEADD(MINUTE, -30, 
            CONVERT(datetime2(3),
                FORMAT(
                    -- Add 30 minutes
                    DATEADD(MINUTE, 30, D.TS), 
                    -- Truncate to hour
                    N'yyyy-MM-dd HH:00:00', 
                    N'en-us'
                ), 120
            )
        )
    )
) AS Slot (TS)
GROUP BY
    Slot.TS
ORDER BY
    Slot.TS;