Função da janela PostgreSQL: partição por comparação
Eu estou tentando encontrar a maneira de fazer uma comparação com a linha atual na cláusula PARTITION BY em uma função WINDOW na consulta PostgreSQL.
Imagine que tenho a lista curta na consulta a seguir desses 5 elementos (no caso real, tenho milhares ou até milhões de linhas). Estou tentando obter para cada linha, a identificação do próximo elemento diferente (coluna de eventos) e a identificação do elemento diferente anterior.
WITH events AS(
SELECT 1 as id, 12 as event, '2014-03-19 08:00:00'::timestamp as date
UNION SELECT 2 as id, 12 as event, '2014-03-19 08:30:00'::timestamp as date
UNION SELECT 3 as id, 13 as event, '2014-03-19 09:00:00'::timestamp as date
UNION SELECT 4 as id, 13 as event, '2014-03-19 09:30:00'::timestamp as date
UNION SELECT 5 as id, 12 as event, '2014-03-19 10:00:00'::timestamp as date
)
SELECT lag(id) over w as previous_different, event
, lead(id) over w as next_different
FROM events ev
WINDOW w AS (PARTITION BY event!=ev.event ORDER BY date ASC);
Eu sei a comparaçãoevent!=ev.event
está incorreto, mas é esse o ponto que desejo alcançar.
O resultado obtido é (o mesmo que se eu excluir a cláusula PARTITION BY):
|12|2
1|12|3
2|13|4
3|13|5
4|12|
E o resultado que eu gostaria de obter é:
|12|3
|12|3
2|13|5
2|13|5
4|12|
Alguém sabe se é possível e como? Muito obrigado!
EDITAR: Eu sei que posso fazer isso com doisJOIN
s, umORDER BY
e umDISTINCT ON
, mas no caso real de milhões de linhas, é muito ineficiente:
WITH events AS(
SELECT 1 as id, 12 as event, '2014-03-19 08:00:00'::timestamp as date
UNION SELECT 2 as id, 12 as event, '2014-03-19 08:30:00'::timestamp as date
UNION SELECT 3 as id, 13 as event, '2014-03-19 09:00:00'::timestamp as date
UNION SELECT 4 as id, 13 as event, '2014-03-19 09:30:00'::timestamp as date
UNION SELECT 5 as id, 12 as event, '2014-03-19 10:00:00'::timestamp as date
)
SELECT DISTINCT ON (e.id, e.date) e1.id, e.event, e2.id
FROM events e
LEFT JOIN events e1 ON (e1.date<=e.date AND e1.id!=e.id AND e1.event!=e.event)
LEFT JOIN events e2 ON (e2.date>=e.date AND e2.id!=e.id AND e2.event!=e.event)
ORDER BY e.date ASC, e.id ASC, e1.date DESC, e1.id DESC, e2.date ASC, e2.id ASC