力扣1454(MySQL)-活跃用户(中等)
(非会员进不去,看的其他博主的题目)
问题:
写一个 SQL 查询, 找到活跃用户的 id 和 name.
活跃用户是指那些至少连续 5 天登录账户的用户.
返回的结果表按照 id 排序.
解题思路:
①使用窗口函数lead()取出以id分组,再按login_date升序排序的当前行之后的四行数据。
1 select id,login_date,lead(login_date,4) over(partition by id order by login_date) as date1 2 from logins;
②再用datediff()函数统计出两个日期的相差天数
1 SELECT 2 id, 3 login_date, 4 datediff( 5 lead(login_date, 4 ) over(PARTITION BY id ORDER BY login_date) 6 ,login_date 7 ) AS count 8 FROM 9 logins 10 GROUP BY id,login_date;
③在筛选出相差4天(连续五天)的id和name,需要去重
1 select distinct t.id,a.name 2 from ( 3 select 4 id, 5 login_date, 6 datediff( 7 lead(login_date, 4 ) over(partition by id order by login_date) 8 ,login_date 9 ) as count 10 from logins 11 group by id,login_date 12 ) as t 13 left join accounts a 14 on t.id = a.id 15 where t.count = 4;
小知识:
①lag/lead(col,n,DEFAULT) 用于统计窗口内当前行往前或者往后第n行值【lag 取得是当前行之前的数据,lead 取的实当前行之后的数据】
- 第一个参数为列名,
- 第二个参数为往后/前第n行(可选,默认为1),
- 第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)
②DATEDIFF(datepart,startdate,enddate):返回两个指定日期之间指定日期部分差的整数。日期范围从开始日期开始,到结束日期结束。(如果enddate早于startdate,datediff 将返回一个负整数值。)
③(本题没有用)using关键字
- using可以代替on出现(比on更好)
- using是针对同名字段(using(id)===on A.id=B.id)