Documentation

Provenance.DistributionExample

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.

@[reducible, inline]
Equations
Instances For
    Dependency graph
    @[reducible, inline]

    The database holding it.

    Equations
    Instances For
      Dependency graph
      @[reducible, inline]

      One row, for the N = 1 clause of percent_rank.

      Equations
      Instances For
        Dependency graph
        @[reducible, inline]

        Its database.

        Equations
        Instances For
          Dependency graph
          @[reducible, inline]

          Two rows tied at 10 and one at 30.

          Equations
          Instances For
            Dependency graph
            @[reducible, inline]

            Its database.

            Equations
            Instances For
              Dependency graph
              @[reducible, inline]

              The base query.

              Equations
              Instances For
                Dependency graph
                @[reducible, inline]

                Ascending on the only column.

                Equations
                Instances For
                  Dependency graph
                  @[reducible, inline]

                  No partitioning: the whole relation is one partition.

                  Equations
                  Instances For
                    Dependency graph
                    @[reducible, inline]

                    Division scaled by 100, so a fraction is visible over ℕ.

                    Equations
                    Instances For
                      Dependency graph
                      @[reducible, inline]

                      SQL's ntile bucket of the rank r among N rows, for two buckets.

                      Equations
                      Instances For
                        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.