Ordered set aggregate functions (WITHIN GROUP)

Ordered set aggregate functions allow operations on a set of rows sorted with ORDER BY via the mandatory WITHIN GROUP clause. Commonly, such functions are used for performing computations that depend on a certain row ordering. Here, we can quickly mention hypothetical set functions such as RANK(), DENSE_RANK(), PERCENT_RANK(), or CUME_DIST(), and inverse distribution functions such as PERCENTILE_CONT(), PERCENTILE_DISC(), or MODE(). A particular case is represented by LISTAGG(), which is covered at the end of this section.

Hypothetical set functions

A hypothetical set function calculates something for a hypothetical value (let's denote it as hv). In this context, DENSE_RANK() computes the rank of hv without gaps, while RANK() does the same thing but with gaps. CUME_DIST() computes the cumulative distribution of hv (the relative rank of a row from 1/n to 1), while PERCENT_RANK() computes the percent rank of hv (the relative...