如何基于同产品同日期的Top Client正确汇总Table_1金额？

错误原因分析 #

• 首次SQL结果错误：关联Table_2时未匹配product和date字段，导致判断范围扩大到所有top client，而非当前产品日期对应的top client，最终汇总金额偏大。
• Scalar subquery produced more than one element错误：直接在标量子查询中使用array_agg会返回所有top client的集合（多个元素），但标量子查询要求只能返回单个值，因此触发报错。

正确实现方法 #

方法1：聚合top client数组后关联过滤

先按产品、日期聚合生成对应top client的数组，再关联Table_1并过滤符合条件的记录：

SELECT
    t1.product,
    t1.date,
    SUM(t1.amount) AS total_amount
FROM
    Table_1 t1
JOIN (
    -- 按产品+日期分组，生成对应维度的top client数组
    SELECT
        product,
        date,
        ARRAY_AGG("top client") AS top_clients
    FROM Table_2
    GROUP BY product, date
) t2 
    ON t1.product = t2.product 
    AND t1.date = t2.date
-- 验证sender和receiver均属于当前维度的top client
WHERE
    t1.sender = ANY(t2.top_clients)
    AND t1.receiver = ANY(t2.top_clients)
GROUP BY
    t1.product,
    t1.date;

方法2：用EXISTS子查询分别验证

无需聚合数组，直接通过两个EXISTS子查询分别校验sender和receiver的身份：

SELECT
    product,
    date,
    SUM(amount) AS total_amount
FROM Table_1 t1
WHERE
    -- 校验sender是当前产品+日期的top client
    EXISTS (
        SELECT 1
        FROM Table_2 t2
        WHERE t2.product = t1.product
          AND t2.date = t1.date
          AND t2."top client" = t1.sender
    )
    -- 校验receiver是当前产品+日期的top client
    AND EXISTS (
        SELECT 1
        FROM Table_2 t2
        WHERE t2.product = t1.product
          AND t2.date = t1.date
          AND t2."top client" = t1.receiver
    )
GROUP BY product, date;

关键注意点 #

两种方法都严格绑定了product和date的关联条件，确保只判断当前维度下的top client，避免了范围错误；方法1通过分组聚合数组解决了标量子查询的多元素问题，方法2则用更直观的存在性校验规避了数组操作。

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