My previous post on calculating percentiles with
MySQL generated some comments and good discussion. In particular,
I got some very interesting comments from Vladimir.
Basically, Vladimir was doubtful whether the
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 < g2.length
GROUP BY g2.length
HAVING p > 0.9
ORDER BY p
LIMIT 1
First, this query sets up two identical subqueries in …