如何修正SQL查询以计算作业有效工作日的累计求和？

原始数据表 #

现有表Data记录员工每日作业情况，包含字段JobID、EmployeeID、Date，数据如下：

注：

• 一个作业可跨多天执行
• 多员工可同日处理同一作业（该情况仅计1天）
• 员工可同日处理多个作业

统计需求与期望输出 #

需按年统计作业有效工作日，并计算累计求和，期望输出如下：

统计规则：

• 作业abc：2010年1天（两名员工同日作业仅算1天）
• 作业xyz：2010年1天、2011年1天，合计2天
• 作业def：2011年1天
• 累计到2011年总天数为4天

当前错误SQL及输出 #

编写的SQL查询如下：

WITH t1 as (
 SELECT
  extract(year from Date) AS Year, JobId, count(distinct Date) AS DaysWorked
 FROM Data
 GROUP BY 1, 2
)
SELECT Year, DaysWorked, sum(DaysWorked) over (order by Year) AS CumulativeDays
FROM t1

当前输出结果不符合预期：

修正后的SQL及说明 #

修正代码 #

WITH unique_job_days AS (
    -- 去重同一作业的同一天记录（多员工处理不重复计数）
    SELECT DISTINCT JobID, Date FROM Data
),
yearly_work_days AS (
    -- 按年统计当年的有效工作日总数
    SELECT
        EXTRACT(YEAR FROM Date) AS Year,
        COUNT(*) AS DaysWorked  -- 已去重，直接计数即可
    FROM unique_job_days
    GROUP BY Year
)
-- 计算年度累计天数
SELECT
    Year,
    DaysWorked,
    SUM(DaysWorked) OVER (ORDER BY Year) AS CumulativeDays
FROM yearly_work_days
ORDER BY Year;

错误原因说明 #

原SQL的问题在于：

1. 先按Year+JobID分组，得到的是每个作业每年的天数，而非每年的总天数
2. 后续直接对作业级别的天数做累计，导致累计逻辑错误（把所有作业的天数一次性累加，而非按年度汇总后再累计）

修正逻辑：

• 先通过DISTINCT去除同一作业同一天的重复记录，确保每个作业每天只计1次
• 按年汇总去重后的记录，得到当年的总有效工作日数
• 基于年度汇总结果计算累计值，即可得到正确的逐年累计天数

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