Gostaria de usar uma função de janela para determinar, para cada linha, o número total de registros precedentes que atendem a determinados critérios.

Um exemplo específico:

clone=# \d test
              Table "pg_temp_2.test"
 Column |            Type             | Modifiers 
--------+-----------------------------+-----------
 id     | bigint                      | 
 date   | timestamp without time zone | 

Eu gostaria de saber para cadadate a contagem de linhas dentro de "1 hora anterior" àqueladate.

Posso fazer isso com uma função de janela? Ou eu preciso investigar CTE's?

Eu realmente quero ser capaz de escrever algo como (não funciona):

SELECT id, date, count(*) OVER (HAVING previous_rows.date >= (date - '1 hour'::interval))
FROM test;

Eu posso escrever isso juntando teste contra si mesmo, como abaixo - mas isso não será escalável com tabelas 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;

Isso é algo que eu poderia fazer com uma consulta recursiva? Ou um CTE normal? CTEs não são algo que eu sei muito sobre ainda. Tenho a sensação de que vou em breve. :)