表t_active_user有字段dt(日期),city(城市),user_id(用户id),is_active(0:表示非活跃,1表示活跃)
需求1:
求在20200801到20200831期间,每个城市,用户活跃数最高的前2名用户。
使用case when实现:
SELECT city,user_id,active_days,rank
FROM
(SELECT city,user_id,active_days,CASE city WHEN @c THEN @r:=@r+1 WHEN @c:=city THEN @r:=1 END rank
FROM
(SELECT city,user_id,active_days
FROM
(SELECT city,user_id,count(is_active) AS active_days
FROM t_active_user
WHERE dt>='20200801' AND dt<='20200831' AND is_active=1
GROUP BY city,user_id) a
ORDER BY city,active_days DESC,user_id) b,(SELECT @c:=NULL,@r:=0) r) c
WHERE rank<=2;
使用if实现:
SELECT city,user_id,active_days,rank
FROM
(SELECT city,user_id,active_days,IF(@c=city,@r:=@r+1,IF(@c:=city,NULL,@r:=1)) AS rank
FROM
(SELECT city,user_id,active_days
FROM
(SELECT city,user_id,count(is_active) AS active_days
FROM t_active_user
WHERE dt>='20200801' AND dt<='20200831' AND is_active=1
GROUP BY city,user_id) a
ORDER BY city,active_days DESC,user_id) b,(SELECT @c:=NULL,@r:=0) r) c
WHERE rank<=2;
注意:无论是使用case when还是if,发现SQL语句的赋值操作的返回值都是false,所以IF语句这么写:
IF(@c=city,@r:=@r+1,IF(@c:=city,NULL,@r:=1),这一点是区别与Java,C等高级语言的。
执行结果如下: