Início / dba / Perguntas / 138252
Accepted
Gonzalo Vasquez
Asked: 2016-05-12 09:55:21 +0800 CST

Encontrar intervalos de tempo em várias linhas

  • 772

Eu tenho uma tabela com (suponha) apenas 2 colunas:

- fecha (a date)
- ignicion (0/1 representing boolean state)

O objetivo é recuperar quais períodos de tempo a ignição foi mantida em '1', para tal tarefa, descobri uma abordagem iterativa como a das seguintes consultas:

select min(fecha) from reports.avl_historico_11557000631206 where ignicion=1  and fecha > '2016-05-11 00:00:00-03' and fecha < '2016-05-12 00:00:00-03'; 

select min(fecha) from reports.avl_historico_11557000631206 where ignicion=0  and fecha > '2016-05-11 03:01:38+00' and fecha < '2016-05-12 00:00:00-03';

select min(fecha) from reports.avl_historico_11557000631206 where ignicion=1  and fecha > '2016-05-11 03:42:19+00' and fecha < '2016-05-12 00:00:00-03';

Programar isso não é problema, mas executá-lo pode ser um problema, já que o relatório pode envolver um mês inteiro de dados, e não apenas de uma única tabela sendo "varrida", mas cerca de 500 com a mesma estrutura de dados (apenas nomes de tabelas diferentes ).

Alguma ideia de como isso poderia ser alcançado de uma maneira mais limpa/melhor/rápida?

Fui aconselhado sobre as funções " janela ", mas realmente não sei se alguma delas realmente se encaixa nessa tarefa.

postgresql postgresql-9.4

1 respostas

  1. Best Answer

    Isso pode ser surpreendentemente simples com uma aplicação inteligente de uma função de janela.
    Com base nesta definição de tabela:

    CREATE TEMP TABLE tbl (
  fecha timestamp PRIMARY KEY
, ignicion bool NOT NULL  -- use an actual boolean!!
);

    Consulta:

    SELECT period, min(fecha), max(fecha), count(*) AS row_ct
FROM  (
   SELECT *, count(*) FILTER (WHERE NOT ignicion) OVER (ORDER BY fecha) AS period
   FROM   tbl
   ) sub
WHERE  ignicion
GROUP  BY period
ORDER  BY period;

    ... combinando a nova FILTERcláusula agregada (pág. 9.4+) com uma função de janela em uma subconsulta.

    O truque é calcular uma contagem contínua de linhas ignicion = FALSEna subconsulta e, em seguida, agrupar as linhas com ignicion = TRUEessa contagem.

    Se fechanão for exclusivo ou uma das colunas puder ser NULL, você precisará fazer mais.

    • 2

relate perguntas