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