PostgreSQL聚合函数嵌套错误：视图创建问题的解决方法

错误原因 #

你遇到的aggregate function calls may not be nested错误，核心原因有两个：

1. 关键字冲突：你的member_log_counts视图中存在名为count的字段，而count是PostgreSQL的保留关键字。当你在查询中写SUM(mlc.count)时，PostgreSQL会误将mlc.count解析为聚合函数COUNT()，而非视图的字段，最终导致SUM(COUNT())这种嵌套聚合的非法调用。
2. 聚合结果二次聚合的语义问题：即使字段名不冲突，若member_log_counts中的count、general等字段本身是通过聚合函数（如SUM）计算得到的结果，当你再次对这些结果使用SUM时，在某些场景下（比如视图未按tc_no分组），也会被PostgreSQL判定为嵌套聚合操作。

修复方案 #

方案1：用双引号包裹关键字字段名 #

直接在查询中对count字段添加双引号，明确告诉PostgreSQL这是字段名而非聚合函数：

CREATE VIEW gender_log_counts AS
 SELECT
     ROW_NUMBER() OVER () AS id, 
     CASE 
     WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) > 18 AND m.gender = 'female' THEN 'f>18'
     WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) > 18 AND m.gender = 'male' THEN 'm>18'
     WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) <= 18 AND m.gender = 'female' THEN 'f<18'
     WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) <= 18 AND m.gender = 'male' THEN 'm<18' 
 END AS gender_age_category,
 mlc.facility_id,
 COALESCE(SUM(mlc."count"), 0) AS total,
 COALESCE(SUM(mlc.general), 0) AS general,
 COALESCE(SUM(mlc.course), 0) AS course,
 COALESCE(SUM(mlc.club), 0) AS club 
FROM
 members m
LEFT JOIN
 member_log_counts mlc ON mlc.tc_no = m.tc_no
GROUP BY
 gender_age_category,
 mlc.facility_id;

方案2：修改视图中的关键字字段名 #

避免使用PostgreSQL保留关键字作为字段名，比如将member_log_counts中的count改为log_count：

-- 先更新member_log_counts视图
CREATE OR REPLACE VIEW member_log_counts AS
SELECT 
    tc_no, 
    facility_id, 
    SUM(log_count) AS log_count, 
    SUM(general) AS general, 
    SUM(course) AS course, 
    SUM(club) AS club
FROM logs
GROUP BY tc_no, facility_id;

-- 再创建目标视图
CREATE VIEW gender_log_counts AS
 SELECT
     ROW_NUMBER() OVER () AS id, 
     CASE 
     WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) > 18 AND m.gender = 'female' THEN 'f>18'
     WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) > 18 AND m.gender = 'male' THEN 'm>18'
     WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) <= 18 AND m.gender = 'female' THEN 'f<18'
     WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) <= 18 AND m.gender = 'male' THEN 'm<18' 
 END AS gender_age_category,
 mlc.facility_id,
 COALESCE(SUM(mlc.log_count), 0) AS total,
 COALESCE(SUM(mlc.general), 0) AS general,
 COALESCE(SUM(mlc.course), 0) AS course,
 COALESCE(SUM(mlc.club), 0) AS club 
FROM
 members m
LEFT JOIN
 member_log_counts mlc ON mlc.tc_no = m.tc_no
GROUP BY
 gender_age_category,
 mlc.facility_id;

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