What mysql query would let me get aggregated stats, by month from this table?

Question

I have a points system setup on my site, where every single point accumulated is logged in the points table. The structure is simple, p_userid, p_points (how many points accumulated during this action), and p_timestamp.

I wanna display top 3 point accumulating users, for each month. So essentially, it should sum the p_points table for the month, for each user id, and display the top 3 users, grouped into months. The user ids will be joined to a users table, to get actual user names.

What would be the best way to do it? I use php/mysql.

EDIT: As a possible solution, I could create another column, and log YYYY-MM into it, and simply group it based on that, but thats more data I gotta log, 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

This isn't easy in MySQL.

First you need to create a table of variables, one for storing the current group, and one for storing the current row number in the group. Initialize them both to NULL.

Then iterate group by month and select all rows ordered by score and select the current rown number and increase it. If the group changes, reset the row number to one.

Then put all this in a subselect and in the outer select, select all rows with rownumber <= 3.

You could 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

Hm,

Too simple?

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

Syntax might be a little bit out (relatively new to SQL) - wouldn't iterating through a query like this get the result you're looking for? Must admit that Mark's response makes me think this definitely is too simple but figured I'd let you see it anyway.

Answer 3

I'm plpgsql addicted and I don't know if something simmilar can work in MySQL, and how PHP will get results (I don't know if multiple queries will be taken as UNION) but few 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 query

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

and at the end

DEALLOCATE PREPARE stmt1;