Malloy Documentation
search

Malloy can compute moving averages on resultsets.

The queries below use the following model

document
## 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

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
# 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
}
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

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: 
      # x
      dep_month
    aggregate: 
      # y
      flight_count
    calculate: 
      # y
      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
}
QUERY RESULTS