Finding consecutive sequence patterns across rows is a common requirement in event streams, log analysis, and time-series data. While SQL:2016 introduced the powerful MATCH_RECOGNIZE clause for row pattern recognition, popular relational databases like PostgreSQL, MySQL, and SQL Server do not yet support it natively.

If you need to match a specific consecutive sequence—such as ASSAULT → ROBBERY → BATTERY → MOTOR_VEHICLE_THEFT with specific time constraints—you can achieve equivalent functionality and excellent performance using standard ANSI SQL window functions.

The Best Approach: Window Functions (LEAD and LAG)

The cleanest, most portable standard SQL method uses the LEAD() and LAG() window functions inside a Common Table Expression (CTE) or subquery.

By looking ahead or behind relative to each row, you can inspect the surrounding event types in a single scan over the ordered dataset:

WITH ordered_events AS (
    SELECT 
        ID,
        TS,
        TYPE,
        DISTRICT,
        LAG(TYPE, 1) OVER (ORDER BY TS)  AS prev_type,
        LAG(ID, 1)   OVER (ORDER BY TS)  AS prev_id,
        LEAD(TYPE, 1) OVER (ORDER BY TS) AS next_type_1,
        LEAD(ID, 1)   OVER (ORDER BY TS) AS next_id_1,
        LEAD(TYPE, 2) OVER (ORDER BY TS) AS next_type_2,
        LEAD(ID, 2)   OVER (ORDER BY TS) AS next_id_2
    FROM CRIMES
)
SELECT 
    ID          AS assault_id,
    next_id_1   AS robbery_id,
    next_id_2   AS battery_id,
    -- You can include subsequent IDs as needed
    TS          AS assault_ts
FROM ordered_events
WHERE TYPE = 'ASSAULT'
  AND next_type_1 = 'ROBBERY'
  AND next_type_2 = 'BATTERY'
  AND next_type_3 = 'MOTOR_VEHICLE_THEFT';

Optimizing for Selective Constraints

In your scenario, the BATTERY event has an explicit timestamp filter (TS BETWEEN 1755864000 AND 1755873000). Scanning the entire table with window functions might be slow if the table is massive.

You can orient the pattern around the filtered event (BATTERY) as the anchor. This allows the query planner to leverage index range scans effectively:

WITH sequenced_crimes AS (
    SELECT 
        ID,
        TS,
        TYPE,
        DISTRICT,
        LAG(TYPE, 2) OVER (ORDER BY TS) AS type_minus_2,
        LAG(ID, 2)   OVER (ORDER BY TS) AS id_minus_2,
        LAG(TYPE, 1) OVER (ORDER BY TS) AS type_minus_1,
        LAG(ID, 1)   OVER (ORDER BY TS) AS id_minus_1,
        LEAD(TYPE, 1) OVER (ORDER BY TS) AS type_plus_1,
        LEAD(ID, 1)   OVER (ORDER BY TS) AS id_plus_1
    FROM CRIMES
)
SELECT 
    id_minus_2  AS assault_id,
    id_minus_1  AS robbery_id,
    ID          AS battery_id,
    id_plus_1   AS theft_id,
    TS          AS battery_ts
FROM sequenced_crimes
WHERE TYPE = 'BATTERY'
  AND TS BETWEEN 1755864000 AND 1755873000
  AND type_minus_2 = 'ASSAULT'
  AND type_minus_1 = 'ROBBERY'
  AND type_plus_1  = 'MOTOR_VEHICLE_THEFT';

Note: Window functions require evaluating neighboring rows in the window partition, so ensure the CTE includes a slightly broader timestamp buffer if you push timestamp filters inside the CTE.

Partitioning by Group (e.g., District)

If the consecutive sequence must occur within the same geographical area or district, add the PARTITION BY clause inside the window function definition:

LAG(TYPE, 1) OVER (PARTITION BY DISTRICT ORDER BY TS)

Performance Tuning & Indexing

To make this sequence search run in milliseconds even over millions of rows, follow these indexing best practices:

  • Composite Index: Create an index on (TS, TYPE, ID) or (DISTRICT, TS, TYPE, ID) if partitioning. This permits an index-only scan without sorting in memory.
  • Narrow CTE Projection: Select only the columns required to evaluate the match to minimize memory usage during window processing.