Malloy can compute moving averages on resultsets.
The queries below use the following model
## renderer_next source: order_items is duckdb.table('../data/order_items.parquet') extend { measure: user_count is count(user_id) order_count is count() }
Simple Moving Average
run: order_items -> { group_by: order_month is created_at.month aggregate: order_count calculate: moving_avg_order_count is avg_moving(order_count, 3) order_by: order_month }
[ { "order_month": "2019-01-01T00:00:00.000Z", "order_count": 1262, "moving_avg_order_count": 1262 }, { "order_month": "2019-02-01T00:00:00.000Z", "order_count": 1230, "moving_avg_order_count": 1246 }, { "order_month": "2019-03-01T00:00:00.000Z", "order_count": 1507, "moving_avg_order_count": 1333 }, { "order_month": "2019-04-01T00:00:00.000Z", "order_count": 1826, "moving_avg_order_count": 1456.25 }, { "order_month": "2019-05-01T00:00:00.000Z", "order_count": 2044, "moving_avg_order_count": 1651.75 }, { "order_month": "2019-06-01T00:00:00.000Z", "order_count": 2150, "moving_avg_order_count": 1881.75 }, { "order_month": "2019-07-01T00:00:00.000Z", "order_count": 2382, "moving_avg_order_count": 2100.5 }, { "order_month": "2019-08-01T00:00:00.000Z", "order_count": 2632, "moving_avg_order_count": 2302 }, { "order_month": "2019-09-01T00:00:00.000Z", "order_count": 2902, "moving_avg_order_count": 2516.5 }, { "order_month": "2019-10-01T00:00:00.000Z", "order_count": 3214, "moving_avg_order_count": 2782.5 }, { "order_month": "2019-11-01T00:00:00.000Z", "order_count": 3387, "moving_avg_order_count": 3033.75 }, { "order_month": "2019-12-01T00:00:00.000Z", "order_count": 4038, "moving_avg_order_count": 3385.25 }, { "order_month": "2020-01-01T00:00:00.000Z", "order_count": 3743, "moving_avg_order_count": 3595.5 }, { "order_month": "2020-02-01T00:00:00.000Z", "order_count": 3570, "moving_avg_order_count": 3684.5 }, { "order_month": "2020-03-01T00:00:00.000Z", "order_count": 4263, "moving_avg_order_count": 3903.5 }, { "order_month": "2020-04-01T00:00:00.000Z", "order_count": 4183, "moving_avg_order_count": 3939.75 }, { "order_month": "2020-05-01T00:00:00.000Z", "order_count": 4019, "moving_avg_order_count": 4008.75 }, { "order_month": "2020-06-01T00:00:00.000Z", "order_count": 4292, "moving_avg_order_count": 4189.25 }, { "order_month": "2020-07-01T00:00:00.000Z", "order_count": 4549, "moving_avg_order_count": 4260.75 }, { "order_month": "2020-08-01T00:00:00.000Z", "order_count": 4874, "moving_avg_order_count": 4433.5 }, { "order_month": "2020-09-01T00:00:00.000Z", "order_count": 5032, "moving_avg_order_count": 4686.75 }, { "order_month": "2020-10-01T00:00:00.000Z", "order_count": 5422, "moving_avg_order_count": 4969.25 }, { "order_month": "2020-11-01T00:00:00.000Z", "order_count": 5782, "moving_avg_order_count": 5277.5 }, { "order_month": "2020-12-01T00:00:00.000Z", "order_count": 6293, "moving_avg_order_count": 5632.25 }, { "order_month": "2021-01-01T00:00:00.000Z", "order_count": 5897, "moving_avg_order_count": 5848.5 }, { "order_month": "2021-02-01T00:00:00.000Z", "order_count": 5821, "moving_avg_order_count": 5948.25 }, { "order_month": "2021-03-01T00:00:00.000Z", "order_count": 6547, "moving_avg_order_count": 6139.5 }, { "order_month": "2021-04-01T00:00:00.000Z", "order_count": 6380, "moving_avg_order_count": 6161.25 }, { "order_month": "2021-05-01T00:00:00.000Z", "order_count": 6378, "moving_avg_order_count": 6281.5 }, { "order_month": "2021-06-01T00:00:00.000Z", "order_count": 6305, "moving_avg_order_count": 6402.5 }, { "order_month": "2021-07-01T00:00:00.000Z", "order_count": 6813, "moving_avg_order_count": 6469 }, { "order_month": "2021-08-01T00:00:00.000Z", "order_count": 7142, "moving_avg_order_count": 6659.5 }, { "order_month": "2021-09-01T00:00:00.000Z", "order_count": 7886, "moving_avg_order_count": 7036.5 }, { "order_month": "2021-10-01T00:00:00.000Z", "order_count": 7814, "moving_avg_order_count": 7413.75 }, { "order_month": "2021-11-01T00:00:00.000Z", "order_count": 8063, "moving_avg_order_count": 7726.25 }, { "order_month": "2021-12-01T00:00:00.000Z", "order_count": 9103, "moving_avg_order_count": 8216.5 }, { "order_month": "2022-01-01T00:00:00.000Z", "order_count": 8609, "moving_avg_order_count": 8397.25 }, { "order_month": "2022-02-01T00:00:00.000Z", "order_count": 8174, "moving_avg_order_count": 8487.25 }, { "order_month": "2022-03-01T00:00:00.000Z", "order_count": 9441, "moving_avg_order_count": 8831.75 }, { "order_month": "2022-04-01T00:00:00.000Z", "order_count": 9100, "moving_avg_order_count": 8831 }, { "order_month": "2022-05-01T00:00:00.000Z", "order_count": 9730, "moving_avg_order_count": 9111.25 }, { "order_month": "2022-06-01T00:00:00.000Z", "order_count": 9297, "moving_avg_order_count": 9392 }, { "order_month": "2022-07-01T00:00:00.000Z", "order_count": 9668, "moving_avg_order_count": 9448.75 }, { "order_month": "2022-08-01T00:00:00.000Z", "order_count": 10481, "moving_avg_order_count": 9794 }, { "order_month": "2022-09-01T00:00:00.000Z", "order_count": 11213, "moving_avg_order_count": 10164.75 }, { "order_month": "2022-10-01T00:00:00.000Z", "order_count": 11398, "moving_avg_order_count": 10690 }, { "order_month": "2022-11-01T00:00:00.000Z", "order_count": 5163, "moving_avg_order_count": 9563.75 } ]
SELECT DATE_TRUNC('month', base."created_at") as "order_month", COUNT(1) as "order_count", AVG((COUNT(1))) OVER( ORDER BY DATE_TRUNC('month', base."created_at") ASC NULLS LAST ROWS BETWEEN 3 PRECEDING AND 0 FOLLOWING) as "moving_avg_order_count" FROM '../data/order_items.parquet' as base GROUP BY 1 ORDER BY 1 ASC NULLS LAST
Visualizing the Results
# line_chart run: order_items -> { group_by: # x order_month is created_at.month aggregate: # y order_count calculate: # y moving_avg_order_count is avg_moving(order_count, 3) order_by: order_month }
[ { "order_month": "2019-01-01T00:00:00.000Z", "order_count": 1262, "moving_avg_order_count": 1262 }, { "order_month": "2019-02-01T00:00:00.000Z", "order_count": 1230, "moving_avg_order_count": 1246 }, { "order_month": "2019-03-01T00:00:00.000Z", "order_count": 1507, "moving_avg_order_count": 1333 }, { "order_month": "2019-04-01T00:00:00.000Z", "order_count": 1826, "moving_avg_order_count": 1456.25 }, { "order_month": "2019-05-01T00:00:00.000Z", "order_count": 2044, "moving_avg_order_count": 1651.75 }, { "order_month": "2019-06-01T00:00:00.000Z", "order_count": 2150, "moving_avg_order_count": 1881.75 }, { "order_month": "2019-07-01T00:00:00.000Z", "order_count": 2382, "moving_avg_order_count": 2100.5 }, { "order_month": "2019-08-01T00:00:00.000Z", "order_count": 2632, "moving_avg_order_count": 2302 }, { "order_month": "2019-09-01T00:00:00.000Z", "order_count": 2902, "moving_avg_order_count": 2516.5 }, { "order_month": "2019-10-01T00:00:00.000Z", "order_count": 3214, "moving_avg_order_count": 2782.5 }, { "order_month": "2019-11-01T00:00:00.000Z", "order_count": 3387, "moving_avg_order_count": 3033.75 }, { "order_month": "2019-12-01T00:00:00.000Z", "order_count": 4038, "moving_avg_order_count": 3385.25 }, { "order_month": "2020-01-01T00:00:00.000Z", "order_count": 3743, "moving_avg_order_count": 3595.5 }, { "order_month": "2020-02-01T00:00:00.000Z", "order_count": 3570, "moving_avg_order_count": 3684.5 }, { "order_month": "2020-03-01T00:00:00.000Z", "order_count": 4263, "moving_avg_order_count": 3903.5 }, { "order_month": "2020-04-01T00:00:00.000Z", "order_count": 4183, "moving_avg_order_count": 3939.75 }, { "order_month": "2020-05-01T00:00:00.000Z", "order_count": 4019, "moving_avg_order_count": 4008.75 }, { "order_month": "2020-06-01T00:00:00.000Z", "order_count": 4292, "moving_avg_order_count": 4189.25 }, { "order_month": "2020-07-01T00:00:00.000Z", "order_count": 4549, "moving_avg_order_count": 4260.75 }, { "order_month": "2020-08-01T00:00:00.000Z", "order_count": 4874, "moving_avg_order_count": 4433.5 }, { "order_month": "2020-09-01T00:00:00.000Z", "order_count": 5032, "moving_avg_order_count": 4686.75 }, { "order_month": "2020-10-01T00:00:00.000Z", "order_count": 5422, "moving_avg_order_count": 4969.25 }, { "order_month": "2020-11-01T00:00:00.000Z", "order_count": 5782, "moving_avg_order_count": 5277.5 }, { "order_month": "2020-12-01T00:00:00.000Z", "order_count": 6293, "moving_avg_order_count": 5632.25 }, { "order_month": "2021-01-01T00:00:00.000Z", "order_count": 5897, "moving_avg_order_count": 5848.5 }, { "order_month": "2021-02-01T00:00:00.000Z", "order_count": 5821, "moving_avg_order_count": 5948.25 }, { "order_month": "2021-03-01T00:00:00.000Z", "order_count": 6547, "moving_avg_order_count": 6139.5 }, { "order_month": "2021-04-01T00:00:00.000Z", "order_count": 6380, "moving_avg_order_count": 6161.25 }, { "order_month": "2021-05-01T00:00:00.000Z", "order_count": 6378, "moving_avg_order_count": 6281.5 }, { "order_month": "2021-06-01T00:00:00.000Z", "order_count": 6305, "moving_avg_order_count": 6402.5 }, { "order_month": "2021-07-01T00:00:00.000Z", "order_count": 6813, "moving_avg_order_count": 6469 }, { "order_month": "2021-08-01T00:00:00.000Z", "order_count": 7142, "moving_avg_order_count": 6659.5 }, { "order_month": "2021-09-01T00:00:00.000Z", "order_count": 7886, "moving_avg_order_count": 7036.5 }, { "order_month": "2021-10-01T00:00:00.000Z", "order_count": 7814, "moving_avg_order_count": 7413.75 }, { "order_month": "2021-11-01T00:00:00.000Z", "order_count": 8063, "moving_avg_order_count": 7726.25 }, { "order_month": "2021-12-01T00:00:00.000Z", "order_count": 9103, "moving_avg_order_count": 8216.5 }, { "order_month": "2022-01-01T00:00:00.000Z", "order_count": 8609, "moving_avg_order_count": 8397.25 }, { "order_month": "2022-02-01T00:00:00.000Z", "order_count": 8174, "moving_avg_order_count": 8487.25 }, { "order_month": "2022-03-01T00:00:00.000Z", "order_count": 9441, "moving_avg_order_count": 8831.75 }, { "order_month": "2022-04-01T00:00:00.000Z", "order_count": 9100, "moving_avg_order_count": 8831 }, { "order_month": "2022-05-01T00:00:00.000Z", "order_count": 9730, "moving_avg_order_count": 9111.25 }, { "order_month": "2022-06-01T00:00:00.000Z", "order_count": 9297, "moving_avg_order_count": 9392 }, { "order_month": "2022-07-01T00:00:00.000Z", "order_count": 9668, "moving_avg_order_count": 9448.75 }, { "order_month": "2022-08-01T00:00:00.000Z", "order_count": 10481, "moving_avg_order_count": 9794 }, { "order_month": "2022-09-01T00:00:00.000Z", "order_count": 11213, "moving_avg_order_count": 10164.75 }, { "order_month": "2022-10-01T00:00:00.000Z", "order_count": 11398, "moving_avg_order_count": 10690 }, { "order_month": "2022-11-01T00:00:00.000Z", "order_count": 5163, "moving_avg_order_count": 9563.75 } ]
SELECT DATE_TRUNC('month', base."created_at") as "order_month", COUNT(1) as "order_count", AVG((COUNT(1))) OVER( ORDER BY DATE_TRUNC('month', base."created_at") ASC NULLS LAST ROWS BETWEEN 3 PRECEDING AND 0 FOLLOWING) as "moving_avg_order_count" FROM '../data/order_items.parquet' as base GROUP BY 1 ORDER BY 1 ASC NULLS LAST
Displaying Charts in Nested Queries
In this example, we've added two queries to the flights
source, one showing flights by month without the moving average applied, and one with the moving average applied. We then use these queries to show charts of flight count for each airport over time.
source: flights is duckdb.table('../data/flights.parquet') extend { measure: flight_count is count() dimension: dep_month is dep_time.month # line_chart view: non_averaged is { group_by: # x dep_month aggregate: # y flight_count calculate: # y moving_avg_flight_count is avg_moving(flight_count, 3) order_by: dep_month } }