SQL如何查找一段时间内超过某个值的连续行计数

WITH continuous_rows AS (
  SELECT
    id,
    value,
    timestamp,
    ROW_NUMBER() OVER (ORDER BY timestamp) AS row_num
  FROM
    data
  WHERE
    value > <某个值>
    AND timestamp BETWEEN <起始时间> AND <结束时间>
),
count_continuous_rows AS (
  SELECT
    id,
    value,
    timestamp,
    row_num,
    row_num - ROW_NUMBER() OVER (PARTITION BY id ORDER BY row_num) AS group_num
  FROM
    continuous_rows
)
SELECT
  MIN(timestamp) AS start_time,
  MAX(timestamp) AS end_time,
  COUNT(*) AS count
FROM
  count_continuous_rows
GROUP BY
  id,
  value,
  group_num
HAVING
  COUNT(*) > <连续行数阈值>
ORDER BY
  count DESC;