Блог

Я хочу вычислить сумму 29 предыдущих дней в строке 30-го дня, я использую функцию фильтра и окна, но FILTER она не работает,

он по-прежнему суммируется от начала до конца, если я использую:

 Select *, Sum(quantity) filter (where time between time - interval '29 day' and time) over ()
from t1 
  

он показывает нулевой столбец, если я использую:

 Select *, Sum(quantity) filter (where time between time - interval '29 day' and time - interval '1 day') over ()
from t1
  

Данные, я уменьшаю столбцы для простоты

 Time        sum_quantity
2020-01-01  1
2020-01-02  2
2020-01-03  3
2020-01-04  6
    ....
2020-01-30  100
  

Тип данных: время — дата, а количество — целое число

Желаемый результат: должен иметь тот же столбец, что и первая таблица, и добавить этот столбец движущейся суммы

День 30 = общее количество дней с 1 по 29 день за каждые 30 дней

Как это исправить

Вам нужна оконная функция с определением рамки окна с помощью range :

 select t1.*,
       sum(quantity) over (order by time
                           range between interval '29 day' preceding and current row
                          ) 
from t1 ;
  

Редактировать:

Если у вас есть данные для всех дат, вы можете использовать rows :

 select t1.*,
       sum(quantity) over (order by time
                           rows between 29 preceding and current row
                          ) 
from t1 ;
  

ПРАВКА II:

Если вам нужно разобраться с пропущенными днями в старых версиях Postgres, которые не поддерживаются range , то расширение данных, вероятно, является самым простым методом:

 select t1.*,
       sum(quantity) over (order by time
                           rows between 29 preceding and current row
                           ) 
from (select generate_series(min(t1.time), max(t1.time), interval '1 day') as dte
      from t1
     ) d left join
     t1
     on d.dte = t1.time;
  

Возможно, вы захотите отфильтровать дополнительные строки:

 select t1.*
from (select t1.*,
             sum(quantity) over (order by time
                                 rows between 29 preceding and current row
                                 ) as running_sum
      from (select generate_series(min(t1.time), max(t1.time), interval '1 day') as dte
            from t1
           ) d left join
           t1
           on d.dte = t1.time
     ) t1
where t1.time is not null;
  

Ваше filter (where) предложение всегда истинно, а пустое over() предложение — это окно, охватывающее весь набор результатов.

Вы должны указать окно в over предложении, а не filter в предложении. Возможно, вам нужно что-то вроде

 sum(quantity) over (order by time rows between 29 preceding and current row)
  

или лучше range between... .