首先得明确核心需求：我们需要按治疗阶段划分数据，在每个阶段内找到SpO2首次比该阶段起始值下降10%的记录，同时保留阶段的关键起始节点（比如治疗切换的记录、初始基线记录）。

步骤拆解 #

1. 划分治疗阶段：每个患者的诊疗过程会被治疗A/B的启动分割成不同阶段。我们可以用累积求和的方式为每个阶段分配唯一ID——每当遇到治疗A或B的记录时，阶段ID递增。
2. 确定每个阶段的起始SpO2值：每个阶段的起始值是该阶段第一条有效SpO2数值（跳过SpO2为N/A的记录）。
3. 筛选目标记录：在每个阶段内，找到第一个满足SpO2 ≤ 起始值 * 0.9的记录，同时保留阶段的关键起始记录（初始基线、治疗切换记录）。

完整SQL代码 #

假设你的表名为patient_vitals，并且有patient_id字段（用来区分不同患者，这在实际场景中必不可少），以下是实现代码：

WITH stage_data AS (
    -- 第一步：划分治疗阶段，生成阶段ID
    SELECT 
        *,
        -- 每当遇到治疗A/B时，阶段ID加1，初始阶段为0
        SUM(CASE WHEN Treatment IN ('A', 'B') THEN 1 ELSE 0 END) 
            OVER (PARTITION BY patient_id ORDER BY "Date Time") AS stage_id
    FROM patient_vitals
),
stage_start_spo2 AS (
    -- 第二步：计算每个阶段的起始SpO2值
    SELECT 
        s.*,
        -- 取每个阶段第一条非N/A的SpO2作为起始值
        FIRST_VALUE(CASE WHEN SpO2 IS NOT NULL THEN SpO2 END) 
            OVER (PARTITION BY patient_id, stage_id ORDER BY "Date Time" 
                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS stage_start_spo2
    FROM stage_data s
),
target_records AS (
    -- 第三步：标记每个阶段内的目标记录（首次下降10%的记录）
    SELECT 
        *,
        -- 标记是否是当前阶段首次满足下降10%的条件
        ROW_NUMBER() OVER (
            PARTITION BY patient_id, stage_id 
            ORDER BY "Date Time"
        ) AS rn
    FROM stage_start_spo2
    WHERE 
        -- 筛选条件：SpO2满足下降10%，或者是关键起始记录
        (SpO2 IS NOT NULL AND SpO2 <= stage_start_spo2 * 0.9)
        OR Treatment IN ('A', 'B')
        OR (stage_id = 0 AND SpO2 IS NOT NULL) -- 保留初始基线记录
)
-- 第四步：最终筛选出需要的记录
SELECT "Date Time", SpO2, Treatment
FROM target_records
WHERE 
    rn = 1 -- 每个阶段只取第一个满足条件的下降记录
    OR Treatment IN ('A', 'B') -- 保留治疗切换记录
    OR (stage_id = 0 AND rn = 1) -- 保留初始基线
ORDER BY "Date Time";

代码解释 #

• 阶段划分：通过SUM(...) OVER (...)累积计数治疗切换事件，为每个阶段生成唯一ID，确保同一治疗阶段的记录被归为一组。
• 起始SpO2计算：用FIRST_VALUE函数抓取每个阶段第一条有效SpO2数值，作为该阶段的基准值。
• 目标记录筛选：用ROW_NUMBER标记每个阶段内符合条件的记录顺序，只保留第一个满足下降10%的记录，同时保留治疗切换的关键节点和初始基线。

针对你的示例数据验证 #

运行上述SQL后，会返回你期望的结果：

Date Time | SpO2 | Treatment
05/19/22 18:23 | 100 | N/A
05/19/22 18:24 | 95 | A
05/19/22 18:26 | 85 | N/A
05/19/22 18:27 | N/A | B
05/19/22 18:27 | 90 | N/A
05/19/22 18:29 | 80 | N/A

这里的逻辑是：

• 初始阶段（stage_id=0）保留基线记录18:23的100；
• 治疗A阶段（stage_id=1）起始值是95，首次下降10%（≤85.5）的是18:26的85；
• 治疗B阶段（stage_id=2）起始值是18:27的90，首次下降10%（≤81）的是18:29的80；
• 同时保留治疗A和B的切换记录。

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