Microsoft SQL Server补全20小时时间分段缺失起始行的方案咨询

需求说明 #

• 设定时间范围：当前时间为结束时间@End_Date，回溯20小时为起始时间@Start_Date
• 对每个分组（Group），需要额外补一条记录：起始时间为@Start_Date，结束时间为该分组现有分段的最早起始时间
• 现有分段数据中如果End_Date为NULL，用@End_Date替换
• 使用UNION类语句实现逻辑，基于Microsoft SQL Server

表结构与测试数据 #

首先创建测试表并插入数据（修正原语句中的表名不一致问题）：

CREATE TABLE tabName
(
    Start_Date datetime,
    End_Date datetime,
    [Group] int
)

INSERT INTO tabName (Start_Date, End_Date, [Group]) VALUES ('2023-01-01 13:00:00', '2023-01-01 20:00:00', 1) 
INSERT INTO tabName (Start_Date, End_Date, [Group]) VALUES ('2023-01-01 12:00:00', '2023-01-01 13:00:00', 1) 
INSERT INTO tabName (Start_Date, End_Date, [Group]) VALUES ('2023-01-01 10:00:00', '2023-01-01 12:00:00', 1) 
INSERT INTO tabName (Start_Date, End_Date, [Group]) VALUES ('2023-01-01 09:00:00', '2023-01-01 10:00:00', 1) 
INSERT INTO tabName (Start_Date, End_Date, [Group]) VALUES ('2023-01-01 06:00:00', '2023-01-01 09:00:00', 1) 
INSERT INTO tabName (Start_Date, End_Date, [Group]) VALUES ('2023-01-01 02:00:00', '2023-01-01 06:00:00', 1) 
INSERT INTO tabName (Start_Date, End_Date, [Group]) VALUES ('2023-01-01 18:00:00', '2023-01-01 20:00:00', 2) 
INSERT INTO tabName (Start_Date, End_Date, [Group]) VALUES ('2023-01-01 16:00:00', '2023-01-01 18:00:00', 2) 
INSERT INTO tabName (Start_Date, End_Date, [Group]) VALUES ('2023-01-01 14:00:00', '2023-01-01 16:00:00', 2) 
INSERT INTO tabName (Start_Date, End_Date, [Group]) VALUES ('2023-01-01 05:00:00', '2023-01-01 14:00:00', 2) 
INSERT INTO tabName (Start_Date, End_Date, [Group]) VALUES ('2023-01-01 04:00:00', '2023-01-01 05:00:00', 2) 
INSERT INTO tabName (Start_Date, End_Date, [Group]) VALUES ('2023-01-01 01:00:00', '2023-01-01 04:00:00', 2) 

实现SQL语句 #

DECLARE @End_Date datetime = GETDATE();
DECLARE @Start_Date datetime = DATEADD(Hour, -20, @End_Date);

-- 处理现有数据，替换NULL的End_Date
SELECT 
    Start_Date,
    CASE WHEN End_Date IS NULL THEN @End_Date ELSE End_Date END AS End_Date,
    [Group]
FROM tabName
UNION ALL
-- 为每个Group补全起始段数据
SELECT 
    @Start_Date AS Start_Date,
    MIN(Start_Date) AS End_Date,
    [Group]
FROM tabName
GROUP BY [Group]
-- 按分组和起始时间排序，保证分段连续性
ORDER BY [Group], Start_Date;

逻辑说明 #

1. 变量定义：先设定@End_Date为当前时间，@Start_Date为当前时间往前推20小时
2. 现有数据处理：用CASE语句把End_Date为NULL的记录替换成@End_Date
3. 补全记录生成：通过GROUP BY按Group分组，获取每个分组的最早Start_Date，和@Start_Date组合成新的分段记录
4. 合并与排序：用UNION ALL合并两部分数据（比UNION高效，无需去重），最后按分组和起始时间排序，方便查看完整分段

内容的提问来源于stack exchange，提问作者LukaszP