SQL Sever 连续登陆查询解析

499次阅读
没有评论

共计 4445 个字符,预计需要花费 12 分钟才能阅读完成。

需求

已有用户登陆信息记录表 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

正文完
 1
太阳
版权声明:本站原创文章,由 太阳 2021-06-16发表,共计4445字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
评论(没有评论)