Блог

Главная - Вопросы по программированию - скользящее среднее за 3 месяца с пропущенными месяцами

скользящее среднее за 3 месяца с пропущенными месяцами

#sql #postgresql

Вопрос:

Я читал здесь соответствующие вопросы, и до сих пор решения требуют, чтобы не было пропущенных месяцев. Хотели бы получить некоторую помощь в том, что я могу сделать, если пропадут месяцы?

Например, я хотел бы рассчитать 3-месячное скользящее среднее количество заказов на единицу товара. Если для товара отсутствует месяц, при расчете предполагается, что количество заказов на этот товар за этот месяц равно 0. Если осталось меньше трех месяцев, скользящее среднее значение не так важно (оно может быть нулевым или иным).

 MONTH   | ITEM | ORDERS | ROLLING_AVG
2021-04 | A    | 5      |  3.33
2021-04 | B    | 4      |  3
2021-03 | A    | 3      |  1.66
2021-03 | B    | 5      |  null
2021-02 | A    | 2      |  null
2021-01 | B    | 2      |  null

Заранее большое спасибо!

Кроме того, есть ли способ «добавить» недостающие строки месяца без использования перекрестного соединения со списком элементов? Например, если у меня 10 миллионов элементов, выполнение перекрестного соединения занимает довольно много времени.

Комментарии:

1. Заполнить недостающие месяцы? Вам не нужна строка, чтобы выполнить расчет скользящего среднего, но если вы хотите, чтобы в результатах отображался месяц, этот месяц должен где -то существовать в виде строки. Я рекомендую таблицу календаря, а затем использовать calendar CROSS JOIN item_table LEFT JOIN orders ON orders.month = calendar.month AND orders.item = item_table.item

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

3. Это пример того, почему вы всегда должны включать SQL, который вы пробовали, в свой вопрос.

Ответ №1:

Вы можете использовать range оконную раму-и некоторую условную логику:

 select t.*,
       (case when min(month) over (partition by item) <= month - interval '2 month'
             then sum(orders) over (partition by item
                                    order by month
                                    range between interval '2 month' preceding and current row
                                   ) / 3.0
        end) as rolling_average
from t;

Вот скрипка db<>. Результаты немного отличаются от того, что указано в вашем вопросе, потому что для A в 2021-03 недостаточно информации, но для B в 2021-03 ее достаточно.

Комментарии:

1. Спасибо вам за помощь! Я никогда не знал, что мы можем использовать диапазон между временным интервалом и строкой. Сегодня узнал кое-что новое. Это намного быстрее, чем создание дополнительных таблиц для перекрестных соединений.

Метки: