基于LifestyleProfile表按规则计算用户的dailyloadtype

原始数据表 #

LifestyleProfile表包含用户月度的每日负载类型记录：

预期输出 #

需要为每个用户确定最终的每日负载类型，结果如下：

计算逻辑 #

• 若用户最近3个月的dailyloadtype取值完全一致，直接选取该值
• 若最近3个月存在多种类型，则选取该用户生命周期内出现次数最多的dailyloadtype

解决方案（SQL） #

以下SQL通过CTE（公共表表达式）实现需求逻辑，兼容大多数支持窗口函数的SQL数据库（如MySQL 8.0+、PostgreSQL、SQL Server等）：

WITH user_recent_3months AS (
    SELECT 
        UserId,
        MAX(dailyloadtype) AS recent_type, -- 类型唯一时，MAX/MIN等价于该类型
        COUNT(DISTINCT dailyloadtype) AS distinct_type_count
    FROM LifestyleProfile
    -- 以表中最晚月份为基准，筛选最近3个月的数据
    WHERE Month >= DATE_SUB((SELECT MAX(Month) FROM LifestyleProfile), INTERVAL 2 MONTH)
    GROUP BY UserId
),
user_lifetime_rank AS (
    SELECT 
        UserId,
        dailyloadtype,
        -- 按出现次数降序排名，次数相同时取任意（可根据需求调整排序规则）
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY COUNT(*) DESC) AS rank_num
    FROM LifestyleProfile
    GROUP BY UserId, dailyloadtype
)
SELECT 
    ulr.UserId,
    CASE 
        WHEN ur3.distinct_type_count = 1 THEN ur3.recent_type
        ELSE ulr.dailyloadtype
    END AS dailyloadtype
FROM user_lifetime_rank ulr
JOIN user_recent_3months ur3 ON ulr.UserId = ur3.UserId
-- 选取每个用户出现次数最多的类型（排名第一）
WHERE ulr.rank_num = 1;

逻辑说明 #

1. user_recent_3months：筛选所有用户的最近3个月数据，统计该时间段内dailyloadtype的不同取值数量。若数量为1，说明最近3个月类型完全一致，同时记录该类型。
2. user_lifetime_rank：统计每个用户生命周期内每种dailyloadtype的出现次数，并用ROW_NUMBER()为每个用户的类型按次数降序排名，排名第一的即为出现次数最多的类型。
3. 最终查询：通过JOIN关联两个CTE，使用CASE语句判断：若最近3个月类型唯一则取该类型，否则取生命周期次数最多的类型。

结果验证 #

• UserId 1：最近3个月（2023-04至2023-06）的dailyloadtype均为LATE_EVE，因此直接选取LATE_EVE。
• UserId 2：最近3个月存在DAY_LOAD和LATE_EVE两种类型，生命周期内DAY_LOAD共出现4次（远多于LATE_EVE的2次），因此选取DAY_LOAD。

内容的提问来源于stack exchange，提问作者Teja Goud Kandula