SQL中结合时间条件关联实验室测量值，如何获取B测量对应值

需求与现状 #

我有一组实验室测量数据集，需要完成以下操作：

• 获取所有患者ID对应的测量A的数值及时间
• 关联特定时间范围内（测量A时间的前90天内、且不晚于A时间）测量B的最早、最晚时间，以及对应的测量值

当前使用的SQL语句仅能获取测量B的最早、最晚时间，无法拿到对应的测量值，且希望找到无需再次关联同一张测量表的实现方式。

当前SQL语句：

SELECT  
    A.patient_id,   
    A.time AS A_time,  
    A.value AS A_value,
    MIN(B.time) AS earliest_B_time,
    MAX(B.time) AS latest_B_time
FROM  
    schema.measurements A 
LEFT JOIN
    schema.measurements B
    ON A.pat_id = B.pat_id
WHERE
    B.name= 'B'
    AND A.name = 'A'
    AND B.time <= A.time
    AND B.time >= (A.time - 90)
GROUP BY
    A.patient_id,   
    A.time,  
    A.value

解决方案 #

以下两种方法均无需多次关联原表，利用窗口函数或子查询一次扫描完成计算，属于高效优雅的实现：

方法一：使用FIRST_VALUE和LAST_VALUE窗口函数 #

适用于支持窗口函数的SQL方言（如PostgreSQL、MySQL 8.0+、SQL Server等），通过窗口函数直接在关联后的结果集中提取对应时间的测量值：

SELECT DISTINCT
    A.patient_id,
    A.time AS A_time,
    A.value AS A_value,
    -- 获取窗口内最早B时间对应的测量值
    FIRST_VALUE(B.value) OVER (
        PARTITION BY A.patient_id, A.time
        ORDER BY B.time ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS earliest_B_value,
    MIN(B.time) OVER (PARTITION BY A.patient_id, A.time) AS earliest_B_time,
    -- 获取窗口内最晚B时间对应的测量值
    LAST_VALUE(B.value) OVER (
        PARTITION BY A.patient_id, A.time
        ORDER BY B.time ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS latest_B_value,
    MAX(B.time) OVER (PARTITION BY A.patient_id, A.time) AS latest_B_time
FROM
    schema.measurements A
LEFT JOIN
    schema.measurements B
    ON A.patient_id = B.patient_id
    AND B.name = 'B'
    AND B.time <= A.time
    AND B.time >= (A.time - 90)
WHERE
    A.name = 'A'

注：添加DISTINCT是为了去除因多B记录关联导致的重复结果，确保每个测量A记录对应唯一的结果行。

方法二：使用ROW_NUMBER()标记目标记录后聚合 #

先在子查询中给每个患者的测量B记录按时间排序并标记序号，再通过条件聚合提取最早、最晚时间对应的测量值：

SELECT
    A.patient_id,
    A.time AS A_time,
    A.value AS A_value,
    MAX(CASE WHEN B.rn_asc = 1 THEN B.time END) AS earliest_B_time,
    MAX(CASE WHEN B.rn_asc = 1 THEN B.value END) AS earliest_B_value,
    MAX(CASE WHEN B.rn_desc = 1 THEN B.time END) AS latest_B_time,
    MAX(CASE WHEN B.rn_desc = 1 THEN B.value END) AS latest_B_value
FROM
    schema.measurements A
LEFT JOIN (
    SELECT
        patient_id,
        time,
        value,
        -- 按时间升序标记最早记录
        ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY time ASC) AS rn_asc,
        -- 按时间降序标记最晚记录
        ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY time DESC) AS rn_desc
    FROM
        schema.measurements
    WHERE
        name = 'B'
) B ON A.patient_id = B.patient_id
    AND B.time <= A.time
    AND B.time >= (A.time - 90)
WHERE
    A.name = 'A'
GROUP BY
    A.patient_id,
    A.time,
    A.value

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