Функции окна или общие табличные выражения: подсчитывать предыдущие строки в пределах диапазона
Я хотел бы использовать оконную функцию, чтобы определить для каждой строки общее количество предыдущих записей, соответствующих определенным критериям.
Конкретный пример:
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 - это не то, о чем я пока что знаю. У меня такое чувство, что я собираюсь очень скоро.:)
2 ответа
Я не думаю, что вы можете сделать это дешево с простым запросом, CTE и оконными функциями - их определение кадра является статическим, но вам нужен динамический кадр.
Как правило, вам нужно тщательно определить нижнюю и верхнюю границу окна: следующие запросы исключают текущую строку и включают нижнюю границу.
Есть еще небольшое отличие: функция включает в себя предыдущие одноранговые узлы текущей строки, а коррелированный подзапрос исключает их...
Прецедент
С помощью ts вместо зарезервированного слова date как имя столбца.
CREATE TEMP TABLE test (
id bigint
,ts timestamp
);
ROM - запрос Романа
Используйте CTE, объединяйте временные метки в массив, снимайте, подсчитывайте...
Несмотря на правильность, производительность резко ухудшается, если на нем больше строк. Здесь есть пара убийц производительности. Увидеть ниже.
ARR - считать элементы массива
Я взял запрос Романа и попытался немного его упростить:
- Удалить 2-й CTE, который не нужен.
- Преобразуйте 1-й CTE в подзапрос, что быстрее.
- Непосредственный count() вместо повторной агрегации в массив и подсчета с array_length(),
Но обработка массивов стоит дорого, а производительность все еще сильно ухудшается с увеличением количества строк.
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 - коррелированный подзапрос
Вы можете решить это с помощью простого и некрасивого подзапроса. Намного быстрее, но все же...
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 - Функция
Обведите ряды в хронологическом порядке с помощью row_number() в функции plpgsql и объедините это с курсором на тот же запрос, охватывающий желаемый период времени. Тогда мы можем просто вычесть номера строк. Должен выступать красиво.
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;
Вызов:
SELECT * FROM running_window_ct();
эталонный тест
Используя приведенную выше таблицу, я провел быстрый тест на моем старом тестовом сервере: PostgreSQL 9.1.9 для 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
Я варьировал жирную часть для каждого пробега и взял лучшее из 5 с EXPLAIN ANALYZE,
100 рядов
ROM: 27,656 мс
ARR: 7,834 мс
COR: 5,488 мс
FNC: 1,115 мс
1000 строк
ROM: 2116,029 мс
ARR: 189,679 мс
COR: 65,802 мс
FNC: 8,466 мс
5000 строк
ROM: 51347 мс!!
ARR: 3167 мс
COR: 333 мс
FNC: 42 мс
100000 строк
ROM: DNF
ARR: DNF
COR: 6760 мс
FNC: 828 мс
Функция - явный победитель. Он самый быстрый на порядок и лучше всего масштабируется.
Обработка массива не может конкурировать.
обновить Моя предыдущая попытка неэффективна, потому что она объединяет все элементы в массив, и это не то, что я хотел сделать. Итак, вот обновленная версия - она не так эффективна, как самостоятельное соединение или функция с курсорами, но она не так ужасна, как моя предыдущая:
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;
Я проверил на своей локальной машине и в sqlfiddle, и на самом деле лучше было выполнить самостоятельное объединение (я был удивлен, мои результаты не совпадают с Erwin), затем функция Erwin и затем этот агрегат. Вы можете проверить это самостоятельно в sqlfiddle
предыдущее Я все еще изучаю PostgreSQL, но мне очень нравятся все возможности. Если бы это был SQL Server, я бы использовал select для xml и select из xml. Я не знаю, как это сделать в PostreSQL, но есть гораздо лучшие вещи для этой задачи - массивы!!!
Итак, вот мой CTE с оконными функциями (я думаю, что он будет работать некорректно, если в таблице есть дублирующиеся даты, и я также не знаю, будет ли он работать лучше, чем self join):
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
см. демо sql fiddle
надеюсь, это поможет