SQL如何将无归属用户按已有群组人数比例分配到对应群组

实现思路 #

• 第一步：统计有效群组的占比区间：先计算所有非空groupID的用户数、总用户量，得到每个群组的分配占比，再生成每个群组的左闭右开占比区间
• 第二步：为无归属用户打随机标签：给所有groupID为NULL的用户生成[0,1)区间的均匀随机数
• 第三步：区间匹配完成分配：将无归属用户的随机数匹配到对应群组的占比区间，完成自动分配
• 第四步：合并数据聚合统计：将原有已分配用户和新分配的无归属用户合并，统计各群组总用户数

可运行SQL示例（兼容Spark SQL/Hive/PostgreSQL/MySQL等主流引擎） #

WITH group_stats AS (
    -- 统计每个非空群组的现有用户数、占比、累计占比区间
    SELECT 
        groupID,
        COUNT(userID) AS current_user_cnt,
        -- 计算当前群组占有效群组总用户的比例
        COUNT(userID) * 1.0 / SUM(COUNT(userID)) OVER() AS ratio,
        -- 累计占比上边界
        SUM(COUNT(userID)) OVER(ORDER BY groupID) * 1.0 / SUM(COUNT(userID)) OVER() AS cum_upper,
        -- 累计占比下边界
        LAG(SUM(COUNT(userID)) OVER(ORDER BY groupID), 1, 0) OVER(ORDER BY groupID) * 1.0 / SUM(COUNT(userID)) OVER() AS cum_lower
    FROM test_task
    WHERE groupID IS NOT NULL
    GROUP BY groupID
),
null_users AS (
    -- 为所有无归属用户生成0到1的随机数
    SELECT 
        userID,
        RAND() AS rnd -- 不同引擎随机函数命名可能有细微差异，可根据实际引擎调整
    FROM test_task
    WHERE groupID IS NULL
),
assigned_null_users AS (
    -- 匹配占比区间完成无归属用户分配
    SELECT 
        n.userID,
        g.groupID
    FROM null_users n
    JOIN group_stats g 
        ON n.rnd >= g.cum_lower AND n.rnd < g.cum_upper
),
all_users AS (
    -- 合并原有已分配用户和新分配的无归属用户
    SELECT userID, groupID FROM test_task WHERE groupID IS NOT NULL
    UNION ALL
    SELECT userID, groupID FROM assigned_null_users
)
-- 输出最终各群组用户数
SELECT 
    groupID,
    COUNT(userID) AS numOfUsers
FROM all_users
GROUP BY groupID
ORDER BY groupID;

方案说明 #

• 无需硬编码任何群组数量、分配比例，群组和用户量动态变化时可自动适配分配规则
• 均匀分布的随机数保证分配结果比例无限接近现有群组的用户占比，无归属用户量越大误差越小
• 若需要固定分配结果不受后续数据变化影响，将assigned_null_users的结果持久化存储即可

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