PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Calculate average score

I have a table like this:

ID score1 score2 score3 score4 score5 score6 score7
1 6 6 7 7.5 6.5 7 5
2 9 8 7 8 7 8.5 7.5
3 7 7 7 7 7 7 7

I want to calculate the average score of each row by removing one of the highest and one of the lowest scores and calculating the average of the remaining scores.

For example,

ID 1: avg=(6+6+7+6.5+7)/5=6.5

ID 2: avg=(8+7+8+8.5+7.5)/5=7.8

ID 3: avg=(7+7+7+7+7)/5=7

The following is the method I use. It is very bloated and the execution time is too long. Especially if there are more score columns(score8, score9…), more UNION ALL will be piled up.

SELECT
  id,
  (SUM(scores) - MAX(scores) - MIN(scores)) / 5 AS adjusted_average
FROM
  (SELECT id, score1 AS scores FROM scores
   UNION ALL
   SELECT id, score2 AS scores FROM scores
   UNION ALL
   SELECT id, score3 AS scores FROM scores
   UNION ALL
   SELECT id, score4 AS scores FROM scores
   UNION ALL
   SELECT id, score5 AS scores FROM scores
   UNION ALL
   SELECT id, score6 AS scores FROM scores
   UNION ALL
   SELECT id, score7 AS scores FROM scores) subquery
GROUP BY id
ORDER BY id

So I would like to ask if there is a neat way to achieve my needs? (You can use PostgreSQL for demo, thank you!)

Tags:

3

Answers


  1. 0

    First you should normalize data, so any addition of new scores does not have (much) impact on your query:

    CREATE TABLE myNEWtable AS
SELECT ID, 1 as scoreId, score1 as score FROM mytable
UNION ALL
SELECT ID, 2 as scoreId, score2 as score FROM mytable
UNION ALL
SELECT ID, 3 as scoreId, score3 as score FROM mytable
UNION ALL
SELECT ID, 4 as scoreId, score4 as score FROM mytable
UNION ALL
SELECT ID, 5 as scoreId, score5 as score FROM mytable
UNION ALL
SELECT ID, 6 as scoreId, score6 as score FROM mytable
UNION ALL
SELECT ID, 7 as scoreId, score7 as score FROM mytable
;

    Then you can do, and the query needs no change when you add a new score!:

    SELECT 
  ID,
  round((sum(score)-max(score)-min(score)) / (count(*)-2),3) as adjusted_average
FROM myNEWtable
GROUP BY ID
ORDER BY ID

    output:

    id adjusted_average
    1 6.500
    2 7.800
    3 7.000

    see: DBFIDDLE

    Your original score list can be retrieved using this SQL, which is easily adapted when a new score is added.

    SELECT
  ID,
  SUM(case when scoreid=1 then score else 0 end) AS score1,
  SUM(case when scoreid=2 then score else 0 end) AS score2,
  SUM(case when scoreid=3 then score else 0 end) AS score3,
  SUM(case when scoreid=4 then score else 0 end) AS score4,
  SUM(case when scoreid=5 then score else 0 end) AS score5,
  SUM(case when scoreid=6 then score else 0 end) AS score6,
  SUM(case when scoreid=7 then score else 0 end) AS score7
FROM myNEWtable
GROUP BY ID
ORDER BY ID

    output:

    id score1 score2 score3 score4 score5 score6 score7
    1 6 6 7 7.5 6.5 7 5
    2 9 8 7 8 7 8.5 7.5
    3 7 7 7 7 7 7 7
    Login or Signup to reply.
  2. 0 
    select s.id, (select (sum(x)- max(x)-min(x))/5 FROM (select 
unnest(array[score1,score2,score3,score4,score5,score6,score7])as x ))
from scores s;
    Login or Signup to reply.
  3. 0

    You can use GREATEST() and LEAST(), it has simple query and faster execution:

    SELECT id, 
       (score1 + score2 + score3 + score4 + score5 + score6 + score7 - GREATEST(score1, score2, score3, score4, score5, score6, score7) - LEAST(score1, score2, score3, score4, score5, score6, score7)) / 5 AS average_score
FROM scores
ORDER BY id

    dbfiddle:https://dbfiddle.uk/G80L1b_d

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search