Various SQL Snippets
Moving Average (using a correlated subquery)
For SQL brevity - at the cost of a Correlated_subquery - here is an easy way to calculated a moving average. Note this query specifies an interval of 3 weeks for a table with weekly datapoint (making the interval 4 weeks causes a fifth week of data to be included).
SELECT
t1.date AS date,
t1.value AS value,
(
SELECT AVG(t2.value)
FROM the_table t2
WHERE t2.date BETWEEN date_sub(t1.date, INTERVAL 3 WEEK) AND t1.date
) AS four_week_avg_value
FROM the_table t1
The value can be double checked by adding another column using group_concat:
SELECT
t1.date AS date,
t1.value AS value,
(
SELECT AVG(t2.value)
FROM the_table t2
WHERE t2.date BETWEEN date_sub(t1.date, INTERVAL 3 WEEK) AND t1.date
) AS four_week_avg_value,
(
SELECT GROUP_CONCAT(t3.value)
FROM the_table t3
WHERE t3.date BETWEEN date_sub(t1.date, INTERVAL 3 WEEK) AND t1.date
) AS 4winput
FROM the_table t1
A faster result (but only really meaningful if there's a large table, and more difficult to maintain because extra joins will be needed if changing the interval of the average, while in this version you can just just the interval in the date subtraction), could be achieved by joining the table against itself for each item to be included in the average.