Greenplum迁移至Hive时子查询不支持的SQL适配问题求助

问题根因 #

你遇到的报错是因为Hive低版本（2.0以下）不支持WHERE子句中直接嵌套非关联标量子查询，原代码中hour <= 子查询的写法不符合Hive语法规范。

修复后的Hive SQL #

-- 预计算阈值小时：当天UVC类型的第二大小时值
WITH hour_threshold AS (
    SELECT hour AS max_hour
    FROM (
        SELECT 
            hour,
            row_number() OVER(ORDER BY hour DESC) iRowsID
        FROM test.tablename 
        WHERE data_date = current_date AND type = 'UVC'
    ) tbl1
    WHERE iRowsID = 2
)
SELECT concat_ws(';', collect_list(Display_String)) AS final_str
FROM (
    SELECT 
        data_day,
        data_day||' '||trim(format_number(sum(revenue), 0)) AS Display_String 
    FROM (
        SELECT 
            CASE 
                WHEN data_date = current_date THEN 'D:' 
                WHEN data_date = date_sub(current_date,1) THEN ' D-01:'
                WHEN data_date = date_sub(current_date,2) THEN ' D-02:'
                WHEN data_date = date_sub(current_date,7) THEN ' D-07:'
                WHEN data_date = date_sub(current_date,28) THEN ' D-28:'
            END data_day, 
            revenue
        FROM test.tablename
        -- 关联预计算的小时阈值，替代原嵌套子查询过滤逻辑
        CROSS JOIN hour_threshold
        WHERE data_date BETWEEN date_sub(current_date,28) AND current_date 
        AND hour <= hour_threshold.max_hour
        AND type = 'UVC'
    ) a
    GROUP BY data_day
    ORDER BY data_day
) aa;

注：如果你的Hive版本不支持format_number函数，可将该部分替换为原逻辑regexp_replace(sum(revenue), '(?!^)(\\d\\d\\d(?=(\\d\\d\\d)*\\b))', ',$1')，效果完全一致。

主要修改点说明 #

• 新增CTEhour_threshold预计算过滤用的小时阈值，通过CROSS JOIN关联到主表，避免WHERE子句嵌套子查询，兼容Hive语法
• 简化字符串拼接逻辑：直接对排序后的Display_String用collect_list+concat_ws实现Greenplum中string_agg的效果，不需要嵌套窗口函数，执行效率更高
• 用Hive内置函数实现千分位格式化，代码更易维护，结果和原Greenplum的to_char逻辑一致

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