PostgreSQL中使用ARRAY_AGG在子查询统计数组元素数量的方法

需求说明 #

按feature_id、版本（新旧）统计每个区域的语言数量，初始查询意图为找出每个feature_id对应的适用语言并转换为数组。

原SQL语句 #

SELECT
  array_length(applied_languages_in_old_version, 1) AS COUNT1,
  array_length(applied_languages_in_new_version, 1) AS COUNT2
FROM (
SELECT
  t1.feature_id,
  t1.territory_type,
  t1.territory_category,
  (array_agg(DISTINCT t2.language), ', ') AS applied_languages_in_old_version,
  (array_agg(DISTINCT t4.language), ', ') AS applied_languages_in_new_verison
FROM kh_bel_territory_2023mar14.kh_bel_territory_2023mar14_territory t1
LEFT OUTER JOIN kh_bel_territory_2023mar14.kh_bel_territory_2023mar14_territory_name t2
ON t1.feature_id = t2.feature_id AND t2.name_type = 'PRIMARY_FOR_LANGUAGE'
JOIN kh_bel_territories_08nov2022.kh_bel_territories_08nov2022_territory t3
ON t1.feature_id = t3.feature_id
LEFT OUTER JOIN kh_bel_territories_08nov2022.kh_bel_territories_08nov2022_territory_name t4
ON t1.feature_id = t4.feature_id AND t4.name_type = 'PRIMARY_FOR_LANGUAGE') a
GROUP BY
  t1.feature_id,
  t1.territory_type,
  t1.territory_category
ORDER BY t1.feature_id; 

报错信息（翻译后） #

错误：列"t1.feature_id"必须出现在GROUP BY子句中或用于聚合函数

问题分析与修正 #

1. 分组字段引用错误：外层查询的数据源是子查询的别名a，而非原表t1，因此GROUP BY和ORDER BY子句中应使用a.前缀引用字段，而非t1.。
2. 数组处理语法错误：子查询中(array_agg(...), ', ')的写法不符合PostgreSQL语法，若要生成数组直接使用array_agg(DISTINCT ...)即可（后续用array_length统计长度无需转字符串）；若需字符串格式则应使用array_to_string(array_agg(DISTINCT ...), ', ')。
3. 字段拼写错误：子查询中applied_languages_in_new_verison拼写错误，需修正为applied_languages_in_new_version与外层字段名保持一致。
4. 聚合逻辑位置错误：原语句将聚合放在子查询却在外层重复分组，应将聚合逻辑移至子查询内完成，外层仅做数组长度统计，逻辑更清晰。

修正后的SQL语句 #

SELECT
  feature_id,
  territory_type,
  territory_category,
  array_length(applied_languages_in_old_version, 1) AS COUNT1,
  array_length(applied_languages_in_new_version, 1) AS COUNT2
FROM (
SELECT
  t1.feature_id,
  t1.territory_type,
  t1.territory_category,
  array_agg(DISTINCT t2.language) AS applied_languages_in_old_version,
  array_agg(DISTINCT t4.language) AS applied_languages_in_new_version
FROM kh_bel_territory_2023mar14.kh_bel_territory_2023mar14_territory t1
LEFT OUTER JOIN kh_bel_territory_2023mar14.kh_bel_territory_2023mar14_territory_name t2
ON t1.feature_id = t2.feature_id AND t2.name_type = 'PRIMARY_FOR_LANGUAGE'
JOIN kh_bel_territories_08nov2022.kh_bel_territories_08nov2022_territory t3
ON t1.feature_id = t3.feature_id
LEFT OUTER JOIN kh_bel_territories_08nov2022.kh_bel_territories_08nov2022_territory_name t4
ON t1.feature_id = t4.feature_id AND t4.name_type = 'PRIMARY_FOR_LANGUAGE'
GROUP BY
  t1.feature_id,
  t1.territory_type,
  t1.territory_category
) a
ORDER BY a.feature_id; 

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