Apache Impala：如何重写查询修复GROUP BY报错问题

报错信息 #

AnalysisException: select list expression not produced by aggregation output (missing from GROUP BY clause?): CASE WHEN unix_timestamp(from_utc_timestamp(now(), 'Asia/Jerusalem')) > unix_timestamp(lat) THEN 'LAT Passed' ELSE substr(CAST(round((((unix_timestamp(from_utc_timestamp(now(), 'Asia/Jerusalem')) - unix_timestamp(eat)) / 60) / ((unix_timestamp(lat) - unix_timestamp(eat)) / 60)) * 100, 2) AS STRING), 1, 5) END EAT_to_LAT

原查询语句 #

SELECT delivery_slot_from_hour
           AS delivery_slot_from,
       COUNT(order_line_id)
           AS order_lines,
       SUM(
           CASE
               WHEN order_line_status IN ('FINISHED', 'CANCELED', 'PICKED')
               THEN 1
               ELSE 0
           END)
           AS order_lines_finished,
       ROUND(
             (SUM(
                 CASE
                     WHEN order_line_status IN ('FINISHED', 'CANCELED', 'PICKED')
                     THEN 1
                     ELSE 0
                 END)
              / COUNT(order_line_id)) * 100,
             2)
           AS picking_lines_percent,
       CASE
           WHEN UNIX_TIMESTAMP(from_utc_timestamp(now(), 'MYRegion')) > UNIX_TIMESTAMP(lat)
           THEN 'LAT Passed'
           ELSE SUBSTR(
               CAST(
                   ROUND(
                         (((UNIX_TIMESTAMP(from_utc_timestamp(now(), 'MYRegion')) - UNIX_TIMESTAMP(eat)) / 60) /
                          ((UNIX_TIMESTAMP(lat) - UNIX_TIMESTAMP(eat)) / 60)) * 100,
                         2) AS STRING),
               1, 5)
       END
           AS EAT_to_LAT,
       ROUND(
             SUM(
                 CASE
                     WHEN order_line_status IN ('FINISHED', 'CANCELED', 'PICKED')
                     THEN 1
                     ELSE 0
                 END) / COUNT(order_line_id) *
             (((UNIX_TIMESTAMP(from_utc_timestamp(now(), 'MYRegion')) - UNIX_TIMESTAMP(eat)) / 60) /
              ((UNIX_TIMESTAMP(lat) - UNIX_TIMESTAMP(eat)) / 60)) * 100,
             2)
           AS ratio
FROM my_table
WHERE SUBSTR(delivery_slot_from, 1, 10) = '2023-06-28'
GROUP BY delivery_slot_from
ORDER BY delivery_slot_from;

尝试改写后的查询（仍报错） #

SELECT main.delivery_slot_from_hour    AS delivery_slot_from,
       main.order_lines,
       main.order_lines_finished,
       ROUND(
             (main.order_lines_finished / main.order_lines) * 100,
             2)                          AS picking_lines_percent,
       CASE
           WHEN UNIX_TIMESTAMP(from_utc_timestamp(now(), 'MYRegion')) > UNIX_TIMESTAMP(MAX(lat))
           THEN 'LAT Passed'
           ELSE SUBSTR(
               CAST(
                   ROUND(
                         (((UNIX_TIMESTAMP(from_utc_timestamp(now(), 'MYRegion')) - UNIX_TIMESTAMP(main.eat)) / 60) /
                          ((UNIX_TIMESTAMP(main.lat) - UNIX_TIMESTAMP(main.eat)) / 60)) * 100,
                         2) AS STRING),
               1, 5)
       END                             AS eat_to_lat,
       main.ratio
FROM (SELECT delivery_slot_from_hour,
             eat,
             lat,
             COUNT(order_line_id)
                 AS order_lines,
             SUM(
                 CASE
                     WHEN order_line_status IN ('FINISHED', 'CANCELED', 'PICKED')
                     THEN 1
                     ELSE 0
                 END)
                 AS order_lines_finished,
             SUM(
                 CASE
                     WHEN order_line_status IN ('FINISHED', 'CANCELED', 'PICKED')
                     THEN 1
                     ELSE 0
                 END) / COUNT(order_line_id) *
             (((UNIX_TIMESTAMP(from_utc_timestamp(now(), 'MYRegion')) - UNIX_TIMESTAMP(eat)) / 60) /
              ((UNIX_TIMESTAMP(lat) - UNIX_TIMESTAMP(eat)) / 60))
                 AS ratio
      FROM (SELECT delivery_slot_from_hour,
                   order_line_id,
                   order_line_status,
                   eat,
                   lat
            FROM my_table
            WHERE SUBSTR(delivery_slot_from_hour, 1, 10) = '2023-06-28') subquery
      GROUP BY delivery_slot_from_hour, eat, lat) main
GROUP BY main.delivery_slot_from_hour,
         main.ratio,
         main.order_lines,
         main.order_lines_finished
ORDER BY delivery_slot_from;

需求 #

已知报错原因是GROUP BY子句缺少eat和lat列，但希望仅按delivery_slot_from列分组，修复查询实现该需求。

核心问题：Impala遵循严格的GROUP BY规则——SELECT子句中的非聚合列必须出现在GROUP BY中，或者被聚合函数包裹。你的查询中EAT_to_LAT和ratio字段依赖eat、lat这两个非聚合列，而你不想把它们加入GROUP BY，需根据业务场景选择对应方案：

场景1：每个delivery_slot_from下eat、lat值唯一 #

如果同一delivery_slot_from对应的eat和lat是固定值（比如时段的起止时间），直接用聚合函数（如MAX()或MIN()）包裹eat和lat，即可无需将它们加入GROUP BY：

SELECT delivery_slot_from_hour AS delivery_slot_from,
       COUNT(order_line_id) AS order_lines,
       SUM(CASE WHEN order_line_status IN ('FINISHED', 'CANCELED', 'PICKED') THEN 1 ELSE 0 END) AS order_lines_finished,
       ROUND(
             (SUM(CASE WHEN order_line_status IN ('FINISHED', 'CANCELED', 'PICKED') THEN 1 ELSE 0 END) / COUNT(order_line_id)) * 100,
             2) AS picking_lines_percent,
       CASE
           WHEN UNIX_TIMESTAMP(from_utc_timestamp(now(), 'MYRegion')) > UNIX_TIMESTAMP(MAX(lat))
           THEN 'LAT Passed'
           ELSE SUBSTR(
               CAST(
                   ROUND(
                         (((UNIX_TIMESTAMP(from_utc_timestamp(now(), 'MYRegion')) - UNIX_TIMESTAMP(MAX(eat))) / 60) /
                          ((UNIX_TIMESTAMP(MAX(lat)) - UNIX_TIMESTAMP(MAX(eat))) / 60)) * 100,
                         2) AS STRING),
               1, 5)
       END AS EAT_to_LAT,
       ROUND(
             SUM(CASE WHEN order_line_status IN ('FINISHED', 'CANCELED', 'PICKED') THEN 1 ELSE 0 END) / COUNT(order_line_id) *
             (((UNIX_TIMESTAMP(from_utc_timestamp(now(), 'MYRegion')) - UNIX_TIMESTAMP(MAX(eat))) / 60) /
              ((UNIX_TIMESTAMP(MAX(lat)) - UNIX_TIMESTAMP(MAX(eat))) / 60)) * 100,
             2) AS ratio
FROM my_table
WHERE SUBSTR(delivery_slot_from, 1, 10) = '2023-06-28'
GROUP BY delivery_slot_from_hour
ORDER BY delivery_slot_from;

场景2：每个delivery_slot_from下eat、lat有多组值 #

如果同一delivery_slot_from下存在多组不同的eat和lat，需先按(delivery_slot_from, eat, lat)分组计算中间值，再对delivery_slot_from做二次聚合（聚合规则需符合业务逻辑，比如取平均值、求和等）：

SELECT delivery_slot_from,
       SUM(order_lines) AS total_order_lines,
       SUM(order_lines_finished) AS total_order_lines_finished,
       ROUND(
             (SUM(order_lines_finished) / SUM(order_lines)) * 100,
             2) AS picking_lines_percent,
       CASE
           WHEN MAX(CASE WHEN UNIX_TIMESTAMP(from_utc_timestamp(now(), 'MYRegion')) > UNIX_TIMESTAMP(lat) THEN 1 ELSE 0 END) = 1
           THEN 'LAT Passed'
           ELSE SUBSTR(
               CAST(
                   ROUND(AVG(ratio_raw), 2) AS STRING),
               1, 5)
       END AS EAT_to_LAT,
       ROUND(AVG(ratio), 2) AS avg_ratio
FROM (
    SELECT delivery_slot_from_hour AS delivery_slot_from,
           eat,
           lat,
           COUNT(order_line_id) AS order_lines,
           SUM(CASE WHEN order_line_status IN ('FINISHED', 'CANCELED', 'PICKED') THEN 1 ELSE 0 END) AS order_lines_finished,
           (((UNIX_TIMESTAMP(from_utc_timestamp(now(), 'MYRegion')) - UNIX_TIMESTAMP(eat)) / 60) /
            ((UNIX_TIMESTAMP(lat) - UNIX_TIMESTAMP(eat)) / 60)) * 100 AS ratio_raw,
           ROUND(
                 SUM(CASE WHEN order_line_status IN ('FINISHED', 'CANCELED', 'PICKED') THEN 1 ELSE 0 END) / COUNT(order_line_id) *
                 (((UNIX_TIMESTAMP(from_utc_timestamp(now(), 'MYRegion')) - UNIX_TIMESTAMP(eat)) / 60) /
                  ((UNIX_TIMESTAMP(lat) - UNIX_TIMESTAMP(eat)) / 60)) * 100,
                 2) AS ratio
    FROM my_table
    WHERE SUBSTR(delivery_slot_from, 1, 10) = '2023-06-28'
    GROUP BY delivery_slot_from_hour, eat, lat
) sub
GROUP BY delivery_slot_from
ORDER BY delivery_slot_from;

关键说明 #

• 你之前的改写报错，是因为外层查询中main.eat和main.lat既不在GROUP BY中，也未被聚合函数包裹，Impala无法确定如何处理这些非聚合列。
• 两种方案的选择完全取决于业务逻辑：若eat和lat是时段属性，同一分组下唯一，用第一种方案最直接；若存在多组值，必须明确聚合规则（平均、求和、取最值等）。

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