What Is Malloy
Auto-binning Histograms
Malloy can compute moving averages on resultsets.
The queries below use the following model
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
run: order_items -> { # line_chart nest: non_averaged is { group_by: order_month is created_at.month aggregate: order_count order_by: order_month } # line_chart nest: moving_averaged is { group_by: order_month is created_at.month calculate: moving_avg_order_count is avg_moving(order_count, 3) order_by: order_month } }
[ { "non_averaged": [ { "order_month": "2019-01-01T00:00:00.000Z", "order_count": 1262 }, { "order_month": "2019-02-01T00:00:00.000Z", "order_count": 1230 }, { "order_month": "2019-03-01T00:00:00.000Z", "order_count": 1507 }, { "order_month": "2019-04-01T00:00:00.000Z", "order_count": 1826 }, { "order_month": "2019-05-01T00:00:00.000Z", "order_count": 2044 }, { "order_month": "2019-06-01T00:00:00.000Z", "order_count": 2150 }, { "order_month": "2019-07-01T00:00:00.000Z", "order_count": 2382 }, { "order_month": "2019-08-01T00:00:00.000Z", "order_count": 2632 }, { "order_month": "2019-09-01T00:00:00.000Z", "order_count": 2902 }, { "order_month": "2019-10-01T00:00:00.000Z", "order_count": 3214 }, { "order_month": "2019-11-01T00:00:00.000Z", "order_count": 3387 }, { "order_month": "2019-12-01T00:00:00.000Z", "order_count": 4038 }, { "order_month": "2020-01-01T00:00:00.000Z", "order_count": 3743 }, { "order_month": "2020-02-01T00:00:00.000Z", "order_count": 3570 }, { "order_month": "2020-03-01T00:00:00.000Z", "order_count": 4263 }, { "order_month": "2020-04-01T00:00:00.000Z", "order_count": 4183 }, { "order_month": "2020-05-01T00:00:00.000Z", "order_count": 4019 }, { "order_month": "2020-06-01T00:00:00.000Z", "order_count": 4292 }, { "order_month": "2020-07-01T00:00:00.000Z", "order_count": 4549 }, { "order_month": "2020-08-01T00:00:00.000Z", "order_count": 4874 }, { "order_month": "2020-09-01T00:00:00.000Z", "order_count": 5032 }, { "order_month": "2020-10-01T00:00:00.000Z", "order_count": 5422 }, { "order_month": "2020-11-01T00:00:00.000Z", "order_count": 5782 }, { "order_month": "2020-12-01T00:00:00.000Z", "order_count": 6293 }, { "order_month": "2021-01-01T00:00:00.000Z", "order_count": 5897 }, { "order_month": "2021-02-01T00:00:00.000Z", "order_count": 5821 }, { "order_month": "2021-03-01T00:00:00.000Z", "order_count": 6547 }, { "order_month": "2021-04-01T00:00:00.000Z", "order_count": 6380 }, { "order_month": "2021-05-01T00:00:00.000Z", "order_count": 6378 }, { "order_month": "2021-06-01T00:00:00.000Z", "order_count": 6305 }, { "order_month": "2021-07-01T00:00:00.000Z", "order_count": 6813 }, { "order_month": "2021-08-01T00:00:00.000Z", "order_count": 7142 }, { "order_month": "2021-09-01T00:00:00.000Z", "order_count": 7886 }, { "order_month": "2021-10-01T00:00:00.000Z", "order_count": 7814 }, { "order_month": "2021-11-01T00:00:00.000Z", "order_count": 8063 }, { "order_month": "2021-12-01T00:00:00.000Z", "order_count": 9103 }, { "order_month": "2022-01-01T00:00:00.000Z", "order_count": 8609 }, { "order_month": "2022-02-01T00:00:00.000Z", "order_count": 8174 }, { "order_month": "2022-03-01T00:00:00.000Z", "order_count": 9441 }, { "order_month": "2022-04-01T00:00:00.000Z", "order_count": 9100 }, { "order_month": "2022-05-01T00:00:00.000Z", "order_count": 9730 }, { "order_month": "2022-06-01T00:00:00.000Z", "order_count": 9297 }, { "order_month": "2022-07-01T00:00:00.000Z", "order_count": 9668 }, { "order_month": "2022-08-01T00:00:00.000Z", "order_count": 10481 }, { "order_month": "2022-09-01T00:00:00.000Z", "order_count": 11213 }, { "order_month": "2022-10-01T00:00:00.000Z", "order_count": 11398 }, { "order_month": "2022-11-01T00:00:00.000Z", "order_count": 5163 } ], "moving_averaged": [ { "order_month": "2019-01-01T00:00:00.000Z", "moving_avg_order_count": 1262 }, { "order_month": "2019-02-01T00:00:00.000Z", "moving_avg_order_count": 1246 }, { "order_month": "2019-03-01T00:00:00.000Z", "moving_avg_order_count": 1333 }, { "order_month": "2019-04-01T00:00:00.000Z", "moving_avg_order_count": 1456.25 }, { "order_month": "2019-05-01T00:00:00.000Z", "moving_avg_order_count": 1651.75 }, { "order_month": "2019-06-01T00:00:00.000Z", "moving_avg_order_count": 1881.75 }, { "order_month": "2019-07-01T00:00:00.000Z", "moving_avg_order_count": 2100.5 }, { "order_month": "2019-08-01T00:00:00.000Z", "moving_avg_order_count": 2302 }, { "order_month": "2019-09-01T00:00:00.000Z", "moving_avg_order_count": 2516.5 }, { "order_month": "2019-10-01T00:00:00.000Z", "moving_avg_order_count": 2782.5 }, { "order_month": "2019-11-01T00:00:00.000Z", "moving_avg_order_count": 3033.75 }, { "order_month": "2019-12-01T00:00:00.000Z", "moving_avg_order_count": 3385.25 }, { "order_month": "2020-01-01T00:00:00.000Z", "moving_avg_order_count": 3595.5 }, { "order_month": "2020-02-01T00:00:00.000Z", "moving_avg_order_count": 3684.5 }, { "order_month": "2020-03-01T00:00:00.000Z", "moving_avg_order_count": 3903.5 }, { "order_month": "2020-04-01T00:00:00.000Z", "moving_avg_order_count": 3939.75 }, { "order_month": "2020-05-01T00:00:00.000Z", "moving_avg_order_count": 4008.75 }, { "order_month": "2020-06-01T00:00:00.000Z", "moving_avg_order_count": 4189.25 }, { "order_month": "2020-07-01T00:00:00.000Z", "moving_avg_order_count": 4260.75 }, { "order_month": "2020-08-01T00:00:00.000Z", "moving_avg_order_count": 4433.5 }, { "order_month": "2020-09-01T00:00:00.000Z", "moving_avg_order_count": 4686.75 }, { "order_month": "2020-10-01T00:00:00.000Z", "moving_avg_order_count": 4969.25 }, { "order_month": "2020-11-01T00:00:00.000Z", "moving_avg_order_count": 5277.5 }, { "order_month": "2020-12-01T00:00:00.000Z", "moving_avg_order_count": 5632.25 }, { "order_month": "2021-01-01T00:00:00.000Z", "moving_avg_order_count": 5848.5 }, { "order_month": "2021-02-01T00:00:00.000Z", "moving_avg_order_count": 5948.25 }, { "order_month": "2021-03-01T00:00:00.000Z", "moving_avg_order_count": 6139.5 }, { "order_month": "2021-04-01T00:00:00.000Z", "moving_avg_order_count": 6161.25 }, { "order_month": "2021-05-01T00:00:00.000Z", "moving_avg_order_count": 6281.5 }, { "order_month": "2021-06-01T00:00:00.000Z", "moving_avg_order_count": 6402.5 }, { "order_month": "2021-07-01T00:00:00.000Z", "moving_avg_order_count": 6469 }, { "order_month": "2021-08-01T00:00:00.000Z", "moving_avg_order_count": 6659.5 }, { "order_month": "2021-09-01T00:00:00.000Z", "moving_avg_order_count": 7036.5 }, { "order_month": "2021-10-01T00:00:00.000Z", "moving_avg_order_count": 7413.75 }, { "order_month": "2021-11-01T00:00:00.000Z", "moving_avg_order_count": 7726.25 }, { "order_month": "2021-12-01T00:00:00.000Z", "moving_avg_order_count": 8216.5 }, { "order_month": "2022-01-01T00:00:00.000Z", "moving_avg_order_count": 8397.25 }, { "order_month": "2022-02-01T00:00:00.000Z", "moving_avg_order_count": 8487.25 }, { "order_month": "2022-03-01T00:00:00.000Z", "moving_avg_order_count": 8831.75 }, { "order_month": "2022-04-01T00:00:00.000Z", "moving_avg_order_count": 8831 }, { "order_month": "2022-05-01T00:00:00.000Z", "moving_avg_order_count": 9111.25 }, { "order_month": "2022-06-01T00:00:00.000Z", "moving_avg_order_count": 9392 }, { "order_month": "2022-07-01T00:00:00.000Z", "moving_avg_order_count": 9448.75 }, { "order_month": "2022-08-01T00:00:00.000Z", "moving_avg_order_count": 9794 }, { "order_month": "2022-09-01T00:00:00.000Z", "moving_avg_order_count": 10164.75 }, { "order_month": "2022-10-01T00:00:00.000Z", "moving_avg_order_count": 10690 }, { "order_month": "2022-11-01T00:00:00.000Z", "moving_avg_order_count": 9563.75 } ] } ]
WITH __stage0 AS ( SELECT group_set, CASE WHEN group_set=1 THEN DATE_TRUNC('month', base."created_at") END as "order_month__1", CASE WHEN group_set=1 THEN COUNT(1) END as "order_count__1", CASE WHEN group_set=2 THEN DATE_TRUNC('month', base."created_at") END as "order_month__2", CASE WHEN group_set=2 THEN AVG((CASE WHEN group_set=2 THEN COUNT(1) END)) OVER(PARTITION BY group_set ORDER BY CASE WHEN group_set=2 THEN DATE_TRUNC('month', base."created_at") END ASC NULLS LAST ROWS BETWEEN 3 PRECEDING AND 0 FOLLOWING) END as "moving_avg_order_count__2" FROM '../data/order_items.parquet' as base CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,2,1)) as group_set ) as group_set GROUP BY 1,2,4 ) SELECT COALESCE(LIST({ "order_month": "order_month__1", "order_count": "order_count__1"} ORDER BY "order_month__1" ASC NULLS LAST) FILTER (WHERE group_set=1),[]) as "non_averaged", COALESCE(LIST({ "order_month": "order_month__2", "moving_avg_order_count": "moving_avg_order_count__2"} ORDER BY "order_month__2" ASC NULLS LAST) FILTER (WHERE group_set=2),[]) as "moving_averaged" FROM __stage0
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: dep_month aggregate: flight_count order_by: dep_month } # line_chart view: moving_averaged is { group_by: dep_month calculate: 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 nest: moving_averaged }