SQL按日期提取月份分组统计时报错1055的技术问询

错误原因 #

你遇到的1055错误是MySQL的ONLY_FULL_GROUP_BY模式导致的：当开启该模式时，SELECT列表中的所有非聚合列必须出现在GROUP BY子句中，或者与GROUP BY中的列存在函数依赖。你的查询里直接保留了原始date列（如n.date），但它既不在GROUP BY里，也无法由分组键（月份、campaign）唯一确定，因此触发报错。

正确实现方案 #

核心思路：将年份+月份作为分组键（避免跨年月合并），SELECT列表仅保留分组键、聚合统计列，同时确保关联两张表时按月份匹配数据。

方案1：用DATE_FORMAT格式化年月

SELECT
    DATE_FORMAT(m.date, '%Y-%m') AS month,
    m.campaign,
    SUM(m.spend) AS total_spend,
    COUNT(DISTINCT n.customer_id) AS new_customers_count,
    -- 避免除以0，无新客时获客成本设为NULL
    CASE WHEN COUNT(DISTINCT n.customer_id) > 0 THEN SUM(m.spend)/COUNT(DISTINCT n.customer_id) ELSE NULL END AS customer_acquisition_cost
FROM
    marketing_campaign m
LEFT JOIN
    new_customers n 
    ON m.campaign = n.campaign 
    AND DATE_FORMAT(m.date, '%Y-%m') = DATE_FORMAT(n.date, '%Y-%m')
GROUP BY
    DATE_FORMAT(m.date, '%Y-%m'), m.campaign;

方案2：用EXTRACT提取年月数字（性能更优）

如果不需要格式化后的字符串，可直接提取YEAR_MONTH数值（如202405代表2024年5月）：

SELECT
    EXTRACT(YEAR_MONTH FROM m.date) AS year_month,
    m.campaign,
    SUM(m.spend) AS total_spend,
    COUNT(DISTINCT n.customer_id) AS new_customers_count,
    CASE WHEN COUNT(DISTINCT n.customer_id) > 0 THEN SUM(m.spend)/COUNT(DISTINCT n.customer_id) ELSE NULL END AS customer_acquisition_cost
FROM
    marketing_campaign m
LEFT JOIN
    new_customers n 
    ON m.campaign = n.campaign 
    AND EXTRACT(YEAR_MONTH FROM m.date) = EXTRACT(YEAR_MONTH FROM n.date)
GROUP BY
    EXTRACT(YEAR_MONTH FROM m.date), m.campaign;

关键注意事项 #

• 分组键必须与SELECT中的年月表达式完全一致，避免因别名或不同写法触发模式校验错误；
• 使用LEFT JOIN确保即使某个Campaign当月无新客，也能正常统计支出数据；
• 用COUNT(DISTINCT customer_id)避免同一用户在当月被重复统计，若new_customers表中每个用户每月仅存一条记录，可简化为COUNT(customer_id)；
• 加CASE WHEN处理无新客时的除以0问题，避免出现无效计算结果。

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