Início / dba / Perguntas / 313769
Accepted
Pantea
Asked: 2022-06-26 03:05:24 +0800 CST

Consulta para encontrar a contagem de dias ativos (dias com status = ON) entre datas especificadas

  • 772

Eu tenho uma tabela como você pode ver abaixo:

create table z_test_duration
( Days     date,
  Status   char(8)
);

Os dados da amostra são como abaixo:

Dias Status
01/01/2022 sobre
02/01/2022 sobre
03/01/2022 sobre
04/01/2022 desligado
05/01/2022 sobre
06/01/2022 desligado
07/01/2022 sobre
08/01/2022 sobre
09/01/2022 desligado

O resultado desejado é este

NA DATA OFF_DATE COUNT_OF_ACTIVE_DAYS
01/01/2022 04/01/2022 3
05/01/2022 06/01/2022 1
07/01/2022 09/01/2022 2

Minha solução até agora é esta:

select min(days) on_date, 
       off_day off_date, 
       off_day - min(days) cnt
       
  from (select t1.off_day, 
               t1.prev_offday, 
               t2.days            
          from (                
                select t.days off_day,
                        nvl(lag(t.days, 1) over(order by t.days),convert(datetime, '1/1/2022') - 100) prev_offday
                  from z_test_duration t
                 where t.status = 'off'
                                 
                ) t1
         inner join z_test_duration t2
            on t2.days > t1.prev_offday
           and t2.days < t1.off_day)
 group by off_day;

Estou pensando se existem maneiras melhores de resolver isso, seria apreciado se você compartilhasse sua maneira de resolver isso.

Desde já, obrigado.

sql-server sql-server-2016

1 respostas

  1. Best Answer

    Este é um problema de 'ilhas'.

    Uma solução popular e eficiente é numerar as linhas na ordem desejada. Quando há uma lacuna na sequência, a diferença entre a coluna de ordenação e o número da linha também salta.

    Vamos ver isso passo a passo. Primeiro, a numeração:

    SELECT 
    Z.*, 
    Seq = Z.[Days], -- ordering column
    rn = ROW_NUMBER() OVER (ORDER BY Z.[Days]) -- numbering
FROM dbo.z_test_duration AS Z
WHERE Z.[Status] = 'on';
    Dias Status Seq rn
    01-01-2022 sobre 01-01-2022 1
    02-01-2022 sobre 02-01-2022 2
    03-01-2022 sobre 03-01-2022 3
    05-01-2022 sobre 05-01-2022 4
    07-01-2022 sobre 07-01-2022 5
    08-01-2022 sobre 08-01-2022 6

    Observe que os Seqvalores aumentam na mesma taxa rnaté que haja uma lacuna. Podemos ver isso mais claramente subtraindo rndo Seqvalor.

    A única pequena complicação aqui é Sequm date, então precisamos convertê-lo em um número antes de subtrair. Eu usei a DATEDIFFfunção aqui, mas qualquer método consistente de transformar uma data em um número serviria.

    SELECT 
    Z.*, 
    Seq = Z.[Days],
    diff = 
        DATEDIFF(DAY, '2022-01-01', Z.[Days]) - 
            ROW_NUMBER() OVER (
                ORDER BY Z.[Days]) 
FROM dbo.z_test_duration AS Z
WHERE Z.[Status] = 'on';
    Dias Status Seq diferença
    01-01-2022 sobre 01-01-2022 -1
    02-01-2022 sobre 02-01-2022 -1
    03-01-2022 sobre 03-01-2022 -1
    05-01-2022 sobre 05-01-2022 0
    07-01-2022 sobre 07-01-2022 1
    08-01-2022 sobre 08-01-2022 1

    Os diffvalores são os mesmos para cada elemento contíguo em um grupo.

    Agora que sabemos como agrupar, a consulta final segue diretamente:

    SELECT
    ON_DATE = MIN(G.Seq), 
    OFF_DATE = DATEADD(DAY, 1, MAX(G.Seq)),
    COUNT_OF_ACTIVE_DAYS = 1 + DATEDIFF(DAY, MIN(G.Seq), MAX(G.Seq))
FROM 
(
    SELECT 
        Z.*, 
        Seq = Z.[Days],
        grp = 
            DATEDIFF(DAY, '2022-01-01', Z.[Days]) - 
                ROW_NUMBER() OVER (
                    ORDER BY Z.[Days]) 
    FROM dbo.z_test_duration AS Z
    WHERE Z.[Status] = 'on'
) AS G
GROUP BY G.grp;
    NA DATA OFF_DATE COUNT_OF_ACTIVE_DAYS
    01-01-2022 04-01-2022 3
    05-01-2022 06-01-2022 1
    07-01-2022 09-01-2022 2

    db<> demonstração online de violino

    • 4

relate perguntas