moving_avg.malloynb

>>>markdown
# Computing Moving Averages

Malloy can compute moving averages on resultsets.

The queries below use the following model
>>>malloy
source: order_items is duckdb.table('../data/order_items.parquet') extend {
  measure: 
    user_count is count(user_id)
    order_count is count()
}
>>>markdown

## Simple Moving Average
>>>malloy
#(docs) size=medium limit=5000
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
}
>>>markdown

## Visualizing the Results
>>>malloy
#(docs) size=large limit=5000
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
  }
}
>>>markdown

## 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.
>>>malloy
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
  }
}
>>>malloy
#(docs) size=large limit=5000
run: flights -> {
  group_by: destination
  aggregate: flight_count
  nest: non_averaged
  nest: moving_averaged
}
>>>markdown