Showing entries 1 to 4 |
GROUP_CONCAT()
solution for calculating quantiles I figured it would be nice to find a better way to calculate the median too. GROUP_CONCAT()
, but I think that this is a better way:
SELECT AVG(length) AS median -- take the average of left and right median
, MIN(length) AS left_median --
, MAX(length) AS right_median --
, @l AS left_median_position --
, @r AS right_median_position --
FROM
GROUP_CONCAT()
solution would be optimal in comparison to a JOIN
. His proposal is to solve it like this:
SELECT SUM(g1.r) sr
, g2.length l
, SUM(g1.r)/(SELECT COUNT(*) FROM film) p
FROM (SELECT COUNT(*) r, length FROM film GROUP BY length) g1
JOIN (SELECT COUNT(*) r, length FROM film GROUP BY length) g2
ON g1.length GROUP BY g2.length
HAVING p > 0.9
ORDER BY p
LIMIT 1
Showing entries 1 to 4 |