MySQL计算用户连续每日会话数及间隔1天会话数的技术问题

1. 如何计算用户的连续每日会话数？ #

咱们先拆解下思路：要统计用户连续几天有会话，核心是把连续的日期归为一组，再统计每组的天数。假设你的表有user_id（用户ID）和day_session（会话日期，需为DATE类型），用窗口函数就能轻松搞定：

步骤很简单：

• 先确保每个用户每天只留一条记录（如果一天有多个会话，也算成一天的话，就用DISTINCT去重）
• 按用户分组、日期排序，用LAG()函数拿到该用户上一次会话的日期
• 判断当前日期和上一次的差值，如果不是1天，就标记为新的连续段起点
• 用累计求和生成每个连续段的分组ID，最后按用户+分组ID统计连续天数

直接上代码：

WITH daily_sessions AS (
    -- 去重，每个用户每天只保留一条会话记录
    SELECT DISTINCT user_id, day_session
    FROM your_session_table
),
session_groups AS (
    SELECT 
        user_id,
        day_session,
        -- 当和前一天间隔不是1天时，生成新分组标记
        SUM(CASE WHEN DATEDIFF(day_session, LAG(day_session) OVER (PARTITION BY user_id ORDER BY day_session)) = 1 THEN 0 ELSE 1 END) 
        OVER (PARTITION BY user_id ORDER BY day_session) AS group_id
    FROM daily_sessions
)
SELECT 
    user_id,
    group_id,
    MIN(day_session) AS 连续会话开始日期,
    MAX(day_session) AS 连续会话结束日期,
    COUNT(*) AS 连续天数
FROM session_groups
GROUP BY user_id, group_id
ORDER BY user_id, 连续会话开始日期;

要是你的表已经是按天去重的，直接去掉daily_sessions这个CTE就行，用原表查询就好。这个结果会清晰展示每个用户的每一段连续会话的起止日期和天数。

2. 如何计算用户间隔1天的会话数量（正确结果为46）？ #

看你提到的现有代码，应该是用了用户变量但只算了首尾记录的差值，这肯定不对呀。咱们要统计的是所有满足“当前会话和上一次会话间隔正好1天”的次数总和，用窗口函数LAG()会更靠谱，也不容易出错：

如果是要统计所有用户的总次数，代码这么写：

WITH session_ordered AS (
    SELECT 
        user_id,
        day_session,
        -- 获取当前用户的上一次会话日期
        LAG(day_session) OVER (PARTITION BY user_id ORDER BY day_session) AS prev_day_session
    FROM your_session_table
    -- 要是同一天有多个会话，记得加DISTINCT去重，避免重复计算
    -- DISTINCT user_id, day_session
)
SELECT COUNT(*) AS 间隔1天的会话总次数
FROM session_ordered
WHERE 
    prev_day_session IS NOT NULL  -- 排除每个用户的第一条记录（没有上一次会话）
    AND DATEDIFF(day_session, prev_day_session) = 1;

要是你需要按用户单独统计，就把COUNT(*)改成user_id, COUNT(*)，再加上GROUP BY user_id就行。

如果你的MySQL版本不支持窗口函数（比如5.7及以前），那用用户变量的方式也能实现，我给你调整下正确的写法：

SET @prev_user = '';
SET @prev_day = NULL;

SELECT 
    SUM(interval_flag) AS 间隔1天的会话总次数
FROM (
    SELECT 
        user_id,
        day_session,
        CASE 
            WHEN user_id = @prev_user AND DATEDIFF(day_session, @prev_day) = 1 THEN 1
            ELSE 0
        END AS interval_flag,
        -- 每次遍历都更新变量，记录当前用户和日期
        @prev_user := user_id,
        @prev_day := day_session
    FROM your_session_table
    ORDER BY user_id, day_session
    -- 同样，需要去重的话加上DISTINCT
) AS temp;

这个查询会逐个遍历每个用户的会话记录，每次和上一条对比日期差，符合条件就记1，最后求和就能得到你想要的46啦。

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