怎么用给窗口使用mysql,在MySQL中使用窗口函数移动平均

My dataset is as below:

Date Price 3 Day Moving Average

-------------------------------------------------

2018-08-01 10

2018-08-02 12

2018-08-03 11 11 (10+12+11)/3\n

2018-08-04 15 12.67 (12+11+15)/3

2018-08-05 13 13 (11+15+13)/3

2018-08-06 17 ...

2018-08-07 18 …

2018-08-08 20 ..

Is this possible using just MySQL window functions?

Environment details:

Server version: 8.0.12 MySQL Community Server - GPL

解决方案SELECT

Date,

Price,

CASE WHEN

ROW_NUMBER() OVER (ORDER BY DATE) >= 3 THEN

AVG(Price) OVER (ORDER BY Date

ROWS BETWEEN 2 PRECEDING AND

CURRENT ROW)

ELSE NULL

END AS avg

FROM yourTable

ORDER BY Date;

Details:

2 PRECEDING means two rows above the current row (excluding the current row). We explicitly define Ascending order on Date. So that would means two closest dates, lower than the current row's date

CURRENT ROW means the current row.

BETWEEN allows us to consider the rows in the defined range (including boundary conditions).

Since, you want moving average to be null for the first two rows, we can check for this using Row_number() function in Case .. When