按Product ID分组，查询连续10个时间戳符合条件首行的SQL需求

针对你的需求，用窗口函数分组连续区间的方式可以大幅提升性能，避免生成列表的低效操作。核心思路是先标记符合条件的行，再把连续的时间戳归为同一分组，最后筛选出分组内记录数≥10的组，取每组的首个时间戳。

具体SQL实现 #

WITH qualified_rows AS (
    -- 筛选符合measure范围的行，生成连续分组标识
    SELECT 
        productid,
        timestamp,
        -- 连续时间戳的行会得到相同的group_id
        DATEDIFF(minute, '1970-01-01', timestamp) - 
        ROW_NUMBER() OVER (PARTITION BY productid ORDER BY timestamp) AS group_id
    FROM your_table_name
    WHERE measure BETWEEN 4 AND 5
),
group_counts AS (
    -- 统计分组记录数，筛选出至少10条的分组
    SELECT 
        productid,
        group_id,
        MIN(timestamp) AS first_timestamp,
        COUNT(*) AS record_count
    FROM qualified_rows
    GROUP BY productid, group_id
    HAVING COUNT(*) >= 10
)
-- 输出每个产品对应的首个符合条件时间戳
SELECT productid, first_timestamp
FROM group_counts
ORDER BY productid, first_timestamp;

性能优化说明 #

• 窗口函数的计算效率远高于生成列表再判断连续性的逻辑，避免了大量自关联或递归操作
• 利用时间戳转分钟数与行号的差值生成分组ID，直接将连续时间戳的行归为一组，后续仅需一次分组聚合
• 主键(productid, timestamp)可被窗口函数和分组操作直接利用，大幅降低索引扫描开销

如果你的数据库不支持DATEDIFF(minute, ...)，可替换为对应数据库的时间转分钟数函数（比如PostgreSQL用EXTRACT(EPOCH FROM timestamp)::INT / 60），核心逻辑保持不变。

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