mysql笔记：连续N天登录

对连续N登录的思路以及需要注意的点的记录。

思路是通过对数据库中的数据进行排序，然后用当前登录日期减去序号就可以得到一个开始日期。如果是连续登录，在减去序号后所得到的开始日期应该是一样的。

数据：

create table game(name text,date datetime);

insert game(name,date)  values
('张三','2021-01-01'),
('张三','2021-01-02'),
('张三','2021-01-03'),
('张三','2021-01-02'),
('李四','2021-01-01'),
('李四','2021-01-02'),
('王五','2021-01-03'),
('王五','2021-01-02'),
('王五','2021-01-02');

最终代码:

with t1 as ( select distinct name,date from game),
t2 as ( select *,row_number() over(partition by name order by date) rn from t1 ),
t3 as ( select *,DATE_SUB(date,INTERVAL rn DAY) date2 from t2 )
select distinct name from t3 group by name,date2 having count(1)>=3;

第一点是序号的得到

思路是通过使用窗口函数over来得到一个结果集，使用row_number()来生成序号。
当然结果集需要先去重，因为同一用户会在同一天登录。在over中用partition by对用户名（或者别的什么唯一标识）进行分组，用order by对登录日期进行排序。最后使用row_number()进行序号生成。这样就得到了序号

with t1 as ( select distinct name,date from game),
t2 as ( select *,row_number() over(partition by name order by date) rn from t1 )
select * from t2 
order by name,rn;

另外关于生成序号的函数，我在查询中发现还有两个函数。rank()和dense_rank()，rank()是会重复，总数不变。dense_rank是会重复，总数减少。详细用图来说明。下图有数据是重复的。使用三个函数的不同的结果。

事实上有了dense_rank函数 上面的代码可以变为下面这样，结果是一样的。

select distinct *,
	dense_rank() over(partition by name order by date) rn 
from game 
order by name,rn;

第二点是计算得到开始日期

这个很简单，直接使用DATE_SUB函数。直接使用登录日期减去一天就可以了。但参数要注意当为负数时表示增加，而且这函数的单位不是只有DAY,还有小时，分钟以及周和月。