MySQL function to calculate percentile

Question

I'm trying to script a mysql function to calculate my percentiles. My code :

CREATE DEFINER=`root`@`localhost` FUNCTION `percentile`(tbl_name VARCHAR(255),col_name VARCHAR(255)) RETURNS double(10,2)
    READS SQL DATA
    DETERMINISTIC
BEGIN

RETURN CONCAT('SELECT `',col_name,'` FROM 
(SELECT t.*,  @row_num :=@row_num + 1 AS row_num FROM `',tbl_name,'` t, 
    (SELECT @row_num:=0) counter ORDER BY `',col_name,'`) 
temp WHERE temp.row_num = ROUND (0.75* @row_num);');
END

When I try to call it I get the error :

Error Code: 1265. Data truncated for column schema.percentile('table', 'column')

I can't figure out the source of the error. Any solutions ?

Your calculated value is a string (e.g. the string "select ...", not the result of that query), returning it as a double(10,2) will not match. You are probably looking for a [dynamic query](https://stackoverflow.com/q/5591338) — Solarflare, Dec 10 '20 at 01:36

score 0 · Answer 1 · edited Dec 10 '20 at 19:17

You can write a PROCEDURE:

    CREATE DEFINER=`root`@`localhost` 
    PROCEDURE `sp_percentile`(tbl_name VARCHAR(2555),col_name VARCHAR(5000))
    BEGIN SET @sql = CONCAT('SELECT `',col_name,'` FROM (SELECT t.*,  @row_num :=@row_num + 1 AS row_num FROM `',tbl_name,'` t, (SELECT @row_num:=0) counter ORDER BY `',col_name,'`)  temp WHERE temp.row_num = ROUND (0.75* @row_num);'); 
    PREPARE stmt FROM @sql; 
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt; 
    END

MySQL function to calculate percentile

1 Answers1