'How to get average starting third previous record to be included to current row as column?
I have some data like below:
| id | date | code | price |
|---|---|---|---|
| 892 | 2022-02-04 | B | 472 |
| 891 | 2022-02-03 | B | 58 |
| 890 | 2022-02-02 | B | 467 |
| 868 | 2022-01-28 | B | 50 |
| 821 | 2022-01-23 | B | 45 |
| 780 | 2022-01-20 | B | 55 |
| 550 | 2022-01-14 | B | 79 |
| 245 | 2022-01-12 | B | 841 |
| 112 | 2022-01-11 | B | 128 |
| 91 | 2022-01-07 | B | 174 |
| 74 | 2022-01-04 | B | 64 |
I want to get the average price of three records, starting from previous third row to be included to current row in one SQL query, so I'm expecting like below:
| id | date | code | price | avg3th |
|---|---|---|---|---|
| 892 | 2022-02-04 | B | 472 | average of ( 467+50+45) |
| 891 | 2022-02-03 | B | 58 | average of ( 50+45+55) |
| 890 | 2022-02-02 | B | 467 | average of ( 50+45+79) |
| 868 | 2022-01-28 | B | 50 | average of ( 45+79+841) |
| 821 | 2022-01-23 | B | 45 | average of ( 79+841+128) |
| 780 | 2022-01-20 | B | 55 | average of ( 841+128+174) |
| 550 | 2022-01-14 | B | 79 | ... |
| 245 | 2022-01-12 | B | 841 | ... |
| 112 | 2022-01-11 | B | 128 | ... |
| 91 | 2022-01-07 | B | 174 | ... |
| 74 | 2022-01-04 | B | 64 | ... |
What I have tried, using below query:
SELECT t.id, t.date, t.code, t.price,
format(
CASE WHEN
ROW_NUMBER() OVER (ORDER BY t.date) >=5 THEN
AVG ( t.price ) OVER (ORDER BY t.date
ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING)
ELSE NULL
END,0) AS average_3th
FROM test t ORDER BY t.id DESC
However, above the query still includes the first element as average (it should begin at third element), so the below result still wrong:
Any idea?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|