如何用SQL按行百分比分配100美元整数值？

现有一张通过以下SQL查询生成的类别-百分比表：

SELECT p.category, 
       sum(p.sales) * 100 / sum(sum(p.sales)) OVER () AS percentage
FROM product p
GROUP BY p.category;

查询结果如下：

需要将100美元（仅整数金额）按上述百分比分配到各品类，要求结合已使用的GROUP BY和窗口函数实现，预期结果如下：

可以通过CTE（公共表表达式）结合窗口函数实现金额的精准分配，确保总金额恰好为100美元：

WITH category_percentages AS (
    -- 生成原有的类别-百分比数据
    SELECT 
        p.category, 
        SUM(p.sales) * 100 / SUM(SUM(p.sales)) OVER () AS percentage
    FROM product p
    GROUP BY p.category
),
base_allocations AS (
    -- 计算每个品类的基础分配金额和小数部分
    SELECT 
        category,
        percentage,
        FLOOR(percentage) AS base_amount,
        percentage - FLOOR(percentage) AS decimal_part
    FROM category_percentages
),
adjustment_calc AS (
    -- 计算需要调整的总差额（确保最终总和为100）
    SELECT 
        *,
        100 - SUM(base_amount) OVER () AS total_adjustment
    FROM base_allocations
)
-- 最终分配：给小数部分最大的品类补足差额
SELECT 
    category,
    CONCAT(ROUND(percentage, 2), '%') AS percentage,
    CASE 
        WHEN ROW_NUMBER() OVER (ORDER BY decimal_part DESC) <= total_adjustment
        THEN base_amount + 1
        ELSE base_amount
    END AS amount
FROM adjustment_calc;

1. category_percentages：复用原查询生成类别与对应百分比，保留窗口函数的计算逻辑。
2. base_allocations：
   • base_amount：取百分比的整数部分作为基础分配金额（如20.51%对应20美元）。
   • decimal_part：提取百分比的小数部分，用于后续差额分配的优先级判断。
3. adjustment_calc：计算基础金额总和与100美元的差额，确定需要额外分配的金额总数。
4. 最终查询：按小数部分从大到小排序，给排名靠前的品类各加1美元，确保总金额刚好为100美元。这种方式优先给小数部分更大的品类补足差额，更贴合百分比的实际占比。

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