What Is Malloy
Auto-binning Histograms
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 } }
run: flights -> { group_by: destination aggregate: flight_count nest: non_averaged }