每天为每个用户选择最高的3个分数 [英] Select highest 3 scores in each day for every user
问题描述
我有一个像这样的MYSQL表:
I have a MYSQL table like this:
id | userid | score | datestamp |
-----------------------------------------------------
1 | 1 | 5 | 2012-12-06 03:55:16
2 | 2 | 0,5 | 2012-12-06 04:25:21
3 | 1 | 7 | 2012-12-06 04:35:33
4 | 3 | 12 | 2012-12-06 04:55:45
5 | 2 | 22 | 2012-12-06 05:25:11
6 | 1 | 16,5 | 2012-12-06 05:55:21
7 | 1 | 19 | 2012-12-06 13:55:16
8 | 2 | 8,5 | 2012-12-07 06:27:16
9 | 2 | 7,5 | 2012-12-07 08:33:16
10 | 1 | 10 | 2012-12-07 09:25:19
11 | 1 | 6,5 | 2012-12-07 13:33:16
12 | 3 | 6 | 2012-12-07 15:45:44
13 | 2 | 4 | 2012-12-07 16:05:16
14 | 2 | 34 | 2012-12-07 18:33:55
15 | 2 | 22 | 2012-12-07 18:42:11
我想显示如下用户分数: 如果某天某个用户的得分超过3，则只会获得最高3分，请对该用户每天重复该得分，然后将所有天数加在一起.我想为每个用户显示此金额.
I would like to display user scores like this: if a user on a certain day has more than 3 scores it would get only highest 3, repeat that for every day for this user and then add all days together. I want to display this sum for every user.
因此，在上面的示例中，用户1在06.12上.我会将前3个得分加在一起，而忽略第4个得分，然后从第二天开始将该数字加到前3个，依此类推.我需要每个用户使用该号码.
So in the example above for user 1 on 06.12. I would add top 3 scores together and ignore 4th score, then add to that number top 3 from the next day and so on. I need that number for every user.
预期输出为:
userid | score
--------------------
1 | 59 //19 + 16.5 + 7 (06.12.) + 10 + 6.5 (07.12.)
2 | 87 //22 + 0.5 (06.12.) + 34 + 22 + 8.5 (07.12.)
3 | 18 //12 (06.12.) + 6 (07.12.)
我希望这更加清楚:)
我非常感谢您的帮助，因为我被困住了.
I would really appreciate the help because I am stuck.
推荐答案
如果您对我的评论的回答为yes，请查看以下代码:)由于您的数据全部为2012年和11月，我花了一天.
Please take a look at the following code, if your answer to my comment is yes :) Since your data all in 2012, and month of november, I took day.
- SQLFIDDLE 示例
查询:
select y.id, y.userid, y.score, y.datestamp
from (select id, userid, score, datestamp
from scores
group by day(datestamp)) as y
where (select count(*)
from (select id, userid, score, datestamp
from scores group by day(datestamp)) as x
where y.score >= x.score
and y.userid = x.userid
) =1 -- Top 3rd, 2nd, 1st
order by y.score desc
;
结果:
ID USERID SCORE DATESTAMP
8 2 8.5 December, 07 2012 00:00:00+0000
20 3 6 December, 08 2012 00:00:00+0000
1 1 5 December, 06 2012 00:00:00+0000
根据您对问题的最新更新.
如果您需要按年/月/日按每位用户的数量来查找最高的用户，则可以简单地向上述查询中添加诸如sum之类的聚合函数.我很高兴，因为您的样本数据只有一年，所以没有按年或按月分组的点.这就是为什么我要花一天的时间.
Based on your latter updates to question.
If you need some per user by year/month/day and then find highest, you may simply add aggregation function like sum to the above query. I am reapeating myself, since your sample data is for just one year, there's no point group by year or month. That's why I took day.
select y.id, y.userid, y.score, y.datestamp
from (select id, userid, sum(score) as score,
datestamp
from scores
group by userid, day(datestamp)) as y
where (select count(*)
from (select id, userid, sum(score) as score
, datestamp
from scores
group by userid, day(datestamp)) as x
where y.score >= x.score
and y.userid = x.userid
) =1 -- Top 3rd, 2nd, 1st
order by y.score desc
;
基于总和的结果:
ID USERID SCORE DATESTAMP
1 1 47.5 December, 06 2012 00:00:00+0000
8 2 16 December, 07 2012 00:00:00+0000
20 3 6 December, 08 2012 00:00:00+0000
已使用新的源数据示例进行更新
Simon，请看一下我自己的样本.随着您数据的变化，我使用了我的.
这是参考.我使用了纯ansi样式，没有任何over partition或dense_rank.
另请注意，我使用的数据获得的是前2名而不是前3名.您可以根据需要进行更改.
UPDATED WITH NEW SOURCE DATA SAMPLE
Simon, please take a look at my own sample. As your data was changing, I used mine.
Here is the reference. I have used pure ansi style without any over partition or dense_rank.
Also note the data I used are getting top 2 not top 3 scores. You can change is accordingly.
猜猜看，答案比您的第一个数据给您的第一印象要简单10倍.
查询到1: -每天按用户排名前2位的金额
Query to 1: -- for top 2 sum by user by each day
SELECT userid, sum(Score), datestamp
FROM scores t1
where 2 >=
(SELECT count(*)
from scores t2
where t1.score <= t2.score
and t1.userid = t2.userid
and day(t1.datestamp) = day(t2.datestamp)
order by t2.score desc)
group by userid, datestamp
;
查询1的结果
USERID SUM(SCORE) DATESTAMP
1 70 December, 06 2012 00:00:00+0000
1 30 December, 07 2012 00:00:00+0000
2 22 December, 06 2012 00:00:00+0000
2 25 December, 07 2012 00:00:00+0000
3 30 December, 06 2012 00:00:00+0000
3 30 December, 07 2012 00:00:00+0000
最终查询: -连续两天用户的前2名总和
Final Query: -- for all two days top 2 sum by user
SELECT userid, sum(Score)
FROM scores t1
where 2 >=
(SELECT count(*)
from scores t2
where t1.score <= t2.score
and t1.userid = t2.userid
and day(t1.datestamp) = day(t2.datestamp)
order by t2.score desc)
group by userid
;
最终结果:
USERID SUM(SCORE)
1 100
2 47
3 60
这里是我使用的数据直接计算的快照.
Here goes a snapshot of direct calculations of data I used.
这篇关于每天为每个用户选择最高的3个分数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
