PostgreSQL如何基于聚合指标匹配查询对应行ID

看起来你需要的是找到表中部分行的子集，使得它们的聚合结果（按Side分组的金额总和、价格平均值）完全匹配给定的指标——这和普通的GROUP BY + HAVING不同，因为后者是对整个Side分组的所有行做聚合，而你要找的是分组内的某个子集。

下面我会结合你的示例，给出具体的解决方案：

先明确需求逻辑 #

从你的示例来看，你提到的price_avg是算术平均值（即子集内所有行的Price相加除以行数），而非金融场景常见的加权平均（总成交额/总数量）。我们先基于这个逻辑来写SQL，之后也会补充加权平均的修改方式。

假设你的表名为orders，先把给定的目标指标定义为参数，然后通过递归CTE枚举所有可能的行子集，筛选符合条件的组合。

解决方案代码 #

WITH target_params AS (
    -- 这里填入你需要匹配的聚合指标
    SELECT 
        'BUY' AS side,
        3 AS target_amount_sum,
        2 AS target_price_avg
    UNION ALL
    SELECT 
        'SELL' AS side,
        10 AS target_amount_sum,
        1 AS target_price_avg
),
-- 递归生成所有可能的行子集（避免重复组合）
subsets AS (
    -- 初始步骤：每个单独的行作为一个子集
    SELECT 
        id,
        side,
        amount AS current_amount_sum,
        price AS current_price_sum,
        1 AS row_count,
        ARRAY[id] AS id_list
    FROM orders
    UNION ALL
    -- 递归步骤：将现有子集与ID更大的同Side行组合，避免重复子集（比如{1,4}和{4,1}视为同一组）
    SELECT 
        s.id,
        s.side,
        s.current_amount_sum + o.amount,
        s.current_price_sum + o.price,
        s.row_count + 1,
        s.id_list || o.id
    FROM subsets s
    JOIN orders o 
        ON o.side = s.side 
        AND o.id > s.id -- 关键：只组合ID更大的行，防止重复
),
-- 筛选符合目标指标的子集
matched_subsets AS (
    SELECT 
        tp.side,
        s.id_list
    FROM subsets s
    JOIN target_params tp 
        ON s.side = tp.side
    WHERE 
        -- 匹配金额总和
        s.current_amount_sum = tp.target_amount_sum
        -- 匹配算术平均价格：总和 = 平均值 × 行数
        AND s.current_price_sum = tp.target_price_avg * s.row_count
)
-- 输出结果
SELECT side, id_list FROM matched_subsets;

代码解释 #

1. target_params：把你需要匹配的聚合指标结构化，方便后续关联查询。
2. subsets：用递归CTE生成所有可能的行子集，每个子集记录当前的金额总和、价格总和、行数、ID列表。通过o.id > s.id确保每个子集只生成一次，避免重复的组合（比如先选ID1再选ID4，不会反过来）。
3. matched_subsets：将生成的子集和目标指标匹配，筛选出完全符合条件的组合。
4. 最终输出每个Side对应的ID列表，和你的预期结果完全一致：

   side | id_list
   ------+---------
    BUY  | {1,4}
    SELL | {3,5}
   

注意事项 #

• 性能限制：递归生成子集的时间复杂度是O(2^n)，其中n是每个Side的行数。如果同Side的行数超过20行，这个方法会非常慢甚至超出内存限制——这种情况下建议用外部程序（比如Python的itertools）来处理组合筛选。
• 加权平均修改：如果你的price_avg是加权平均（总成交额/总数量），只需要修改两个地方：
  1. 在subsets的初始步骤中，添加price * amount AS current_weighted_price_sum，递归步骤中改为s.current_weighted_price_sum + o.price * o.amount。
  2. 在matched_subsets的WHERE条件中，把价格匹配条件改为s.current_weighted_price_sum = tp.target_price_avg * tp.target_amount_sum。
• 多子集情况：如果存在多个符合条件的子集，这个SQL会返回所有结果，你可以根据需求添加额外的筛选条件（比如只取第一个，或者按子集大小排序）。

为什么普通GROUP BY不行？ #

你之前尝试的GROUP BY Side + HAVING是对整个Side分组的所有行做聚合，而你需要的是分组内的部分行子集满足聚合条件——普通GROUP BY无法枚举和筛选这种子集组合，所以必须用递归CTE或者其他组合枚举的方式来处理。

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