MS SQL 2019夜班生产OK/NOK数量求和实现咨询

针对夜班（22:00至次日6:00）的统计需求，核心是将跨日期的夜班记录统一归到夜班开始日期（即22:00所在的自然日）下，以下是实现的SQL视图语句：

CREATE VIEW vw_Machine1_NightShift
AS
SELECT
    -- 生成夜班开始日期：22点及以后的记录用当天日期，6点前的记录用前一天日期
    CASE
        WHEN DATEPART(HOUR, [timestamp]) >= 22 THEN CAST([timestamp] AS DATE)
        WHEN DATEPART(HOUR, [timestamp]) < 6 THEN DATEADD(DAY, -1, CAST([timestamp] AS DATE))
    END AS NightShiftStartDate,
    SUM(OK) AS TotalOK,
    SUM(NOK) AS TotalNOK
FROM machine1
-- 筛选属于夜班的时间范围
WHERE DATEPART(HOUR, [timestamp]) >= 22 OR DATEPART(HOUR, [timestamp]) < 6
GROUP BY
    CASE
        WHEN DATEPART(HOUR, [timestamp]) >= 22 THEN CAST([timestamp] AS DATE)
        WHEN DATEPART(HOUR, [timestamp]) < 6 THEN DATEADD(DAY, -1, CAST([timestamp] AS DATE))
    END
ORDER BY NightShiftStartDate;

关键逻辑说明 #

• 日期统一规则：通过CASE表达式，将次日0:00-6:00的记录归属到前一天的夜班，确保整个夜班周期（22:00至次日6:00）的统计结果都以夜班开始的自然日作为标识。
• 时间范围筛选：用WHERE子句精准过滤出所有属于夜班时段的生产记录。
• 分组统计：按统一后的夜班开始日期分组，汇总OK和NOK的生产数量。

如果需要将早班、中班、夜班的统计整合到同一个视图中，可以参考以下扩展写法：

CREATE VIEW vw_Machine1_AllShifts
AS
SELECT
    CASE
        -- 早班：6:00-14:00，用当天日期作为班次日期
        WHEN DATEPART(HOUR, [timestamp]) >=6 AND DATEPART(HOUR, [timestamp]) <14 THEN CAST([timestamp] AS DATE)
        -- 中班：14:00-22:00，用当天日期作为班次日期
        WHEN DATEPART(HOUR, [timestamp]) >=14 AND DATEPART(HOUR, [timestamp]) <22 THEN CAST([timestamp] AS DATE)
        -- 夜班：22:00-次日6:00，用开始日期（22点所在日期）作为班次日期
        WHEN DATEPART(HOUR, [timestamp]) >=22 THEN CAST([timestamp] AS DATE)
        WHEN DATEPART(HOUR, [timestamp]) <6 THEN DATEADD(DAY, -1, CAST([timestamp] AS DATE))
    END AS ShiftDate,
    CASE
        WHEN DATEPART(HOUR, [timestamp]) >=6 AND DATEPART(HOUR, [timestamp]) <14 THEN '早班'
        WHEN DATEPART(HOUR, [timestamp]) >=14 AND DATEPART(HOUR, [timestamp]) <22 THEN '中班'
        ELSE '夜班'
    END AS ShiftName,
    SUM(OK) AS TotalOK,
    SUM(NOK) AS TotalNOK
FROM machine1
GROUP BY
    CASE
        WHEN DATEPART(HOUR, [timestamp]) >=6 AND DATEPART(HOUR, [timestamp]) <14 THEN CAST([timestamp] AS DATE)
        WHEN DATEPART(HOUR, [timestamp]) >=14 AND DATEPART(HOUR, [timestamp]) <22 THEN CAST([timestamp] AS DATE)
        WHEN DATEPART(HOUR, [timestamp]) >=22 THEN CAST([timestamp] AS DATE)
        WHEN DATEPART(HOUR, [timestamp]) <6 THEN DATEADD(DAY, -1, CAST([timestamp] AS DATE))
    END,
    CASE
        WHEN DATEPART(HOUR, [timestamp]) >=6 AND DATEPART(HOUR, [timestamp]) <14 THEN '早班'
        WHEN DATEPART(HOUR, [timestamp]) >=14 AND DATEPART(HOUR, [timestamp]) <22 THEN '中班'
        ELSE '夜班'
    END
ORDER BY ShiftDate, ShiftName;

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