Chciałbym użyć funkcji okna do określenia, dla każdego wiersza, całkowitej liczby poprzednich rekordów spełniających określone kryteria.

Konkretny przykład:

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

Chciałbym wiedzieć dla każdegodate liczba wierszy w ciągu „1 godziny wcześniej” do tegodate.

Czy mogę to zrobić za pomocą funkcji okna? Czy muszę zbadać CTE?

Naprawdę chcę móc napisać coś takiego (nie działa):

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

Mogę to napisać, łącząc test przeciwko sobie, jak poniżej - ale nie będzie to skalowane ze szczególnie dużymi tabelami.

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;

Czy jest to coś, co mogę zrobić z zapytaniem rekurencyjnym? Lub zwykły CTE? CTE nie są jeszcze czymś, o czym jeszcze wiele wiem. Mam wrażenie, że niedługo pójdę. :)