sql - 查询每个学生的平均分

Question

我有 2 个表:

学生:

   id       name     
   2        ABC      
   13       DEF      
   22       GHI      

学期:

   id       student_id     sem     marks                                    
   1        2              1       {"math":3,   "physic":4,   "chemis":5}   
   2        2              2       {"math":2.5, "physic":4.5, "chemis":5}   
   3        2              3       {"math":3,   "physic":3.5, "chemis":4}   
   5        13             1       {"math":3,   "physic":4,   "chemis":5}   
   6        13             2       {"math":3,   "physic":4,   "chemis":5}   

例如，student_id=2:

平均分 = ((3+4+5)/3)+(2.5+4.5+5)/3+(3+3.5+4)/3)/3 = 3.83

有什么方法可以按学号分组查询平均分吗？

   student_id      average     number_of_sems     
   2               3.83        3                  
   13              xxx         2         

我正在尝试按主题计数:

SELECT
  t1.student_id,
  t1.count,
  (SELECT sum(xx.count)
   FROM
     (SELECT (marks:: JSON ->> 'math') :: DOUBLE PRECISION AS count
      FROM "Semester"
      WHERE student_id= t1.student_id) AS xx)
FROM
  (
    SELECT
      student_id,
      count(1)
    FROM "Semester"
    GROUP BY student_id
  ) AS t1;

但仍然不知道如何继续。这可能是不好的解决方案。

Answer 1

这包括没有分数的学生:

SELECT st.id, avg(m.value)
FROM student st
   LEFT JOIN semester se
      ON st.id = se.student_id
   LEFT JOIN LATERAL (SELECT value::numeric
                      FROM jsonb_each_text(se.marks)
                     ) m
      ON TRUE
GROUP BY st.id;

┌────┬────────────────────┐
│ id │        avg         │
├────┼────────────────────┤
│  2 │ 3.8333333333333333 │
│ 13 │ 4.0000000000000000 │
│ 22 │                    │
└────┴────────────────────┘
(3 rows)