如何在Postgres中实现工作日累计计数及有效天数统计？

先明确下你的核心需求：

• working_days：从统计周期起始日到当前日期的累计工作日数（仅周一至周五，不含周末；如需排除法定节假日可额外调整）
• productive_days：从统计周期起始日到当前日期，当天有通话记录（Total_calls>0）的工作日累计数
• 覆盖周期内所有日期，包括无通话记录的日期，且非工作日的working_days和productive_days显示为空

假设你的原始数据表结构 #

先假设你的通话记录表名为call_records，字段定义如下：

CREATE TABLE call_records (
    date DATE,
    owner_name VARCHAR(100),
    total_calls INT
);

完整SQL实现 #

下面的SQL会生成和你示例格式完全匹配的结果，我会分步解释关键逻辑：

WITH date_range AS (
    -- 1. 生成统计周期内的所有日期（这里以你示例的2019-11-01至2019-11-29为例）
    SELECT generate_series('2019-11-01'::DATE, '2019-11-29'::DATE, '1 day'::INTERVAL)::DATE AS date
),
owner_daily_data AS (
    -- 2. 关联所有日期与每个员工，确保每个员工每天都有记录（无通话则total_calls设为0）
    SELECT 
        dr.date,
        owners.owner_name,
        COALESCE(cr.total_calls, 0) AS total_calls
    FROM date_range dr
    CROSS JOIN (SELECT DISTINCT owner_name FROM call_records) owners
    LEFT JOIN call_records cr ON dr.date = cr.date AND owners.owner_name = cr.owner_name
),
daily_flags AS (
    -- 3. 标记当天是否为工作日、是否为有效工作日（工作日+有通话）
    SELECT 
        date,
        owner_name,
        total_calls,
        -- 工作日判断：ISO星期中1=周一，5=周五，7=周日
        CASE WHEN EXTRACT(isodow FROM date) BETWEEN 1 AND 5 THEN 1 ELSE 0 END AS is_working_day,
        -- 有效工作日：是工作日且当天有通话记录
        CASE WHEN EXTRACT(isodow FROM date) BETWEEN 1 AND 5 AND total_calls > 0 THEN 1 ELSE 0 END AS is_productive_day
    FROM owner_daily_data
)
-- 4. 计算累计值并格式化输出
SELECT 
    TO_CHAR(date, 'MM/DD/YYYY') AS date,
    owner_name,
    -- 仅工作日显示累计工作日数，否则为空
    CASE WHEN is_working_day = 1 THEN SUM(is_working_day) OVER (PARTITION BY owner_name ORDER BY date) ELSE NULL END AS working_days,
    -- 仅有效工作日显示累计有效天数，否则为空
    CASE WHEN is_productive_day = 1 THEN SUM(is_productive_day) OVER (PARTITION BY owner_name ORDER BY date) ELSE NULL END AS productive_days,
    total_calls
FROM daily_flags
ORDER BY date DESC;

关键逻辑说明 #

1. date_range CTE：用generate_series生成周期内的所有日期，避免遗漏任何一天（包括周末和无通话的工作日）。
2. owner_daily_data CTE：通过CROSS JOIN确保每个员工在周期内每天都有记录，左连接原始通话数据后用COALESCE把无通话日期的total_calls设为0。
3. daily_flags CTE：用EXTRACT(isodow FROM date)判断工作日（ISO星期的1-5对应周一到周五），同时标记有效工作日。
4. 窗口函数计算累计：SUM() OVER (PARTITION BY owner_name ORDER BY date)按员工分组、日期升序累计工作日和有效工作日数量，再通过CASE语句让非工作日的累计值显示为空，完全匹配你的示例格式。

扩展：排除法定节假日 #

如果需要排除公司法定节假日，可以新建一个节假日表：

CREATE TABLE company_holidays (holiday_date DATE PRIMARY KEY);
-- 插入节假日数据示例
INSERT INTO company_holidays VALUES ('2019-11-28');

然后修改daily_flags中的is_working_day判断：

CASE 
    WHEN EXTRACT(isodow FROM date) BETWEEN 1 AND 5 
         AND date NOT IN (SELECT holiday_date FROM company_holidays) 
    THEN 1 
    ELSE 0 
END AS is_working_day

这样就能准确排除节假日，计算真实的工作日累计数了。

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