I would like to get the 5th, 50th, 95th percentile of a table
SELECT col1, col2, col3, AVG(col4), STD(col4),
PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY col4)
OVER (PARTITION BY col1, col2, col3) as 5th_percentile,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col4)
OVER (PARTITION BY col1, col2, col3) as 50th_percentile,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY col4)
OVER (PARTITION BY col1, col2, col3) as 95th_percentile
FROM table
GROUP BY col1, col2, col3
LIMIT 100
What I end up getting back is 5th_percentile == 50th_percentile == 95th_percentile
AVG(col4) STD(col4) 5th_percentile 50th_percentile 95th_percentile
300.000000 0.000000 300.000000 300.000000 300.000000
67.076600 16.968851 82.031792 82.031792 82.031792
66.166136 11.452172 78.348846 78.348846 78.348846
544.262809 68.269014 605.797302 605.797302 605.797302
22.523138 1.820358 24.000000 24.000000 24.000000
Whats going on?
Edit: The db is MemSQL
WITH a AS (
SELECT col1, col2, col3,
PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY col4)
OVER (PARTITION BY col1, col2, col3) as 5th_percentile,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col4)
OVER (PARTITION BY col1, col2, col3) as 50th_percentile,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY col4)
OVER (PARTITION BY col1, col2, col3) as 95th_percentile
FROM table
)
SELECT DISTINCT col1, col2, col3, 5th_percentile, 50th_percentile, 95th_percentile
FROM a
LIMIT 100
This works, looks like you can't do a groupby with percentile_cont