Запросы временных рядов в Postgres

Это ответ на вопрос из ответа @Erwin на Эффективный запрос временных рядов в Postgres.

Для простоты я буду использовать ту же структуру таблицы, что и этот вопрос.

id | widget_id | for_date | score |

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

Пример данных:

INSERT INTO score (id, widget_id, for_date, score) values
(1, 1337, '2012-04-07', 52),
(2, 2222, '2012-05-05', 99),
(3, 1337, '2012-05-07', 112),
(4, 2222, '2012-05-07', 101);

Когда я запрашиваю диапазон с 5 по 10 мая 2012 года (т.е. generate_series('2012-05-05'::date, '2012-05-10'::date, '1d')), я хотел бы получить следующее:

DAY          WIDGET_ID  SCORE
May, 05 2012    1337    52
May, 05 2012    2222    99
May, 06 2012    1337    52
May, 06 2012    2222    99
May, 07 2012    1337    112
May, 07 2012    2222    101
May, 08 2012    1337    112
May, 08 2012    2222    101
May, 09 2012    1337    112
May, 09 2012    2222    101
May, 10 2012    1337    112
May, 10 2012    2222    101

Лучшее решение на данный момент (также от @Erwin):

SELECT a.day, a.widget_id, s.score
FROM  (
   SELECT d.day, w.widget_id
         ,max(s.for_date) OVER (PARTITION BY w.widget_id ORDER BY d.day) AS effective_date
   FROM  (SELECT generate_series('2012-05-05'::date, '2012-05-10'::date, '1d')::date AS day) d
   CROSS  JOIN (SELECT DISTINCT widget_id FROM score) AS w
   LEFT   JOIN score s ON s.for_date = d.day AND s.widget_id = w.widget_id
   ) a
LEFT JOIN  score s ON s.for_date = a.effective_date AND s.widget_id = a.widget_id
ORDER BY a.day, a.widget_id;

Но, как вы можете видеть в этом скрипте SQL, он выдает нулевые оценки для виджет 1337 в первые два дня. Я хотел бы видеть более раннюю оценку 52 из строки 1 на своем месте.

Можно ли сделать это эффективным способом?


postgresql sql greatest-n-per-group time-series generate-series
person bpaul    schedule 18.10.2013    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Как упомянул @Roman, DISTINCT ON может решить эту проблему. Подробности в этом связанном ответе:

Однако подзапросы, как правило, немного быстрее, чем CTE:

SELECT DISTINCT ON (d.day, w.widget_id)
       d.day, w.widget_id, s.score
FROM   generate_series('2012-05-05'::date, '2012-05-10'::date, '1d') d(day)
CROSS  JOIN (SELECT DISTINCT widget_id FROM score) AS w
LEFT   JOIN score s ON s.widget_id = w.widget_id AND s.for_date <= d.day
ORDER  BY d.day, w.widget_id, s.for_date DESC;

Вы можете использовать функцию возврата набора, например таблицу в списке FROM.

скрипт SQL

Один многоколоночный индекс должен быть ключом к производительности:

CREATE INDEX score_multi_idx ON score (widget_id, for_date, score)

Третий столбец score включен только для того, чтобы сделать его покрывающим индексом в Postgres 9.2 или более поздней версии . Вы бы не включили его в более ранние версии.

Конечно, если у вас много виджетов и широкий диапазон дней, CROSS JOIN выдает много строк, что имеет свою цену. Выбирайте только те виджеты и дни, которые вам действительно нужны.

person Erwin Brandstetter    schedule 18.10.2013
comment
Это работает, но, кажется, действительно замедляется по мере увеличения количества строк. У меня 40-50 тыс. строк, и это занимает более 2 минут. Замедляется ли количество записей в перекрестном объединении? - person bpaul; 19.10.2013
comment
@bpaul у вас есть индексы на вашей таблице? - person Roman Pekar; 19.10.2013
comment
@bpaul: В частности, многоколоночный индекс (возможно, охватывающий). Я добавил некоторые детали. - person Erwin Brandstetter; 19.10.2013
comment
@RomanPekar, @Erwin в настоящее время я индексирую widget_id и for_date отдельно. Я добавлю многоколоночный индекс и отчитаюсь. У меня Postgres 9.1.10, поэтому я сделаю widget_id, for_date. - person bpaul; 19.10.2013
comment
Многоколоночный индекс не очень помог. Теперь я кэширую значения для больших запросов в сводных таблицах. - person bpaul; 24.10.2013

arrow_upward
1
arrow_downward

Как вы написали, вы должны найти совпадающую оценку, но если есть пробел - заполните его ближайшей более ранней оценкой. В SQL это будет:

SELECT d.day, w.widget_id, 
  coalesce(s.score, (select s2.score from score s2
    where s2.for_date<d.day and s2.widget_id=w.widget_id order by s2.for_date desc limit 1)) as score
from (select distinct widget_id FROM score) AS w
cross join (SELECT generate_series('2012-05-05'::date, '2012-05-10'::date, '1d')::date AS day) d
left join score s ON (s.for_date = d.day AND s.widget_id = w.widget_id)
order by d.day, w.widget_id;

Слияние в данном случае означает «если есть разрыв».

person Tomasz Myrta    schedule 18.10.2013
comment
Отличное решение, спасибо, это пока самое быстрое решение для больших наборов данных. - person bpaul; 19.10.2013

arrow_upward
1
arrow_downward

Вы можете использовать синтаксис distinct on в PostgreSQL.

with cte_d as (
    select generate_series('2012-05-05'::date, '2012-05-10'::date, '1d')::date as day
), cte_w as (
    select distinct widget_id from score
)
select distinct on (d.day, w.widget_id)
    d.day, w.widget_id, s.score
from cte_d as d
    cross join cte_w as w
    left outer join score as s on s.widget_id = w.widget_id and s.for_date <= d.day
order by d.day, w.widget_id, s.for_date desc;

или получить максимальную дату по подзапросу:

with cte_d as (
    select generate_series('2012-05-05'::date, '2012-05-10'::date, '1d')::date as day
), cte_w as (
    select distinct widget_id from score
)
select
    d.day, w.widget_id, s.score
from cte_d as d
    cross join cte_w as w
    left outer join score as s on s.widget_id = w.widget_id
where
    exists (
        select 1
        from score as tt
        where tt.widget_id = w.widget_id and tt.for_date <= d.day
        having max(tt.for_date) = s.for_date
    )
order by d.day, w.widget_id;

Производительность действительно зависит от индексов, которые у вас есть в вашей таблице (уникальные widget_id, for_date, если это возможно). Я думаю, что если у вас много строк для каждого widget_id, то второй будет более эффективным, но вы должны проверить его на своих данных.

>> демонстрация скрипки sql ‹‹

person Roman Pekar    schedule 18.10.2013
comment
Спасибо за ответ. Кажется, что выбрать отличный вариант, но я думаю, что решение @Erwins чище и эффективнее. - person bpaul; 19.10.2013