关联表时避免重复数据：获取符合条件的水果统计数据

现有两张数据表：

• table_A（商品销售清单）：存储水果销售记录，包含fruit_name（水果名称）、sales_amount（销售额）、sales_price（销售价格）等字段
• table_B（网站水果搜索记录）：存储用户搜索记录，包含fruit_name（水果名称）、customer_id（客户ID）等字段

需求：筛选出总销售额超过200欧元的水果，同时统计该水果被独立客户搜索的次数，还要保留该水果的所有销售价格数组。例如：Apple总销售额250欧元（符合条件），需统计其被3个独立客户搜索的次数；Banana总销售额不足200欧元，需排除。但直接用LEFT JOIN关联两表会产生大量重复数据，导致统计结果错误，该如何解决？

直接关联原始表会因为一对多的关系产生笛卡尔积——比如某水果有多条销售记录和多条搜索记录，关联后会出现重复组合，导致销售额被重复求和、搜索次数被重复统计。正确的做法是先分别对两张表按水果维度完成聚合计算，再关联聚合后的结果，具体步骤如下：

1. 从销售表筛选符合条件的水果 #

先对table_A按水果分组，计算总销售额并收集价格数组，同时过滤出总销售额超过200欧元的水果：

SELECT
    fruit_name,
    SUM(sales_amount) AS total_sales,
    ARRAY_AGG(sales_price) AS price_array
FROM table_A
GROUP BY fruit_name
HAVING SUM(sales_amount) > 200;

这个子查询会得到每个符合条件的水果的唯一行记录，包含总销售额和所有销售价格的数组。

2. 计算每个水果的独立客户搜索次数 #

对table_B按水果分组，用COUNT(DISTINCT customer_id)统计独立客户的搜索次数（避免同一客户多次搜索被重复计数）：

SELECT
    fruit_name,
    COUNT(DISTINCT customer_id) AS unique_search_count
FROM table_B
GROUP BY fruit_name;

这个子查询会得到每个水果对应的独立客户搜索次数，同样每个水果仅一行记录。

3. 关联两个聚合结果 #

将上述两个子查询的结果通过fruit_name关联，得到最终的完整数据。用COALESCE处理那些有销售记录但无搜索记录的水果，将其搜索次数设为0：

SELECT
    agg_sales.fruit_name,
    agg_sales.total_sales,
    agg_sales.price_array,
    COALESCE(agg_searches.unique_search_count, 0) AS unique_search_count
FROM (
    -- 子查询1：符合条件的销售聚合数据
    SELECT
        fruit_name,
        SUM(sales_amount) AS total_sales,
        ARRAY_AGG(sales_price) AS price_array
    FROM table_A
    GROUP BY fruit_name
    HAVING SUM(sales_amount) > 200
) agg_sales
LEFT JOIN (
    -- 子查询2：搜索次数聚合数据
    SELECT
        fruit_name,
        COUNT(DISTINCT customer_id) AS unique_search_count
    FROM table_B
    GROUP BY fruit_name
) agg_searches ON agg_sales.fruit_name = agg_searches.fruit_name;

这种方式从根源上避免了重复数据的产生，因为两个子查询都已经将数据聚合到水果维度，关联时不会出现笛卡尔积，统计结果准确。

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