Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12985

support percentile and median window functions

    Details

      Description

      The percentile_cont and percentile_disc window functions are available in columnstore and many other databases. These allow calculation of percentiles. Percentile_cont will average 2 rows if one is not identified while Percentile_disc picks the first row in the window. Finally a median function should exist which is equivalent to percentile_cont(0.5).

      These have slightly different syntax than other window function to specify the column:

      percentile_cont(0.5) within group (order by amount) over (partition by owner) pct_cont,
      percentile_disc(0.5) within group (order by amount) over (partition by owner) pct_disc

      Some investigation

      percentile_cont and percentile_disc are not specifically window functions. They originally are "ordered-set aggregate functions" (#1) which one can also use as window functions (#2):

      Ordered-set aggreates

      The syntax for case #1:

        percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)
      

      Note the lack of OVER clause.
      Ordered-set aggregate functions are supported by:

      Ordered-set aggregates as window functions

      Syntax for case #2 (ordered-set aggregate, used as window function)

      PERCENTILE_DISC ( percentile )
      WITHIN GROUP (ORDER BY expr)
      OVER (  [ PARTITION BY expr_list ]  )
      

      (BTW: note that PostgreSQL doesn't support ordered-set-aggregates-as-window functions: https://www.postgresql.org/docs/current/static/functions-window.html ,

      any built-in or user-defined normal aggregate function (but not ordered-set or hypothetical-set aggregates) can be used as a window function)

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                varun Varun Gupta
                Reporter:
                dthompson David Thompson (Inactive)
              • Votes:
                1 Vote for this issue
                Watchers:
                9 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: