What mysql query allowed me to get aggregated monthly statistics from this table?

Question

I have a point system setup on my site, where each accumulated point is recorded in a point table. The structure is simple, p_userid, p_points (the number of points accumulated during this action) and p_timestamp.

I want to display the top 3-point batteries for each month. Essentially, it should summarize the p_points table for the month for each user ID and display the top three users grouped in months. User IDs will be joined to the user table to get the actual user names.

What would be the best way to do this? I am using php / mysql.

EDIT: As a possible solution, I could create another column and write YYYY-MM into it and just group it based on this, but this is more data that I have to write for an already huge table.

EDIT 2:

Data stored as such

INSERT INTO `points` (`point_userid`, `point_points`, `point_code`, `point_date`) VALUES (8465, 20, 3, 1237337627), (46745, 20, 3, 1237337678), (7435, 20, 3, 1237337733), (46565, 20, 3, 1237337802), (4466, 20, 3, 1237337836), (34685, 20, 3, 1237337885), (8544, 20, 3, 1237337908), (6454, 20, 3, 1237337998), (45765, 20, 3, 1237338008), (3476, 20, 3, 1237338076); 

Answer 1

In MySQL, this is not easy.

First you need to create a variable table, one to store the current group, and one to store the current line number in the group. Initialize them as NULL.

Then we sort through the group by month and select all the rows sorted by account, and select the current rown number and increase it. If the group changes, reset the line number to one.

Then put it all in the sub-selection and in the outer select, select all rows with rownumber <= 3 ..

You can use this query:

 SELECT month, p_userid, points FROM ( SELECT *, (@rn := CASE WHEN month = @last_month THEN @rn + 1 ELSE 1 END) AS rn, (@last_month := month) FROM ( SELECT p_userid, month(p_timestamp) AS month, SUM(p_points) AS points FROM Table1, (SELECT @last_month := NULL, @rn := 0) AS vars GROUP BY p_userid, month(p_timestamp) ORDER BY month, points DESC ) AS T1 ) AS T2 WHERE rn <= 3 

Result:

 Month User Score 1 4 7 1 3 5 1 2 4 2 4 17 2 5 10 2 3 6 

Test data:

 CREATE TABLE Table1 (p_userid INT NOT NULL, p_points INT NOT NULL, p_timestamp TIMESTAMP NOT NULL); INSERT INTO Table1 (p_userid, p_points, p_timestamp) VALUES (1, 1, '2010-01-01'), (1, 2, '2010-01-02'), (1, 3, '2010-02-01'), (2, 4, '2010-01-01'), (3, 5, '2010-01-01'), (3, 6, '2010-02-01'), (4, 7, '2010-01-01'), (4, 8, '2010-02-01'), (4, 9, '2010-02-02'), (5, 10, '2010-02-02'); 

Answer 2

Hmm

Too easy?

 SELECT COUNT(tb1.p_points) as total_points, tb1.p_userid, tb1.p_timestamp, tb2.username FROM tb1, tb2 WHERE tb1.p_userid = tb2.username AND p_timestamp BETWEEN 'start_of_date' AND 'end_of_month' GROUP BY p_userid ORDER BY total_points DESC LIMIT 3 

The syntax may be slightly different (relatively new to SQL) - would it not repeat the query in such a way as to get the result you are looking for? I have to admit that Mark’s answer makes me think that it’s definitely too easy, but decided that I would see him anyway.

Answer 3

I am plpgsql dependent and I don’t know if something simmilar can work in MySQL and how PHP will get the results (I don’t know if several queries will be executed as UNION), but several tests were promising.

 CREATE PROCEDURE topusers(OUT query TEXT) BEGIN DECLARE time TIMESTAMP; SELECT MIN(CONCAT(EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(p_timestamp)), '01')) INTO time FROM t; SET @query = ''; REPEAT SET @query = CONCAT(@query, '(SELECT SUM(p_points) as total_points, p_userid, ', UNIX_TIMESTAMP(time), ' FROM t WHERE p_timestamp BETWEEN ', UNIX_TIMESTAMP(time), ' AND ', UNIX_TIMESTAMP(ADDDATE(time, INTERVAL 1 MONTH)), ' GROUP BY p_userid ORDER BY total_points DESC LIMIT 3)'); SELECT ADDDATE(time, INTERVAL 1 MONTH) INTO time; IF time < NOW() THEN SET @query=CONCAT(@query, ' UNION '); END IF; UNTIL time > NOW() END REPEAT; SELECT @query INTO query; END// 

And request

 CALL topusers(@query); PREPARE stmt1 FROM @q; EXECUTE stmt1; 

and in the end

 DEALLOCATE PREPARE stmt1;