如何在SQL中按60分钟时间间隔为患者数据分配唯一Group ID

完全可以实现这个需求，核心思路是按患者分组后，通过递归或窗口函数判断每条记录是否需要开启新组，再为组内记录分配递增序号。以下是针对不同数据库的具体实现：

通用逻辑说明 #

1. 按患者唯一标识csn分组，每个患者的记录单独处理
2. 对每个患者的记录按record_time（记录时间）排序
3. 以每条记录与当前组的起始时间对比：
   • 若间隔≤60分钟，属于当前组，组内序号+1
   • 若间隔>60分钟，开启新组，组内序号重置为1，新组起始时间为当前记录时间

PostgreSQL 实现 #

WITH patient_records AS (
    SELECT
        csn,
        record_time,
        ROW_NUMBER() OVER (PARTITION BY csn ORDER BY record_time) AS rn
    FROM blood_pressure -- 替换为你的表名
),
recursive_groups AS (
    SELECT
        csn,
        record_time,
        record_time AS group_start,
        rn,
        1 AS group_id -- 这里的group_id就是你需要的组内序号
    FROM patient_records
    WHERE rn = 1
    UNION ALL
    SELECT
        pr.csn,
        pr.record_time,
        CASE WHEN pr.record_time - rg.group_start > INTERVAL '60 minutes' THEN pr.record_time ELSE rg.group_start END,
        pr.rn,
        CASE WHEN pr.record_time - rg.group_start > INTERVAL '60 minutes' THEN 1 ELSE rg.group_id + 1 END
    FROM patient_records pr
    JOIN recursive_groups rg ON pr.csn = rg.csn AND pr.rn = rg.rn + 1
)
SELECT
    csn,
    record_time,
    group_id
FROM recursive_groups
ORDER BY csn, record_time;

MySQL 8.0+ 实现 #

WITH patient_records AS (
    SELECT
        csn,
        record_time,
        ROW_NUMBER() OVER (PARTITION BY csn ORDER BY record_time) AS rn
    FROM blood_pressure -- 替换为你的表名
),
recursive_groups AS (
    SELECT
        csn,
        record_time,
        record_time AS group_start,
        rn,
        1 AS group_id
    FROM patient_records
    WHERE rn = 1
    UNION ALL
    SELECT
        pr.csn,
        pr.record_time,
        CASE WHEN TIMESTAMPDIFF(MINUTE, rg.group_start, pr.record_time) > 60 THEN pr.record_time ELSE rg.group_start END,
        pr.rn,
        CASE WHEN TIMESTAMPDIFF(MINUTE, rg.group_start, pr.record_time) > 60 THEN 1 ELSE rg.group_id + 1 END
    FROM patient_records pr
    JOIN recursive_groups rg ON pr.csn = rg.csn AND pr.rn = rg.rn + 1
)
SELECT
    csn,
    record_time,
    group_id
FROM recursive_groups
ORDER BY csn, record_time;

SQL Server 实现 #

WITH patient_records AS (
    SELECT
        csn,
        record_time,
        ROW_NUMBER() OVER (PARTITION BY csn ORDER BY record_time) AS rn
    FROM blood_pressure -- 替换为你的表名
),
recursive_groups AS (
    SELECT
        csn,
        record_time,
        record_time AS group_start,
        rn,
        1 AS group_id
    FROM patient_records
    WHERE rn = 1
    UNION ALL
    SELECT
        pr.csn,
        pr.record_time,
        CASE WHEN DATEDIFF(MINUTE, rg.group_start, pr.record_time) > 60 THEN pr.record_time ELSE rg.group_start END,
        pr.rn,
        CASE WHEN DATEDIFF(MINUTE, rg.group_start, pr.record_time) > 60 THEN 1 ELSE rg.group_id + 1 END
    FROM patient_records pr
    JOIN recursive_groups rg ON pr.csn = rg.csn AND pr.rn = rg.rn + 1
)
SELECT
    csn,
    record_time,
    group_id
FROM recursive_groups
ORDER BY csn, record_time;

效果验证（以你提供的患者614为例） #

患者297的记录会按规则执行：首条8/27 1pm对应group_id=1，后续与该起点间隔≤60分钟的记录依次为2、3…，超出则重置为1，循环判断。

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