row_number - sql server rank() over
Funciones de ventana o expresiones de tabla comunes: cuenta las filas anteriores dentro del rango (2)
actualización Mi intento anterior no funciona bien, porque combina todos los elementos en una matriz, y eso no es lo que quería hacer. Así que aquí hay una versión actualizada: no funciona tan bien como uno mismo o funciona con cursores, pero no es tan terrible como mi anterior:
CREATE OR REPLACE FUNCTION agg_array_range_func
(
accum anyarray,
el_cur anyelement,
el_start anyelement,
el_end anyelement
)
returns anyarray
as
$func$
declare
i int;
N int;
begin
N := array_length(accum, 1);
i := 1;
if N = 0 then
return array[el_cur];
end if;
while i <= N loop
if accum[i] between el_start and el_end then
exit;
end if;
i := i + 1;
end loop;
return accum[i:N] || el_cur;
end;
$func$
LANGUAGE plpgsql;
CREATE AGGREGATE agg_array_range
(
anyelement,
anyelement,
anyelement
)
(
SFUNC=agg_array_range_func,
STYPE=anyarray
);
select
id, ts,
array_length(
agg_array_range(ts, ts - interval ''1 hour'', ts) over (order by ts)
, 1) - 1
from test;
Lo probé en mi máquina local y en sqlfiddle, y en realidad me incorporé mejor (me sorprendió, mis resultados no son los mismos que Erwin), luego funcionó Erwin y luego este agregado. Puedes probarlo tú mismo en sqlfiddle
anterior Todavía estoy aprendiendo PostgreSQL, pero me gustan mucho todas las posibilidades. Si fuera SQL Server, usaría select para xml y seleccionaría de xml. No sé cómo hacerlo en PostreSQL, pero hay cosas mucho mejores para esa tarea: ¡matrices!
Así que aquí está mi CTE con funciones de ventana (creo que funcionaría incorrectamente si hay fechas duplicadas en la tabla, y tampoco sé si funcionaría mejor que la unión automática):
with cte1 as (
select
id, ts,
array_agg(ts) over(order by ts asc) as dates
from test
), cte2 as (
select
c.id, c.ts,
array(
select arr
from (select unnest(dates) as arr) as x
where x.arr >= c.ts - ''1 hour''::interval
) as dates
from cte1 as c
)
select c.id, c.ts, array_length(c.dates, 1) - 1 as cnt
from cte2 as c
Espero que ayude
Me gustaría usar una función de ventana para determinar, para cada fila, el número total de registros precedentes que cumplen con ciertos criterios.
Un ejemplo específico:
clone=# /d test
Table "pg_temp_2.test"
Column | Type | Modifiers
--------+-----------------------------+-----------
id | bigint |
date | timestamp without time zone |
Me gustaría saber para cada date el recuento de filas dentro de "1 hora anterior" a esa date .
¿Puedo hacer esto con una función de ventana? ¿O necesito investigar CTE?
Realmente quiero poder escribir algo como (no funciona):
SELECT id, date, count(*) OVER (HAVING previous_rows.date >= (date - ''1 hour''::interval))
FROM test;
Puedo escribir esto uniendo la prueba contra sí mismo, como se muestra a continuación, pero esto no se escalará con tablas particularmente grandes.
SELECT a.id, a.date, count(b.*)-1
FROM test a, test b
WHERE (b.date >= a.date - ''1 hour''::interval AND b.date < a.date)
GROUP BY 1,2
ORDER BY 2;
¿Esto es algo que podría hacer con una consulta recursiva? O un CTE regular? Los CTE no son algo de lo que sé mucho todavía. Tengo la sensación de que voy a hacerlo muy pronto. :)
No creo que pueda hacer esto a bajo costo con una consulta simple, CTE y funciones de ventana: su definición de marco es estática, pero necesita un marco dinámico .
En general, deberá definir cuidadosamente el borde inferior y superior de su ventana: las siguientes consultas excluyen la fila actual e incluyen el borde inferior.
Todavía hay una pequeña diferencia: la función incluye pares anteriores de la fila actual, mientras que la subconsulta correlacionada los excluye ...
Caso de prueba
Usar ts lugar de date palabra reservada como nombre de columna.
CREATE TEMP TABLE test (
id bigint
,ts timestamp
);
ROM - Consulta de Roman
Use CTE, agregue marcas de tiempo en una matriz, unnest, count ...
Si bien es correcto, el rendimiento se deteriora drásticamente con más de una mano llena de filas. Hay un par de asesinos de rendimiento aquí. Vea abajo.
ARR - cuenta los elementos de la matriz
Tomé la consulta de Roman e intenté racionalizarla un poco:
- Eliminar 2do CTE que no es necesario.
- Transforma 1er CTE en subconsulta, que es más rápido.
- count() directo count() lugar de volver a agregar en una matriz y contar con array_length() .
Pero el manejo de matrices es costoso, y el rendimiento aún se deteriora con más filas.
SELECT id, ts
,(SELECT count(*)::int - 1
FROM unnest(dates) x
WHERE x >= sub.ts - interval ''1h'') AS ct
FROM (
SELECT id, ts
,array_agg(ts) OVER(ORDER BY ts) AS dates
FROM test
) sub;
COR - subconsulta correlacionada
Podrías resolver esto con una subconsulta simple, fea y correlacionada. Mucho más rápido, pero aún así ...
SELECT id, ts
,(SELECT count(*)
FROM test t1
WHERE t1.ts >= t.ts - interval ''1h''
AND t1.ts < t.ts) AS ct
FROM test t
ORDER BY ts;
FNC - Función
row_number() un bucle sobre las filas en orden cronológico con un row_number() en la función plpgsql y combine eso con un cursor sobre la misma consulta, abarcando el marco de tiempo deseado. Entonces podemos simplemente restar números de fila. Debería funcionar muy bien.
CREATE OR REPLACE FUNCTION running_window_ct()
RETURNS TABLE (id bigint, ts timestamp, ct int) AS
$func$
DECLARE
i CONSTANT interval = ''1h''; -- given interval for time frame
cur CURSOR FOR
SELECT t.ts + i AS ts1 -- incremented by given interval
, row_number() OVER (ORDER BY t.ts) AS rn
FROM test t
ORDER BY t.ts; -- in chronological order
rec record; -- for current row from cursor
rn int;
BEGIN
OPEN cur; FETCH cur INTO rec; -- open cursor, fetch first row
ct := -1; -- init; -1 covers special case at start
FOR id, ts, rn IN
SELECT t.id, t.ts, row_number() OVER (ORDER BY t.ts)
FROM test t
ORDER BY t.ts -- in same chronological order as cursor
LOOP
IF rec.ts1 >= ts THEN -- still in range ...
ct := ct + 1; -- ... just increment
ELSE -- out of range ...
LOOP -- ... advance cursor
FETCH cur INTO rec;
EXIT WHEN rec.ts1 >= ts; -- earliest row within time frame
END LOOP;
ct := rn - rec.rn; -- new count
END IF;
RETURN NEXT;
END LOOP;
END
$func$ LANGUAGE plpgsql;
Llamada:
SELECT * FROM running_window_ct();
Punto de referencia
Con la tabla de arriba ejecuté un punto de referencia rápido en mi antiguo servidor de prueba: PostgreSQL 9.1.9 en Debian).
-- TRUNCATE test;
INSERT INTO test
SELECT g, ''2013-08-08''::timestamp
+ g * interval ''5 min''
+ random() * 300 * interval ''1 min'' -- halfway realistic values
FROM generate_series(1, 10000) g;
CREATE INDEX test_ts_idx ON test (ts);
ANALYZE test; -- temp table needs manual analyze
Varié la parte más atrevida de cada carrera y obtuve lo mejor de 5 con EXPLAIN ANALYZE .
100 filas
ROM: 27.656 ms
ARR: 7.834 ms
COR: 5.488 ms
FNC: 1.115 ms
1000 filas
ROM: 2116.029 ms
ARR: 189.679 ms
COR: 65.802 ms
FNC: 8.466 ms
5000 filas
ROM: 51347 ms !!
ARR: 3167 ms
COR: 333 ms
FNC: 42 ms
100000 filas
ROM: DNF
ARR: DNF
COR: 6760 ms
FNC: 828 ms
La función es el claro vencedor. Es más rápido en un orden de magnitud y escala mejor.
El manejo de matrices no puede competir.