row_number - sql server rank() over

Funciones de ventana o expresiones de tabla comunes: cuenta las filas anteriores dentro del rango

Question

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. :)

Answer 1

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

ver demostración fiddle sql

Espero que ayude

Answer 2

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();

SQL Fiddle.

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.