Database Reference
In-Depth Information
FROM sales_by_week
WHERE year = 2014
AND week <= 26
ORDER BY year,
week
year week sales moving_avg
2014 1 1564539 1572999.333 ←average of weeks 1, 2, 3
2014 2 1572128 1579941.75 ←average of weeks 1, 2, 3, 4
2014 3 1582331 1579982.6 ←average of weeks 1, 2, 3, 4, 5
2014 4 1600769 1584834.4 ←average of weeks 2, 3, 4, 5, 6
2014 5 1580146 1583037.2 ←average of weeks 3, 4, 5, 6, 7
2014 6 1588798 1579179.6
2014 7 1563142 1563975.6
2014 8 1563043 1553665
2014 9 1524749 1547534.8
2014 10 1528593 1548051.6
2014 11 1558147 1545714.2
2014 12 1565726 1549404
2014 13 1551356 1548812.6
2014 14 1543198 1543820.2
2014 15 1525636 1536767.6
2014 16 1533185 1531662.2
2014 17 1530463 1527313.6
2014 18 1525829 1528787.8
2014 19 1521455 1532649
2014 20 1533007 1533370
2014 21 1552491 1532116
2014 22 1534068 1539713.6
2014 23 1519559 1538199.6
2014 24 1559443 1539086.2
←average of weeks 22,23,24,25,26
2014 25 1525437 1540340.75
←average of weeks 23,24,25,26
2014 26 1556924 1547268
←average of weeks 24,25,26
The windowing function uses the built-in aggregate function
AVG()
, which
computes the arithmetic average of a set of values. The
ORDER BY
clause sorts
the records in chronological order and specifies which rows should be included
in the averaging process with the current row. In this SQL query, the moving
average is based on the current row, the preceding two rows, and the following two
