sqlpostgresqlpieclouddb

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!)


Solution

  • You can use GREATEST() and LEAST(), it's simple and executes faster:
    demo at db<>fiddle

    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;
    
    id average_score
    1 6.5000000000000000
    2 7.8000000000000000
    3 7.0000000000000000

    An example plan in a test on 50k random rows:

    QUERY PLAN
    Sort (cost=3396.18..3455.78 rows=23839 width=36) (actual time=251.658..267.348 rows=50000 loops=1)
    Output: 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'::numeric))
    Sort Key: scores.id
    Sort Method: external merge Disk: 1328kB
    -> Seq Scan on public.scores (cost=0.00..1662.96 rows=23839 width=36) (actual time=0.020..237.222 rows=50000 loops=1)
    Output: 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'::numeric)
    Planning Time: 0.077 ms
    Execution Time: 269.442 ms