如何获取转诊记录中每条记录的上一次转诊起止日期？已尝试排名法

你之前尝试按用户分组排名再关联的思路是对的，但其实用SQL的**窗口函数LAG()**可以更直接、高效地完成需求，不用手动做复杂的自关联操作。

最佳解决方案：使用LAG()窗口函数 #

LAG()专门用于获取当前行在分区内的前N行数据，正好匹配你需要的“同一用户的上一次转诊”场景：

SELECT
    DIM_PERSON_ID AS "Per ID",
    FACT_REFERRAL_ID AS "Ref ID",
    REFRL_START_DTTM AS "Refrl_Start_D",
    REFRL_END_DTTM AS "Refrl_End_D",
    -- 取同一用户的上一条转诊开始日期，按转诊时间排序
    LAG(REFRL_START_DTTM) OVER (
        PARTITION BY DIM_PERSON_ID 
        ORDER BY REFRL_START_DTTM
    ) AS "Prev_Refrl_S",
    -- 取同一用户的上一条转诊结束日期
    LAG(REFRL_END_DTTM) OVER (
        PARTITION BY DIM_PERSON_ID 
        ORDER BY REFRL_START_DTTM
    ) AS "Prev_Refrl_E"
FROM FACT_REFERRALS
-- 按用户ID和转诊开始日期倒序，和你给出的示例结果一致
ORDER BY DIM_PERSON_ID, REFRL_START_DTTM DESC;

代码解释：

• PARTITION BY DIM_PERSON_ID：确保只在同一个用户的记录范围内查找上一次转诊；
• ORDER BY REFRL_START_DTTM：按转诊开始时间排序，保证“上一次”是时间上的前一条记录；
• LAG(字段)：自动获取当前行的前一行对应字段的值，没有前一行时返回NULL，正好符合你的示例需求。

如果你坚持用排名+自关联的方式 #

如果之前的思路是用排名来关联，这里也给出正确的关联写法（适合不支持窗口函数的老版本SQL）：

-- 先给每个用户的转诊记录按时间排名
WITH ranked_referrals AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY DIM_PERSON_ID 
            ORDER BY REFRL_START_DTTM
        ) AS referral_rank
    FROM FACT_REFERRALS
)
-- 关联当前记录和排名+1的记录（即上一次转诊）
SELECT
    curr.DIM_PERSON_ID AS "Per ID",
    curr.FACT_REFERRAL_ID AS "Ref ID",
    curr.REFRL_START_DTTM AS "Refrl_Start_D",
    curr.REFRL_END_DTTM AS "Refrl_End_D",
    prev.REFRL_START_DTTM AS "Prev_Refrl_S",
    prev.REFRL_END_DTTM AS "Prev_Refrl_E"
FROM ranked_referrals curr
LEFT JOIN ranked_referrals prev
    ON curr.DIM_PERSON_ID = prev.DIM_PERSON_ID
    AND curr.referral_rank = prev.referral_rank + 1
ORDER BY curr.DIM_PERSON_ID, curr.REFRL_START_DTTM DESC;

这个方法通过LEFT JOIN关联同一用户下排名+1的记录，也能得到和示例一致的结果，但效率上不如窗口函数。

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