SQL多行合并单行逻辑异常：间断title场景结果不符咨询

Got it, let's tackle this problem head-on. The core issue with your original SQL is that it groups all identical titles together (even when they’re interrupted by other roles) instead of only grouping consecutive identical titles. That’s why your second Wp row got incorrectly merged with the W WP row—their generated seq values matched, even though they belong to separate, non-consecutive employment periods.

The Correct Approach: Grouping Consecutive Title Sequences #

We need to create a grouping key that only clusters rows where the title is the same as the immediately preceding row for the same id. Here's the revised SQL to fix this:

WITH consecutive_groups AS (
    SELECT
        id,
        title,
        hired_reason,
        fired_reason,
        hired_date,
        fired_date,
        -- Flag when the title changes from the previous row for the same id
        CASE 
            WHEN title = LAG(title) OVER (PARTITION BY id ORDER BY hired_date) 
            THEN 0 
            ELSE 1 
        END AS title_change_flag,
        -- Generate a unique group ID for each consecutive title block
        SUM(CASE 
            WHEN title = LAG(title) OVER (PARTITION BY id ORDER BY hired_date) 
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY id ORDER BY hired_date) AS group_id
    FROM your_table
),
final_results AS (
    SELECT
        id,
        title,
        -- Keep the hired reason from the first row of the consecutive group
        FIRST_VALUE(hired_reason) OVER (PARTITION BY id, group_id ORDER BY hired_date) AS hired_reason,
        -- Keep the fired reason from the last row of the consecutive group
        LAST_VALUE(fired_reason) OVER (PARTITION BY id, group_id ORDER BY hired_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS fired_reason,
        MIN(hired_date) AS hired_date,
        MAX(fired_date) AS fired_date
    FROM consecutive_groups
    GROUP BY id, group_id, title
)
SELECT id, title, hired_reason, fired_reason, hired_date, fired_date
FROM final_results
ORDER BY id, hired_date;

Breakdown of How This Works #

Let’s walk through the key components:

1. LAG(title): This pulls the title from the previous row for the same id, ordered by hired_date. We use this to check if the current row’s title matches the last one.
2. title_change_flag: Marks a 1 whenever the title changes from the prior row, and 0 when it stays the same.
3. group_id: A running total of the title_change_flag for each id. This creates a unique ID for every consecutive block of the same title—so even if the same title reappears later (after other roles), it gets a new, distinct group_id.
4. Grouping & Aggregating: We group by id and group_id (instead of the old seq), then use MIN(hired_date) and MAX(fired_date) to capture the start and end of the consecutive period. FIRST_VALUE and LAST_VALUE ensure we retain the correct reason values from the first and last rows of each group.

Testing With Your Sample Data #

For your input, the consecutive_groups CTE will generate these group_id values:

When grouped, this produces exactly the output you expect—no merging of discontinuous Wp rows, and each consecutive title block remains separate.

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