寻求替代CTE提升Impala SQL查询性能的优化方案

核心优化：移除冗余自连接 #

原查询的CTE+自连接是性能瓶颈的关键——它对同一张表做了两次全表扫描，还额外增加了关联计算开销。直接将地址拼接逻辑整合到主聚合查询中，只需要一次扫描即可完成所有操作，优化后的SQL如下：

SELECT 
    site_name,
    MIN(parent_address_region) AS region,
    GROUP_CONCAT(DISTINCT CONCAT(parent_address_line_1, COALESCE(parent_address_line_2, " "), COALESCE(parent_address_line_3, " "), COALESCE(parent_address_line_4, " ")), " | ") AS address_line_1,
    MIN(parent_city) AS city,
    MIN(parent_cntry_code) AS city_code,
    MIN(parent_county) AS country,
    MIN(parent_state_province) AS state_province,
    MIN(parent_state_province_code) AS province_code,
    MIN(parent_location_status) AS status,
    MIN(parent_location_sub_type) AS location_subtype,
    MIN(parent_location_type) AS location_type,
    MIN(parent_longitude) AS longitude,
    MIN(parent_latitude) AS latitude,
    MIN(parent_postal_code) AS postal_code,
    MIN(parent_postal_code_ext) AS postal_code_ext,
    GROUP_CONCAT(DISTINCT source_system_code, ", ") AS source_system,
    GROUP_CONCAT(DISTINCT business_group_description, ", ") AS business_group
FROM locations_all_vw
GROUP BY site_name

优化点说明 #

• 去掉了CTE和自连接，避免重复扫描大表和关联计算，直接减少IO与CPU开销
• 完全保留原查询的业务逻辑，聚合结果和原查询一致
• 减少临时数据集生成，节省中间存储资源

额外优化（针对重复行较多的场景） #

如果原表中同一site_name下存在大量重复行，可以先做全局去重再聚合，进一步降低聚合阶段的数据量：

WITH deduplicated_data AS (
    SELECT DISTINCT
        site_name,
        parent_address_region,
        CONCAT(parent_address_line_1, COALESCE(parent_address_line_2, " "), COALESCE(parent_address_line_3, " "), COALESCE(parent_address_line_4, " ")) AS concated_address,
        parent_city,
        parent_cntry_code,
        parent_county,
        parent_state_province,
        parent_state_province_code,
        parent_location_status,
        parent_location_sub_type,
        parent_location_type,
        parent_longitude,
        parent_latitude,
        parent_postal_code,
        parent_postal_code_ext,
        source_system_code,
        business_group_description
    FROM locations_all_vw
)
SELECT 
    site_name,
    MIN(parent_address_region) AS region,
    GROUP_CONCAT(concated_address, " | ") AS address_line_1,
    MIN(parent_city) AS city,
    MIN(parent_cntry_code) AS city_code,
    MIN(parent_county) AS country,
    MIN(parent_state_province) AS state_province,
    MIN(parent_state_province_code) AS province_code,
    MIN(parent_location_status) AS status,
    MIN(parent_location_sub_type) AS location_subtype,
    MIN(parent_location_type) AS location_type,
    MIN(parent_longitude) AS longitude,
    MIN(parent_latitude) AS latitude,
    MIN(parent_postal_code) AS postal_code,
    MIN(parent_postal_code_ext) AS postal_code_ext,
    GROUP_CONCAT(source_system_code, ", ") AS source_system,
    GROUP_CONCAT(business_group_description, ", ") AS business_group
FROM deduplicated_data
GROUP BY site_name

索引优化建议 #

如果site_name是高频分组字段，建议在locations_all_vw对应的物理表上：

• 按site_name做分区（适合数据量极大且按site_name分布均匀的场景）
• 创建site_name的布隆过滤器索引（适合过滤或分组时快速定位数据）

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