Office中国论坛/Access中国论坛
标题:
连续登录天数最多的用户
[打印本页]
作者:
roych
时间:
2023-3-2 18:04
标题:
连续登录天数最多的用户
先上测试数据源:
<div>DROP TABLE IF EXISTS user_login;</div><div>CREATE TABLE user_login( user_id VARCHAR(8), date DATE ) ;</div><div>INSERT INTO user_login VALUES ('a001','2021-01-01') ,('b001','2021-01-01') ,('a001','2021-01-02') ,('a002','2021-01-02') ,</div><div>('a001','2021-01-03') ,('b001','2021-01-03') ,('a001','2021-01-04') ,('a001','2021-01-06') ,('a002','2021-01-06') ,('b001','2021-01-07')</div>
复制代码
返回结果如下图所示:
[attach]64458[/attach]
以下给出两种解决方法。
1.递归查询(CTE通用表):
WITH cte (user_id, date, days, prev_date)
AS
(
SELECT user_id, date, 1 as days, date as prev_date
FROM user_login
UNION ALL
SELECT a.user_id, a.date, case when datediff(day,a.date, b.prev_date)=1 then
days +1 else 0 end , a.date
FROM user_login AS a
INNER JOIN cte AS b
ON a.user_id=b.user_id and b.date=DATEADD(day,1,a.date)
)
select top 1 user_id,max(days) continue_days from cte
GROUP BY user_id
ORDER BY continue_days DESC
复制代码
递归查询先定义一个底层,再通过两个表互相关联,根据日期差对
days
字段自增
,
最后统计出最大次数的用户
。
需要注意的是,加上”
b.date=DATEADD(day,1,a.date)
“
这句以确定终止条件,否则可能会陷入死循环
。
2.窗口函数(lead和row_numnber):
select top 1 user_id, max(case when dates=1 then rnk+1 else 1 end) continue_days
from(
select user_id, date, datediff(day,date, lead(date,1,null) over(partition by user_id order by date)) dates,
row_number() over(partition by user_id order by date) rnk from user_login
)
a
GROUP BY user_id
ORDER BY continue_days DESC
复制代码
相对复杂一些,需要使用lead窗口函数计算下一天和当天是否相差为1(1表示连续,其它数值则表示不连续),再通过row_number()排序,把等于1的最大值求出来,加上1就是连续天数了。
考虑到不少版友面试时可能用的是MySQL,所以,最后再给出MySQL的三种写法,语法上略有差异,这里就不再赘述了。
--方法1
WITH continuous_login AS (
SELECT user_id,
`date`,
@rn:=IF(@previous_user = user_id AND @previous_date = DATE_ADD(`date`, INTERVAL -1 DAY),
@rn + 1,
IF(@previous_user := user_id, 1, 1)) AS rn,
@previous_date:=`date`
FROM user_login,
(SELECT @rn:=0, @previous_user:='', @previous_date:='') AS init_vars
ORDER BY user_id, `date`
)
SELECT user_id, MAX(rn) AS continue_days
FROM continuous_login
GROUP BY user_id
ORDER BY continue_days DESC
LIMIT 1;
--方法2
WITH RECURSIVE cet(user_id, date, continue_days, prev_date) AS (
SELECT user_id, date, 1, date
FROM user_login
UNION ALL
SELECT ul.user_id, ul.date,
IF(DATEDIFF(ul.date, prev_date) = 1, continue_days + 1, 1),
ul.date
FROM user_login ul
JOIN cet ON ul.user_id = cet.user_id AND ul.date = DATE_ADD(cet.date, INTERVAL 1 DAY)
)
SELECT user_id, MAX(continue_days) AS continue_days
FROM cet
GROUP BY user_id
ORDER BY continue_days DESC
LIMIT 1;
--方法3
SELECT user_id
, max(if(dates = 1, rnk + 1, 1)) AS continue_days
FROM (
SELECT user_id, date
, datediff(lead(date, 1, NULL) OVER (PARTITION BY user_id ORDER BY date), date) AS dates
, row_number() OVER (PARTITION BY user_id ORDER BY date) AS rnk
FROM user_login
) a
GROUP BY user_id
ORDER BY continue_days DESC
LIMIT 1;
复制代码
喜欢的话就回个贴吧。SQL Server板块毕竟那么冷清
作者:
accben
时间:
2023-3-15 08:08
厉害了!谢谢分享。
不过如果是我碰到这种统计,我只会用ADO去解决。纯SQL语法搞不掂。
作者:
roych
时间:
2023-3-15 11:59
accben 发表于 2023-3-15 08:08
厉害了!谢谢分享。
不过如果是我碰到这种统计,我只会用ADO去解决。纯SQL语法搞不掂。
Access里只能用ADO,SQL Server或MySQL等数据库才能使用这个语法。分享出来也是便于大家用来解决面试题或工作中的需求。
欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)
Powered by Discuz! X3.3