CODEWITHSUNDEEP
sqlpostgresql
OrSmolnik
OrSmolnik

Reputation: 309

how to query average data for all rows in the last 3 occurence

I have a PostgreSQL DB with a table of events with "ID", "TYPE", "DATE" (YYYY-MM-DD) , "DATA" (Number). For example:

---------------------------------
| ID | TYPE |   DATE     | DATA |
---------------------------------
| 1  |   1  | 2018-03-01 |  5   |
---------------------------------
| 2  |   1  | 2018-03-02 |  15  |
---------------------------------
| 3  |   1  | 2018-03-03 |  13  |
---------------------------------
| 4  |   2  | 2018-03-01 |  4   |
---------------------------------
| 5  |   2  | 2018-03-02 |  2   |
---------------------------------
| 6  |   3  | 2018-03-01 |  5   |

I want to create a query on this table so the result will be the same table with new row - average "DATA" for the last 3 records (by the date). Wanted result:

------------------------------------------------------
| ID | TYPE |   DATE     | DATA | AVG DATA 3 RECORDS | 
------------------------------------------------------
| 1  |   1  | 2018-03-01 |  5   |          5         |
------------------------------------------------------
| 2  |   1  | 2018-03-02 |  15  |          10        |          
------------------------------------------------------
| 3  |   1  | 2018-03-03 |  13  |          11        |
------------------------------------------------------
| 4  |   2  | 2018-03-01 |  4   |          4         |
------------------------------------------------------
| 5  |   2  | 2018-03-02 |  2   |          3         |
------------------------------------------------------
| 6  |   3  | 2018-03-01 |  5   |          5         |

What is the query I should run? Thanks!

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use avg() with a windowing clause:

select t.*,
       avg(date) over (partition by type
                       order by date
                       rows between 2 preceding and current row
                      ) as avg_3
from t;

Upvotes: 2

Related Questions