I have two tables in a MySQL 5.7.22 database:
reasons. Each post row has and belongs to many reason rows. Each reason has a weight associated with it, and each post therefore has a total aggregated weight associated with it.
For each increment of 10 points of weight (i.e. for 0, 10, 20, 30, etc), I want to get a count of posts that have a total weight less than or equal to that increment. I'd expect the results for that to look something like this:
weight | post_count --------+------------ 0 | 0 10 | 5 20 | 12 30 | 18 ... | ... 280 | 20918 290 | 21102 ... | ... 1250 | 118005 1260 | 118039 1270 | 118040
The total weights are approximately normally distributed, with a few very low values and a few very high values (maximum is currently 1277), but the majority in the middle. There are just under 120,000 rows in
posts, and around 120 in
reasons. Each post has on average 5 or 6 reasons.
The relevant parts of the tables look like this:
CREATE TABLE `posts` ( id BIGINT PRIMARY KEY ); CREATE TABLE `reasons` ( id BIGINT PRIMARY KEY, weight INT(11) NOT NULL ); CREATE TABLE `posts_reasons` ( post_id BIGINT NOT NULL, reason_id BIGINT NOT NULL, CONSTRAINT fk_posts_reasons_posts (post_id) REFERENCES posts(id), CONSTRAINT fk_posts_reasons_reasons (reason_id) REFERENCES reasons(id) );
So far, I've tried dropping the post ID and total weight into a view, then joining that view to itself to get an aggregated count:
CREATE VIEW `post_weights` AS ( SELECT posts.id, SUM(reasons.weight) AS reason_weight FROM posts INNER JOIN posts_reasons ON posts.id = posts_reasons.post_id INNER JOIN reasons ON posts_reasons.reason_id = reasons.id GROUP BY posts.id ); SELECT FLOOR(p1.reason_weight / 10) AS weight, COUNT(DISTINCT p2.id) AS cumulative FROM post_weights AS p1 INNER JOIN post_weights AS p2 ON FLOOR(p2.reason_weight / 10) <= FLOOR(p1.reason_weight / 10) GROUP BY FLOOR(p1.reason_weight / 10) ORDER BY FLOOR(p1.reason_weight / 10) ASC;
That is, however, unusably slow - I let it run for 15 minutes without terminating, which I can't do in production.
Is there a more efficient way to do this?