Teradata无通用分组ID时按连续日期分组汇总在职天数

可以实现，核心思路是通过窗口函数生成连续在职段的分组标识，以此替代job_id完成分组统计，遇到on_job_ind = 'N'时自动中断分组。以下是具体实现步骤和代码示例：

假设你的数据结构如下（可根据实际表结构调整） #

实现SQL #

WITH ranked_records AS (
    SELECT
        employee_id,
        record_date,
        on_job_ind,
        -- 标记每个连续在职段的起始行：当前为Y，且上一行不是Y（或为第一条记录）
        CASE
            WHEN on_job_ind = 'Y'
                 AND (LAG(on_job_ind) OVER (PARTITION BY employee_id ORDER BY record_date) != 'Y'
                      OR LAG(on_job_ind) OVER (PARTITION BY employee_id ORDER BY record_date) IS NULL)
            THEN 1
            ELSE 0
        END AS group_flag
    FROM your_table_name
),
grouped_records AS (
    SELECT
        employee_id,
        record_date,
        on_job_ind,
        -- 累加起始标记，生成每个连续在职段的唯一分组ID
        SUM(group_flag) OVER (PARTITION BY employee_id ORDER BY record_date) AS continuous_group_id
    FROM ranked_records
)
-- 按员工+分组ID统计连续在职天数
SELECT
    employee_id,
    continuous_group_id,
    MIN(record_date) AS period_start,
    MAX(record_date) AS period_end,
    COUNT(*) AS continuous_onjob_days
FROM grouped_records
WHERE on_job_ind = 'Y'
GROUP BY employee_id, continuous_group_id
ORDER BY employee_id, period_start;

代码说明 #

1. ranked_records CTE：

   • 用LAG()函数获取当前员工上一条记录的on_job_ind
   • 当当前记录为Y且上一条不是Y（或无前置记录）时，标记为分组起始（group_flag=1）

2. grouped_records CTE：

   • 对每个员工的group_flag进行累加，得到continuous_group_id——同一个连续在职段的所有记录会拥有相同的ID，遇到N时后续的Y会生成新的ID

3. 最终统计：

   • 过滤on_job_ind='Y'的记录，按employee_id和continuous_group_id分组，计算每个连续段的起止日期和天数

如果你的数据不是每日一条（比如单条记录代表一段在职周期），只需调整日期计算逻辑（例如用DATEDIFF(MAX(end_date), MIN(start_date)) + 1替代COUNT(*)），核心分组逻辑不变。

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