Блог

У меня есть гипертекстовая таблица в масштабе времени, которая называется prices , которая выглядит следующим образом:

Где каждая строка представляет собой измерение ( value ) в заданную метку времени ( ts ) для данного прибора ( instrumentId ). Каждый инструмент поступает в разное время, и у меня нет гарантии, что у меня будет точка данных для данного инструмента в заданное время.

Моя гипертаблица использует столбец временной метки в качестве индекса времени, и для дополнительного контекста у меня есть ~200 миллионов строк, охватывающих несколько лет субсекундных данных для десятков инструментов.

Я хочу иметь возможность генерировать 5-секундные невзвешенные исторические средние значения для каждого инструмента для каждого измерения, которое у меня есть. Для каждой строки в моей таблице я хочу иметь возможность найти все строки, в которых используется один и тот же инструмент, и иметь метку времени между (row_timestamp, row_timestamp - 5 seconds) ними (этот набор будет включать строку, для которой мы генерируем данные). Затем я хотел бы взять среднее значение этого, но разумно, что я хотел бы взять и другие статистические данные из этого (stdev, сумма и т. Д.).

Результат этого запроса для приведенной выше таблицы примера будет выглядеть следующим образом:

Я могу добиться этого небольшими партиями данных, объединив таблицу против самой себя, однако это очень неэффективное решение, и я знаю, что правильный способ достижения этого будет иметь какое-то преимущество под капотом. Запрос для этого выглядит следующим образом:

 WITH lhs AS (
    SELECT 
        ts, instrumentId, value, rank() OVER (ORDER BY ts)
    FROM prices
),
splay AS (
    SELECT
        lhs.instrumentId, lhs.ts, lhs.rank, rhs.value
    FROM lhs as rhs
    JOIN lhs
    ON 
        lhs.instrumentId=rhs.instrumentId
        AND rhs.ts BETWEEN lhs.ts - INTERVAL '5s' AND lhs.ts
    ORDER BY lhs.instrumentId, lhs.rank, rhs.rank
)
SELECT
    MAX(instrumentId), min(ts), AVG(value)
FROM splay
GROUP BY rank;
 

Вышесказанное занимает ~2 минуты для работы с данными в течение одного дня, но я могу выполнить операцию в pandas с данными за 1 год за ~45 секунд, поэтому я уверен, что в SQL есть лучший способ сделать это.

Как я могу добиться эффективных функций сгруппированных окон в timescaledb/postgres?

Я думаю, что вы ищете time_bucket группу рядом, так что что-то вроде:

 SELECT time_bucket('5s'::interval, ts), instrumentID, avg(value)
FROM prices
GROUP BY time_bucket('5s'::interval, ts), instrumentID;
 

Это сгруппируется по метке времени в начале диапазона, если вы хотите получить конец, вы можете просто добавить к нему «5s»…

Если вы пытаетесь сделать это для каждой строки в исходном наборе данных, то вам следует использовать оконную функцию с предложением partition by и предложением range следующим образом:

 SELECT *, avg(value) OVER (PARTITION BY instrumentId ORDER BY ts RANGE '5s' PRECEDING)
FROM prices;