create table tmp_login (
  user_id int(11) ,
  login_date datetime
);
insert into tmp_login values(2,'2020-05-29 11:12:12');
insert into tmp_login values(2,'2020-05-29 15:12:12');
insert into tmp_login values(2,'2020-05-30 11:12:12');
insert into tmp_login values(2,'2020-05-31 11:12:12');
insert into tmp_login values(2,'2020-06-01 11:12:12');
insert into tmp_login values(2,'2020-06-02 11:12:12');
insert into tmp_login values(2,'2020-06-03 11:12:12');
insert into tmp_login values(2,'2020-06-04 11:12:12');
insert into tmp_login values(2,'2020-06-05 11:12:12');
insert into tmp_login values(2,'2020-06-06 11:12:12');
insert into tmp_login values(2,'2020-06-07 11:12:12');
insert into tmp_login values(7,'2020-06-01 11:12:12');
insert into tmp_login values(7,'2020-06-02 11:12:12');
insert into tmp_login values(7,'2020-06-03 11:12:12');
insert into tmp_login values(7,'2020-06-05 11:12:12');
insert into tmp_login values(7,'2020-06-06 11:12:12');
insert into tmp_login values(7,'2020-06-07 11:12:12');
insert into tmp_login values(7,'2020-06-08 11:12:12');

方法一 row_number()

1.查詢所有用戶的每日登錄記錄

select distinct user_id, date(login_date) as days?
from tmp_login;

在這里插入圖片描述

2.row_number()計算登錄時間排序

select user_id, days, row_number() over(partition by user_id order by days) as rn
from (
	select distinct user_id, date(login_date) as days from tmp_login) t1;

在這里插入圖片描述

3.用登錄時間 - row_number()，如果得到的日期相同，則認(rèn)為是連續(xù)登錄日期

select *, date_sub(days, interval rn day) as  results
from(
	select user_id, days, row_number() over(partition by user_id order by days) as rn
	from (
		select distinct user_id, date(login_date) as days from tmp_login) t1
) t2;

在這里插入圖片描述

4. 按user_id、results分組就可得出連續(xù)登錄天數(shù)

select user_id, count(*) as num_days
from (
	select *, date_sub(days, interval rn day) as  results
	from(
		select user_id, days, row_number() over(partition by user_id order by days) as rn
		from (
			select distinct user_id, date(login_date) as days from tmp_login) t1
	) t2) t3
group by user_id , results;

在這里插入圖片描述

直接用日期減去row_number()，不用date_sub的話，遇到登錄日期跨月時會計算錯誤，

方法二lead() 或 lag()

這種情況適合的場景是，需要查找連續(xù)登錄超過n天的用戶，n為確定值

如果n為4，即計算連續(xù)登錄超過4天的用戶

-- lead計算連續(xù)登錄
select distinct user_id 
from(
	select user_id, days, datediff(lead(days, 3, '1970-01-01') over(partition by user_id order by days), days)as results
	from (
		select distinct user_id, date(login_date) as days from tmp_login) t1) t2
where results = 3;

連續(xù)登錄4天，則日期差應(yīng)該為3。

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: