PostgreSQL日期表迭代与每月工作日累计计数视图创建求助

嘿，这两个需求其实都不用存储过程就能搞定，PostgreSQL的日期函数和窗口函数完全能胜任，我给你分步拆解：

1. 计算每个月的总工作日数 #

核心思路是先生成目标日期范围，筛选出周一到周五的日期（排除周末），再按月份分组统计数量。如果需要考虑节假日，还可以结合自定义节假日表扩展。

基础版本（仅排除周末） #

-- 统计2024年每个月的工作日数量
SELECT
    DATE_TRUNC('month', date_col) AS month_start,
    COUNT(*) AS total_workdays
FROM
    GENERATE_SERIES('2024-01-01'::DATE, '2024-12-31'::DATE, '1 day'::INTERVAL) AS date_col
WHERE
    -- 排除周日（0）和周六（6）
    EXTRACT(DOW FROM date_col) NOT IN (0, 6)
GROUP BY
    DATE_TRUNC('month', date_col)
ORDER BY
    month_start;

扩展版本（排除周末+自定义节假日） #

假设你有一张holidays表，包含holiday_date字段存储节假日日期：

SELECT
    DATE_TRUNC('month', d.date_col) AS month_start,
    COUNT(*) AS total_workdays
FROM
    GENERATE_SERIES('2024-01-01'::DATE, '2024-12-31'::DATE, '1 day'::INTERVAL) AS d(date_col)
LEFT JOIN
    holidays h ON d.date_col = h.holiday_date
WHERE
    EXTRACT(DOW FROM d.date_col) NOT IN (0, 6)
    -- 排除存在于节假日表的日期
    AND h.holiday_date IS NULL
GROUP BY
    DATE_TRUNC('month', d.date_col)
ORDER BY
    month_start;

2. 创建带每月重置工作日累计的视图 #

这个需求用窗口函数SUM()就能实现，核心是按月份分区，对工作日标记为1、非工作日标记为0，然后累计求和——这样非工作日时累计值不会递增，且每个月自动重置计数。

基础视图（仅排除周末） #

CREATE OR REPLACE VIEW monthly_workday_counter AS
SELECT
    date_col AS current_date,
    -- 标记当前日期是否为工作日
    CASE
        WHEN EXTRACT(DOW FROM date_col) NOT IN (0, 6) THEN 1
        ELSE 0
    END AS is_workday,
    -- 按月份分区，累计工作日计数
    SUM(CASE
        WHEN EXTRACT(DOW FROM date_col) NOT IN (0, 6) THEN 1
        ELSE 0
    END) OVER (
        PARTITION BY DATE_TRUNC('month', date_col)
        ORDER BY date_col
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS monthly_workday_accumulator
FROM
    GENERATE_SERIES('2024-01-01'::DATE, '2024-12-31'::DATE, '1 day'::INTERVAL) AS date_col;

扩展视图（排除周末+自定义节假日） #

CREATE OR REPLACE VIEW monthly_workday_counter_with_holidays AS
WITH date_range AS (
    -- 生成目标日期范围
    SELECT GENERATE_SERIES('2024-01-01'::DATE, '2024-12-31'::DATE, '1 day'::INTERVAL) AS date_col
),
workday_flags AS (
    -- 标记每个日期是否为工作日（排除周末和节假日）
    SELECT
        d.date_col,
        CASE
            WHEN EXTRACT(DOW FROM d.date_col) NOT IN (0, 6)
                 AND NOT EXISTS (SELECT 1 FROM holidays h WHERE h.holiday_date = d.date_col)
            THEN 1
            ELSE 0
        END AS is_workday
    FROM date_range d
)
SELECT
    date_col AS current_date,
    is_workday,
    -- 按月份分区累计工作日计数
    SUM(is_workday) OVER (
        PARTITION BY DATE_TRUNC('month', date_col)
        ORDER BY date_col
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS monthly_workday_accumulator
FROM workday_flags;

关键说明 #

• PARTITION BY DATE_TRUNC('month', date_col)：确保每个月的累计计数独立，到下一个月自动重置为0开始累计。
• ORDER BY date_col：保证日期按顺序排列，累计结果符合时间逻辑。
• SUM(is_workday)：只有当is_workday为1（工作日）时，累计值才会递增，非工作日时保持上一个累计值不变。

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