如何对ARRAY_AGG中新增STRUCT的student.credits字段求和？

要实现按学生姓名分组求和学分，再将结果纳入ARRAY_AGG聚合，你需要先对学生维度做分组求和，再封装STRUCT后完成最终聚合，同时保留原有professor和class的聚合逻辑。

示例SQL（以BigQuery语法为例） #

WITH raw_data AS (
  -- 模拟你的原始表结构与数据
  SELECT
    'school_1' AS school_id,
    STRUCT('Prof. Smith' AS name, 'Math' AS subject) AS professor,
    STRUCT('Math 101' AS name, 'Fall 2024' AS semester) AS class,
    STRUCT('John' AS name, 4 AS credits) AS student
  UNION ALL
  SELECT
    'school_1' AS school_id,
    STRUCT('Prof. Smith' AS name, 'Math' AS subject) AS professor,
    STRUCT('Math 101' AS name, 'Fall 2024' AS semester) AS class,
    STRUCT('John' AS name, 4 AS credits) AS student
  UNION ALL
  SELECT
    'school_1' AS school_id,
    STRUCT('Prof. Smith' AS name, 'Math' AS subject) AS professor,
    STRUCT('Math 101' AS name, 'Fall 2024' AS semester) AS class,
    STRUCT('Mary' AS name, 5 AS credits) AS student
  UNION ALL
  SELECT
    'school_1' AS school_id,
    STRUCT('Prof. Smith' AS name, 'Math' AS subject) AS professor,
    STRUCT('Math 101' AS name, 'Fall 2024' AS semester) AS class,
    STRUCT('Mary' AS name, 6 AS credits) AS student
  UNION ALL
  SELECT
    'school_1' AS school_id,
    STRUCT('Prof. Smith' AS name, 'Math' AS subject) AS professor,
    STRUCT('Math 101' AS name, 'Fall 2024' AS semester) AS class,
    STRUCT('Mary' AS name, 2 AS credits) AS student
)

SELECT
  school_id,
  ARRAY_AGG(DISTINCT professor) AS professors,
  ARRAY_AGG(DISTINCT class) AS classes,
  ARRAY_AGG(STRUCT(student_name AS name, total_credits AS credits)) AS students
FROM (
  -- 核心：先按学生姓名分组计算总学分
  SELECT
    school_id,
    professor,
    class,
    student.name AS student_name,
    SUM(student.credits) AS total_credits
  FROM raw_data
  GROUP BY school_id, professor, class, student.name
)
GROUP BY school_id;

逻辑拆解 #

1. 子查询层：通过GROUP BY school_id, professor, class, student.name将同一班级下的学生记录合并，用SUM(student.credits)计算每个学生的总学分，这一步直接得到John(8)、Mary(12)的结果。
2. 外层聚合：将子查询中得到的学生姓名和总学分重新封装为student STRUCT，再对professor、class和新的student结构执行ARRAY_AGG，完全匹配你原有聚合逻辑的同时，新增了处理后的学生数组。

优化提示：如果同一school_id下的professor和class是唯一的，无需用ARRAY_AGG，可以替换为ANY_VALUE(professor)和ANY_VALUE(class)，提升查询效率。

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