Median and percentiles — g.Median(…), g.Percentile(…, 0.9)

g.Median(x => value) · g.Percentile(x => value, fraction) · g.Select(x => value).Distinct().Count()

The middle value of a group, or any percentile of it — "half the jobs were done sooner than this", "nine in ten sooner than this" — computed by the database in the same GROUP BY as the counts and sums. Interpolated between ranks, the way a spreadsheet's PERCENTILE.INC does it, for numbers and for durations alike; the same answer from a list in memory. And the number of DIFFERENT values in a group, as `g.Select(…).Distinct().Count()`.

Summary#

An average hides the slow cases a team is judged on. A median and a percentile say them:

class TeamTimes {
  public string Team;
  public int Jobs;
  public decimal? MedianHours;
  public TimeSpan? Slowest;
  public int People;
}

entity Job {
  [Required, MaxLength(20)] string Team;
  [Required, MaxLength(60)] string Who;
  decimal Hours;
  DateTime At;
  DateTime? DoneAt;
}

List<TeamTimes> ByTeam() {
  return Job.Where(j => j.DoneAt != null)
            .GroupBy(j => j.Team)
            .Select(g => new TeamTimes {
              Team = g.Key,
              Jobs = g.Count(),
              MedianHours = g.Median(j => j.Hours),
              Slowest = g.Percentile(j => j.DoneAt - j.At, 0.9),     // nine in ten were done sooner than this
              People = g.Select(j => j.Who).Distinct().Count() })      // how many different people worked them
            .OrderBy(t => t.Team)
            .ToList();
}

One statement: percentile_cont(0.5), percentile_cont(0.9) and COUNT(DISTINCT …) beside COUNT(*), grouped by team.

Signature#

g.Median(x => value)                 // the 50th percentile
g.Percentile(x => value, 0.9)        // any percentile: a CONSTANT fraction from 0 to 1
g.Select(x => value).Distinct().Count()   // how many different values the group holds

value is a number (the answer is a decimal?) or a duration (TimeSpan?, a time between two dates included). A group with no values answers null.

Description#

Interpolated, the same everywhere#

A percentile sits at rank fraction × (n − 1) of the sorted values, and between two values it is interpolated: the hours 1, 2, 3, 4 have a median of 2.5 and a 90th percentile of 3.7. That is PERCENTILE.INC in a spreadsheet and percentile_cont in Postgres, and the in-memory engine answers the same for a List<T> — so a test over a list and a report over the table agree.

The fraction is a constant#

g.Percentile(x => x.Hours, 0.9) — written in the source, between 0 and 1. A fraction outside that is refused when it compiles; the database, the in-memory engine and a data source's plan all read the same number. Over a data source whose provider declares QueryFeatures.Percentiles (Snowflake, Databricks), a group's percentile of a number is worked out by the source, to ten decimal places (data held somewhere else (app.DataSources)).

Durations stay durations#

g.Percentile(j => j.DoneAt - j.At, 0.9) answers a TimeSpan, computed on the intervals themselves: nothing is rounded to seconds or hours on the way.