在SQL Workbench中基于轮班模式生成结果列的可行性咨询

我所在的卡车零部件生产工厂采用A、B、C、D四班制，轮班规则如下：

• 每个轮班先执行2周白班（7:00-19:00），再执行2周夜班（19:00-次日7:00）
• 整个轮班模式每28天循环一次，自2019年12月起持续运行

2019年12月的轮班示例如下：

生产数据存储在AWS Amazon Redshift数据库中，通过SQL Workbench访问，操作表为RS_MACHINESTATUS，其中RUN_TIMESTAMP列格式为'13-May-2019 23:09:30'。需要新增一列SHIFT，根据RUN_TIMESTAMP的时间值匹配对应的轮班次，预期结果示例：

可以通过计算RUN_TIMESTAMP相对于轮班起始基准日的偏移天数，结合轮班循环规则和时段（白班/夜班）来匹配班次，以下是适配Redshift的SQL实现：

核心逻辑 #

1. 基准日与循环偏移：以2019年12月2日（轮班模式启用后的第一个周一）为基准日，计算目标日期与基准日的间隔天数，再对28取模锁定到28天循环内的位置。
2. 时段区分：
   • 7:00-19:00为白班时段
   • 19:00-次日7:00为夜班时段，需将该时段的日期映射到前一天，确保匹配正确的夜班班次

完整SQL代码 #

SELECT
    RUN_TIMESTAMP,
    CASE
        -- 处理19:00-23:59的夜班时段，日期减1后计算偏移
        WHEN EXTRACT(HOUR FROM RUN_TIMESTAMP) BETWEEN 19 AND 23 THEN
            CASE MOD(DATEDIFF(day, '2019-12-02', DATEADD(day, -1, RUN_TIMESTAMP)), 28)
                WHEN 0 THEN 'C' WHEN 1 THEN 'C' WHEN 2 THEN 'B' WHEN 3 THEN 'B'
                WHEN 4 THEN 'C' WHEN 5 THEN 'C' WHEN 6 THEN 'C' WHEN 7 THEN 'D'
                WHEN 8 THEN 'D' WHEN 9 THEN 'C' WHEN 10 THEN 'C' WHEN 11 THEN 'D'
                WHEN 12 THEN 'D' WHEN 13 THEN 'D' WHEN 14 THEN 'A' WHEN 15 THEN 'A'
                WHEN 16 THEN 'D' WHEN 17 THEN 'D' WHEN 18 THEN 'A' WHEN 19 THEN 'A'
                WHEN 20 THEN 'A' WHEN 21 THEN 'B' WHEN 22 THEN 'B' WHEN 23 THEN 'A'
                WHEN 24 THEN 'A' WHEN 25 THEN 'B' WHEN 26 THEN 'B' WHEN 27 THEN 'B'
            END
        -- 处理00:00-06:59的夜班时段，同样映射到前一天
        WHEN EXTRACT(HOUR FROM RUN_TIMESTAMP) BETWEEN 0 AND 6 THEN
            CASE MOD(DATEDIFF(day, '2019-12-02', DATEADD(day, -1, RUN_TIMESTAMP)), 28)
                WHEN 0 THEN 'C' WHEN 1 THEN 'C' WHEN 2 THEN 'B' WHEN 3 THEN 'B'
                WHEN 4 THEN 'C' WHEN 5 THEN 'C' WHEN 6 THEN 'C' WHEN 7 THEN 'D'
                WHEN 8 THEN 'D' WHEN 9 THEN 'C' WHEN 10 THEN 'C' WHEN 11 THEN 'D'
                WHEN 12 THEN 'D' WHEN 13 THEN 'D' WHEN 14 THEN 'A' WHEN 15 THEN 'A'
                WHEN 16 THEN 'D' WHEN 17 THEN 'D' WHEN 18 THEN 'A' WHEN 19 THEN 'A'
                WHEN 20 THEN 'A' WHEN 21 THEN 'B' WHEN 22 THEN 'B' WHEN 23 THEN 'A'
                WHEN 24 THEN 'A' WHEN 25 THEN 'B' WHEN 26 THEN 'B' WHEN 27 THEN 'B'
            END
        -- 处理7:00-18:59的白班时段
        ELSE
            CASE MOD(DATEDIFF(day, '2019-12-02', RUN_TIMESTAMP), 28)
                WHEN 0 THEN 'A' WHEN 1 THEN 'A' WHEN 2 THEN 'D' WHEN 3 THEN 'D'
                WHEN 4 THEN 'A' WHEN 5 THEN 'A' WHEN 6 THEN 'A' WHEN 7 THEN 'B'
                WHEN 8 THEN 'B' WHEN 9 THEN 'A' WHEN 10 THEN 'A' WHEN 11 THEN 'B'
                WHEN 12 THEN 'B' WHEN 13 THEN 'B' WHEN 14 THEN 'C' WHEN 15 THEN 'C'
                WHEN 16 THEN 'B' WHEN 17 THEN 'B' WHEN 18 THEN 'C' WHEN 19 THEN 'C'
                WHEN 20 THEN 'C' WHEN 21 THEN 'D' WHEN 22 THEN 'D' WHEN 23 THEN 'C'
                WHEN 24 THEN 'C' WHEN 25 THEN 'D' WHEN 26 THEN 'D' WHEN 27 THEN 'D'
            END
    END AS SHIFT
FROM RS_MACHINESTATUS;

说明 #

• 所有CASE分支严格对应2019年12月的轮班示例，28天循环后逻辑自动复用
• Redshift原生支持DATEDIFF、DATEADD、EXTRACT等函数，可直接运行该语句

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