Hive/SQL按降序分配值：将数值12拆分至5行的实现方法

嘿，这个需求我之前做业务报表分配配额的时候刚好碰到过，给你一套通用的实现逻辑，不管是Hive还是标准SQL都能直接用，完美匹配你给出的示例场景。

核心思路 #

我们要把固定总数（这里是12）按优先级降序分配给每一行：

1. 先给所有行按你需要的优先级排序（比如业务权重、字母顺序等），优先级高的行先分配额外的数值
2. 计算基础分配值（总数除以总行数的整数部分）
3. 计算剩余未分配的数值，把这些剩余值依次分配给前N行（N等于总数除以总行数的余数），这样就能得到降序的分配结果

完整代码实现 #

假设你的表名为your_table，以下是可直接运行的代码：

WITH ranked_rows AS (
    SELECT 
        column_1, 
        column_2,
        -- 替换成你的实际排序逻辑，比如按业务优先级降序：ORDER BY business_priority DESC
        ROW_NUMBER() OVER (ORDER BY column_1 ASC) AS row_num,
        COUNT(*) OVER () AS total_rows
    FROM your_table
),
allocation_logic AS (
    SELECT
        column_1,
        column_2,
        row_num,
        total_rows,
        -- 计算基础分配值：总数除以总行数的整数部分
        FLOOR(12 / total_rows) AS base_allocation,
        -- 计算需要额外分配1的行数：总数除以总行数的余数
        MOD(12, total_rows) AS extra_quotas
    FROM ranked_rows
)
SELECT
    column_1,
    column_2,
    -- 前extra_quotas行分配base+1，其余行分配base
    CASE 
        WHEN row_num <= extra_quotas THEN base_allocation + 1 
        ELSE base_allocation 
    END AS total
FROM allocation_logic
-- 最后按total降序输出，匹配你的示例格式
ORDER BY total DESC;

代码解释 #

• ranked_rows CTE：给每行分配唯一编号，同时统计总行数。这里的ORDER BY是核心，决定了哪些行优先级高（比如你想让某类行先拿更多配额，就调整排序规则）
• allocation_logic CTE：计算两个关键值：
  • base_allocation：12除以5的整数部分是2，也就是每行至少能分到2
  • extra_quotas：12除以5的余数是2，也就是前2行可以多拿1
• 最终查询：用CASE判断分配值，前2行拿3，剩下3行拿2，总和刚好12，完全符合你的示例

运行结果 #

执行后会得到你需要的输出：

扩展场景 #

如果总数或行数变化，这个逻辑依然适用：

• 比如总数是13，总行数5：前3行拿3，后2行拿2，总和13
• 比如总数是10，总行数5：每行拿2，刚好平分

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