Функции окна или общие табличные выражения: подсчитывать предыдущие строки в пределах диапазона
Я хотел бы использовать оконную функцию, чтобы определить для каждой строки общее количество предыдущих записей, соответствующих определенным критериям.
Конкретный пример:
clone=# \d test
Table "pg_temp_2.test"
Column | Type | Modifiers
--------+-----------------------------+-----------
id | bigint |
date | timestamp without time zone |
Я хотел бы знать для каждогоdate
количество строк за 1 час до этогоdate
.
Могу ли я сделать это с помощью оконной функции? Или мне нужно исследовать CTE?
Я действительно хочу иметь возможность написать что-то вроде (не работает):
SELECT id, date, count(*) OVER (HAVING previous_rows.date >= (date - '1 hour'::interval))
FROM test;
Я могу написать это, присоединившись к тесту против себя, как показано ниже - но это не будет масштабироваться с особенно большими таблицами.
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;
Это то, что я мог бы сделать с рекурсивным запросом? Или обычный CTE? CTE - это не то, о чем я пока что знаю. У меня такое чувство, что я собираюсь очень скоро. :)