My SQL Dump

MySQL musings by a self professed MySQL Geek


  • 1

Modified percentile function

This is the flexviews function that creates a percentile calculation for a given expression, based on this blog post:
http://rpbouman.blogspot.com/2008/07/calculating-nth-percentile-in-mysql.html

CREATE DEFINER=flexviews@localhost FUNCTION flexviews.`get_percentile`( v_mview_expression TEXT, v_percentile INT) RETURNS TEXT CHARSET latin1
READS SQL DATA
BEGIN

-- FIXME - needs to be made a variable in flexviews.settings table
set group_concat_max_len = 1024 * 1024 * 1024;

return CONCAT(
'SUBSTRING_INDEX(
SUBSTRING_INDEX(
SUBSTR(
GROUP_CONCAT(DISTINCT IFNULL(',
v_mview_expression,
', "") ORDER BY ',v_mview_expression,'
),
MAX(',v_mview_expression,' IS NULL) + 1
)
, ","
, ', v_percentile, '/100 * COUNT(distinct ', v_mview_expression, ') + 1)
, ","
, -1
)'
);


END ;;

  • 1
?

Log in