Consult a great god, and to the MYSQL data table TMPtable, want to in the name column, calculating the average every five days, the HTML code is as follows, but after the calculation has been is NULL, the wrong code?

B. SELECT *,
(
SELECT avg (t1. ` close `) from (
SELECT t. *, (@ rownum:=@ rownum + 1) AS rownum FROM (SELECT @ rownum:=0) r, ` TMPtable ` t ORDER BY ` name `, ` index ` asc) t1
WHERE a t1. Rownum & lt;=b.r ownum + 5 and t1. Rownum & gt; B.r ownum
) avg5
The from
(select *, b. (@ rownum:=@ rownum + 1) AS rownum FROM (select @ rownum:=0) r, ` TMPtable ` b ORDER BY ` name `, ` index ` asc) b



Calculated before:

After the calculation:

CodePudding user response:

Objective: according to the name column group calculating the average of 5 days prior to the close column

CodePudding user response:

Good complex SQL, didn't look too understand
My idea is like this:
To remove the time before the first five days of data, and then grouped according to the name average, but to see your data requirements:
1. A data every day?

SELECT the name, avg (close)
The FROM TMPtable
WHERE the index & gt;=SUBDATE (CURDATE (), INTERVAL 5 DAY)
GROUP BY name