PostgreSQL统计员工连续上班周数的频次问题

场景：有一张记录全年每周员工（如Jon、Andy）是否上班的表格，需要统计每位员工连续上班x周的出现次数（示例：Andy有2次连续2周上班的情况）。已知用Python循环可实现，但不清楚PostgreSQL的纯SQL实现方式。

核心思路 #

利用PostgreSQL的窗口函数识别连续的上班周分组，再通过两次分组统计得到最终结果，完全不需要循环。

假设表结构 #

先假设你的表名为employee_work_weeks，字段如下：

• employee_name：员工姓名（varchar类型）
• week_number：周数（int类型，1到52）
• is_working：是否上班（boolean类型，true表示上班）

完整SQL代码 #

-- 第一步：过滤上班记录，标记连续周的分组
WITH working_weeks AS (
    SELECT
        employee_name,
        week_number,
        -- 关键：同一连续周分组的计算值相同
        week_number - ROW_NUMBER() OVER (
            PARTITION BY employee_name 
            ORDER BY week_number
        ) AS consecutive_group
    FROM employee_work_weeks
    WHERE is_working = true -- 只保留上班的周
),
-- 第二步：统计每个分组的连续周数
consecutive_counts AS (
    SELECT
        employee_name,
        consecutive_group,
        COUNT(*) AS consecutive_weeks -- 该分组的连续上班周数
    FROM working_weeks
    GROUP BY employee_name, consecutive_group
)
-- 第三步：按员工和连续周数统计出现次数
SELECT
    employee_name,
    consecutive_weeks,
    COUNT(*) AS occurrence_count -- 连续x周的出现次数
FROM consecutive_counts
GROUP BY employee_name, consecutive_weeks
ORDER BY employee_name, consecutive_weeks;

代码解释 #

1. working_weeks CTE：

   • 过滤出所有员工上班的周记录
   • 用week_number - ROW_NUMBER()生成分组标识：如果周数连续，这个计算值会保持不变（比如周1、2、3的row_number是1、2、3，1-1=0，2-2=0，3-3=0，属于同一组；如果中间断了一周，周5的row_number是4，5-4=1，成为新组）

2. consecutive_counts CTE：

   • 按员工和分组标识分组，统计每个分组的周数，得到该员工每一段连续上班的时长

3. 最终查询：

   • 按员工和连续时长分组，统计相同时长的出现次数，得到想要的结果

示例验证 #

假设Andy的上班记录为：

执行SQL后，Andy的结果会是：

完全符合示例中“2次连续2周上班”的需求。

扩展说明 #

• 如果你的表中用日期而非周数，只需把week_number替换为DATE_TRUNC('week', work_date)（按周截断日期），逻辑完全一致
• 该方法效率远高于Python循环，适合处理全年甚至多年的大量数据

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