1.连续登录三天
思路分析:
对用户登录记录按照用户ID和登录日期进行排序。
使用ROW_NUMBER()函数为每个用户的登录记录分配一个序列号,序列号的分配依据是登录日期的先后顺序。
计算相邻登录记录之间的日期差异,如果这个差异为1,则表明这两个登录记录是连续的。
根据日期差异,筛选出连续登录的用户记录。
步骤
数据去重:由于同一用户可能在同一天内有多次登录记录,因此首先需要对登录记录进行去重处理,只保留每个用户每天的一条记录。
排序:使用ORDER BY语句对去重后的记录按照用户ID和登录日期进行排序。
分配序列号:使用ROW_NUMBER()函数为每个用户的登录记录分配序列号,序列号会随着日期依次递增。
计算日期差异:通过DATE_SUB()函数计算当前登录日期与前一登录日期的差异,如果这个差异为1,则认为这两个登录记录是连续的。减去间隔排序天数。
筛选连续登录记录:根据日期差异的结果,筛选出连续登录的用户记录。
代码补充:WITH t1 AS (
SELECT DISTINCT uid, date(login_time) ymd
FROM t_login),
t2 as (
select uid,ymd,row_number() over (partition by uid order by ymd) as ra,
date_sub(ymd,interval (row_number() over (partition by uid order by ymd)) day) sub_date
from t1)
select uid from t2
group by uid
having count(*)>=3;
方法二,Lag()函数和datediff()
– 窗口函数lag
with t1 as (
select distinct uid, date(login_time) ymd
from t_login
),
t2 as (
select uid,ymd,datediff(ymd,lag(ymd,2) over (partition by uid order by ymd)) diff
from t1
)
select * from t2
where diff=2
结果表:
如果用户在1月1日、1月2日和1月3日连续登录,那么在1月3日的记录中,LAG(ymd, 2)将返回1月1日的日期,而在1月2日的记录中,LAG(ymd, 2)将返回NULL或者一个特定的值,因为在1月2日时没有前两天的日期。
2.连续登录最大天数用户
*思路前部与连续登录3天相同,这里构建表t3,根据uid对t2的结果集进行分组,并计算每个分组的数量,将这个数量命名为day_con。这里的day_con代表了每个用户连续登录的天数。这是根据uid而不是uid和sub_date进行分组的。这意味着它计算的是每个用户所有的登录天数;
最后的查询根据day_con的最大值筛选出t3中的记录。这个day_con代表了每个用户连续登录的天数(尽管在t3的分组中实际上计算的是总的登录天数)。这个查询将返回连续登录天数最多的用户的uid和他们连续登录的天数。
WITH t1 AS (
SELECT DISTINCT uid, date(login_time) ymd
FROM t_login),
t2 as (
select uid,ymd,row_number() over (partition by uid order by ymd) as ra,
date_add(ymd,interval (-row_number() over (partition by uid order by ymd)) day) sub_date
from t1),
t3 as (select uid,count(*) as day_con from t2
group by uid)
select uid,day_con as max_day from t3
where day_con=(select max(day_con) from t3);