按行数占比拆分数据集并兼顾order_sum占比的SQL实现

核心思路 #

要同时达成20%行数占比和**第一部分金额总和接近总金额20%**的要求，不能仅依赖随机排序，需结合订单金额的权重调整排序逻辑，同时兼顾执行效率：

• 先一次性获取全局统计数据（总订单数、总金额），避免重复扫描表；
• 以「订单金额与目标单份金额的偏差+随机扰动」作为排序依据，让金额适中的订单优先进入第一部分，避免极端金额订单集中；
• 用行数占比为核心判断标准，搭配金额占比的弹性区间，在保证行数要求的前提下尽可能贴近金额占比目标。

改写后的SQL #

WITH order_stats AS (
    -- 一次性扫描获取总订单数与总金额，提升效率
    SELECT
        COUNT(*) AS total_rows,
        SUM(order_sum) AS total_sum
    FROM orders
),
ranked_orders AS (
    SELECT
        o.order_id,
        o.order_sum,
        -- 计算累计行数占比
        100 * ROW_NUMBER() OVER (ORDER BY abs(o.order_sum - (os.total_sum * 0.2)/(os.total_rows * 0.2)) + random()) / os.total_rows AS row_percent,
        -- 计算累计金额占比
        100 * SUM(o.order_sum) OVER (ORDER BY abs(o.order_sum - (os.total_sum * 0.2)/(os.total_rows * 0.2)) + random()) / os.total_sum AS sum_percent
    FROM orders o
    CROSS JOIN order_stats os
)
SELECT
    order_id,
    order_sum,
    CASE
        -- 优先保证20%行数，同时给金额占比留弹性空间
        WHEN row_percent <= 20 OR (sum_percent <= 22 AND row_percent <= 25) THEN '1'
        ELSE '2'
    END AS part
FROM ranked_orders;

逻辑说明 #

• order_stats CTE：仅扫描一次表获取全局统计值，避免重复计算，减少IO开销；
• 排序键设计：abs(o.order_sum - (os.total_sum * 0.2)/(os.total_rows * 0.2)) 让订单金额越接近「目标单份金额（总金额20%/总订单数20%）」的订单越靠前，叠加random()避免同金额订单过度集中；
• 划分规则：以20%行数为核心标准，同时设置金额占比22%、行数25%的弹性区间，在不偏离行数要求的前提下，让第一部分金额尽可能贴近总金额的20%；
• 执行效率：仅需两次表扫描（一次统计、一次排序计算），窗口函数的排序逻辑在主流数据库中均有优化，性能优于多层嵌套子查询。

大数据量场景优化方案 #

如果订单数据量极大，全表排序的窗口函数可能存在性能瓶颈，可改用分层抽样方案，避免全表排序：

WITH order_stats AS (
    SELECT
        COUNT(*) AS total_rows,
        SUM(order_sum) AS total_sum
    FROM orders
),
bucketed_orders AS (
    SELECT
        order_id,
        order_sum,
        -- 按金额与目标单份金额的偏差分桶
        CASE
            WHEN order_sum < (os.total_sum * 0.2)/(os.total_rows * 0.2) * 0.5 THEN 'small'
            WHEN order_sum > (os.total_sum * 0.2)/(os.total_rows * 0.2) * 1.5 THEN 'large'
            ELSE 'medium'
        END AS bucket
    FROM orders
    CROSS JOIN order_stats os
)
-- 优先抽取金额适中的订单满足20%行数要求
SELECT
    order_id,
    order_sum,
    '1' AS part
FROM bucketed_orders
WHERE bucket = 'medium'
LIMIT (SELECT total_rows * 0.2 FROM order_stats)
UNION ALL
-- 剩余订单归为第二部分
SELECT
    order_id,
    order_sum,
    '2' AS part
FROM bucketed_orders
WHERE order_id NOT IN (
    SELECT order_id FROM bucketed_orders WHERE bucket = 'medium' LIMIT (SELECT total_rows * 0.2 FROM order_stats)
);

该方案通过优先抽取金额适中的订单，快速满足行数与金额的双重要求，无需全表排序，适合超大规模数据集。

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