mysql - 显示达到特定计数阈值的不同用户

Question

这是我的表格，显示用户名和他们得分的时间戳:

id  user    date
1   Aaron   23/02/2012 22:44
2   Betty   23/02/2012 22:47
3   Carlos  24/02/2012 16:01
4   David   28/02/2012 11:40
5   David   28/02/2012 12:32
6   David   28/02/2012 16:59
7   Aaron   2/03/2012 13:46
8   Aaron   30/03/2012 18:37
9   Betty   30/03/2012 19:58
10  Emma    9/04/2012 6:49
11  Emma    9/04/2012 13:19
12  Emma    9/04/2012 18:20
13  Emma    9/04/2012 20:46
14  Aaron   10/04/2012 15:47
15  Betty   10/04/2012 19:15
16  Betty   10/04/2012 20:40
17  Carlos  11/04/2012 9:44
18  Carlos  11/04/2012 20:01
19  David   11/04/2012 23:17
20  David   12/04/2012 17:09

这是我试图实现的结果表，即 x 轴显示月-年，y 轴显示在该范围内达到特定积分阈值的用户数量月份:

date    1 point First time? 2 points    First time? 3 points    First time? 4 points    First time?     Total
Feb-12  A,B,C   A,B,C                               D           D                                       4
Mar-12  B                   A           A                                                               3
Apr-12  A,B,C               B,C,D       B,C,D                               E           E               4

我只能计算给定月份内的总得分和不同得分者的总数:

SELECT DISTINCT CONCAT (MONTHNAME(date), ' ', YEAR(date)) as 'date', COUNT(id) as total_points, COUNT(distinct referrer_id) as number_of_scorers
from points
group by CONCAT (MONTH(date), ' ', YEAR(date))
order by YEAR(date), MONTH(date)

这只是给我:

date    total_points    number_of_scorers
Feb-12  6               4
Mar-12  3               3
etc.

所以我的问题是:

1. 如何修改查询以显示每个月内哪些用户达到了每个积分阈值？
2. 如何修改查询以显示哪些用户在该月内首次达到每个积分阈值？

谢谢

Answer 1

您需要的基本查询是这样的:

select date_format(date, '%Y-%m') as yyyymm, user, count(*) as points
from t
group by date_format(date, '%Y-%m') as yyyymm, user;

这将获取每个用户一个月内的积分数。

剩下的只是聚合、连接和条件:

select ymu.yyyymm,
       group_concat(case when ymu.points = 1 then user end) as Points1_Users,
       group_concat(case when ymu.points = 1 and ymu.yyyymm = u.min_yyyymm then user end) as Points1_Users_First,
       group_concat(case when ymu.points = 2 then user end) as Points2_Users,
       group_concat(case when ymu.points = 2 and ymu.yyyymm = u.min_yyyymm then user end) as Points2_Users_First
from (select date_format(date, '%Y-%m') as yyyymm, user, count(*) as points
      from t
      group by date_format(date, '%Y-%m') as yyyymm, user
     ) ymu join
     (select user, min(yyyymm) as min_yyyymm
      from (select date_format(date, '%Y-%m') as yyyymm, user, count(*) as points
            from t
            group by date_format(date, '%Y-%m') as yyyymm, user
           ) t
      group by user
     ) u
     on ymu.user = u.user
group by yyyymm
order by yyyymm;