SQL Sever 连续登陆查询解析

共计 4197 个字符，预计需要花费 11 分钟才能阅读完成。

需求

已有用户登陆信息记录表 userid,record_date 查询连续登陆大于7天以上玩家

准备数据

/* 创建临时表 */
CREATE TABLE #UserLoginInfo (
    --自增id
    Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    --用户id
    UserId INT NOT NULL,
    --记录时间
    RecordDate DATETIME2 NOT NULL, 
);

/* 插入模拟数据 */
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(1, '2021-05-01 20:33:21');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(1, '2021-05-01');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(1, '2021-06-01');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(1, '2021-06-03');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(1, '2021-06-07');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(1, '2021-06-08');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(1, '2021-06-09');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(1, '2021-06-10 20:33:21');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(1, '2021-06-11');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(1, '2021-06-15');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(1, '2021-06-16');

INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(2, '2021-05-01 20:33:21');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(2, '2021-06-01');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(2, '2021-06-03');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(2, '2021-06-07');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(2, '2021-06-08');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(2, '2021-06-09');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(2, '2021-06-10');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(2, '2021-06-11');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(2, '2021-06-15');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(2, '2021-06-16');

INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(3, '2021-05-01 20:33:21');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(3, '2021-06-01');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(3, '2021-06-03');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(3, '2021-06-07');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(3, '2021-06-08');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(3, '2021-06-09');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(3, '2021-06-10');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(3, '2021-06-11');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(3, '2021-06-15');
INSERT INTO #UserLoginInfo(UserId, RecordDate)VALUES(3, '2021-06-16');

查询过程

用户每天登陆次数可能不止一次，需要将日期去重。再用row_number()函数将用户分组，按照去重后的日期排序后计数。然后用日期减去计数得到结果，在这里，如果用户每次减去的结果相同则代表是连续登陆。最后按照用户和结果分组，计算和，此时和大于等于2的就是连续2天登陆的用户了。

• 1.日期去重

SELECT DISTINCT CONVERT(NVARCHAR(10), RecordDate, 120) AS RecordDate, UserId
FROM #UserLoginInfo;

• 2.row_number()分组排序计数

WITH DistUserLoginInfo AS (
    SELECT DISTINCT CONVERT(NVARCHAR(10), RecordDate, 120) AS RecordDate, UserId 
    FROM #UserLoginInfo
)
SELECT ROW_NUMBER() over(PARTITION by UserId order by RecordDate) as UserLoginNumber,RecordDate,UserId 
FROM DistUserLoginInfo;

• 3.日期减去计数得到结果值

WITH DistUserLoginInfo AS (
    SELECT DISTINCT CONVERT(NVARCHAR(10), RecordDate, 120) AS RecordDate, UserId
    FROM #UserLoginInfo
), 
NumberUserLoginInfo AS (
    SELECT 
        ROW_NUMBER() over(PARTITION by UserId order by RecordDate) as UserLoginNumber,
        RecordDate,
        UserId
    FROM DistUserLoginInfo
)
SELECT 
    DATEADD(DAY,-UserLoginNumber,RecordDate) AS StartDate,
    UserLoginNumber,
    RecordDate,
    UserId
FROM NumberUserLoginInfo;

• 4.按照用户id和结果值分组并求和，得到连续等于大于等于2天的用户

WITH DistUserLoginInfo AS (
    SELECT DISTINCT CONVERT(NVARCHAR(10), RecordDate, 120) AS RecordDate, UserId
    FROM #UserLoginInfo
), 
NumberUserLoginInfo AS (
    SELECT 
        ROW_NUMBER() over(PARTITION by UserId order by RecordDate) as UserLoginNumber,
        RecordDate,
        UserId
    FROM DistUserLoginInfo
),
StartUserLoginInfo AS(
    SELECT 
        DATEADD(DAY,-UserLoginNumber,RecordDate) AS StartDate,
        UserLoginNumber,
        RecordDate,
        UserId
    FROM NumberUserLoginInfo
)
SELECT UserId,StartDate,COUNT(1) ContinuousCount
FROM StartUserLoginInfo
GROUP BY StartDate,UserId
HAVING COUNT(1) >= 2

总结

WITH DistUserLoginInfo AS (
    SELECT DISTINCT CONVERT(NVARCHAR(10), RecordDate, 120) AS RecordDate, UserId
    FROM #UserLoginInfo
), 
NumberUserLoginInfo AS (
    SELECT 
        ROW_NUMBER() over(PARTITION by UserId order by RecordDate) as UserLoginNumber,
        RecordDate,
        UserId
    FROM DistUserLoginInfo
),
StartUserLoginInfo AS(
    SELECT 
        DATEADD(DAY,-UserLoginNumber,RecordDate) AS StartDate,
        UserLoginNumber,
        RecordDate,
        UserId
    FROM NumberUserLoginInfo
)
SELECT UserId,StartDate,COUNT(1) ContinuousCount
FROM StartUserLoginInfo
GROUP BY StartDate,UserId
HAVING COUNT(1) >= 2

/* tips */
/* 删除临时表 */
DROP TABLE #UserLoginInfo