如何在数据库辅助日历表中生成flag列标记每月前3后2工作日

核心思路 #

通过窗口函数对每月的工作日进行正序、倒序编号，再根据编号范围标记对应的flag值：

• 非工作日直接标记为0
• 当月前3个工作日依次标记1、2、3
• 当月最后2个工作日依次标记4、5
• 其余工作日标记为0

通用SQL实现（支持窗口函数的数据库：PostgreSQL、MySQL 8+、SQL Server等） #

基础查询（生成flag列）

SELECT
    day,
    is_working_day,
    CASE
        WHEN is_working_day = 0 THEN 0
        WHEN workday_seq <= 3 THEN workday_seq
        WHEN reverse_workday_seq <= 2 THEN 3 + reverse_workday_seq
        ELSE 0
    END AS flag
FROM (
    SELECT
        day,
        is_working_day,
        -- 按月分组，对工作日正序编号
        ROW_NUMBER() OVER (
            PARTITION BY DATE_TRUNC('month', day)
            ORDER BY day
        ) FILTER (WHERE is_working_day = 1) AS workday_seq,
        -- 按月分组，对工作日倒序编号
        ROW_NUMBER() OVER (
            PARTITION BY DATE_TRUNC('month', day)
            ORDER BY day DESC
        ) FILTER (WHERE is_working_day = 1) AS reverse_workday_seq
    FROM your_table_name -- 替换为你的表名
) AS subquery;

不同数据库的语法适配

• MySQL 8+：不支持FILTER子句，需用CASE处理非工作日的编号：

SELECT
    day,
    is_working_day,
    CASE
        WHEN is_working_day = 0 THEN 0
        WHEN workday_seq <= 3 THEN workday_seq
        WHEN reverse_workday_seq <= 2 THEN 3 + reverse_workday_seq
        ELSE 0
    END AS flag
FROM (
    SELECT
        day,
        is_working_day,
        ROW_NUMBER() OVER (
            PARTITION BY DATE_FORMAT(day, '%Y-%m')
            ORDER BY day
        ) * CASE WHEN is_working_day = 1 THEN 1 ELSE NULL END AS workday_seq,
        ROW_NUMBER() OVER (
            PARTITION BY DATE_FORMAT(day, '%Y-%m')
            ORDER BY day DESC
        ) * CASE WHEN is_working_day = 1 THEN 1 ELSE NULL END AS reverse_workday_seq
    FROM your_table_name -- 替换为你的表名
) AS subquery;

• SQL Server：替换日期分组函数为DATEFROMPARTS(YEAR(day), MONTH(day), 1)：

SELECT
    COALESCE(subquery.day, t.day) AS day,
    t.is_working_day,
    CASE
        WHEN t.is_working_day = 0 THEN 0
        WHEN subquery.workday_seq <= 3 THEN subquery.workday_seq
        WHEN subquery.reverse_workday_seq <= 2 THEN 3 + subquery.reverse_workday_seq
        ELSE 0
    END AS flag
FROM your_table_name t
LEFT JOIN (
    SELECT
        day,
        ROW_NUMBER() OVER (
            PARTITION BY DATEFROMPARTS(YEAR(day), MONTH(day), 1)
            ORDER BY day
        ) AS workday_seq,
        ROW_NUMBER() OVER (
            PARTITION BY DATEFROMPARTS(YEAR(day), MONTH(day), 1)
            ORDER BY day DESC
        ) AS reverse_workday_seq
    FROM your_table_name
    WHERE is_working_day = 1
) subquery ON t.day = subquery.day
ORDER BY t.day;

更新表中已有的flag列 #

如果需要直接更新表内的flag列，以PostgreSQL为例：

UPDATE your_table_name
SET flag = CASE
    WHEN is_working_day = 0 THEN 0
    WHEN workday_seq <= 3 THEN workday_seq
    WHEN reverse_workday_seq <= 2 THEN 3 + reverse_workday_seq
    ELSE 0
END
FROM (
    SELECT
        day,
        ROW_NUMBER() OVER (
            PARTITION BY DATE_TRUNC('month', day)
            ORDER BY day
        ) FILTER (WHERE is_working_day = 1) AS workday_seq,
        ROW_NUMBER() OVER (
            PARTITION BY DATE_TRUNC('month', day)
            ORDER BY day DESC
        ) FILTER (WHERE is_working_day = 1) AS reverse_workday_seq
    FROM your_table_name
) AS subquery
WHERE your_table_name.day = subquery.day;

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