基于父表关联的广告维度SQL聚合查询及全量汇总实现

你之前写的查询存在两个核心问题，导致结果不符合预期：

• 日期过滤条件放置错误：把ad_insight的日期过滤写在WHERE子句里，会让原本的LEFT JOIN退化为INNER JOIN，那些在指定日期没有点击记录的广告（比如ad3）会被直接过滤，不会出现在结果集中。
• 多表直接关联的笛卡尔积问题：如果直接把广告表、广告洞察表、产品表、产品洞察表全量关联，会因为一对多的关联关系（一个广告对应多个产品、多个日期的洞察数据），把点击数、销售额重复放大几倍，统计结果完全失真。
  另外逗号拼接产品名的逻辑，需要用到数据库内置的字符串聚合函数实现，不同SQL方言的函数名有差异，核心逻辑是通用的。

1. 按广告维度的统计结果 #

核心思路是把每个维度的聚合逻辑单独做在子查询里，避免笛卡尔积，最后和广告主表左关联，没有匹配到的指标补0：

-- 适配MySQL语法，其他数据库只需替换字符串拼接函数即可
SELECT
    a.ad_name,
    -- 没匹配到点击数据的补0
    IFNULL(ad_click_stats.clicks, 0) AS clicks,
    -- 没匹配到销售数据的补0
    IFNULL(prod_sale_stats.product_sale, 0) AS product_sale,
    IFNULL(prod_sale_stats.products, '') AS products
FROM ad a
-- 预聚合每个广告在指定日期范围的累计点击
LEFT JOIN (
    SELECT 
        ad_id,
        SUM(clicks) AS clicks
    FROM ad_insight
    WHERE `date` BETWEEN '2021-04-23' AND '2021-04-25'
    GROUP BY ad_id
) ad_click_stats ON a.ad_id = ad_click_stats.ad_id
-- 预聚合每个广告下的累计销售额、拼接的产品名
LEFT JOIN (
    SELECT 
        p.ad_id,
        SUM(pi.sale) AS product_sale,
        -- 对同一个广告下的产品名去重，按字典序拼接成逗号分隔的字符串
        GROUP_CONCAT(DISTINCT p.product_name ORDER BY p.product_name SEPARATOR ', ') AS products
    FROM product p
    LEFT JOIN product_insight pi 
        ON p.product_id = pi.product_id 
        -- 把日期过滤放到JOIN的ON条件里，避免过滤掉没有销售记录的产品
        AND pi.`date` BETWEEN '2021-04-23' AND '2021-04-25'
    GROUP BY p.ad_id
) prod_sale_stats ON a.ad_id = prod_sale_stats.ad_id
ORDER BY a.ad_id;

不同数据库的字符串聚合适配：

• PostgreSQL 将 GROUP_CONCAT 替换为 STRING_AGG(DISTINCT p.product_name, ', ' ORDER BY p.product_name)
• SQLite 直接沿用 GROUP_CONCAT(DISTINCT p.product_name, ', ') 即可

2. 全局汇总结果 #

核心思路是基于上面得到的广告维度结果，做二次全局聚合，需要注意把每个广告拼接好的产品名拆成单值，再做全局去重拼接，避免出现产品名重复的问题：

SELECT
    COUNT(*) AS total_ads,
    SUM(clicks) AS total_clicks,
    SUM(product_sale) AS total_product_sale,
    GROUP_CONCAT(DISTINCT split_product ORDER BY split_product SEPARATOR ', ') AS unique_all_products
FROM (
    SELECT
        clicks,
        product_sale,
        -- 拆分拼接好的产品名字符串，得到单个产品值
        SUBSTRING_INDEX(SUBSTRING_INDEX(products, ', ', helper.n), ', ', -1) AS split_product
    FROM (
        -- 这里复用第一部分的广告维度统计结果
        SELECT
            a.ad_name,
            IFNULL(ad_click_stats.clicks, 0) AS clicks,
            IFNULL(prod_sale_stats.product_sale, 0) AS product_sale,
            IFNULL(prod_sale_stats.products, '') AS products
        FROM ad a
        LEFT JOIN (
            SELECT ad_id, SUM(clicks) AS clicks FROM ad_insight WHERE `date` BETWEEN '2021-04-23' AND '2021-04-25' GROUP BY ad_id
        ) ad_click_stats ON a.ad_id = ad_click_stats.ad_id
        LEFT JOIN (
            SELECT 
                p.ad_id,
                SUM(pi.sale) AS product_sale,
                GROUP_CONCAT(DISTINCT p.product_name ORDER BY p.product_name SEPARATOR ', ') AS products
            FROM product p
            LEFT JOIN product_insight pi 
                ON p.product_id = pi.product_id AND pi.`date` BETWEEN '2021-04-23' AND '2021-04-25'
            GROUP BY p.ad_id
        ) prod_sale_stats ON a.ad_id = prod_sale_stats.ad_id
    ) ad_level_result
    -- 用数字辅助表拆分字符串，最多支持5个产品名的拆分，可根据实际产品数量调整
    JOIN (
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    ) helper ON helper.n <= 1 + LENGTH(ad_level_result.products) - LENGTH(REPLACE(ad_level_result.products, ', ', ''))
) global_calc;

• 原测试脚本中product表的插入语句存在语法错误：第4条values记录结尾缺少分号/逗号，导致后续第5条(1,3,'prod1')的插入语句执行失败，实际测试时需要先修正这个语法问题。
• 所有日期范围的过滤条件，建议统一放到关联的ON子句或者子查询内部，不要放到最外层的WHERE中，避免出现主表记录被误过滤的问题。

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