The distribution functions, and the two ranks, on a worked relation #
percent_rank, cume_dist and ntile are aggregate expressions over
two frames of one partition (AggQueryIn.WinExpr), so what they compute
is not read off either frame alone. This file checks the values against
SQL's on a three-row relation, including the two cases where a reading
that treated the two frames separately would go wrong: a partition of
one row, where percent_rank is 0 by its own clause rather than by
dividing by zero, and a partition with peers, where the rank keeps a
class together.
The arithmetic is the parameter each of the three takes: here the
numerators are scaled by 100 so that ℕ's truncating division shows
the fraction.
The file also contrasts rank with dense_rank, which differ exactly
where the order has peers: the one counts rows before a class, the other
counts distinct order values.
The checks are theorems closed by decide: the clause's order is read by
an insertion sort (OrderSpec.sortSeq), which the kernel reduces, so each
computed relation is compared against its expected value by the kernel
rather than printed. The two ranks go through AggQueryIn.castKind, whose
cast the kernel cannot reduce, so their checks rewrite with
evaluatePlain_rank and evaluatePlain_denseRank first – which is the
same equation, stated on the cast-free right-hand side those lemmas
give.
Three rows in one partition, ordered by their only column.
Instances For
Dependency graph
The database holding it.
Equations
Instances For
Dependency graph
One row, for the N = 1 clause of percent_rank.
Instances For
Dependency graph
Its database.
Equations
Instances For
Dependency graph
Two rows tied at 10 and one at 30.
Instances For
Dependency graph
Its database.
Equations
Instances For
Dependency graph
The base query.
Equations
Instances For
Dependency graph
Ascending on the only column.
Equations
Instances For
Dependency graph
No partitioning: the whole relation is one partition.
Equations
- DistributionExample.noP x✝ = 0
Instances For
Dependency graph
Division scaled by 100, so a fraction is visible over ℕ.
Equations
- DistributionExample.pct a b = a * 100 / b
Instances For
Dependency graph
Dependency graph
percent_rank on three distinct rows: (r-1)/(N-1), so 0,
1/2, 1 – printed as 0, 50, 100.
cume_dist on the same: c/N with c over the rows up to the
current one and its peers, so 1/3, 2/3, 1 – 33, 66, 100.
ntile(2) on three rows: buckets 1, 1, 2.
percent_rank on one row is 0, by its own clause for N = 1
and not by a division. This is the case a reading of the two frames as
separate aggregates would have to invent a value for.
percent_rank keeps a class of peers together: the two rows
tied at 10 both have rank 1, so both are 0 – 0, 0, 100.
cume_dist counts the peers in: both tied rows are 2/3 –
66, 66, 100.
The two families the expression reads, encoded as
10 * before + whole: 3, 13, 23, so the frame of the rows strictly
before gives 0, 1, 2 and the whole partition gives 3 on every row.
That pair is what percent_rank divides, and reading the two
separately would allow 2 beside 1.
rank against dense_rank with peers: on 10, 10, 30 the ranks
are 1, 1, 3 – the two tied rows share rank 1 and the third counts the
two rows before it – while the dense ranks are 1, 1, 2, counting the
one distinct order value before 30. That is the whole difference
between the two, and it is why drank counts values and rank counts
rows.
And on three distinct values the two agree, at 1, 2, 3.