有間隔的日期範圍的累積執行 SUM

我正在尋求幫助，以獲得位於不同行的兩個日期之間差異的執行總和。

表看起來像：

我有一個查詢，查看前一個 end_date，如果它在一天之內，那麼這是一份持續契約。

SELECT
   case when
       (start_date - coalesce(lag(end_date) over (partition by client_id order by end_date), end_date)::date)::int <= 1 then true
        else false
   end as continous_contract,
   end_date - start_date as contract_days,
   client_id,
   contract_id,
   autopay_status,
   start_date,
   end_date
FROM
   client_contracts
ORDER BY
   client_id, start_date

這增加了兩個額外的別名列。

我的目標是總結客戶擁有連續契約的天數，因此上述範例的表格如下所示：

我將下面的查詢放在一起，但它只總結了前兩個值。

SELECT
*
FROM
(
   SELECT
       *,
       case
           when cc.continuous_contract = true then
               cc.contract_days + coalesce(lag(cc.contract_days) over (partition by cc.client_id), 1)
           else cc.contract_days
       end as added_contract_days
   FROM (
       SELECT
           case when
               (start_date - coalesce(lag(end_date) over (partition by client_id order by end_date), end_date)::date)::int <= 1 then true
                else false
           end as continuous_contract,
           end_date - start_date as contract_days,
           client_id,
           contract_id,
           autopay_status,
           start_date,
           end_date
       FROM
           client_contracts
       ) as cc
   ) as ccc

我很高興改變任何事情來完成這項工作。

Fiddle 用於協助查看數據和結構： Fiddle

可以分三步完成：

SELECT *, sum(contract_days) OVER (PARTITION BY client_id, contract_nr ORDER BY end_date) AS sum_days
FROM  (
  SELECT *, count(*) FILTER (WHERE NOT continous_contract) OVER (PARTITION BY client_id ORDER BY end_date) AS contract_nr
  FROM  (
     SELECT client_id, start_date, end_date
          , start_date <= lag(end_date, 1, end_date) OVER (PARTITION BY client_id ORDER BY end_date) + 1 AS continous_contract
          , end_date - start_date AS contract_days  -- + 1 ???
     FROM   client_contracts
     ) sub1
  ) sub2
ORDER  BY client_id, start_date;

db<>在這裡擺弄

內部子查詢sub1基本上是您開始時的簡化。

lag()可選地接受 3 個參數，如果沒有找到行，則第三個是備份。

sub2``contract_nr為每個連續的行組添加一個：合約間隔中的每個間隙都會啟動一個新合約。

外部SELECT最終添加執行總和。

這假設每個客戶的契約永遠不會重疊。

看：

• 選擇最長的連續序列

旁白：end_date - start_date AS contract_days看起來像是一個錯誤的錯誤？如果應包括下限和上限，則添加+ 1。（當然，重疊邊界會被計算兩次。）

引用自：https://dba.stackexchange.com/questions/316177