Tengo una consulta siguiente:
WITH t as ( SELECT date_trunc('hour', time_series) as trunc FROM generate_series('2013-02-27 22:00'::timestamp, '2013-02-28 2:00', '1 hour') as time_series GROUP BY trunc ORDER BY trunc ) SELECT DISTINCT ON(trunc) trunc, id FROM t LEFT JOIN ( SELECT id, created, date_trunc('hour', created) as trunc_u FROM event ORDER BY created DESC ) u ON trunc = trunc_u que produce el siguiente resultado:
"2013-02-27 22:00:00"; "2013-02-27 23:00:00";2 "2013-02-28 00:00:00";5 "2013-02-28 01:00:00"; "2013-02-28 02:00:00";
El event Table tiene id , created y algunas otras columnas, pero solo aquellas son relevantes aquí. La consulta anterior me da la id del último evento generado por cada período de time del trunc (gracias a DISTINCT ON obtengo una buena agregación por período).
Ahora, esta consulta arroja NULL si no sucedieron events en un período de time determinado. Me gustaría que devuelva el id disponible anterior, incluso si es de un período de time diferente. Es decir:
"2013-02-27 22:00:00";0 "2013-02-27 23:00:00";2 "2013-02-28 00:00:00";5 "2013-02-28 01:00:00";5 "2013-02-28 02:00:00";5
Estoy seguro de que me falta una manera fácil de lograr esto. ¿Algún consejo?
Tratar:
WITH t as ( SELECT time_series as trunc FROM generate_series('2013-02-27 22:00'::timestamp, '2013-02-28 2:00', '1 hour') as time_series ) SELECT DISTINCT ON(t.trunc) t.trunc, e.id FROM t JOIN event e ON e.created < t.trunc ORDER BY t.trunc, e.created DESC
Si es demasiado lento, dime. Le daré una consulta más rápida.
Usted puede mezclar una auto unión y las funciones de Windows
Simplificando tomo esta tabla con estos valores de muestra:
create table t ( a int, b int); insert into t values ( 1, 1), ( 2, Null), ( 3, Null), ( 4, 2 ), ( 5, Null), ( 6, Null);
En su consulta, a es trunc_u b es su id . La consulta es:
with cte as ( select t1.a, coalesce( t1.b, t2.b, 0) as b, rank() OVER (PARTITION BY t1.a ORDER BY t2.a DESC) as pos from t t1 left outer join t t2 on t2.b is not null and t2.a < t1.a ) select a, b from cte where pos = 1;
Y resultados :
| A | B | --------- | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 2 | | 6 | 2 |