Skip to content

Instantly share code, notes, and snippets.

@rk
Created May 16, 2016 21:01
Show Gist options
  • Save rk/d44f72c7316064d421937a6088fb8107 to your computer and use it in GitHub Desktop.
Save rk/d44f72c7316064d421937a6088fb8107 to your computer and use it in GitHub Desktop.
Calculate Quartiles & Median
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment