PERCENTILE_CONT

Description of percentile_cont.eps follows

Syntax

  • p: The target percentile (0 to 1).
  • expr: The column or expression used for sorting.
  • partition_expr: Optional. Specifies the partitioning of the result set.

Example 1 Usage:

Above: Finds the median (50th percentile) of column2 within the entire result set.

Partitioning:

Calculates the 75th percentile within each partition defined by column3.

Multiple Percentiles: Computes multiple percentiles for a better understanding of the data distribution.

SELECT
column1,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY column2) OVER () AS first_quartile,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column2) OVER () AS median,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY column2) OVER () AS third_quartile
FROM
your_table;

Handling NULLs: Filters out NULL values to calculate percentiles only for non-null data.

Custom Aggregation: Example.. Converts the median to a percentage by multiplying it by 100.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top