在墨天輪上看到一個(gè)有意思的面試題
需求是這樣的
求每個(gè)用戶的最長連續(xù)登錄天數(shù)，兩個(gè)日期的間隔小于或等于 3 均視為連續(xù)登錄。比如 01-01 號(hào)登錄，最近的下一次登錄是 01-04 號(hào)，兩個(gè)日期的間隔等于 3 天，因此這兩個(gè)日期之間的天數(shù)都算作活躍天數(shù)，一共 4 天。
也貼上原貼解決方法：
https://www.modb.pro/db/43021?yll
但我覺得此方法太復(fù)雜，特別是5.x版本。 我進(jìn)行了改寫
上代碼 構(gòu)造測試數(shù)據(jù)
create table test_login(user_id int,login_date timestamp);
insert into test_login values (1,'2020-01-01 00:01:00');
insert into test_login values (1,'2020-01-01 00:02:00');
insert into test_login values (1,'2020-01-01 00:03:00');
insert into test_login values (1,'2020-01-02 00:03:00');
insert into test_login values (1,'2020-01-05 00:03:00');
insert into test_login values (1,'2020-01-07 00:03:00'); -- 這里一直從 1號(hào)到7號(hào) 符合需求中間未有間斷3天的。所以連續(xù)登陸7天
insert into test_login values (1,'2020-01-11 00:03:00'); -- 這里超過了三天，需重新計(jì)數(shù)
insert into test_login values (1,'2020-01-12 00:03:00');
insert into test_login values (1,'2020-01-13 00:03:00');
insert into test_login values (1,'2020-01-14 00:03:00');
insert into test_login values (1,'2020-01-17 00:03:00');
insert into test_login values (1,'2020-01-18 00:03:00');
insert into test_login values (1,'2020-01-19 00:03:00');
insert into test_login values (1,'2020-01-22 00:03:00'); -- 這里從11號(hào)到22號(hào) 符合需求中間未有間斷3天的。連續(xù)登陸12天
insert into test_login values (2,'2020-01-01 00:04:00'); -- userid 變了，重新計(jì)數(shù)
insert into test_login values (2,'2020-01-01 00:05:00');
insert into test_login values (2,'2020-01-01 00:06:00');
insert into test_login values (2,'2020-01-02 00:06:00'); -- 1號(hào)到2號(hào) 連續(xù)登陸2天
insert into test_login values (2,'2020-01-07 00:06:00'); -- 7號(hào)1天 連續(xù)登陸 1天
insert into test_login values (2,'2020-01-11 00:06:00');
insert into test_login values (2,'2020-01-12 00:06:00');
insert into test_login values (2,'2020-01-14 00:06:00'); -- 11號(hào)到14號(hào) 連續(xù)登陸 4天
insert into test_login values (2,'2020-01-18 00:06:00');
insert into test_login values (2,'2020-01-19 00:06:00');
insert into test_login values (2,'2020-01-22 00:06:00'); -- 18號(hào)到 22 號(hào) 連續(xù)登陸 5天
貼上解決方案
with tmp1 AS
-- tmp1臨時(shí)表 一天多次登陸算一次
(
SELECT user_id,date_format(login_date,'%Y-%m-%d') login_date
from test_login
group by user_id,date_format(login_date,'%Y-%m-%d')
),
tmp2 as
(
select user_id,login_date, if(@id = user_id and DATEDIFF(login_date,@up_date) <= 3,@cnt := @cnt + DATEDIFF(login_date,@up_date),@cnt := 1 ) as days,(@id := user_id),(@up_date := login_date)
from tmp1,(select @id := user_id,@up_date := login_date,@cnt := 1 from tmp1 order by user_id,login_date limit 1 ) as t
order by user_id,login_date
)
select user_id,max(days) as max_days from tmp2
group by user_id
執(zhí)行的結(jié)果
uesr_id max_days
1 12
2 5
這種解法就用了最基礎(chǔ)的知識(shí)點(diǎn) mysql select 的執(zhí)行順序。 列的話是從左到右的。
