PostgreSQL：用string_agg与count实现教师组学生主修科目统计

需求说明 #

需要编写PostgreSQL查询，展示教师组的group_id，以及该组学生的主修科目列表和对应人数。

表用途说明 #

dm_group表有两种用途：

• 标识教师：当entity_type='Group'且group_type='TRAINER'时，该记录代表教师，name字段为教师姓名；
• 记录用户主修科目：每个用户在dm_group中至少有一条entity_type='Discipline'的记录，name字段为用户的主修科目名称。

统计逻辑步骤 #

1. 筛选dm_group中group_type='TRAINER'的记录，识别所有教师；
2. 通过dm_user_to_group表找到每个教师组下的所有用户；
3. 根据用户ID，在dm_group中找到对应的主修科目（entity_type='Discipline'），统计每个科目在该教师组的人数。

预期结果格式 #

group_id| 科目(人数);科目(人数);...

现有未达预期的SQL #

SELECT tr.id AS "Trainer ID",
       string_agg(info.name || '(' || info.number || ')',
                  '; ' order by info.name
           ) AS "Discipline Counts"
FROM (
         SELECT d.name, count(u.userid) as number
         FROM dm_group tr
                  LEFT JOIN
              dm_user_to_group utg ON tr.id = utg.group_id
                  LEFT JOIN
              dm_user u ON utg.user_id = u.userid
                  LEFT JOIN
              dm_group d ON u.userid = d.id AND d.entity_type = 'Discipline'
         group by d.name, u.userid) info
         full outer JOIN dm_group tr on tr.name = info.name
where tr.group_type = 'TRAINER'
GROUP BY tr.id
ORDER BY tr.id;

涉及表结构 #

dm_group表 #

create table dm_group (
  entity_type  varchar(15) not null,
  id           bigserial primary key,
  description  text,
  name         varchar(255),
  group_type   varchar(255)
);

（注：原表结构中description行末尾缺失逗号，已修正）

dm_user_to_group表 #

create table dm_user_to_group (
  group_id bigint constraint fk_gr_to_u references dm_group,
  user_id  bigint constraint fk_us_to_gr references dm_user
);

修正后的SQL #

SELECT 
    tr.id AS "group_id",
    string_agg(
        CONCAT(d.name, '(', COUNT(DISTINCT u.userid), ')'),
        '; ' ORDER BY d.name
    ) AS "科目统计"
FROM dm_group tr
LEFT JOIN dm_user_to_group utg ON tr.id = utg.group_id
LEFT JOIN dm_user u ON utg.user_id = u.userid
LEFT JOIN dm_group d ON u.userid = d.id AND d.entity_type = 'Discipline'
WHERE tr.group_type = 'TRAINER'
GROUP BY tr.id
ORDER BY tr.id;

修正说明 #

1. 移除冗余子查询：原SQL子查询逻辑混乱，直接通过主查询关联所有表即可完成统计；
2. 修正错误关联：原查询中用教师姓名关联科目名称的逻辑完全错误，改为从教师表出发，依次关联用户组关系表、用户表、科目表；
3. 正确统计人数：使用COUNT(DISTINCT u.userid)避免同一用户因多个主修科目被重复计数（若业务规定每个用户只有一个主修科目，可简化为COUNT(u.userid)）；
4. 简化字符串拼接：用CONCAT函数替代字符串连接符，代码更简洁易读；
5. 对齐预期结果字段：将输出字段名调整为符合预期的group_id和科目统计。

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