SQL Server/ANSI SQL：分区内事件时间窗口重置的高效实现方案（替代递归CTE）

Hey there, I totally get why recursive CTEs are choking on your large dataset—they’re great for small hierarchical tasks but scale terribly when dealing with 100k+ rows of time-series data. Let’s break down optimized recursive and non-recursive approaches that’ll handle big data smoothly, plus key performance tweaks to make them sing.

Optimized Recursive CTE (Batch Processing) #

The biggest issue with most recursive CTEs for this task is they process rows one by one. Instead, we can rewrite it to jump directly to the next valid event per user, cutting recursion steps from O(n) to O(k) (where k is the number of kept events, usually way smaller than total rows).

Code Implementation #

WITH recursive_cte AS (
    -- Anchor: Grab the first event for each user (our initial window start)
    SELECT
        person_id,
        event_time,
        DATEADD(MINUTE, 3, event_time) AS window_end -- Track when the current window expires
    FROM (
        SELECT
            person_id,
            event_time,
            ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY event_time) AS rn
        FROM #res
    ) ranked
    WHERE rn = 1

    UNION ALL

    -- Recursive step: Jump to the first event that falls outside the current window
    SELECT
        t.person_id,
        t.event_time,
        DATEADD(MINUTE, 3, t.event_time) AS window_end
    FROM recursive_cte r
    CROSS APPLY (
        SELECT TOP 1 event_time
        FROM #res t
        WHERE t.person_id = r.person_id
          AND t.event_time > r.window_end
        ORDER BY t.event_time
    ) t
)
SELECT person_id, event_time
FROM recursive_cte
ORDER BY person_id, event_time
OPTION (MAXRECURSION 0); -- Remove recursion limit for users with many kept events

Why This Works #

For your sample data:

1. The anchor starts with the 10:00 am event, setting a window end of 10:03 am.
2. The recursive step finds the first event after 10:03 am (10:05 am), sets a new window end of 10:08 am.
3. Next, it finds the first event after 10:08 am (10:09 am), sets a window end of 10:12 am.
4. Finally, it jumps to 10:45 am, which is outside the 10:12 am window.

This skips all irrelevant rows in between, making it orders of magnitude faster than row-by-row recursion.

Non-Recursive Alternative (Tally Table Approach) #

If you need to avoid recursion entirely, use a tally (numbers) table to simulate iterative window selection. This is ideal for older SQL Server versions with limited CTE optimizations.

Step 1: Create a Tally Table #

CREATE TABLE #tally (n INT PRIMARY KEY);
INSERT INTO #tally (n)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2;

Step 2: Window Selection Query #

WITH ranked_events AS (
    SELECT
        person_id,
        event_time,
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY event_time) AS rn
    FROM #res
),
person_windows AS (
    -- Start with the first event for each user
    SELECT
        re.person_id,
        re.event_time,
        DATEADD(MINUTE, 3, re.event_time) AS window_end,
        t.n AS window_number
    FROM ranked_events re
    CROSS JOIN #tally t
    WHERE re.rn = 1 AND t.n = 1

    UNION ALL

    -- Find the next valid event for each window
    SELECT
        re.person_id,
        re.event_time,
        DATEADD(MINUTE, 3, re.event_time) AS window_end,
        pw.window_number + 1 AS window_number
    FROM person_windows pw
    INNER JOIN ranked_events re
        ON re.person_id = pw.person_id
        AND re.rn = (
            SELECT MIN(rn)
            FROM ranked_events
            WHERE person_id = pw.person_id
              AND event_time > pw.window_end
        )
)
SELECT person_id, event_time
FROM person_windows
ORDER BY person_id, event_time;

Critical Performance Optimizations #

No matter which approach you use, these tweaks will make a huge difference for large datasets:

• Add a covering index: Create an index on (person_id, event_time) to eliminate expensive sorts. For the temp table #res:

  CREATE NONCLUSTERED INDEX IX_res_person_event ON #res (person_id, event_time);
  

• Use temp tables over table variables: Temp tables generate better statistics, helping the query optimizer choose faster execution plans.
• Limit columns: Only select the columns you need in CTEs to reduce data transfer overhead.
• Avoid unnecessary computations: Precompute window end times once instead of recalculating them multiple times.

Testing with Your Sample Data #

Both approaches will return your expected results:

person_id | event_time
----------|-----------------------
2         | 2021-09-28 10:00:00.000
2         | 2021-09-28 10:05:00.000
2         | 2021-09-28 10:09:00.000
2         | 2021-09-28 10:45:00.000

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