用一句SQL找出连续7天在线的用户

104 阅读 0 评论 69 点赞

我是靠谱客的博主称心音响，最近开发中收集的这篇文章主要介绍用一句SQL找出连续7天在线的用户，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

废话不多说先上SQL：

select 活跃用户, 登录天数 from (select 用户 as 活跃用户 ,count(用户) as 登录天数 from(select distinct log ,用户 from rihuo) as aaa group by 用户) as ccc where 登录天数=7;

括号不能省略，这里用了两次子查询。类似嵌套，先执行最里面的查询，再用结果查询。

解释：

mysql> select * from rihuo ;
+-----------+------------+
| 用户 | log |
+-----------+------------+
| 张三 | 2021-07-01 |
| 李四 | 2021-07-01 |
| 王麻子 | 2021-07-01 |
| 王麻子 | 2021-07-01 |
| 李四 | 2021-07-01 |
| 李四 | 2021-07-01 |
| 张三 | 2021-07-01 |
| 王麻子 | 2021-07-01 |
| 张三 | 2021-07-03 |
| 张三 | 2021-07-03 |
| 张三 | 2021-07-03 |
| 张三 | 2021-07-04 |
| 张三 | 2021-07-05 |
| 张三 | 2021-07-05 |
| 张三 | 2021-07-06 |
| 张三 | 2021-07-07 |
| 李四 | 2021-07-02 |
| 李四 | 2021-07-03 |
| 李四 | 2021-07-05 |
| 李四 | 2021-07-07 |
| 王麻子 | 2021-07-01 |
| 王麻子 | 2021-07-01 |
| 王麻子 | 2021-07-02 |
| 张三 | 2021-07-02 |
+-----------+------------+

首先这是我自己做的样本数据，因为一周内有重复登录的用户，比如一天登录2到3次的用户，甚至更多。注意我的原表名字叫rihuo。

首先是去重SQL：select distinct log ,用户 from rihuo;
+------------+-----------+
| log | 用户 |
+------------+-----------+
| 2021-07-01 | 张三 |
| 2021-07-01 | 李四 |
| 2021-07-01 | 王麻子 |
| 2021-07-03 | 张三 |
| 2021-07-04 | 张三 |
| 2021-07-05 | 张三 |
| 2021-07-06 | 张三 |
| 2021-07-07 | 张三 |
| 2021-07-02 | 李四 |
| 2021-07-03 | 李四 |
| 2021-07-05 | 李四 |
| 2021-07-07 | 李四 |
| 2021-07-02 | 王麻子 |
| 2021-07-02 | 张三 |
+------------+-----------+
14 rows in set (0.00 sec)

这样得到了一组某一天至少登录过一次的用户。

第二步：分组和统计。用到一个统计函数，和分组group by。其中aaa是给表起的别名。这里用了上个SQL的查询结果所以需要给表起别名。

mysql> select 用户 as 活跃用户 ,count(用户) as 登录天数 from(select distinct log ,用户 from rihuo) as aaa group by 用户;
+--------------+--------------+
| 活跃用户 | 登录天数 |
+--------------+--------------+
| 张三 | 7 |
| 李四 | 5 |
| 王麻子 | 2 |
+--------------+--------------+

第三步：找出连续登录7天的用户ID，再将上边的查询结果进行条件查询。count（用户）之后我给列起了别名叫登陆天数。

mysql> select 活跃用户, 登录天数 from (select 用户 as 活跃用户 ,count(用户) as 登录天数 from(select distinct log ,用户 from rihuo) as aaa group by 用户) as ccc where 登录天数=7;
+--------------+--------------+
| 活跃用户 | 登录天数 |
+--------------+--------------+
| 张三 | 7 |
+--------------+--------------+
1 row in set (0.00 sec)

以上就是求日活的3个步骤，先去重，再统计分组，再条件查询。最终3条SQL合成一句where条件可以随意变换比将条件改为登录天数<7，查到的是一周内登录5天李四和2的天王麻子

如mysql> select 活跃用户, 登录天数 from (select 用户 as 活跃用户 ,count(用户) as 登录天数 from(select distinct log ,用户 from rihuo) as aaa group by 用户) as ccc where 登录天数<7;
+--------------+--------------+
| 活跃用户 | 登录天数 |
+--------------+--------------+
| 李四 | 5 |
| 王麻子 | 2 |
+--------------+--------------+

制作不易给个赞吧！