SQL如何计算连续登录并存储过程_SQL创建连续登录存储过程

UserLogins

CREATE TABLE UserLogins (
    UserID INT,
    LoginDate DATE,
    -- 其他可能的字段，如LoginTime等
    PRIMARY KEY (UserID, LoginDate) -- 确保每个用户每天只有一条登录记录
);

-- 插入一些示例数据
INSERT INTO UserLogins (UserID, LoginDate) VALUES
(1, '2023-01-01'),
(1, '2023-01-02'),
(1, '2023-01-03'),
(1, '2023-01-05'),
(1, '2023-01-06'),
(2, '2023-01-10'),
(2, '2023-01-11'),
(3, '2023-01-01'),
(3, '2023-01-03'),
(3, '2023-01-04'),
(3, '2023-01-05');

CREATE PROCEDURE CalculateConsecutiveLogins
AS
BEGIN
    -- 防止SET NOCOUNT ON干扰结果集，但对于存储过程，通常建议开启以减少网络流量
    SET NOCOUNT ON;

    -- 第一步：为每个用户的每次登录按日期排序，并生成行号
    -- 这一步是为后续计算“连续组标识”做准备，RowNumber会给我们一个递增的序列
    WITH RankedLogins AS (
        SELECT
            UserID,
            LoginDate,
            ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY LoginDate) AS rn
        FROM
            UserLogins
    ),
    -- 第二步：计算“连续组标识”
    -- 这是整个逻辑的核心。如果LoginDate减去其对应的rn值（或转换为天数再减）得到一个常数，
    -- 那么这些登录日期就是连续的。这个常数就是我们的GroupKey。
    -- 例如：2023-01-01 (rn=1) -> GroupKey = 2023-01-01 - 1天 = 2022-12-31
    --       2023-01-02 (rn=2) -> GroupKey = 2023-01-02 - 2天 = 2022-12-31
    --       2023-01-03 (rn=3) -> GroupKey = 2023-01-03 - 3天 = 2022-12-31
    -- 非连续的：2023-01-05 (rn=4) -> GroupKey = 2023-01-05 - 4天 = 2023-01-01
    ConsecutiveGroups AS (
        SELECT
            UserID,
            LoginDate,
            DATEADD(day, -rn, LoginDate) AS GroupKey -- SQL Server语法，其他数据库可能需要DATEDIFF
        FROM
            RankedLogins
    )
    -- 第三步：按UserID和GroupKey分组，计算每个连续组的起始日期、结束日期和连续天数
    -- 这一步我们就能得到每个用户所有连续登录的详细信息了
    SELECT
        UserID,
        MIN(LoginDate) AS StreakStartDate,
        MAX(LoginDate) AS StreakEndDate,
        COUNT(LoginDate) AS ConsecutiveDays
    FROM
        ConsecutiveGroups
    GROUP BY
        UserID,
        GroupKey
    HAVING
        COUNT(LoginDate) >= 1 -- 过滤掉那些不构成连续登录的（尽管在我们的逻辑中不会出现少于1天的情况）
    ORDER BY
        UserID,
        StreakStartDate;

END;
GO

-- 执行存储过程来查看结果
-- EXEC CalculateConsecutiveLogins;

CalculateConsecutiveLogins

GroupKey

GroupKey

MIN(LoginDate)

MAX(LoginDate)

GROUP BY UserID, GroupKey

GroupKey

2022-12-31

UserID

GroupKey

@MinConsecutiveDays

ALTER PROCEDURE CalculateConsecutiveLogins
    @MinConsecutiveDays INT = 1 -- 默认值设为1，表示查询所有连续登录（即只要有登录就算1天）
AS
BEGIN
    SET NOCOUNT ON;

    WITH RankedLogins AS (
        SELECT
            UserID,
            LoginDate,
            ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY LoginDate) AS rn
        FROM
            UserLogins
    ),
    ConsecutiveGroups AS (
        SELECT
            UserID,
            LoginDate,
            DATEADD(day, -rn, LoginDate) AS GroupKey
        FROM
            RankedLogins
    )
    SELECT
        UserID,
        MIN(LoginDate) AS StreakStartDate,
        MAX(LoginDate) AS StreakEndDate,
        COUNT(LoginDate) AS ConsecutiveDays
    FROM
        ConsecutiveGroups
    GROUP BY
        UserID,
        GroupKey
    HAVING
        COUNT(LoginDate) >= @MinConsecutiveDays -- 这里加入了参数过滤
    ORDER BY
        UserID,
        StreakStartDate;

END;
GO

-- 示例：查询所有连续登录天数大于等于2的用户记录
-- EXEC CalculateConsecutiveLogins @MinConsecutiveDays = 2;

-- 示例：查询所有连续登录天数大于等于3的用户记录
-- EXEC CalculateConsecutiveLogins @MinConsecutiveDays = 3;

-- 示例：查询所有连续登录天数大于等于1的用户记录 (等同于不加参数)
-- EXEC CalculateConsecutiveLogins;

@MinConsecutiveDays