Window

Select((s, i) => …) · Window.Rank(orderBy: …, partitionBy: …) · RowNumber · DenseRank · Lag · Lead · Sum · Avg · Min · Max · Count

Ranking and neighbours inside a query's result. The indexed `Select((s, i) => …)` over an ordered chain is C#'s own spelling of a row number; the `Window.*` functions add what C# has no spelling for — per-partition ranks, the previous/next row's value, running aggregates — all computed by the database in the same statement.

Summary#

A window function computes a value about a row's place among the other rows — its rank, the previous row's value, a per-group aggregate — without collapsing the rows the way GroupBy does. Every row still comes back; each carries its answer.

Signature#

<ordered query>.Select((s, i) => …)                          // i = the 0-based position, C#'s own spelling
Window.RowNumber(orderBy: k)                                  → int
Window.Rank(orderBy: k, partitionBy: p)                       → int    // ties share, next rank skips
Window.DenseRank(orderBy: k, partitionBy: p)                  → int    // ties share, no gap
Window.Lag(value, orderBy: k, partitionBy: p)                 → T?     // the PREVIOUS row's value — null at the edge
Window.Lead(value, orderBy: k, partitionBy: p)                → T?     // the NEXT row's value — null at the edge
Window.Sum(value, orderBy: k, partitionBy: p) · Avg · Min · Max · Count
Window.Sum(value, orderBy: k, rowsBefore: n, rowsAfter: m)   // a sliding frame — aggregators only

orderByDescending: orders the window the other way. partitionBy: is optional — without it the window is the whole result; partitionBy: new { a, b } partitions on the pair. rowsBefore:/rowsAfter: bound an aggregate to the rows around the current one; without them an ordered aggregate is a running total. Window.* lives inside a .Select(…) projection over stored rows, nowhere else.

Description#

How do I number the rows?#

With the index C# already gives a Select — legal over stored rows once the chain names an order (a table has no position until you say which):

entity Score {
  [Required] string Player;
  [Required] string Region;
  [Required] string Tier;
  int Points;
}

string Board() {
  var rows = Score.OrderByDescending(s => s.Points)
                  .Select((s, i) => new { Line = (i + 1) + ". " + s.Player });
  var outText = "";
  foreach (var r in rows) { outText = outText + r.Line + "\n"; }
  return outText;
}

Without the OrderBy this refuses at compile, with the order as the remedy.

How do I rank within groups?#

partitionBy: restarts the window per group — every region gets its own ranking, in one statement:

string RegionBoards() {
  var rows = Score.OrderBy(s => s.Region)
                  .Select(s => new {
                    s.Player, s.Region,
                    Rank = Window.Rank(orderByDescending: s.Points, partitionBy: s.Region),
                  });
  var outText = "";
  foreach (var r in rows) { outText = outText + r.Region + ":" + r.Player + "#" + r.Rank + "\n"; }
  return outText;
}

Rank gives ties the same number and skips the next (1, 1, 3); DenseRank doesn't skip (1, 1, 2); RowNumber never ties.

A group keyed by more than one column is an anonymous object — the same spelling GroupBy uses for a composite key. The window restarts wherever any part of the pair changes:

string TierBoards() {
  var rows = Score.OrderBy(s => s.Region).ThenBy(s => s.Tier)
                  .Select(s => new {
                    s.Player, s.Region, s.Tier,
                    Rank = Window.Rank(orderByDescending: s.Points, partitionBy: new { s.Region, s.Tier }),
                  });
  var outText = "";
  foreach (var r in rows) { outText = outText + r.Region + "/" + r.Tier + ":" + r.Player + "#" + r.Rank + "\n"; }
  return outText;
}

How do I sum a sliding window?#

An aggregate with an orderBy: is a running total by default — every row sums itself and everything before it. rowsBefore: and rowsAfter: narrow that to the rows around the current one, counted in the window's order: rowsBefore: 2 is this row and the two before it; rowsAfter: 1 is this row and the next; both together is a centred frame. A bound counts ROWS, not values — three rows with equal points are still three rows.

string Trend() {
  var rows = Score.OrderBy(s => s.Points)
                  .Select(s => new {
                    s.Player,
                    Running = Window.Sum(s.Points, orderBy: s.Points),
                    Around  = Window.Avg(s.Points, orderBy: s.Points, rowsBefore: 1, rowsAfter: 1),
                    Ahead   = Window.Count(orderBy: s.Points, rowsAfter: 2),
                  });
  var outText = "";
  foreach (var r in rows) { outText = outText + r.Player + ":" + r.Running + "/" + r.Around + "/" + r.Ahead + "\n"; }
  return outText;
}

A frame belongs to the aggregators — Sum, Avg, Min, Max, Count. A rank is over the whole partition and Lag/Lead reach a fixed distance already, so a bound on any of those refuses at compile; so does a frame with no orderBy: (a slice of an unordered set means nothing) and a negative bound.

How do I read the previous row?#

Lag (and Lead) hand you a neighbouring row's value. At the window's edge there is no neighbour, so the answer is null — the type says so, and you answer for it like any other absence:

string Gaps() {
  var rows = Score.OrderBy(s => s.Points)
                  .Select(s => new {
                    s.Player,
                    Gap = s.Points - (Window.Lag(s.Points, orderBy: s.Points) ?? s.Points),
                  });
  var outText = "";
  foreach (var r in rows) { outText = outText + r.Player + ":" + r.Gap + "\n"; }
  return outText;
}

Where may a window stand?#

Only inside a .Select(…) projection over stored rows — a window ranks a SET the database holds. Anywhere else it refuses at compile, pointing here; over a local list, C#'s own tools (Select((x, i) => …) on the list, sorting, indexing) already answer.