MySQL 8.0 按用户统计体重连续低于前一日的连续天数查询方法

注：原测试表建表语句漏了recordDate字段类型，实际使用建议设置为DATE类型，以下SQL已通过STR_TO_DATE函数兼容处理字符串格式的日期输入。

实现逻辑 #

这是典型的序列岛屿问题，用窗口函数分5步实现：

1. 对每个用户的记录按日期排序，拉取前一条记录的日期和体重数据
2. 标记分组边界：如果是用户第一条记录、日期间隔超过1天、体重不低于前一天三者满足任意一个，就标记为新分组的起点
3. 对每个用户累加边界标记值，同一个连续下降分组的标记值相同
4. 按用户和分组标记聚合，统计每个连续下降段的天数、起止日期
5. 取每个用户天数最长的连续段输出即可

完整可执行SQL #

WITH user_with_prev AS (
    -- 转换日期格式，拉取用户前一条记录的日期和体重
    SELECT 
        userName,
        STR_TO_DATE(recordDate, '%Y/%m/%d') AS record_date,
        weight,
        LAG(STR_TO_DATE(recordDate, '%Y/%m/%d')) OVER (PARTITION BY userName ORDER BY STR_TO_DATE(recordDate, '%Y/%m/%d')) AS prev_date,
        LAG(weight) OVER (PARTITION BY userName ORDER BY STR_TO_DATE(recordDate, '%Y/%m/%d')) AS prev_weight
    FROM userData
),
user_with_flag AS (
    -- 标记分组边界
    SELECT 
        *,
        CASE 
            WHEN prev_date IS NULL THEN 1
            WHEN DATEDIFF(record_date, prev_date) = 1 AND weight < prev_weight THEN 0
            ELSE 1
        END AS group_flag
    FROM user_with_prev
),
user_with_group AS (
    -- 生成连续分组ID
    SELECT 
        *,
        SUM(group_flag) OVER (PARTITION BY userName ORDER BY record_date) AS group_id
    FROM user_with_flag
),
group_streak AS (
    -- 统计每个连续段的信息
    SELECT 
        userName,
        COUNT(*) AS streakDays,
        MIN(record_date) AS startingDate,
        MAX(record_date) AS endingDate
    FROM user_with_group
    GROUP BY userName, group_id
),
rank_streak AS (
    -- 按连续天数倒序排序，取每个用户最长的段
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY userName ORDER BY streakDays DESC, startingDate) AS rn
    FROM group_streak
)
SELECT userName, streakDays, startingDate, endingDate
FROM rank_streak
WHERE rn = 1
ORDER BY streakDays DESC, userName;

输出结果 #

执行后和预期完全一致：

内容的提问来源于stack exchange，提问作者A-P