SQL报错Cannot mix aggregate and non-aggregate 按通话时长摊分成本方案咨询

你遇到的报错是因为分组查询时，Cost、单客户总时长属于非聚合字段，和sum(Duration)这类聚合字段同时出现在select子句中，且没有加入分组条件，SQL引擎无法确认非聚合字段的计算规则，因此抛出异常。

有两种主流实现方式，均兼容绝大多数SQL引擎：

方案1：分层CTE实现（兼容性最好） #

逐层计算客户总时长、单客户单分类分摊成本，最后按三级分类汇总：

WITH client_total AS (
    -- 计算每个客户的总通话时长，关联对应成本
    SELECT 
        t1.`Client ID`,
        SUM(t1.Duration) AS total_duration_per_client,
        t2.Cost
    FROM table1 t1
    LEFT JOIN table2 t2 ON t1.`Client ID` = t2.`Client ID`
    GROUP BY t1.`Client ID`, t2.Cost
),
client_cat_allocation AS (
    -- 计算单客户在对应三级分类下的分摊成本
    SELECT 
        t1.`Cat 1` AS Cat1,
        t1.`Cat 2` AS Cat2,
        t1.`Cat 3` AS Cat3,
        t1.`Client ID`,
        SUM(t1.Duration) AS cat_duration,
        (SUM(t1.Duration) * 1.0 / ct.total_duration_per_client) * ct.Cost AS allocated_cost
    FROM table1 t1
    JOIN client_total ct ON t1.`Client ID` = ct.`Client ID`
    GROUP BY t1.`Cat 1`, t1.`Cat 2`, t1.`Cat 3`, t1.`Client ID`, ct.total_duration_per_client, ct.Cost
)
-- 最终按三级分类汇总得到结果
SELECT 
    Cat1,
    Cat2,
    Cat3,
    SUM(cat_duration) AS `Total Duration`,
    SUM(allocated_cost) AS `Blended Cost`
FROM client_cat_allocation
GROUP BY Cat1, Cat2, Cat3
ORDER BY Cat1, Cat2, Cat3;

方案2：窗口函数实现（写法更简洁） #

如果你的数据库支持窗口函数，可以用更短的代码实现：

WITH t1_with_client_total AS (
    SELECT 
        *,
        -- 窗口函数直接计算每行对应客户的总通话时长
        SUM(Duration) OVER (PARTITION BY `Client ID`) AS client_total_duration
    FROM table1
)
SELECT 
    t1.`Cat 1` AS Cat1,
    t1.`Cat 2` AS Cat2,
    t1.`Cat 3` AS Cat3,
    SUM(t1.Duration) AS `Total Duration`,
    SUM((t1.Duration * 1.0 / t1.client_total_duration) * t2.Cost) AS `Blended Cost`
FROM t1_with_client_total t1
LEFT JOIN table2 t2 ON t1.`Client ID` = t2.`Client ID`
GROUP BY t1.`Cat 1`, t1.`Cat 2`, t1.`Cat 3`
ORDER BY Cat1, Cat2, Cat3;

• 计算时乘以1.0是为了避免整数除法导致的精度损失，若你的数据库默认支持浮点数除法可省略
• 若存在客户总通话时长为0的情况，可添加CASE WHEN判断避免除以0的报错

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