Me gustaría usar una función de ventana para determinar, para cada fila, el número total de registros anteriores que cumplan 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 unodate el conteo de filas dentro de '1 hora anterior' a esodate.

¿Puedo hacer esto con una función de ventana? ¿O necesito investigar CTE's?

Tengo muchas ganas de 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 prueba contra sí mismo, como se muestra a continuación, pero esto no se puede 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;

¿Es esto 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 ir muy pronto. :)