如何优化计算各区域国家平均语言数量的SQL查询语句？

原写法存在的问题 #

• 额外创建2张中间临时表，产生不必要的磁盘IO开销，数据量较大时性能损耗明显
• 多表关联逻辑冗余，多次扫描相同的regions、countries表，执行效率低
• 缺少边界处理，若区域内无国家时除法运算会触发除零报错

优化后实现方案 #

仅需一次查询即可完成计算，无需创建中间表，逻辑更简洁执行效率更高：

SELECT 
    r.name AS region_name,
    COUNT(DISTINCT c.country_id) AS total_countries,
    COUNT(cl.country_id) AS total_langs,
    ROUND(
        CASE WHEN COUNT(DISTINCT c.country_id) = 0 THEN 0 
             ELSE COUNT(cl.country_id)::DECIMAL / COUNT(DISTINCT c.country_id) 
        END, 
    1) AS avg_lang_count_per_country
FROM regions r
LEFT JOIN countries c ON r.region_id = c.region_id
LEFT JOIN country_languages cl ON c.country_id = cl.country_id
GROUP BY r.name
ORDER BY avg_lang_count_per_country DESC;

逻辑说明 #

• 用LEFT JOIN保留所有区域数据，即使该区域没有国家也会出现在结果中
• COUNT(DISTINCT c.country_id)统计每个区域下的国家总数，避免多语言条目导致国家数重复统计
• 加入CASE判断处理区域无国家的场景，避免除零报错
• 一次分组聚合完成所有指标计算，仅需扫描三张表各一次，性能远高于原写法

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