org.apache.spark.sql.AnalysisException:表达式't2.`sum_click_passed`‘既不存在于组中，也不是聚合函数

Question

例如：

SELECT
    bucket,
    repeat_all_click,
    sum_click_passed,
    sum_imp_passed,
    sum_charge,
    sum_click_passed as acp,
    sum_roi_cnt / sum_click_passed as shop_cvr,
    sum_roi_amt / sum_charge as shop_roi,
    sum_roi_pay_cnt / sum_click_passed as pay_cvr,
    sum_roi_pay_amt / sum_charge as pay_roi,
    1.0 * sum_cpv_all / 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

用户类抛出异常: org.apache.spark.sql.AnalysisException:表达式't2.sum_click_passed‘既不存在于组by中，也不是聚合函数。添加到group by ()或first_value中，如果您不关心得到哪个值。；先排序bucket#152 ASC，repeat_all_click#141 ASC优先，true +聚合bucket#152，repeat_all_click#141，[bucket#152，repeat_all_click#141，

我不太熟悉Hiveql，但是它在SQL中不应该是错误的。

不幸的是，它像以前一样有错误，我不知道如何正确地修复它，因为我认为sum(click_passed) as sum_click_passed应该是一个聚合函数。。

有人能帮我吗？

提前谢谢。

Answer 1

我认为hql语句语法是错误的。删除这些额外的括号后，第一，从和最后一组子句。语法应该是

SELECT ..
FROM 
          (SELECT... FROM T1)T1
JOIN (SELECT... )T2 ON ...
JOIN (SELECT... )T3 ON ...
GROUP BY...
ORDER BY...

请在下面使用。

SELECT
    bucket,
    repeat_all_click,
    sum_click_passed,
    sum_imp_passed,
    sum_charge,
    sum_click_passed as acp,
    sum_roi_cnt / sum_click_passed as shop_cvr,
    sum_roi_amt / sum_charge as shop_roi,
    sum_roi_pay_cnt / sum_click_passed as pay_cvr,
    sum_roi_pay_amt / sum_charge as pay_roi,
    1.0 * sum_cpv_all / sum_spv_all as imp_rate
FROM
    --( removed/commented out
        (
            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
   --) removed
GROUP BY
    1,
    2
ORDER BY
    1,
    2