如何在不添加至GROUP BY的情况下为查询新增ad_group字段

要在不修改原有GROUP BY逻辑的前提下新增ad_group字段，核心思路是让ad_group的取值和原有聚合维度保持一致，或是用聚合函数合并同组内的ad_group值，以下是几种实用方案：

1. 用窗口函数匹配对应维度的ad_group #

如果每个原有聚合维度（比如广告系列ID、创意ID）对应的ad_group是唯一的，可通过窗口函数FIRST_VALUE或聚合函数MAX/MIN获取对应值，无需改动原有GROUP BY：

WITH original_cte AS (
    -- 你的原有CTE逻辑：计算广告系列和创意花费
    SELECT 
        sg.campaign_id,
        sg.creative_id,
        SUM(sg.spend) AS total_spend
    FROM spend_google sg
    JOIN spend_google_data sgdt ON sg.campaign_id = sgdt.campaign_id AND sg.creative_id = sgdt.creative_id
    GROUP BY sg.campaign_id, sg.creative_id
)
SELECT 
    oc.*,
    -- 取同组内唯一的ad_group值
    FIRST_VALUE(sgdt.ad_group) OVER (PARTITION BY oc.campaign_id, oc.creative_id) AS ad_group
FROM original_cte oc
JOIN spend_google_data sgdt ON oc.campaign_id = sgdt.campaign_id AND oc.creative_id = sgdt.creative_id;

如果只需要同组内任意一个ad_group值，也可以用子查询简化：

WITH original_cte AS (
    -- 原有CTE逻辑
    SELECT 
        sg.campaign_id,
        sg.creative_id,
        SUM(sg.spend) AS total_spend
    FROM spend_google sg
    JOIN spend_google_data sgdt ON sg.campaign_id = sgdt.campaign_id AND sg.creative_id = sgdt.creative_id
    GROUP BY sg.campaign_id, sg.creative_id
)
SELECT 
    oc.*,
    (SELECT MAX(ad_group) FROM spend_google_data sgdt WHERE sgdt.campaign_id = oc.campaign_id AND sgdt.creative_id = oc.creative_id) AS ad_group
FROM original_cte oc;

2. 合并同组内的多个ad_group值 #

如果同一聚合组内存在多个不同的ad_group，且不想改变原有聚合结果，可以用字符串聚合函数把所有值合并为一个字符串，不同数据库函数略有差异：

• MySQL/MariaDB 用GROUP_CONCAT：

WITH original_cte AS (
    -- 原有CTE逻辑
    SELECT 
        sg.campaign_id,
        sg.creative_id,
        SUM(sg.spend) AS total_spend
    FROM spend_google sg
    JOIN spend_google_data sgdt ON sg.campaign_id = sgdt.campaign_id AND sg.creative_id = sgdt.creative_id
    GROUP BY sg.campaign_id, sg.creative_id
)
SELECT 
    oc.*,
    (SELECT GROUP_CONCAT(DISTINCT ad_group SEPARATOR ', ') FROM spend_google_data sgdt WHERE sgdt.campaign_id = oc.campaign_id AND sgdt.creative_id = oc.creative_id) AS ad_groups
FROM original_cte oc;

• PostgreSQL/SQL Server（2017+）用STRING_AGG：

WITH original_cte AS (
    -- 原有CTE逻辑
    SELECT 
        sg.campaign_id,
        sg.creative_id,
        SUM(sg.spend) AS total_spend
    FROM spend_google sg
    JOIN spend_google_data sgdt ON sg.campaign_id = sgdt.campaign_id AND sg.creative_id = sgdt.creative_id
    GROUP BY sg.campaign_id, sg.creative_id
)
SELECT 
    oc.*,
    (SELECT STRING_AGG(DISTINCT ad_group, ', ') FROM spend_google_data sgdt WHERE sgdt.campaign_id = oc.campaign_id AND sgdt.creative_id = oc.creative_id) AS ad_groups
FROM original_cte oc;

3. 确认维度关联后直接加入GROUP BY（可选） #

如果ad_group和原有聚合维度（campaign_id+creative_id）是一一对应的，直接把ad_group加到原有CTE的SELECT和GROUP BY里，聚合结果不会改变——因为每个分组的ad_group唯一，不会拆分原有聚合单元：

WITH original_cte AS (
    SELECT 
        sg.campaign_id,
        sg.creative_id,
        sgdt.ad_group,
        SUM(sg.spend) AS total_spend
    FROM spend_google sg
    JOIN spend_google_data sgdt ON sg.campaign_id = sgdt.campaign_id AND sg.creative_id = sgdt.creative_id
    GROUP BY sg.campaign_id, sg.creative_id, sgdt.ad_group
)
SELECT * FROM original_cte;

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