SQL：計算歷史表上日期范圍之間的天數

我有一個Person表，其中有一Status列反映了該人任務的當前狀態。

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

我還有一個PersonStatusHistory表，其中包含一個LoggedDate列，用于指示每個Person.

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

我想獲取所有 Person 記錄，其中當前狀態 = In Progress, the number of it has been in In Progressminus the days were Blocked.

預期結果應如下所示：

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

我可以很容易地獲得前 2 個所需的結果，但我很難弄清楚如何處理最后一個被多次阻止的用例。到目前為止，這是我的代碼

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

uj5u.com熱心網友回復：

我認為更好的方法是讓狀態更改事件結束（通過 LEAD）并僅總結“進行中”狀態持續時間

select personid, status, sum(event_duration)   1
from (
    SELECT personid, status , LoggedDate event_start,  
    coalesce (lead(LoggedDate,1) OVER( partition by personid ORDER BY LoggedDate),
    current_date) - LoggedDate event_duration,
    coalesce (lead(LoggedDate,1) OVER( partition by personid ORDER BY LoggedDate), current_date) as event_end
            from personstatushistory p 
            order by 1,3 ) q
 where status = 'In Progress'
 group by personid, status
 order by 1,2

標籤：数据库postgresql加入窗口函数

上一篇：SELECT陳述句中的可選列

下一篇：SQL條件列資料回傳（基于元組）