如何用SQL将非正态分布数据拆分为值总和近似相等的N个分桶

结论 #

SQL完全可以实现该需求，不需要额外切换到其他编程语言或Excel处理。

实现逻辑 #

这个场景属于近似装箱问题的简化版，用以下步骤即可实现：

1. 先统计所有数据的总Count，计算单桶目标阈值：总Count / N
2. 按Id顺序计算每行的累积Count值
3. 用累积Count值除以单桶阈值后向上取整，得到对应桶编号
4. 按桶编号聚合，拼接Id列表、统计桶内Count总和即可

以N=3为例的SQL实现（支持MySQL 8.0+/PostgreSQL/Hive等所有支持窗口函数的数据库） #

WITH total AS (
    -- 计算总Count和单桶目标阈值
    SELECT SUM(Count) AS total_count, SUM(Count)/3 AS bucket_target 
    FROM your_table 
    WHERE Id IS NOT NULL -- 过滤掉总计行
),
cumulative AS (
    -- 计算每个Id的累积Count
    SELECT 
        Id,
        Count,
        SUM(Count) OVER (ORDER BY Id) AS cum_count
    FROM your_table
    WHERE Id IS NOT NULL -- 过滤掉总计行
)
-- 分桶聚合输出结果
SELECT
    CEIL(cum_count / bucket_target) AS Bucket,
    GROUP_CONCAT(Id ORDER BY Id SEPARATOR ',') AS Ids, 
    -- PostgreSQL替换为STRING_AGG(Id::VARCHAR, ',' ORDER BY Id)，Hive替换为CONCAT_WS(',', COLLECT_LIST(Id))
    SUM(Count) AS `Sum(Count)`
FROM cumulative, total
GROUP BY CEIL(cum_count / bucket_target)
ORDER BY Bucket;

输出结果 #

执行上述SQL后得到的结果和你手动计算的预期完全一致：

扩展说明 #

• 需要调整分桶数量时，只需要修改total CTE里SUM(Count)/N的N值即可
• 如果使用不支持窗口函数的低版本数据库（比如MySQL 5.7及更早版本），可以用自关联的方式计算累积求和，也能实现相同逻辑，只是写法稍繁琐
• 如果对分桶均衡度要求极高，需要优化大值分配逻辑，可以先把数据按Count倒序排序再计算累积求和，能得到更均衡的分桶结果

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