Relatude Help

28.2 GroupBy

GroupBy is SQL's GROUP BY, written the way LINQ and EF Core write it. It is the typed, one-axis form of a pivot and runs on the same engine. Use it in application code, and a pivot for two axes, sub-totals, or tables defined at runtime.

// SELECT Status, COUNT(*) FROM Event GROUP BY Status
var byStatus = db.Query<IEvent>().GroupBy(e => e.Status).Execute();
foreach (var g in byStatus) Console.WriteLine($"{g.Label}: {g.Count}");   // g.Key is the EventStatus

// A composite key, aggregates, HAVING, ORDER BY and LIMIT
var report = db.Query<IEvent>()
    .Where(e => e.StartsUtc >= since)
    .GroupBy(e => new { e.Status, e.StartsUtc.Year, e.StartsUtc.Month })
    .Select(g => new {
        g.Key.Status,
        g.Key.Year,
        g.Key.Month,
        Events   = g.Count(),
        Revenue  = g.Sum(e => e.Price),
        AvgPrice = g.Average(e => e.Price),
        Dearest  = g.Max(e => e.Price),
        Prices   = g.CountDistinct(e => e.Price),
        PerEvent = g.Sum(e => e.Price) / g.Count(),   // arithmetic on the aggregates
    })
    .Where(r => r.Events >= 3)                        // HAVING; Having(...) does the same
    .OrderByDescending(r => r.Revenue)
    .Take(20)
    .Execute();

No node is read: the groups and aggregates come from the indexes. So the properties you group and aggregate must be indexed, or the query throws when it is built. To group anything else, load the nodes and use LINQ. GroupBy also works after a facet selection: .Facets()…GroupBy(…).

Keys

The key is translated, not run, so it can only name stored values. e => e.Price / 100 is refused; work that out from the key on the result.

Key Groups by
e => e.Status Each value; an enum comes back as the enum
e => new { e.Status, e.StartsUtc.Year } Each combination, read as g.Key.Status and g.Key.Year
e => e.StartsUtc.Year (or .Month, .Day, .Date, .Hour) A calendar interval; key months as new { e.StartsUtc.Year, e.StartsUtc.Month }
e => Bucket.Interval(e.StartsUtc, DateInterval.Quarter) Quarters or weeks; the key is the start of the interval
e => Bucket.Ranges(e.Price, 5) About five ranges; the key is a GroupRange<decimal> with From, To, Label
e => Bucket.Ranges(e.Price, new[] { 0m, 100m, 500m }) Your own ranges, inclusive at both ends
e => e.Tags Each element, so a node with two tags is in two groups

Nodes without a value for the key form a group of their own, as null does in SQL. Its Label is (none) and its IsMissing is set; IncludeMissing(false) drops it. To group by a relation, use a runtime key (Without a Select) or a pivot: the group then holds the related node.

Aggregates

Inside Select, the group is an IGrouping<TKey, T>, so the calls are LINQ's: Count, LongCount, Sum, Average, Min and Max, plus CountDistinct. Each takes a property of the node, and the result has the type of the LINQ call. Over a group where no node has a value, a non-nullable result is 0 and a nullable one null. The nodes of a group are never read, so g.First(), g.Where(…) and the like are refused: filter with Where before GroupBy.

Ordering, filtering and paging the groups

Where (or Having), OrderBy, ThenBy, Skip, Take and Page work on the group rows. One sort by one aggregate of a single-key query runs in the engine with the paging, so Take(20) over a million nodes does not build every group. Execute() returns a ResultSetGroups<T> with TotalCount (the groups) and SourceCount (the nodes grouped).

Without a Select

Execute() straight after GroupBy gives NodeGroup<TKey> rows: Key, Count, Label, Labels and IsMissing. When the properties are known only at runtime, as in a dynamic report, choose keys and aggregates by property id:

// venueProperty, startsProperty, priceProperty: property ids, e.g. from db.Datastore.Datamodel
var rows = db.Query<IEvent>()
    .GroupBy(GroupKey.Values(venueProperty), GroupKey.Interval(startsProperty, DateInterval.Year))
    .Aggregate(PivotFunction.Sum, priceProperty)
    .Aggregate(PivotFunction.Average, priceProperty)
    .OrderByDescending(g => g["Price.Sum"])
    .Page(0, 50)
    .Execute();

foreach (var g in rows) Console.WriteLine($"{g.Label}: {g.Count} events, {g["Price.Sum"]:0} in total");

The key is then an object?[] with one entry per level. Read a measure by its name, <Property>.<Function>.

The query string and the admin UI

A GroupBy also goes to the store as a query string: IEvent.GroupBy("IEvent.Venue", "IEvent.Status").AddCount().AddSum("IEvent.Price"). Calendar and range levels are added with AddRow("IEvent.StartsUtc", "Month") or AddRowRanges(...). ToString() on a typed query gives its string. The admin UI's Query section has a groups view: pick the keys and aggregates, sort by a column, and click a row to see its nodes.