Missing Functions: PERCENTILE_DISC, PERCENTILE_CONT & MEDIAN
Blog
The best minds from Teradata, our partners, and customers blog about relevant topics and features.
turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
06-21-2011
03:45 PM
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Email to a Friend
- Printer Friendly Page
- Report Inappropriate Content
06-21-2011
03:45 PM
In my previous posts on missing functions i covered SQL:2008 DENSE_RANK and CUME_DIST & NTILE.
This time i'll talk about additional functions based on sorted sets of values, SQL:2008's PERCENTILE_DISC and PERCENTILE_CONT plus a MEDIAN calculation.
PERCENTILE_DISC
The CUME_DIST function computes the cumulative distribution of a set of values. The inverse operation to find the value for a given percentile is done using PERCENTILE_DISC.
According to SQL:2008 PERCENTILE_DISC(x) is the first value with a CUME_DIST greater than or equal to x.
This directly translates to the row with a
ROW_NUMBER() OVER (PARTITION BY part_col ORDER BY order_col)
= CEILING(COUNT(*) OVER (PARTITION BY part_col) * x
There's no CEILING function prior to TD13.10, but this returns the same value:
CAST(COUNT(*) OVER (PARTITION BY part_col) * x + 0.999999999999999 AS INT)
Caution:
This calculation will not work for x = 0, but you better use good old MIN instead of PERCENTILE_DISC(0).
-- SQL:2008 PERCENTILE_DISC aggregate function
SELECT
part_col
,PERCENTILE_DISC(x) WITHIN GROUP (ORDER BY data_col) --aggregate function
FROM tab
GROUP BY
part_col;
-- Teradata equivalent
SELECT
part_col
,data_col
FROM tab
QUALIFY ROW_NUMBER() OVER (PARTITION BY part_col ORDER BY data_col)
= CAST(x * COUNT(*) OVER (PARTITION BY part_col) + 0.999999999999999 AS INT);
PERCENTILE_DISC is also defined as OLAP function adding the OVER clause. This requires moving the QUALIFY-condition into a CASE and another nested OLAP function:
-- SQL:2008 PERCENTILE_DISC OLAP function
SELECT
part_col
,data_col
,PERCENTILE_DISC(x) WITHIN GROUP (ORDER BY data_col) OVER (PARTITION BY part_col)
FROM tab
-- Teradata equivalent
SELECT
part_col
,data_col
,MIN(pc) OVER (PARTITION BY part_col) AS PERCENTILE_DISC
FROM
(
SELECT
part_col
,data_col
,CASE
WHEN ROW_NUMBER () OVER (PARTITION BY part_col ORDER BY data_col)
= CAST(COUNT(*) OVER (PARTITION BY part_col) * x + 0.999999999999999 AS INT)
THEN data_col
END AS pc
FROM tab
) AS dt
PERCENTILE_CONT
Whereas PERCENTILE_DISC returns one of the existing values, PERCENTILE_CONT is based on a linear interpolation between two consecutive values. First you calculate a hypothetical row number rn = (x * (n - 1)) + 1, where x is the percentile and n is the number of rows per group. If rn has no fractional part then you already found the result row, else you have to consider the following row, too:
-- SQL:2008 PERCENTILE_CONT aggregate function
SELECT
part_col
,PERCENTILE_CONT(x) WITHIN GROUP (ORDER BY order_col)
FROM tab
GROUP BY
part_col;
-- Teradata equivalent
SELECT
part_col
,data_col
+ ((MIN(data_col) OVER (PARTITION BY part_col ORDER BY data_col ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - data_col)
* (((COUNT(*) OVER (PARTITION BY part_col) - 1) * x) MOD 1)) AS percentile_cont
FROM tab
QUALIFY ROW_NUMBER() OVER (PARTITION BY part_col ORDER BY data_col)
= CAST((COUNT(*) OVER (PARTITION BY part_col) - 1) * x AS INT) + 1;
-- SQL:2008 PERCENTILE_CONT OLAP function
SELECT
part_col,
data_col,
PERCENTILE_CONT(x) WITHIN GROUP (ORDER BY data_col)
OVER (PARTITION BY part_col)
FROM tab
-- Teradata equivalent
SELECT
part_col
,data_col
,MIN(pc) OVER (PARTITION BY part_col) AS PERCENTILE_CONT
FROM
(
SELECT
part_col
,data_col
,COUNT(*) OVER (PARTITION BY part_col) - 1 AS N
,ROW_NUMBER() OVER (PARTITION BY part_col ORDER BY data_col) - 1 AS rowno
,MIN(data_col) OVER (PARTITION BY part_col ORDER BY data_col ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - data_col AS diff
,CASE
WHEN rowno = CAST(N * x AS INT)
THEN data_col +(((N * x) MOD 1) * diff)
END AS pc
FROM tab
) AS dt
The CASE technique can also be used to calculate multiple percentiles in a single query, e.g. the Quartiles as aggregate functions:
SELECT part_col
,MIN(pc25) OVER (PARTITION BY part_col) AS quartile_1
,MIN(pc50) OVER (PARTITION BY part_col) AS quartile_2
,MIN(pc75) OVER (PARTITION BY part_col) AS quartile_3
FROM
(
SELECT
part_col
,COUNT(*) OVER (PARTITION BY part_col) - 1 AS N
,ROW_NUMBER() OVER (PARTITION BY part_col ORDER BY data_col) - 1 AS rowno
,MIN(data_col) OVER (PARTITION BY part_col ORDER BY data_col ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - data_col AS diff
,CASE
WHEN rowno = CAST(N * 0.25 AS INT)
THEN data_col +(((N * 0.25) MOD 1) * diff)
END AS pc25
,CASE
WHEN rowno = CAST(N * 0.50 AS INT)
THEN data_col +(((N * 0.50) MOD 1) * diff)
END AS pc50
,CASE
WHEN rowno = CAST(N * 0.75 AS INT)
THEN data_col +(((N * 0.75) MOD 1) * diff)
END AS pc75
FROM tab
QUALIFY rowno = CAST(N * 0.25 AS INT)
OR rowno = CAST(N * 0.50 AS INT)
OR rowno = CAST(N * 0.75 AS INT)
) AS dt
QUALIFY ROW_NUMBER() OVER (PARTITION BY part_col ORDER BY part_col) = 1
Omitting both QUALIFY clauses will return the quartiles as OLAP functions.
MEDIAN
The MEDIAN is just the special case of the 50th percentile.
In SQL:2008 there's no reason to define an extra funcion for that, but above formula can be simplified for a median calculation:
If the number of rows N is odd, then the median value is found in row (N/2)+1 else it's the average of this row's and the previous row's value:
-- MEDIAN aggregate function
SELECT
part_col
,MEDIAN(data_col)
FROM tab
GROUP BY part_col;
-- Teradata equivalent
SELECT
part_col
,CASE
WHEN COUNT(*) OVER (PARTITION BY part_col) MOD 2 = 1 THEN data_col
ELSE AVG(data_col) OVER (PARTITION BY part_col ORDER BY data_col ROWS 1 PRECEDING)
END
FROM tab
QUALIFY
ROW_NUMBER() OVER (PARTITION BY part_col ORDER BY data_col)
= COUNT(*) OVER (PARTITION BY part_col) / 2 + 1;
-- MEDIAN OLAP function
SELECT
part_col
,MEDIAN(data_col) OVER (PARTITION BY part_col)
FROM tab
GROUP BY part_col;
-- Teradata equivalent
SELECT
part_col
,MIN(med) OVER (PARTITION BY part_col) AS "MEDIAN"
FROM
(
SELECT
part_col
,CASE
WHEN ROW_NUMBER() OVER (PARTITION BY part_col ORDER BY data_col)
= COUNT(*) OVER (PARTITION BY part_col) / 2 + 1
THEN
CASE
WHEN COUNT(*) OVER (PARTITION BY part_col) MOD 2 = 1 THEN data_col
ELSE AVG(data_col) OVER (PARTITION BY part_col ORDER BY data_col ROWS 1 PRECEDING)
END
END AS med
FROM tab
) AS dt
This MEDIAN calculation returns the same result as the correspondent function in Excel and PERCENTILE_CONT reproduces Excel's PERCENTILE function.
11 Comments
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.