在PieCloudDB中统计每个用户的有效登录次数（间隔≥10分钟）

核心问题在于不能直接用普通LAG()函数取上一条登录时间，因为我们需要追踪的是「上一次有效登录的时间」，而非上一条记录的时间。以下是两种通用的数据库实现方案，适配MySQL 8.0+、PostgreSQL等支持窗口函数/CTE的数据库：

假设你的表名为user_login，包含字段：user_id（用户ID）、login_time（登录时间，datetime类型）。

方案1：递归CTE（直观追踪有效时间链） #

通过递归逐条处理每个用户的登录记录，实时更新上一次有效登录时间：

WITH ranked_logins AS (
    -- 给每个用户的登录记录按时间排序，生成序号
    SELECT 
        user_id,
        login_time,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time) AS rn
    FROM user_login
),
valid_logins AS (
    -- 递归起始：每个用户的首次登录默认有效
    SELECT 
        user_id,
        login_time,
        rn,
        login_time AS last_valid_time,
        1 AS is_valid
    FROM ranked_logins
    WHERE rn = 1

    UNION ALL

    -- 递归处理后续记录：判断与上一次有效登录的间隔
    SELECT 
        r.user_id,
        r.login_time,
        r.rn,
        -- 若当前登录有效，更新上一次有效时间为当前时间；否则沿用之前的
        CASE 
            WHEN TIMESTAMPDIFF(MINUTE, v.last_valid_time, r.login_time) >= 10 THEN r.login_time
            ELSE v.last_valid_time
        END AS last_valid_time,
        -- 标记是否为有效登录
        CASE 
            WHEN TIMESTAMPDIFF(MINUTE, v.last_valid_time, r.login_time) >= 10 THEN 1
            ELSE 0
        END AS is_valid
    FROM ranked_logins r
    JOIN valid_logins v ON r.user_id = v.user_id AND r.rn = v.rn + 1
)
-- 统计每个用户的有效登录次数及对应时间
SELECT 
    user_id,
    SUM(is_valid) AS valid_login_count,
    GROUP_CONCAT(CASE WHEN is_valid = 1 THEN DATE_FORMAT(login_time, '%H:%i') END ORDER BY login_time SEPARATOR ', ') AS valid_login_times
FROM valid_logins
GROUP BY user_id;

逻辑说明 #

1. ranked_logins：给每个用户的登录记录按时间排序，生成序号，确保递归能逐条处理。
2. valid_logins递归块：
   • 起始部分取每个用户的第一条登录，标记为有效，并记录该时间为last_valid_time。
   • 递归部分关联上一条处理结果，判断当前登录与last_valid_time的间隔是否≥10分钟，以此标记有效性并更新last_valid_time。
3. 最终统计：通过SUM(is_valid)得到有效登录次数，同时可拼接出所有有效登录的时间点。

方案2：窗口函数累积分组（简洁高效） #

通过累积有效登录的标记，将同一有效周期内的登录归为一组，最终统计分组数量：

WITH login_with_lag AS (
    -- 计算当前登录与上一条记录的时间差
    SELECT
        user_id,
        login_time,
        TIMESTAMPDIFF(MINUTE, LAG(login_time) OVER (PARTITION BY user_id ORDER BY login_time), login_time) AS diff_prev
    FROM user_login
),
valid_markers AS (
    SELECT
        user_id,
        login_time,
        -- 标记是否为有效登录：首次登录 或 与上一条间隔≥10分钟
        CASE
            WHEN diff_prev IS NULL THEN 1
            WHEN diff_prev >= 10 THEN 1
            ELSE 0
        END AS is_valid,
        -- 累积有效标记，生成有效登录的分组ID
        SUM(CASE WHEN diff_prev IS NULL OR diff_prev >=10 THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY login_time) AS valid_group
    FROM login_with_lag
)
-- 每个分组对应一次有效登录，统计分组数量即可
SELECT
    user_id,
    COUNT(DISTINCT valid_group) AS valid_login_count,
    STRING_AGG(CASE WHEN is_valid=1 THEN TO_CHAR(login_time, 'HH24:MI') END, ', ' ORDER BY login_time) AS valid_login_times
FROM valid_markers
GROUP BY user_id;

逻辑说明 #

1. login_with_lag：用LAG()函数获取上一条登录时间，计算时间差。
2. valid_markers：标记有效登录后，用SUM()窗口函数累积有效标记，每次有效登录时valid_group会递增，无效登录则保持原分组ID。
3. 最终统计：每个valid_group对应一次有效登录，通过COUNT(DISTINCT valid_group)得到有效次数。

适配不同数据库的细节调整 #

• PostgreSQL：将TIMESTAMPDIFF(MINUTE, a, b)替换为EXTRACT(EPOCH FROM (b - a))/60，DATE_FORMAT替换为TO_CHAR。
• SQL Server：将TIMESTAMPDIFF替换为DATEDIFF(MINUTE, a, b)，GROUP_CONCAT替换为STRING_AGG。

内容的提问来源于stack exchange，提问作者Joe