Spark SQL分组报错：t.name不在GROUP BY或聚合函数中问题咨询

报错原因 #

Spark SQL（以及多数SQL方言）的语句解析顺序是FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY，也就是说GROUP BY子句的解析早于SELECT子句：

• 你在GROUP BY中写的Area、date_formated是SELECT里定义的别名，但在解析GROUP BY时这些别名还未生效，Spark会把它们当作表t中的原始列（而你的表并没有这些列）。
• SELECT中的Area是通过t.name计算得到的，Code是通过t.cod计算得到的，这两个原始列t.name、t.cod既没有出现在GROUP BY中，也没有被聚合函数（比如first()、max()）包裹，不符合分组查询的规则，因此触发报错。

解决方案 #

方案1：将SELECT中的CASE表达式完整复制到GROUP BY中 #

把计算Area和Code的逻辑直接放到GROUP BY里，同时注意不能使用date_formated别名，要改用原始列f_date：

SELECT
        COUNT(*) number,
        COALESCE(Presents, 0) Presents,
        f_date date_formated,
        CASE 
            WHEN (REPLACE(t.name, '.', '') LIKE '%TEST1%') THEN 'TEST1' 
            WHEN (REPLACE(t.name, '.', '') LIKE '%TEST2%') THEN 'TEST2' 
            ELSE 'UINT' 
        END Area,
        CASE 
            WHEN (t.cod= 1) THEN 'A' 
            WHEN (t.cod= 2) THEN 'B' 
            WHEN (t.cod= 3) THEN 'C' 
            WHEN (t.cod= 4) THEN 'E' 
            ELSE 'N/A' 
        END Code
     FROM
       TABLE t
     GROUP BY
       Presents, 
       f_date,
       CASE 
            WHEN (REPLACE(t.name, '.', '') LIKE '%TEST1%') THEN 'TEST1' 
            WHEN (REPLACE(t.name, '.', '') LIKE '%TEST2%') THEN 'TEST2' 
            ELSE 'UINT' 
        END,
       CASE 
            WHEN (t.cod= 1) THEN 'A' 
            WHEN (t.cod= 2) THEN 'B' 
            WHEN (t.cod= 3) THEN 'C' 
            WHEN (t.cod= 4) THEN 'E' 
            ELSE 'N/A' 
        END

方案2：用CTE/子查询先计算派生列，再外层分组 #

先通过CTE把Area、Code、date_formated这些派生列计算好，再在外层执行分组，这样GROUP BY就可以直接使用别名：

WITH temp_data AS (
    SELECT
        COALESCE(Presents, 0) Presents,
        f_date date_formated,
        CASE 
            WHEN (REPLACE(t.name, '.', '') LIKE '%TEST1%') THEN 'TEST1' 
            WHEN (REPLACE(t.name, '.', '') LIKE '%TEST2%') THEN 'TEST2' 
            ELSE 'UINT' 
        END Area,
        CASE 
            WHEN (t.cod= 1) THEN 'A' 
            WHEN (t.cod= 2) THEN 'B' 
            WHEN (t.cod= 3) THEN 'C' 
            WHEN (t.cod= 4) THEN 'E' 
            ELSE 'N/A' 
        END Code
    FROM TABLE t
)
SELECT
    COUNT(*) number,
    Presents,
    date_formated,
    Area,
    Code
FROM temp_data
GROUP BY Presents, date_formated, Area, Code

方案3：用聚合函数包裹原始列（谨慎使用） #

如果能确保同一个分组内的t.name、t.cod计算出的Area、Code是唯一的，可以用first()、max()这类聚合函数包裹t.name和t.cod，这样就不需要把CASE表达式放到GROUP BY里：

SELECT
        COUNT(*) number,
        COALESCE(Presents, 0) Presents,
        f_date date_formated,
        CASE 
            WHEN (REPLACE(first(t.name), '.', '') LIKE '%TEST1%') THEN 'TEST1' 
            WHEN (REPLACE(first(t.name), '.', '') LIKE '%TEST2%') THEN 'TEST2' 
            ELSE 'UINT' 
        END Area,
        CASE 
            WHEN (first(t.cod)= 1) THEN 'A' 
            WHEN (first(t.cod)= 2) THEN 'B' 
            WHEN (first(t.cod)= 3) THEN 'C' 
            WHEN (first(t.cod)= 4) THEN 'E' 
            ELSE 'N/A' 
        END Code
     FROM
       TABLE t
     GROUP BY
       Presents, 
       f_date

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