How to get average starting third previous record to be included to current row as column?-CodePudding

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?

CodePudding user response：

Using the row number idea average in a sub query

WITH CTE AS
(SELECT T.*,ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY ID DESC) RN FROM T ),
CTE1 AS
(SELECT T.*,ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY ID DESC) RN FROM T )
SELECT CTE.*,
         (SELECT AVG(CTE1.PRICE) FROM CTE1 WHERE CTE1.CODE = CTE.CODE AND CTE1.RN BETWEEN CTE.RN   2 AND CTE.RN   4),
          (SELECT GROUP_CONCAT(CTE1.ID) FROM CTE1 WHERE CTE1.CODE = CTE.CODE AND CTE1.RN BETWEEN CTE.RN   2 AND CTE.RN   4)
FROM CTE
ORDER BY CTE.ID DESC
;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=437342579b3165f31292e288e3165bed