如何将空白市场金额按比例分配至其他市场并优化SQL查询？

算力阿峰

2026-8-16

问题描述

我有一个包含市场列表及对应金额的表格：

Market	Amount
A	10
B	30
C	50
D	10
	10

我希望将空白市场对应的10美元，按照排除空白市场后的其他市场金额比例（例如：amount(A)/sum(A+B+C+D)）分配给其余市场。

期望输出如下：

Market	Amount
A	11
B	33
C	55
D	11

我认为可以使用多个CTE进行查询，但想了解是否能用尽可能少的CTE甚至不使用CTE来实现该分配逻辑。

解决方案（无CTE实现）

可以通过窗口函数或者关联子查询两种方式实现，完全不需要CTE：

方法1：使用窗口函数（性能更优）

窗口函数能一次性计算出非空白市场的总金额、空白市场的待分配金额，直接推导最终结果：

SELECT
  Market,
  -- 原金额加上按比例分配到的金额
  Amount + (Amount / total_non_blank) * unallocated_amount AS Amount
FROM (
  SELECT
    Market,
    Amount,
    -- 计算所有非空白市场的总金额
    SUM(CASE WHEN Market IS NOT NULL AND Market != '' THEN Amount END) OVER () AS total_non_blank,
    -- 计算空白市场的待分配总金额
    SUM(CASE WHEN Market IS NULL OR Market = '' THEN Amount END) OVER () AS unallocated_amount
  FROM market_amounts
) t
-- 过滤掉空白市场，只保留分配后的有效市场
WHERE Market IS NOT NULL AND Market != ''
ORDER BY Market;

方法2：使用关联子查询

如果数据库不支持窗口函数，也可以用两个子查询分别获取关键统计值：

SELECT
  m.Market,
  m.Amount + 
  (m.Amount / (SELECT SUM(Amount) FROM market_amounts WHERE Market IS NOT NULL AND Market != '')) * 
  (SELECT SUM(Amount) FROM market_amounts WHERE Market IS NULL OR Market = '') AS Amount
FROM market_amounts m
WHERE m.Market IS NOT NULL AND m.Market != ''
ORDER BY m.Market;