Calculate moving sum/count by time condition using window function and filter PostgreSQL

user_12544460 0
Viewed 1466

I want to calculate the sum 29 previous days in the 30th-day row, I use filter and window function but the FILTER not work,

it still sums from the beginning to the end if I use:

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

it show null column if I use:

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

Data, I reduce columns for simplicity

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

Data type: Time is date and quantity is integer

Desired result: Should have the same column as first table and add this moving sum column

Day 30 = total quantity of day 1 to day 29, for every 30 days

How to fix this

moving-average window-functions postgresql sql
4 Answers

You want a window function with a window frame definition using range:

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

EDIT:

If you have data for all dates, you can use rows:

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

EDIT II:

If you need to deal with missing days in older versions of Postgres that do not support range, then expanding the data is probably the simplest method:

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;

You may want to filter out the additional rows:

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;
user_11440350

Your filter (where) clause is always true and empty over() clause is window spanned over all result set.

You should specify the window in over clause, not filter clause. Probably you need something like

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

or better range between....

user_6535390

Please, use condition in where caluse Because you use windows function it's like contional expression like:

SUM(<expression>) FILTER(WHERE <condition>)
SUM(CASE WHEN <condition> THEN <expression> END)
user_11228790

Is this what you want :

select m1.Time
       , (select sum(sum_quantity) 
          from mytable m
          where m.time between (m1.time - interval '29 day') and (m1.time)) sum_total
from mytable m1
group by m1.Time
order by m1.Time;

Or maybe this is better:

select m1.Time
       , sum(m.sum_quantity) 
from mytable m
     join mytable m1 on m.time between (m1.time - interval '29 day') and (m1.time)
group by m1.Time
order by m1.Time;

Here is a demo:

DEMO

user_65650380
Related