Spark SQL报错sum_click_passed未在group by也非聚合函数如何解决

你外层查询的GROUP BY子句仅指定了bucket、repeat_all_click两个分组维度，但外层SELECT列表中sum_click_passed、sum_imp_passed等字段既不属于分组维度，也没有被聚合函数包裹，不符合Spark SQL的聚合语义要求：

当查询使用GROUP BY分组时，SELECT子句中只能出现分组维度字段，或者被聚合函数包裹的非分组维度字段。

虽然sum_click_passed是子查询t2中聚合生成的字段，但三个子查询join后得到的中间结果中，同一个bucket+repeat_all_click分组下会存在多条不同request_id对应的sum_click_passed取值，Spark无法直接确定分组后该字段的返回逻辑，因此抛出异常。

根据按实验桶、点击频次分组统计汇总指标的常见业务场景，只需要在外层SELECT的非分组维度字段外包裹对应的聚合函数即可，常用逻辑为对同分组内的指标求和：

• 对于直接取的指标字段，如sum_click_passed、sum_imp_passed，统一套sum()聚合
• 对于比值类计算字段，分子分母分别套sum()后再做运算

如果业务上不需要求和，仅需要取分组内任意一个值，也可以按照报错提示用first()/first_value()包裹字段。

SELECT
    bucket,
    repeat_all_click,
    sum(sum_click_passed) as sum_click_passed,
    sum(sum_imp_passed) as sum_imp_passed,
    sum(sum_charge) as sum_charge,
    sum(sum_click_passed) as acp,
    sum(sum_roi_cnt) / sum(sum_click_passed) as shop_cvr,
    sum(sum_roi_amt) / sum(sum_charge) as shop_roi,
    sum(sum_roi_pay_cnt) / sum(sum_click_passed) as pay_cvr,
    sum(sum_roi_pay_amt) / sum(sum_charge) as pay_roi,
    1.0 * sum(sum_cpv_all) / sum(sum_spv_all) as imp_rate
FROM
    (
        (
            SELECT
                request_id,
                IF (
                    all_click_cnt = 1,
                    '= 1',
                    IF (
                        all_click_cnt > 1,
                        '> 1',
                        '= 0'
                    )
                ) as repeat_all_click
            FROM
                a
            WHERE
                partition_date BETWEEN '2021-09-08'
                AND '2021-09-17'
                AND channel = 'HS'
                AND slot_id = 5
                AND (
                    rerank_algo = 'algo1'
                    OR rerank_algo = 'algo2'
                )
            GROUP BY
                1,
                2
        ) t1
        JOIN (
            SELECT
                request_id,
                sum(click_passed) as sum_click_passed,
                sum(imp_passed) as sum_imp_passed,
                sum(charge) as sum_charge,
                sum(roi_cnt) as sum_roi_cnt,
                sum(roi_amt) as sum_roi_amt,
                sum(roi_pay_cnt) as sum_roi_pay_cnt,
                sum(roi_pay_amt) as sum_roi_pay_amt,
                sum(cpv_all) as sum_cpv_all,
                sum(spv_all) as sum_spv_all
            FROM
                b
            WHERE
                partition_date BETWEEN '2021-09-14'
                AND '2021-09-17'
                AND slotid = 5
            GROUP BY
                request_id
        ) t2 ON t1.request_id = t2.request_id
        JOIN (
            SELECT
                requestid AS request_id,
                IF (strategy_path LIKE '%4-54-2612%', 'EXP', 'BASE') AS bucket
            FROM
                c
            WHERE
                (
                    dt BETWEEN '20210914'
                    AND '20210917'
                    AND channel = 'S'
                    AND (
                        slot_ids LIKE '%50011%'
                        OR slot_ids LIKE '%50020%'
                    )
                    AND (
                        strategy_path LIKE '%54-4%'
                        OR strategy_path LIKE '%54-2%'
                    )
                )
            GROUP BY
                1,
                2
        ) t3 ON t2.request_id = t3.request_id
    )
GROUP BY
    1,
    2
ORDER BY
    1,
    2

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