Malloy Documentation
search

Malloy can compute moving averages on resultsets.

The queries below use the following model

document
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

document
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
}
QUERY RESULTS
[
  {
    "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

document
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
  }
}
QUERY RESULTS
[
  {
    "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.

document
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
  }
}
document
run: flights -> {
  group_by: destination
  aggregate: flight_count
  nest: non_averaged
  nest: moving_averaged
}
QUERY RESULTS