How can't fill number when days in calculating is less than specified span?

44 Views Asked by At

There is the raw data to calculate moving average number:

 2020-04-01     210
 2020-04-02     125
 2020-04-03     150
 2020-04-04     230
 2020-04-05     200
 2020-04-06     25
 2020-04-07     215
 2020-04-08     300
 2020-04-09     250
 2020-04-10     220

To calculate the moving average with 5 days in excel:

enter image description here

Frome c1 till c5 there is no data.(In c5 ,=AVERAGE(B1:B5) is the formula,same logic for c6 till c10.)

Now calculate it with postgres.

postgres=# create table sales(order_date date,sale int);
postgres=# insert into sales values('2020-04-01',210),
           ('2020-04-02',125),('2020-04-03',150),('2020-04-04',230),
           ('2020-04-05',200),('2020-04-06',25),
           ('2020-04-07',215),('2020-04-08',300),('2020-04-09',250),('2020-04-10',220);

To calculate:

SELECT a.order_date,a.sale, 
       AVG(a.sale)
       OVER(ORDER BY a.order_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
       AS avg_sales
       FROM sales a ;
 order_date | sale |      avg_sales
------------+------+----------------------
 2020-04-01 |  210 | 210.00
 2020-04-02 |  125 | 167.50
 2020-04-03 |  150 | 161.66
 2020-04-04 |  230 | 178.75
 2020-04-05 |  200 | 183.00
 2020-04-06 |   25 | 146.00
 2020-04-07 |  215 | 164.00
 2020-04-08 |  300 | 194.00
 2020-04-09 |  250 | 198.00
 2020-04-10 |  220 | 202.00

How can make the result as below with psql command?

 order_date | sale |      avg_sales
------------+------+----------------------
 2020-04-01 |  210 |  
 2020-04-02 |  125 | 
 2020-04-03 |  150 |  
 2020-04-04 |  230 | 
 2020-04-05 |  200 | 183.00
 2020-04-06 |   25 | 146.00
 2020-04-07 |  215 | 164.00
 2020-04-08 |  300 | 194.00
 2020-04-09 |  250 | 198.00
 2020-04-10 |  220 | 202.00
0

There are 0 best solutions below