如何使用纯SQL合并连续相同GR分组的记录并保留时间顺序

Your core issue here is that your original query groups all records with the same GR together, regardless of whether they're interrupted by other GR values. What you need instead is to group consecutive sequences of the same GR—this is a classic "gap and island" problem in SQL.

Here's a pure SQL solution that works across most modern databases (PostgreSQL, MySQL 8.0+, SQL Server, Oracle, etc.):

WITH ranked_records AS (
    SELECT
        GR,
        I,
        VON,
        BIS,
        -- Mark when the GR changes from the previous row
        CASE 
            WHEN GR = LAG(GR) OVER (ORDER BY VON) THEN 0
            ELSE 1
        END AS grp_change
    FROM table_test
),
blocked_records AS (
    SELECT
        GR,
        I,
        VON,
        BIS,
        -- Create a unique ID for each consecutive GR block
        SUM(grp_change) OVER (ORDER BY VON ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS block_id
    FROM ranked_records
)
SELECT
    GR,
    FIRST_VALUE(I) OVER (PARTITION BY block_id ORDER BY VON) AS I,
    MIN(VON) AS VON,
    MAX(BIS) AS BIS
FROM blocked_records
GROUP BY block_id, GR
ORDER BY VON;

How this works: #

1. ranked_records CTE:

   • Uses LAG(GR) OVER (ORDER BY VON) to fetch the GR value from the immediately preceding record (ordered by your start date VON).
   • The grp_change flag is set to 1 whenever the current row's GR is different from the previous row, marking the start of a new consecutive block.

2. blocked_records CTE:

   • Uses a running total (SUM(grp_change) OVER (...)) to assign a unique block_id to each consecutive sequence of the same GR. Every time grp_change is 1, the total increments, creating a new block.

3. Final SELECT:

   • Groups by block_id (each representing a consecutive GR sequence) and GR.
   • Uses FIRST_VALUE(I) to get the I value from the first record in the block (matches your expected output where c is kept for the merged GR=1 block).
   • Takes the minimum VON and maximum BIS to merge the time interval for the block.

Testing with your sample data: #

Running this query on your table_test will produce exactly the expected result:

Why your original query failed: #

Your original query used PARTITION BY GR, which grouped all GR=1 records together (even the ones separated by GR=2). This is why it merged the first, third, and fourth rows into a single block, which wasn't what you wanted. The gap-and-island approach fixes this by only grouping consecutive same-GR records.

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