MySQL 5.7.35下基于10Hz频率识别连续时间戳区间的高效实现问询（禁用WITH等函数）

Absolutely! Even without CTEs (unavailable in MySQL 5.7), we can efficiently solve this problem using user-defined variables to group contiguous sequences of timestamps with exactly 100ms intervals (10Hz frequency). This approach works smoothly for 100k+ rows as long as we optimize for performance.

Step-by-Step SQL Implementation #

Here's the query that will return your desired start/end intervals:

SELECT
    MIN(timestamps) AS Start,
    MAX(timestamps) AS End
FROM (
    SELECT
        timestamps,
        -- Assign group ID: increment only when interval is not 100ms
        @group_id := CASE
            WHEN TIMESTAMPDIFF(MICROSECOND, @prev_ts, timestamps) = 100000 THEN @group_id
            ELSE @group_id + 1
        END AS group_id,
        -- Update previous timestamp variable for next row
        @prev_ts := timestamps
    FROM
        your_table_name, -- Replace with your actual table name
        -- Initialize variables in a subquery to ensure proper starting state
        (SELECT @group_id := 0, @prev_ts := NULL) AS init_vars
    ORDER BY
        timestamps -- Critical: must sort timestamps to compare adjacent rows
) AS grouped_data
GROUP BY
    group_id
HAVING
    COUNT(*) >= 2; -- Filter out single-timestamp "intervals" (not truly contiguous)

How This Works #

1. Variable Initialization: We start by setting up two variables:

   • @group_id: Tracks the ID of the current contiguous sequence group.
   • @prev_ts: Stores the timestamp of the previous row to calculate time intervals between adjacent entries.

2. Group Assignment:

   • First, we sort the table by timestamps (this is mandatory to ensure we're comparing the correct adjacent rows).
   • For each row, we calculate the time difference in microseconds between the current timestamp and the previous one (100ms equals 100,000 microseconds).
   • If the difference is exactly 100,000, the row stays in the same group as the previous entry. If not, we increment the group ID to start a new contiguous sequence.

3. Aggregate Groups:

   • The outer query groups rows by group_id, then takes the minimum (start) and maximum (end) timestamp for each valid group.
   • The HAVING COUNT(*) >= 2 clause filters out groups with only one timestamp, since those don't form a meaningful contiguous interval.

Performance Optimization for 100k Rows #

• Add an Index on timestamps: This is critical for fast sorting and row comparison. Run this if you don't already have an index:

  CREATE INDEX idx_timestamps ON your_table_name(timestamps);
  

• The query uses a single pass over the table (with the index) to assign groups, followed by a lightweight aggregation. This will handle 100k rows efficiently without excessive resource usage.

Verification with Your Sample Data #

If you run this query against your sample data, it will return exactly the intervals you expect:

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