Início / dba / Perguntas / 105017
Accepted
user4150760
Asked: 2015-06-25 06:16:39 +0800 CST

PostgreSQL: evite SUM sobre os mesmos valores várias vezes

  • 772

Tenho uma tabela de alunos e uma tabela de notas em várias disciplinas. Quero somar a pontuação1 e a pontuação2 de cada aluno em todas as disciplinas e, em seguida, adicionar o bônus para cada aluno.

CREATE TABLE student (
   id serial PRIMARY KEY,
   name text NOT NULL,
   bonus integer NOT NULL,
);

CREATE TABLE score (
  id serial PRIMARY KEY,
  subject text NOT NULL,
  score1 integer NOT NULL,
  score2 integer NOT NULL,
  student_id integer NOT NULL,
  CONSTRAINT s_id FOREIGN KEY (student_id) REFERENCES student (id),
);

A consulta para unir score1 e score2 se parece com isto:

SELECT st.name, sum(sc.score1 + sc.score2) as total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
group by st.name

Se eu adicionar bonusa esta consulta, ou seja sum(sc.score1 + sc.score2 + st.bonus), ela será repetida para cada aluno várias vezes (dependendo de quantas vezes o student_id ocorrer na tabela de pontuação).

Tenho que usar uma subconsulta, ou seja, calcular a soma de score1 e score2 primeiro e depois adicionar isso ao bônus (veja abaixo) ou existe uma maneira melhor?

SELECT sq.name, sum(sq.bonus+sq.total) FROM
( SELECT st.bonus, st.name, sum(sc.score1 + sc.score2) as total
  FROM student st
  LEFT JOIN score sc ON sc.student_id = st.id
  group by st.name
) AS sq
postgresql aggregate

2 respostas

  1. Best Answer

    Você pode usar uma subconsulta, mas não precisa. Apenas não some bonuse adicione na GROUP BYlista.
    Observe que você também deve adicionar o student.id, mesmo em sua consulta original, caso tenha 2 alunos com o mesmo nome.
    Você provavelmente também precisará coalesce()de alunos sem pontuações:

    SELECT st.name, 
       coalesce(sum(sc.score1),0) + coalesce(sum(sc.score2),0) + st.bonus AS total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
GROUP BY st.id, st.name, st.bonus ;

    Nas versões mais recentes do Postgres, você pode usar apenas a chave primária da studenttabela no grupo por:

    SELECT st.name, 
       coalesce(sum(sc.score1),0) + coalesce(sum(sc.score2),0) + st.bonus AS total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
GROUP BY st.id ;

    Se você quiser uma subconsulta, esta é uma maneira:

    SELECT st.name, 
       coalesce(sc.score, 0) + st.bonus AS total
FROM student st
LEFT JOIN 
    ( SELECT student_id, sum(score1) + sum(score2) AS score
      FROM score 
      GROUP BY student_id
    ) AS sc ON sc.student_id = st.id ;
    • 5

  2. Você precisa agrupar por sudjects ou então você obtém a soma multiplicada cada vez que adiciona mais um sudject:

    SELECT st.name, sum(sc.score1 + sc.score2) as total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
group by st.name,sc.subject

    Espero que ajude.

    • 1

relate perguntas