CODEWITHSUNDEEP
sqlpostgresqlwindow-functionsmoving-average
Tom Tom
Tom Tom

Reputation: 352

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

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

Upvotes: 2

Views: 2965

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

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;

Upvotes: 4

VBoka
VBoka

Reputation: 9083

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

Upvotes: 1

Vikram Jain
Vikram Jain

Reputation: 5588

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)

Upvotes: 2

Tom&#225;š Z&#225;lusk&#253;
Tom&#225;š Z&#225;lusk&#253;

Reputation: 12000

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....

Upvotes: 1

Related Questions