Início / dba / Perguntas / 96974
Accepted
hank
Asked: 2015-04-04 04:06:25 +0800 CST

Verifique se existe intervalo de data contíguo

  • 772

Tenho uma tabela no PostgreSQL que descreve alguns eventos que possuem uma data inicial e uma data final:

CREATE TABLE my_table
(
  event_id serial NOT NULL,
  start_date timestamp without time zone NOT NULL,
  end_date timestamp without time zone NOT NULL
)

Um único evento pode se sobrepor ao anterior e ao seguinte. Na tabela a seguir, os primeiros 3 dos 4 eventos formam um intervalo de tempo contíguo:

1   '2015-04-02 22:09:03'   '2015-04-02 22:19:05'
2   '2015-04-02 22:17:38'   '2015-04-02 22:27:38'
3   '2015-04-02 22:25:21'   '2015-04-02 22:36:23'
4   '2015-04-02 22:45:23'   '2015-04-02 22:55:23'

É possível escrever uma consulta que verifique se existe um intervalo de data contíguo entre duas datas especificadas?

Eu gostaria de ter algo como:

select ...
from my_table
where start_date > '2015-04-02' and end_date < '2015-04-06'
postgresql window-functions

2 respostas

  1. Best Answer

    Primeiro, combinamos intervalos que se sobrepõem para encontrar todas as "ilhas" contíguas dos intervalos:

    with c as
  ( select *, max(end_date) over (order by start_date
                                  rows between unbounded preceding
                                           and 1 preceding)
                as previous_max
    from my_table
  )
select start_date, 
       coalesce(lead(previous_max) over (order by start_date),
                (select max(end_date) from my_table)
               ) as end_date
from c 
where previous_max < start_date 
   or previous_max is null ;

    Depois disso, é fácil verificar se um determinado intervalo está completamente rodeado por uma das ilhas contíguas encontradas.

       with c as
  ( select *, max(end_date) over (order by start_date
                                  rows between unbounded preceding
                                           and 1 preceding)
                as previous_max
    from my_table
  ) ,
cont as
  ( select start_date, 
           coalesce(lead(previous_max) over (order by start_date),
                    (select max(end_date) from my_table)
                   ) as end_date
    from c 
    where previous_max < start_date 
       or previous_max is null  
   )
select *
from cont
where tsrange(start_date, end_date)
       @>                             -- contains
      tsrange('2015-04-02 22:10:00', '2015-04-02 22:30:00') 
limit 1 ;

    Teste no SQLfiddle

    • 5

  2. Esta consulta retorna todas as lacunas nos dados que se sobrepõem ao intervalo de tempo fornecido. Também cobre gaps iniciais , finais ou totais (cobrindo):

    WITH input(t1, t2) AS (SELECT '2015-04-02 22:00'::timestamp
                            , '2015-04-02 23:00'::timestamp)  -- t1 < t2
,    gap  AS (
   SELECT max(end_date) OVER (ORDER BY start_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS start_gap
        , start_date AS end_gap
        , end_date  -- for possible trailing gap
   FROM   my_table
   WHERE  end_date   >= (SELECT t1 FROM input)  -- exclude rows early
   AND    start_date <= (SELECT t2 FROM input)
   )
SELECT start_gap, end_gap
FROM   gap
WHERE  end_gap > start_gap  -- gap within time range
   OR  start_gap IS NULL AND end_gap > (SELECT t1 FROM input) -- leading gap

UNION ALL
SELECT max(end_date), NULL  -- trailing gap
FROM   gap
HAVING max(end_date) < (SELECT t2 FROM input)

UNION ALL
SELECT NULL, NULL  -- total gap
WHERE  NOT EXISTS (SELECT 1 FROM gap)
ORDER  BY end_gap;

    Se nada for retornado, a verificação é bem-sucedida:

    verifica se existe um intervalo de data contíguo entre duas datas fornecidas?

    Precisa apenas de uma única varredura na mesa base e uma única função de janela. Deve ser rápido .

    Se você não estiver familiarizado com as funções da janela, leia este capítulo do manual .

    Relacionado:

    Sobre OVERLAPS:

    Lacunas de contagem de funções

    Para uma resposta rápida à sua pergunta sem detalhes, a consulta pode ser simplificada. Além disso, a manipulação de parâmetros é mais conveniente em uma função:

    CREATE OR REPLACE FUNCTION f_find_gaps(t1 timestamp, t2 timestamp)
  RETURNS int
  LANGUAGE sql AS
$func$
WITH gap AS (
   SELECT max(end_date) OVER (ORDER BY start_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS start_gap
        , start_date AS end_gap
        , end_date  -- for possible trailing gap & total gap
   FROM   my_table
   WHERE  end_date   >= t1  -- exclude rows early
   AND    start_date <= t2
   )
SELECT count(*)::int
     + CASE WHEN (SELECT max(end_date) >= t2 FROM gap) THEN 0 ELSE 1 END
     -- covers trailing gap & total gap
FROM   gap
WHERE  end_gap > start_gap  -- gap within time range
   OR  start_gap IS NULL AND end_gap > t1  -- leading gap
$func$;

    Ligar:

    SELECT f_find_gaps('2015-04-02 22:00', '2015-04-02 23:00');

    Retorna o número de lacunas. 0significa que seu alcance está coberto.

    • 3

relate perguntas