Calculate Quartiles & Median

quartile.sql

-- Calculates the 1st and 3rd quartiles, and the median, from a given table.
-- This cannot be optimized except by adding an index to the column, as MySQL
-- doesn't support NTILE() natively.

SET @temp_rows  = (SELECT GROUP_CONCAT(column ORDER BY column ASC SEPARATOR ',') FROM table WHERE column IS NOT NULL);
SET @temp_count = (SELECT COUNT(column) FROM table WHERE column IS NOT NULL);

SELECT
(SUBSTRING_INDEX(SUBSTRING_INDEX(@temp_rows, ',', ROUND(@temp_count * 0.25 + 1)), ',', -1)) AS 'q1',
(SUBSTRING_INDEX(SUBSTRING_INDEX(@temp_rows, ',', ROUND(@temp_count * 0.5 + 1)), ',', -1)) AS 'median',
(SUBSTRING_INDEX(SUBSTRING_INDEX(@temp_rows, ',', ROUND(@temp_count * 0.75 + 1)), ',', -1)) AS 'q3';

-- Clear the results so that they don't sit around forever, just in case connections
-- are persistant.
SET @temp_rows  = NULL;
SET @temp_count = NULL;