Aggregations

Learn how to aggregate data.

Simple aggregations

Elements of a collection can be aggregated using available aggregations such as Collection.Min, Collection.Max, Collection.Sum, Collection.Count, Collection.Avg, ... Similar functions exist to aggregate elements of a list: List.Min, List.Max, List.Sum, List.Count, List.Avg, ...

These functions behave similarly: each receives the collection or list to aggregate. For instance to obtain the largest net amount of an order:

let data = Csv.InferAndRead("https://raw-tutorial.s3.eu-west-1.amazonaws.com/Lokad_Orders.csv"),
    subset = Collection.Filter(data, row -> row.Date == day)
    in Collection.Max(subset.NetAmount)

info

If you want to try this example, you can deploy the following endpoint:

Tutorial 6 - Aggregating data in a collection

Learn how to compute aggregations such as max, min, sum, count, and more.

Overview

Sample usage:

/tutorial/6-aggregation?day=<date>

For instance:

/tutorial/6-aggregation?day=2017-12-04

Code

main() =
    let
        day: date = Date.Build(2017, 12, 04),
        orders = Csv.InferAndRead("https://raw-tutorial.s3.eu-west-1.amazonaws.com/Lokad_Orders.csv"),
        ordersInDay = Collection.Filter(orders, order -> order.Date == day),
        v1 = Collection.Max(ordersInDay.NetAmount),
        v2 = Collection.Min(ordersInDay.NetAmount),
        v3 = Collection.Avg(ordersInDay.Quantity)
    in
        {maxAmount: v1, minAmount: v2, avgQ: v3}

To perform multiple aggregations and return the result as a single record:

main(day: date) =
    let data = Csv.InferAndRead("https://raw-tutorial.s3.eu-west-1.amazonaws.com/Lokad_Orders.csv"),
        subset = Collection.Filter(data, row -> row.Date == day),
        v1 = Collection.Max(subset.NetAmount),
        v2 = Collection.Min(subset.NetAmount),
        v3 = Collection.Avg(subset.Quantity)
    in {maxAmount: v1, minAmount: v2, avgQ: v3}

Aggregating Grouped Data

When rows of a collection have been grouped using Collection.GroupBy, the input collection has been turned into a collection of nested collections, one per key. Regular aggregations can be applied to the nested using Collection.Transform, which applies the aggregation functions to the groups.

info

If you want to try this example, you can deploy the following endpoint:

Tutorial 8 - Aggregating groups of data

Learn how to to agggregate data in groups.

Overview

Sample usage:

/tutorial/8-group-aggregation?country=<string>&year=<int>

For instance:

/tutorial/8-group-aggregation?country=GBR&year=1992

Code

main(country: string = "GBR", year: int = 1992) =
    let
        olympics = Csv.InferAndRead("https://raw-tutorial.s3.eu-west-1.amazonaws.com/summer_olympics.csv"),
        medals = Collection.Filter(olympics, entry -> entry.Country == country and entry.Year == year),
        medalsPerDiscipline = Collection.GroupBy(medals, entry -> entry.Discipline)
    in
        Collection.Transform(
            medalsPerDiscipline,
            row ->
                {
                    country: row.key,
                    gold: Collection.Count(Collection.Filter(row.group, entry -> entry.Medal == "Gold")),
                    silver: Collection.Count(Collection.Filter(row.group, entry -> entry.Medal == "Silver")),
                    bronze: Collection.Count(Collection.Filter(row.group, entry -> entry.Medal == "Bronze"))
                }
        )

Here's an example that returns the number of gold, silver and bronze medals per discipline.

let
    olympics = Csv.InferAndRead(
        "https://raw-tutorial.s3.eu-west-1.amazonaws.com/summer_olympics.csv"),
    medals = Collection.Filter(
        olympics,
        (entry) -> entry.Country == country and entry.Year == year),
    medalsPerDiscipline = Collection.GroupBy(medals, (e) -> e.Discipline)
in
    Collection.Transform(
        medalsPerDiscipline,
        (row) -> {country: row.key, gold: Collection.Count(
                Collection.Filter(row.group, (e) -> e.Medal == "Gold")), silver: Collection.Count(
                Collection.Filter(row.group, (e) -> e.Medal == "Silver")), bronze: Collection.Count(
                Collection.Filter(row.group, (e) -> e.Medal == "Bronze"))})

And here are the sample results for country GBR with year 1992:

[
  {
    "discipline": "Archery",
    "gold": 0,
    "silver": 0,
    "bronze": 4
  },
  {
    "discipline": "Athletics",
    "gold": 2,
    "silver": 0,
    "bronze": 12
  },
  {
    "discipline": "Boxing",
    "gold": 0,
    "silver": 0,
    "bronze": 1
  },
  {
    "discipline": "Canoe / Kayak S",
    "gold": 0,
    "silver": 1,
    "bronze": 0
  },
  {
    "discipline": "Cycling Track",
    "gold": 1,
    "silver": 0,
    "bronze": 0
  },
  {
    "discipline": "Hockey",
    "gold": 0,
    "silver": 0,
    "bronze": 16
  },
  {
    "discipline": "Judo",
    "gold": 0,
    "silver": 2,
    "bronze": 2
  },
  {
    "discipline": "Rowing",
    "gold": 5,
    "silver": 0,
    "bronze": 0
  },
  {
    "discipline": "Sailing",
    "gold": 0,
    "silver": 0,
    "bronze": 3
  },
  {
    "discipline": "Swimming",
    "gold": 0,
    "silver": 0,
    "bronze": 1
  }
]