SQL实现按组首时间为起点的动态时间间隔分组并计算THAW_COUNT

核心思路 #

该需求属于典型的动态会话分组场景，分组边界不固定，由每组首条记录的时间动态计算，需要通过递归CTE逐行维护当前组的起始时间来实现：

1. 先对每个PARENT_SAMPLE_ID下的有效记录按FREEZE_TIME升序排序
2. 递归遍历排序后的记录，判断当前记录是否超出当前组起始时间+2小时（匹配示例间隔），超出则生成新组，组号+1
3. 最终统计每个PARENT_SAMPLE_ID的最大组号，即为总组数THAW_COUNT

实现代码（兼容SQL Server，其他数据库仅需修改时间差计算逻辑） #

WITH RankedSamples AS (
    -- 过滤无效数据，按父样本ID分组、冻结时间升序排序
    SELECT 
        PARENT_SAMPLE_ID, 
        FREEZE_TIME,
        ROW_NUMBER() OVER (PARTITION BY PARENT_SAMPLE_ID ORDER BY FREEZE_TIME ASC) AS rn
    FROM SAMPLE
    WHERE FREEZE_TIME IS NOT NULL AND PARENT_SAMPLE_ID IS NOT NULL
),
RecursiveGroups AS (
    -- 递归锚点：每个父样本的第一条记录作为第一组起点
    SELECT 
        PARENT_SAMPLE_ID,
        FREEZE_TIME AS current_group_start,
        rn,
        1 AS group_num
    FROM RankedSamples
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归逻辑：逐行判断是否触发新组
    SELECT 
        rs.PARENT_SAMPLE_ID,
        CASE WHEN DATEDIFF(MINUTE, rg.current_group_start, rs.FREEZE_TIME) >= 120 
             THEN rs.FREEZE_TIME 
             ELSE rg.current_group_start END AS current_group_start,
        rs.rn,
        CASE WHEN DATEDIFF(MINUTE, rg.current_group_start, rs.FREEZE_TIME) >= 120 
             THEN rg.group_num + 1 
             ELSE rg.group_num END AS group_num
    FROM RankedSamples rs
    INNER JOIN RecursiveGroups rg 
        ON rs.PARENT_SAMPLE_ID = rg.PARENT_SAMPLE_ID 
        AND rs.rn = rg.rn + 1
)
-- 统计每个父样本对应的总组数
SELECT 
    PARENT_SAMPLE_ID,
    MAX(group_num) AS THAW_COUNT
FROM RecursiveGroups
GROUP BY PARENT_SAMPLE_ID

逻辑说明 #

• 代码中的120为分组间隔的分钟数（对应示例的2小时），调整该值即可自定义分组间隔
• 针对示例数据，递归计算后SAMPLE_ID 2、3、4的group_num为1，SAMPLE_ID 5、6的group_num为2，最终统计得到的THAW_COUNT为2，完全符合预期输出
• 若使用PostgreSQL/MySQL等其他数据库，只需将DATEDIFF(MINUTE, 时间1, 时间2)替换为对应数据库的分钟级时间差计算函数即可

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