Início / dba / Perguntas / 316177
Accepted
michaelosmith
Asked: 2022-08-30 03:20:33 +0800 CST

SUM em execução cumulativa para intervalos de datas com intervalos

  • 772

Estou procurando ajuda para obter uma soma contínua da diferença entre duas datas que estão em linhas separadas.

A tabela se parece com:

ID do Cliente nome autopay_status contract_id data de início data final
1 Contrato 1 Inativo 1111 "2019-08-30" "2020-02-29"
1 Contrato 1 Inativo 1112 "2020-02-29" "2020-08-29"
1 Contrato 1 Ativo 1113 "2020-08-29" "2021-02-28"
2 Contrato 2 Inativo 2221 "2019-08-30" "2020-02-29"
2 Contrato 2 Inativo 2222 "2020-02-29" "2020-08-29"
2 Contrato 2 Ativo 2223 "2020-08-29" "2021-02-28"
3 Contrato 3 Inativo 3331 "2019-08-30" "2020-02-29"
3 Contrato 3 Inativo 3332 "2020-03-29" "2020-09-29"
3 Contrato 3 Inativo 3333 "2020-09-29" "2021-03-28"
3 Contrato 3 Ativo 3334 "2021-03-28" "2021-09-28"

Eu tenho uma consulta que analisa a data_final anterior e, se estiver dentro de um dia, esse é um contrato contínuo.

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

Isso adiciona duas colunas de alias extras.

contrato_contínuo contrato_dias ID do Cliente nome autopay_status contract_id data de início data final
verdadeiro 183 3 Contrato 3 Inativo 3331 "2019-08-30" "2020-02-29"
falso 184 3 Contrato 3 Inativo 3332 "2020-03-29" "2020-09-29"
verdadeiro 183 3 Contrato 3 Inativo 3333 "2020-09-29" "2021-03-28"
verdadeiro 182 3 Contrato 3 Ativo 3334 "2021-03-28" "2021-09-28"

Meu objetivo aqui é resumir os dias em que um cliente teve um contrato contínuo para que a tabela seja semelhante à abaixo para o exemplo acima:

soma_dias contrato_contínuo contrato_dias ID do Cliente
183 verdadeiro 183 3
184 falso 184 3
367 verdadeiro 183 3
549 verdadeiro 182 3
733 verdadeiro 184 3
181 falso 181 3

Eu coloquei a consulta abaixo, mas ela resume apenas os dois valores anteriores.

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

Estou feliz em mudar qualquer coisa para fazer este trabalho.

Fiddle para auxiliar na visualização de dados e estrutura: Fiddle

postgresql window-functions

1 respostas

  1. Best Answer

    Pode ser feito em três etapas:

    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<>fique aqui

    A subconsulta interna sub1é basicamente o que você começou, simplificada.
    lag()opcionalmente, recebe 3 argumentos, sendo o terceiro o fallback se nenhuma linha for encontrada.

    sub2adiciona um contract_nrpara cada grupo contínuo de linhas: cada intervalo no intervalo do contrato inicia um novo contrato.

    O externo SELECTfinalmente adiciona a soma corrente.

    Isso pressupõe que os contratos nunca se sobrepõem por cliente.

    Ver:

    Aparte: end_date - start_date AS contract_daysparece um erro off-by-one? Se os limites inferior e superior devem ser incluídos, adicione + 1. (Claro, os limites sobrepostos são contados duas vezes então.)

    • 4

relate perguntas