Amazon Redshift：统计员工连续上班周数的SQL实现求助

假设表结构 #

先明确你的数据表（假设名为employee_work_weeks）包含以下核心字段：

• employee_name：员工姓名
• work_week：上班周次（建议用数字格式，比如1-52代表全年周数，或YYYYWW格式如202401）

实现步骤 #

通过窗口函数和分组统计实现，Redshift完全支持以下语法：

1. 给每个员工的上班周生成顺序编号
   按员工分组，对上班周排序后生成连续行号，用于后续识别连续周区间：

WITH ranked_weeks AS (
    SELECT
        employee_name,
        work_week,
        ROW_NUMBER() OVER (PARTITION BY employee_name ORDER BY work_week) AS rn
    FROM employee_work_weeks
)

2. 识别连续上班的周区间
   用work_week减去行号rn，同一个连续周区间的结果值固定（比如连续周1、2、3，rn为1、2、3，计算后均为0，属于同一组）：

, continuous_groups AS (
    SELECT
        employee_name,
        work_week,
        work_week - rn AS group_id
    FROM ranked_weeks
)

3. 统计每个连续区间的周数
   按员工和区间标识分组，计算每个区间的周数，即连续上班时长：

, continuous_durations AS (
    SELECT
        employee_name,
        COUNT(*) AS consecutive_weeks
    FROM continuous_groups
    GROUP BY employee_name, group_id
)

4. 最终统计连续周数的出现次数
   按员工和连续周数分组，统计每种连续时长的出现次数：

SELECT
    employee_name,
    consecutive_weeks,
    COUNT(*) AS occurrence_count
FROM continuous_durations
GROUP BY employee_name, consecutive_weeks
ORDER BY employee_name, consecutive_weeks;

示例结果 #

如果Andy有2次连续2周上班、1次连续3周上班，结果如下：

注意事项 #

• 如果work_week是字符串格式（比如'2024-W01'），需先转换为数字类型再计算，例如用TO_NUMBER(REPLACE(work_week, '2024-W', ''), '99')提取周数数字。
• 确保work_week的排序逻辑符合业务需求（比如按自然周顺序）。

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