sql连续登录问题
前言：在数分工作中，计算用户“连续”行为是一项重要的任务。该指标可以直接反应用户活跃度、产品健康度、产品/店铺运营效果等，对后面预测用户行为和提供决策支持提供很大的帮助。下面我们详细介绍如何计算“连续”。
测试数据
CREATE TABLE user_login (
login_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
login_time DATETIME NOT NULL,
login_device VARCHAR(20),
login_ip VARCHAR(15),
login_city VARCHAR(30),
login_status TINYINT DEFAULT 1 COMMENT '1-成功, 0-失败'
);
-- 创建索引以提高查询性能
CREATE INDEX idx_user_id ON user_login(user_id);
CREATE INDEX idx_login_time ON user_login(login_time);
INSERT INTO user_login (user_id, login_time, login_device, login_ip, login_city, login_status) VALUES
-- 用户1001的登录记录（包含一天多次登录和失败记录）
(1001, '2023-10-01 08:15:23', 'iPhone', '192.168.1.1', '北京', 1),
(1001, '2023-10-01 12:30:45', 'PC', '192.168.1.10', '北京', 1), -- 同一天第二次登录
(1001, '2023-10-02 09:22:45', 'Android', '192.168.1.2', '北京', 0), -- 登录失败
(1001, '2023-10-02 14:15:33', 'iPhone', '192.168.1.11', '北京', 1), -- 同一天第二次登录（失败后成功）
(1001, '2023-10-03 10:05:12', 'iPhone', '192.168.1.3', '北京', 1),
(1001, '2023-10-04 11:30:56', 'PC', '192.168.1.4', '北京', 1),
(1001, '2023-10-04 19:45:22', 'Android', '192.168.1.12', '北京', 0), -- 同一天第二次登录（失败）
(1001, '2023-10-05 14:45:33', 'iPhone', '192.168.1.5', '北京', 1),
(1001, '2023-10-06 18:12:47', 'Android', '192.168.1.6', '北京', 1),
(1001, '2023-10-07 08:30:19', 'PC', '192.168.1.7', '北京', 1),
(1001, '2023-10-07 20:05:19', 'iPhone', '192.168.1.13', '北京', 1), -- 同一天第二次登录
-- 用户1002的登录记录（包含失败记录）
(1002, '2023-10-01 07:30:11', 'Android', '192.168.2.1', '上海', 0), -- 登录失败
(1002, '2023-10-02 08:45:33', 'iPhone', '192.168.2.2', '上海', 1),
(1002, '2023-10-03 09:22:55', 'PC', '192.168.2.3', '上海', 1),
(1002, '2023-10-03 16:15:44', 'Android', '192.168.2.10', '上海', 1), -- 同一天第二次登录
(1002, '2023-10-05 10:15:44', 'Android', '192.168.2.4', '上海', 1),
(1002, '2023-10-06 11:30:22', 'iPhone', '192.168.2.5', '上海', 0), -- 登录失败
(1002, '2023-10-07 14:45:11', 'PC', '192.168.2.6', '上海', 1),
(1002, '2023-10-08 16:30:55', 'Android', '192.168.2.7', '上海', 1),
(1002, '2023-10-09 18:15:33', 'iPhone', '192.168.2.8', '上海', 1),
-- 用户1003的登录记录（包含一天多次登录）
(1003, '2023-10-01 09:15:33', 'PC', '192.168.3.1', '广州', 1),
(1003, '2023-10-01 13:30:44', 'iPhone', '192.168.3.9', '广州', 1), -- 同一天第二次登录
(1003, '2023-10-03 10:30:44', 'iPhone', '192.168.3.2', '广州', 1),
(1003, '2023-10-04 11:45:55', 'Android', '192.168.3.3', '广州', 1),
(1003, '2023-10-05 08:00:11', 'PC', '192.168.3.4', '广州', 1),
(1003, '2023-10-05 17:15:22', 'iPhone', '192.168.3.10', '广州', 0), -- 同一天第二次登录（失败）
(1003, '2023-10-06 14:15:22', 'iPhone', '192.168.3.5', '广州', 1),
(1003, '2023-10-07 15:30:33', 'Android', '192.168.3.6', '广州', 1),
(1003, '2023-10-08 16:45:44', 'PC', '192.168.3.7', '广州', 1),
(1003, '2023-10-10 18:00:55', 'iPhone', '192.168.3.8', '广州', 1),
-- 原有30条记录（略，保持之前修改后的数据）
-- 以下是新增的20条记录
-- 用户1001新增记录（增加更多重复登录和失败案例）
(1001, '2023-10-08 09:15:22', 'Android', '192.168.1.14', '北京', 1),
(1001, '2023-10-08 18:30:45', 'iPhone', '192.168.1.15', '北京', 0), -- 同一天第二次登录（失败）
(1001, '2023-10-09 10:45:33', 'PC', '192.168.1.16', '北京', 1),
(1001, '2023-10-10 08:15:22', 'iPhone', '192.168.1.17', '北京', 1),
(1001, '2023-10-10 20:30:45', 'Android', '192.168.1.18', '北京', 1), -- 同一天第二次登录
-- 用户1002新增记录（增加跨地区登录）
(1002, '2023-10-10 09:15:33', 'iPhone', '192.168.2.11', '杭州', 1), -- 不同城市
(1002, '2023-10-11 10:30:44', 'PC', '192.168.2.12', '上海', 0), -- 登录失败
(1002, '2023-10-11 15:45:55', 'Android', '192.168.2.13', '上海', 1), -- 同一天第二次登录
(1002, '2023-10-12 11:00:11', 'iPhone', '192.168.2.14', '上海', 1),
-- 用户1003新增记录（增加设备切换）
(1003, '2023-10-09 07:15:22', 'Android', '192.168.3.11', '广州', 1),
(1003, '2023-10-09 12:30:33', 'PC', '192.168.3.12', '广州', 1), -- 同一天不同设备
(1003, '2023-10-11 08:45:44', 'iPhone', '192.168.3.13', '广州', 1),
(1003, '2023-10-12 14:00:55', 'Android', '192.168.3.14', '广州', 0), -- 登录失败
-- 新增用户1005（全新用户，有异常登录模式）
(1005, '2023-10-01 05:15:22', 'Android', '192.168.5.1', '成都', 1),
(1005, '2023-10-01 23:30:33', 'PC', '192.168.5.2', '成都', 1), -- 同一天极早和极晚登录
(1005, '2023-10-02 06:45:44', 'iPhone', '192.168.5.3', '成都', 0), -- 凌晨登录失败
(1005, '2023-10-03 12:00:55', 'Android', '192.168.5.4', '成都', 1),
(1005, '2023-10-04 18:15:11', 'PC', '192.168.5.5', '成都', 1),
(1005, '2023-10-05 00:30:22', 'iPhone', '192.168.5.6', '成都', 0); -- 午夜登录失败
实现
方法一：使用窗口函数（逻辑清晰，实现简单，最推荐）
注：该方法只适用于支持窗口函数的数据库，如mysql8.0+、hiveql、PostgreSQL, SQL Server等
需求：查询出连续登陆7天及以上的用户,并给出连续登陆时间范围
思路：我们将每个用户登陆信息按照时间排序，然后用（登陆时间-序号），会发现如果连续的情况，每次差值都是一样的，根据这个规律很容易能想到相同差值的个数即为连续登陆的天数
如图：
select user_id 用户id
,continuous_days 连续登陆天数
,concat_ws('~',min(login_date),max(login_date)) 连续登陆时段
from (
-- 若是连续登陆情况，每次差值应该是相同的,计算相同个数>=7为连续登陆7天及以上
-- 此处若只需要连续登陆的用户id，使用count()聚合即可；需要连续登陆起止日期使用开窗函数比较方便（若使用聚合后面需要考虑自连接的情况）
select user_id
,login_date
,date_diff
,count(user_id) over (partition by user_id,date_diff) continuous_days
from (
-- 使用开窗函数对每个用户登陆情况进行计数并计算差值
select *
,row_number() over (partition by user_id order by login_date) rn
,date_sub(login_date,interval row_number() over (partition by user_id order by login_date) day) date_diff
from (
-- 考虑有一天重复登陆的情况，需对登陆日期作去重处理
select distinct
user_id
,date(login_time) login_date
from user_login
where login_status = 1
) a
) b
) c
where continuous_days >= 7
group by user_id,continuous_days
结果：
方法二：使用自关联（适用于大多数数据库）
需求：找出连续登陆7天及以上的用户
思路：7天及以上，我们可以考虑以7天为时间节点，将某一个登陆时间限定为该时间后的7天进行自关联，如果能关联上即说明在该时间后的7天内该用户是有登陆的，如果连续登陆的话，左表的一个时间会关联到右表7条数据，根据此逻辑对右表进行计数判断，我们既然筛选出连续7天登陆用户，那么连续登陆8天、9天等等肯定也是符合7天这个规则的。
with login_info as (
select distinct
user_id
,date(login_time) login_date
from user_login
where login_status = 1
)
select distinct a.user_id
from login_info a
left join login_info b on a.user_id = b.user_id and b.login_date between a.login_date and date_add(a.login_date,interval 6 day)
group by a.user_id,a.login_date
having count(b.user_id) = 7
结果：
知识补充：
sql执行顺序为：from - where - group by - having - select - order by - limit
因此，此处可以先对a.user_id,a.login_date 进行group by,然后进行count()，最后distinct查处结果即可，不需要先count在嵌套查询distinct
浙公网安备 33010602011771号