Mysql 查询用户周登录次数、排名以及TOP3--两种方法思路

目录

背景简介

登录总次数以及周排名

登录总次数TOP3的用户

---

背景简介

有game表，记录某个游戏用户登录时间 record_time 以及 用户ID user_id

game(record_time , user_id)

相关数据示例如下：

 

现有两个SQL语句查询问题，描述如下：

1. 查询每周用户的登录总次数及相应周排名

2. 查询每周登录总次数排行TOP3的user_id

登录总次数以及周排名

在直接上手写SQL语句前，先想一遍查询逻辑。

要注意两点，一、用户：每周，需要对周次进行分组

                     二、登录总次数：每个用户 ，需要对用户分组

理解上面的两点，周排名的问题就很容易想到：

        把每周、每个用户登录总次数降序排序后，依次标上排名。

分组：

GROUP BY 周次,用户ID

原表中时间类型为DATETIME ，获取周次：

WEEK(时间)，获取一年中的第几周

由此，查询每周每个用户的登录总次数，可以这样实现：

SELECT WEEK(record_time) weekno,user_id,COUNT(user_id) log_count
FROM game
GROUP BY weekno,user_id;

然后就是按照 log_conut 对用户进行排名，实现思路有两种：

        1 直接使用函数，由系统实现排名逻辑

        2 通过条件判断，由用户实现排名逻辑

第一种，函数。MySQL中提供 RANK() 获取记录排名，语法如下：

RANK([column1_value])

OVER([PARTITION BY column2_name] ORDER BY column1_name)

含义：以column1列为排序依据，获取column1列中值为column_value的记录的排名

        [PARTITION BY column2_name]为可选项，意思是按照column2列先分组

则这种方法最终实现语句如下：

SELECT WEEK(record_time) weekno,user_id,COUNT(user_id) log_count,
    RANK() OVER(PARTITION BY WEEK(record_time) ORDER BY COUNT(user_id) DESC) rankno
FROM game
GROUP BY weekno,user_id;

查询结果：成功。

 

第二种，条件判断。（失败，原因是这样的条件判断适用于整个表，不能实现分组判断）

登录次数少的，排名较前一个用户就低一位；

登录次数相同的用户的排名按常理显然应该相同，称作“并列”。

在SELECT子句中，条件判断有和两种常用语法:

1、IF(expr,true_value,false_value)  -- 只能判断一种条件

2、CASE   -- 可判断多个条件

        WHEN expr THEN true_value

        ELSE fause_value

END

判断谁和谁是否相等？查询的结果是一行一行增加的，那就是最新行和上一行之间的比较。

在查询过程中，两者无法直接进行比较，所以需要设置一个中间变量，记录上一行记录的次数。

mysql的变量分为局部变量、系统变量、用户变量等，这里仅用用户变量即可。语法如下：

mysql中用户变量不用事前声明，在用的时候直接用“@变量名”使用即可。

可以通过set或者select赋值：

set @a=1;

select @c:='abc';

 在理解以上分组、计数、排序、比较、标号几个关键操作之后，再想一想整体实现的逻辑：

排名计数rank初始值为0，前一条记录次数的记录变量last初始值为NULL，从排序后第一条记录开始，依次用下一条记录与前一条记录比较，如果比前一条记录的次数少，就使排名rank计数+1，否则（这里因为降序排，不小于就是等于）就rank计数不变。两种情况last值都自动后移。

最终语句如下：

SELECT  weekno,user_id,log_count,
	CASE
	    WHEN @last_n = log_count THEN
	    @rank
	    WHEN @last_n := log_count THEN
	    @rank := @rank+1
	    WHEN @last_n = 0 THEN
	    @rank := @rank+1
	END AS rankno
FROM (SELECT COUNT(*) log_count,WEEK(record_time) weekno,user_id
    FROM game 
    GROUP BY weekno,user_id) r,
    (SELECT @rank := 0,@last_n := 0) a

 这里" (SELECT @rank := 0,@last_n := 0) a "的作用是为变量@rank和@last_n 赋初始值。

查询结果：失败。

 两种思路都比较好理解，第二种条件判断虽然没有成功，但如果不考虑每周的分组，还是可以实现查询目的，可以很好的训练逻辑思维，还是有一定意义的。

登录总次数TOP3的用户

有了上面的登陆次数周排名表，这个问题就迎刃而解了，仅需再外层嵌套一个

SELECT FROM WHERE 语句即可实现。

SELECT user_id
FROM (上面的全部语句) rank_table
WHERE rank_table.rankno<=3;

 需要注意有一个问题，FROM 子表需要给定一个别名，否则会报错如下：

[Err] 1248 - Every derived table must have its own alias

（以上仅为个人学习过程中的收获记录，如有错误欢迎批评指正！希望对有相关疑问的同学有所帮助。）