SQL如何计算In Progress状态人员的有效在途天数（排除多次Blocked时长）

现有两张表：

• Person表：记录人员当前任务状态
• PersonStatusHistory表：记录人员状态变更的历史时间

需要筛选出当前Status为In Progress的人员，计算其处于In Progress状态的总天数减去Blocked状态的天数，要求正确处理多次Blocked的场景。

Person表数据 #

PersonId    Status
--------------------------------------------
1           In Progress
2           In Progress
3           Completed
4           In Progress

PersonStatusHistory表数据 #

PersonId   Status            LoggedDate
--------------------------------------------
1          Created           11/11/2022
1          In Progress       11/15/2022

2          Created           11/05/2022
2          In Progress       11/07/2022
2          Blocked           11/10/2022
2          In Progress       11/15/2022

3          Created           11/03/2022
3          In Progress       11/12/2022
3          Completed         11/17/2022

4          Created           11/01/2022
4          In Progress       11/03/2022
4          Blocked           11/05/2022
4          In Progress       11/10/2022
4          Blocked           11/12/2022
4          In Progress       11/15/2022

预期结果 #

Emp Id      No of Days In Progress
--------------------------------------------
1           4
              > 11/18 (date today) - 11/15 (first In Progress LoggedDate)
              > 18 - 15 + 1 (current day) = 4 days
2           7
              > 11/18 (date today) minus 11/07 (first In Progress LoggedDate)
              > less the number of Blocked days 
              > 18 - 7 - 5 + 1 (current day) = 7
4           8 days
              > 11/18 (date today) minus 11/03 (first In Progress LoggedDate)
              > less the number of Blocked days (5 and 3 days) 
              > 18 - 3 - 5 - 3 + 1 (current day) = 8

当前代码（无法处理多次Blocked场景） #

SELECT x.PersonId
       , (DATE_PART('day', CURRENT_DATE + 1) - DATE_PART('day', x.start_in_progress_date)
         - DATE_PART('day', end_blocked_date) - DATE_PART('day', x.start_blocked_date)
         ) as no_of_days_in_progress
FROM
(
  SELECT p.PersonId
         , MIN (psh.LoggedDate) AS start_in_progress_date
         , (SELECT MIN(psh.LoggedDate)
             FROM PersonStatusHistory psh2
             WHERE psh2.PersonId = p.PersonId
             AND psh2.Status = 'Blocked'
           ) as start_blocked_date
         , (SELECT MAX(psh.LoggedDate)
             FROM PersonStatusHistory psh3
             WHERE psh3.PersonId = p.PersonId
             AND psh3.Status = 'In Progress'
           ) as end_blocked_date
  FROM Person p
  INNER JOIN PersonStatusHistory psh
     ON psh.PersonId = p.PersonId
  WHERE p.Status = 'In Progress'
  GROUP BY p.PersonId
) x

解决方案 #

核心思路是用窗口函数LEAD()获取每条状态记录的下一次状态变更日期，分别计算In Progress和Blocked的总时长，最后做差值计算。

完整SQL代码 #

WITH StatusWithNextDate AS (
    SELECT 
        PersonId,
        Status,
        LoggedDate,
        -- 获取下一次状态变更的日期，无后续变更则用当前日期
        LEAD(LoggedDate, 1, CURRENT_DATE) OVER (PARTITION BY PersonId ORDER BY LoggedDate) AS NextStatusDate
    FROM PersonStatusHistory
),
StatusDuration AS (
    SELECT 
        PersonId,
        Status,
        -- 计算状态持续天数（包含生效当天）
        DATE_PART('day', NextStatusDate - LoggedDate) + 1 AS DurationDays
    FROM StatusWithNextDate
    -- 仅保留需要计算的状态类型
    WHERE Status IN ('In Progress', 'Blocked')
),
AggregatedDurations AS (
    SELECT 
        PersonId,
        SUM(CASE WHEN Status = 'In Progress' THEN DurationDays ELSE 0 END) AS TotalInProgressDays,
        SUM(CASE WHEN Status = 'Blocked' THEN DurationDays ELSE 0 END) AS TotalBlockedDays
    FROM StatusDuration
    GROUP BY PersonId
)
SELECT 
    ad.PersonId AS "Emp Id",
    (ad.TotalInProgressDays - ad.TotalBlockedDays) AS "No of Days In Progress"
FROM AggregatedDurations ad
JOIN Person p ON ad.PersonId = p.PersonId
WHERE p.Status = 'In Progress'
ORDER BY ad.PersonId;

代码说明 #

1. StatusWithNextDate：为每条状态记录匹配下一次状态变更的日期，若为最后一条记录则用当前日期，确保能计算到当前的持续时长。
2. StatusDuration：计算每条状态记录的持续天数（公式：下一个日期 - 当前日期 +1，包含状态生效的当天），只保留In Progress和Blocked状态。
3. AggregatedDurations：按人员分组，分别汇总两种状态的总天数。
4. 最后关联Person表筛选当前状态为In Progress的人员，计算两者的差值得到最终结果。

该方案可正确处理多次Blocked的场景，比如人员4的两次Blocked时长会被分别计算并求和，再从In Progress总时长中扣除。

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