Search code examples
sqlsinglestore

PERCENTILE_CONT() returns same value regardless of input parameter


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


Solution

  • 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