如何判定患者90天周期内xpostaccessuse值是否反复变更

Alright, let's work through this problem step by step. First, let's recap the context and goals to align:

背景：现有可用查询可获取患者（各表中xpid列对应对象）确诊慢性疾病日期（d1.ddiadate +'90 days'）90天后的访问类型（d8_5_B.xpostaccessuse），输出为日期、整数两列。
目标：需判定该90天周期内患者的访问类型是否存在来回变更，理想情况下还可标注变更类型及时间。

1. 先定义患者的观察时间窗口 #

First, we need to pin down the 90-day window we're analyzing: it starts 90 days after the patient's chronic disease diagnosis, and lasts for 90 days. We'll use a CTE to define this window for each patient:

WITH patient_time_window AS (
    SELECT
        xpid,
        -- 窗口起始：确诊日期+90天
        ddiadate + INTERVAL '90 days' AS window_start,
        -- 窗口结束：起始日期+90天（即确诊后180天）
        ddiadate + INTERVAL '180 days' AS window_end
    FROM d1
)

2. 关联访问数据并标记变更点 #

Next, we'll join this window with the access type table, then use the LAG() window function to compare each visit's type with the previous one for the same patient. This lets us spot changes easily:

WITH patient_time_window AS (
    SELECT
        xpid,
        ddiadate + INTERVAL '90 days' AS window_start,
        ddiadate + INTERVAL '180 days' AS window_end
    FROM d1
),
patient_access_records AS (
    SELECT
        ptr.xpid,
        -- 替换成你实际的访问日期列名（原问题未给出，这里假设为visit_date）
        a.visit_date AS 日期,
        a.xpostaccessuse AS 整数,
        -- 获取上一次的访问类型，用于对比
        LAG(a.xpostaccessuse) OVER (
            PARTITION BY ptr.xpid 
            ORDER BY a.visit_date
        ) AS previous_access_type
    FROM patient_time_window ptr
    JOIN d8_5_B a
        ON ptr.xpid = a.xpid
        -- 筛选窗口内的访问记录
        AND a.visit_date >= ptr.window_start
        AND a.visit_date < ptr.window_end
)

3. 生成最终结果（含变更标注和来回变更判定） #

Now we can build the final output: we'll add columns to flag changes, note the change type, and even detect if there are "back-and-forth" changes (e.g., type A → B → A):

WITH patient_time_window AS (
    SELECT
        xpid,
        ddiadate + INTERVAL '90 days' AS window_start,
        ddiadate + INTERVAL '180 days' AS window_end
    FROM d1
),
patient_access_records AS (
    SELECT
        ptr.xpid,
        a.visit_date AS 日期,
        a.xpostaccessuse AS 整数,
        LAG(a.xpostaccessuse) OVER (
            PARTITION BY ptr.xpid 
            ORDER BY a.visit_date
        ) AS previous_access_type,
        -- 记录变更对（如"1→2"）
        CASE
            WHEN previous_access_type IS NOT NULL 
                 AND a.xpostaccessuse != previous_access_type
            THEN CONCAT(previous_access_type, ' → ', a.xpostaccessuse)
            ELSE NULL
        END AS 变更类型,
        -- 变更发生的时间
        CASE
            WHEN previous_access_type IS NOT NULL 
                 AND a.xpostaccessuse != previous_access_type
            THEN a.visit_date
            ELSE NULL
        END AS 变更时间
    FROM patient_time_window ptr
    JOIN d8_5_B a
        ON ptr.xpid = a.xpid
        AND a.visit_date >= ptr.window_start
        AND a.visit_date < ptr.window_end
),
patient_change_summary AS (
    SELECT
        xpid,
        -- 判定是否存在来回变更：检查是否有互为反向的变更对（如"1→2"和"2→1"）
        CASE
            WHEN COUNT(DISTINCT 变更类型) >= 2
                 AND EXISTS (
                     SELECT 1
                     FROM patient_access_records p2
                     WHERE p2.xpid = p1.xpid
                           AND p2.变更类型 = REPLACE(p1.变更类型, ' → ', ' ← ')
                 )
            THEN '存在来回变更'
            ELSE '无来回变更'
        END AS 来回变更判定
    FROM patient_access_records p1
    WHERE 变更类型 IS NOT NULL
    GROUP BY xpid
)
-- 合并明细和汇总判定
SELECT
        par.日期,
        par.整数,
        COALESCE(par.变更类型, '无变更') AS 变更类型,
        par.变更时间,
        COALESCE(pcs.来回变更判定, '无变更记录') AS 来回变更判定
FROM patient_access_records par
LEFT JOIN patient_change_summary pcs
    ON par.xpid = pcs.xpid
ORDER BY par.xpid, par.日期;

关键说明 #

• 替换visit_date为你实际的访问日期列名（原问题未给出该字段名，这里做了假设）。
• LAG()窗口函数是核心逻辑：它按患者分组、访问日期排序，帮我们获取上一次的访问类型，从而对比出当前记录是否发生了变更。
• 来回变更的判定逻辑：检查是否存在互为反向的变更对（比如同时出现1→2和2→1），如果有则标记为存在来回变更。
• 最终输出严格包含你要求的日期和整数列，同时补充了变更类型、变更时间以及来回变更的判定结果。

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