如何在聚合SQL中正确统计关联子表的SUM与COUNT值

要解决这个问题，核心是避免直接关联未聚合的两张表导致的一对多重复匹配。正确的做法是先分别对两张表按promotion_id完成聚合统计，再将结果关联起来。

方法一：先聚合子表再关联 #

先对purchases表按promotion_id聚合出购买次数和营收，再和已经聚合好的page_visit_actions统计结果左连接：

SELECT
    va.promotion_id,
    va.total,
    va.share,
    va.favorite,
    va.basket,
    COALESCE(p.purchases, 0) AS purchases,
    p.revenue
FROM
    (
        SELECT
            promotion_id,
            COUNT(*) AS total,
            SUM(CASE WHEN action_type = 'share_product' THEN 1 ELSE 0 END) AS share,
            SUM(CASE WHEN action_type = 'add_product_to_favorites' THEN 1 ELSE 0 END) AS favorite,
            SUM(CASE WHEN action_type = 'add_product_to_basket' THEN 1 ELSE 0 END) AS basket
        FROM page_visit_actions
        GROUP BY promotion_id
    ) va
LEFT JOIN
    (
        SELECT
            promotion_id,
            COUNT(*) AS purchases,
            SUM(price) AS revenue
        FROM purchases
        GROUP BY promotion_id
    ) p ON va.promotion_id = p.promotion_id;

结果验证 #

执行后会得到符合预期的统计：

+--------------------------------------+-------+-------+----------+--------+-----------+---------+
| promotion_id                         | total | share | favorite | basket | purchases | revenue |
+--------------------------------------+-------+-------+----------+--------+-----------+---------+
| 5ac4ea14-f157-45a0-a706-765f4c624920 |     2 |     1 |        1 |      0 |         2 |   20.00 |
| 7140beb4-5fc9-46b1-9790-0b580b6c83f3 |     1 |     0 |        1 |      0 |         0 |    NULL |
| 97a207e6-c9f8-4edf-8f9f-397544c790b9 |     1 |     0 |        1 |      0 |         1 |    5.00 |
+--------------------------------------+-------+-------+----------+--------+-----------+---------+

方法二：使用关联子查询 #

如果只需要少量购买相关字段，也可以在SELECT语句中直接嵌套关联子查询，避免额外的JOIN操作：

SELECT
    promotion_id,
    COUNT(*) AS total,
    SUM(CASE WHEN action_type = 'share_product' THEN 1 ELSE 0 END) AS share,
    SUM(CASE WHEN action_type = 'add_product_to_favorites' THEN 1 ELSE 0 END) AS favorite,
    SUM(CASE WHEN action_type = 'add_product_to_basket' THEN 1 ELSE 0 END) AS basket,
    (SELECT COUNT(*) FROM purchases p WHERE p.promotion_id = va.promotion_id) AS purchases,
    (SELECT SUM(price) FROM purchases p WHERE p.promotion_id = va.promotion_id) AS revenue
FROM page_visit_actions va
GROUP BY promotion_id;

原查询问题分析 #

你之前的查询直接左连接两张未聚合的表，导致promotion_id对应的每条行为记录都会匹配所有对应的购买记录。比如5ac4ea14-f157-45a0-a706-765f4c624920有2条行为记录、2条购买记录，连接后会生成2*2=4条记录，最终统计时total、share、favorite、purchases、revenue全部被翻倍，出现错误结果。

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