Mysql – Show all results for past N “insert batches”

MySQL

Every once in a while I have several users check in with some value:

+-------+-------------+------+---------------------+
| id    | user        | RT   | time                |
+-------+-------------+------+---------------------+
| 78984 | Twitter     | 0.19 | 2013-04-12 06:04:16 |
| 78983 | Instagram   | 0.15 | 2013-04-12 06:04:16 |
| 78982 | Facebook    | 1.44 | 2013-04-12 06:04:16 |
| 78981 | Youtube     | 0.12 | 2013-04-12 06:04:16 |
| 78980 | Dailymotion | 3.72 | 2013-04-12 06:04:16 |
| 78979 | Metacafe    | 0.29 | 2013-04-12 06:04:16 |
| 78978 | Google+     | 0.32 | 2013-04-12 06:04:16 |

| 78977 | Twitter     | 0.47 | 2013-04-12 06:04:01 |
| 78976 | Flickr      | 1.69 | 2013-04-12 06:04:01 |
| 78975 | Instagram   | 0.15 | 2013-04-12 06:04:01 |
| 78974 | Facebook    | 3.01 | 2013-04-12 06:04:01 |
| 78973 | Photobucket | 1.29 | 2013-04-12 06:04:01 |
| 78972 | Youtube     | 0.22 | 2013-04-12 06:04:01 |
| 78971 | Dailymotion | 4.11 | 2013-04-12 06:04:01 |
| 78970 | Vimeo       | 1.12 | 2013-04-12 06:04:01 |
| 78969 | Metacafe    | 0.73 | 2013-04-12 06:04:01 |
| 78968 | Google+     | 0.31 | 2013-04-12 06:04:01 |

| 78967 | Twitter     | 0.56 | 2013-04-12 06:04:13 |
| 78966 | Youtube     | 0.36 | 2013-04-12 06:04:13 |
| 78965 | Dailymotion | 4.39 | 2013-04-12 06:04:13 |
| 78964 | Metacafe    | 0.51 | 2013-04-12 06:04:13 |
| 78963 | Google+     | 0.66 | 2013-04-12 06:04:13 |

| 78962 | Twitter     | 0.21 | 2013-04-12 06:03:16 |
| 78961 | Instagram   | 0.11 | 2013-04-12 06:03:16 |
| 78960 | Facebook    |  1.4 | 2013-04-12 06:03:16 |
| 78959 | Youtube     | 0.15 | 2013-04-12 06:03:16 |
| 78958 | Dailymotion | 0.38 | 2013-04-12 06:03:16 |
| 78957 | Metacafe    | 0.23 | 2013-04-12 06:03:16 |
| 78956 | Google+     | 0.31 | 2013-04-12 06:03:16 |
+-------+-------------+------+---------------------+

Each 'batch' will have the same time value. For clarity I've added spaces between each 'batch' in the example output. Not every user is represented in each 'batch'. Due to latencies, the time values for each batch are not necessarily sequential, though they should only be outside of true sequentiality by less than a minute (in the example output it can been seen that the second and third batches' positions are transposed in time).

How might I get the total RT for each user for the last N 'batches'? For instance the total RT for user 'Twitter' is 0.19 + 0.47 + 0.56 + 0.21 whereas the total RT for user 'Vimeo' is 0 + 1.12 + 0 + 0.

If I were to do this in a sloppy way via Python or PHP, I would pull (amountOfUsers * N) rows from the table (ordered by time, not id) and add them up until I got to an N+1 distinct time value. This would require at least two queries (one to get count(*) DISTINCT USERS and one for the real query) and also a bit of 'logic'. However, is there a clever SQL query which could get me the answer in one query? This would be the ideal output:

+-------------+-------+
| user        | RT    |
+-------------+-------+
| Twitter     |  1.43 |
| Flickr      |  1.69 |
| Instagram   |  0.41 |
| Facebook    |  5.85 |
| Photobucket |  1.29 |
| Youtube     |  0.95 |
| Dailymotion | 12.62 |
| Vimeo       |  1.12 |
| Metacafe    |  1.78 |
| Google+     |  1.60 |
+-------------+-------+

I would like to avoid the use of a stored procedure as A) my user doesn't have CALL OR EXECUTE privilege, and B) I would like to learn to use SQL more effectively.

The target environment is MySQL 5.x with InnoDB tables running on any arbitrary Linux distro. Thanks.

Best Answer

if i understand you correctly, here will do:

select user, sum(RT) 
from test2 a
inner join ( select distinct(time) from test2 order by time desc limit 2) t
on a.time=t.time
group by user

The 'N' for number of latest batch is determined by the limit 2 in the subquery t.

For example, if i run the above query, showing data for the last 2 batches, the output is:

Dailymotion 8.11
Facebook    1.44
Google+     0.98
Instagram   0.15
Metacafe    0.80
Twitter     0.75
Youtube     0.48

But the query above won't show any users who don't have data in the last 'N' batch. If you need to show those users as well (as '0' RT), you will have to join the above query with your users table.

P/S: The test2 table in the query above contains the exact data as in your first table in your post.

Related Question