SQL计算提案各状态持续天数：结果缺失/重复异常求助

正确SQL查询语句 #

要计算每个状态的持续时长，最直接的方式是用LEAD()窗口函数，获取同一提案下下一个状态的创建时间作为当前状态的退出时间，无需复杂的自关联：

SELECT
    PropostaID,
    Status,
    Descricao_Status,
    Data_Hora_Criacao AS Entrada,
    -- 获取下一个状态的创建时间，最后一个状态无后续则显示NULL
    LEAD(Data_Hora_Criacao) OVER (PARTITION BY PropostaID ORDER BY Data_Hora_Criacao ASC) AS Saida,
    -- 分钟级时长（更精确），如需天级可替换为DAY
    CASE
        WHEN LEAD(Data_Hora_Criacao) OVER (PARTITION BY PropostaID ORDER BY Data_Hora_Criacao ASC) IS NULL
            THEN NULL -- 最后一个状态无退出时间，可按需改为0或其他值
            ELSE DATEDIFF(MINUTE, Data_Hora_Criacao, LEAD(Data_Hora_Criacao) OVER (PARTITION BY PropostaID ORDER BY Data_Hora_Criacao ASC))
    END AS Duracao_Minutos,
    -- 可选：天级时长
    CASE
        WHEN LEAD(Data_Hora_Criacao) OVER (PARTITION BY PropostaID ORDER BY Data_Hora_Criacao ASC) IS NULL
            THEN NULL
            ELSE DATEDIFF(DAY, Data_Hora_Criacao, LEAD(Data_Hora_Criacao) OVER (PARTITION BY PropostaID ORDER BY Data_Hora_Criacao ASC))
    END AS Duracao_Dias
FROM
    [DS_Market_FATO_Status_Proposta_Atual]
WHERE
    PropostaID = 5437
ORDER BY
    Data_Hora_Criacao ASC;

原查询的问题分析 #

1. 关联逻辑完全错误：自关联子查询用B2.PropostaID > A.PropostaID，但目标是同一提案（ID=5437），该条件永远不成立，导致关联到其他提案的错误时间（比如结果里的2022-07-18记录，原数据根本不存在）。
2. 丢失初始状态：错误关联导致第一个状态（ID=30）未被匹配到，直接丢失。
3. 重复记录：错误关联让同一状态匹配到多个无关的退出时间，出现重复行。

正确查询的输出结果 #

执行后会得到与原数据一一对应的状态时长：

PropostaID  Status  Descricao_Status              Entrada                 Saida                   Duracao_Minutos  Duracao_Dias
-----------------------------------------------------------------------------------------------------------------------------------
5437        30      Análise de crédito enviada    2022-07-13 19:10:37.030 2022-07-13 19:50:56.470 40               0
5437        1       Crédito aprovado              2022-07-13 19:50:56.470 2022-07-14 17:24:43.570 1373             0
5437        5       Documentação enviada          2022-07-14 17:24:43.570 2022-07-15 19:30:58.680 2826             1
5437        9       Documentação aprovada         2022-07-15 19:30:58.680 2022-07-15 19:36:41.490 5                0
5437        28      Crédito liberado              2022-07-15 19:36:41.490 NULL                    NULL             NULL

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