对值进行排名以确定最高值 [英] Ranking values to determine highest value

问题描述

我的数据看起来像这样:

I have data that looks as such:

+-----+--------+--------+--------+
| ID  | score1 | score2 | score3 |
+-----+--------+--------+--------+
| 123 |     14 |    561 |    580 |
| 123 |    626 |    771 |    843 |
| 123 |    844 |    147 |    904 |
| 456 |    922 |    677 |    301 |
| 456 |    665 |    578 |    678 |
| 456 |    416 |    631 |    320 |
+-----+--------+--------+--------+

我想做的是创建另一列，该列提供三者中最高的分数.记住，我不是在寻找值，而是在寻找列的名称.因此，最终输出将如下所示:

What I'm trying to do is create another column that provides which score is the highest amongst the three. Remember, I'm not looking for what the value is, I'm looking for the name of the column. Therefore, the final output would look as such:

+-----+--------+--------+--------+------------+
| ID  | score1 | score2 | score3 | high_score |
+-----+--------+--------+--------+------------+
| 123 |     14 |    561 |    580 | score3     |
| 123 |    626 |    771 |    843 | score3     |
| 123 |    844 |    998 |    904 | score2     |
| 456 |    922 |    677 |    301 | score1     |
| 456 |    665 |    578 |    678 | score3     |
| 456 |    416 |    631 |    320 | score1     |
+-----+--------+--------+--------+------------+

我一直在尝试进行涉及RANK()且涉及PARTITION BY的查询，但是它没有提供我想要的信息，因此我被正式卡住了.

I keep trying to do a query involving RANK() involving PARTITION BY but it's not giving me what I'm looking for and I'm officially stuck.

推荐答案

在Redshift中，使用case最容易做到这一点:

In Redshift, this might be most easily done using case:

select t.*,
       (case greatest(score1, score2, score3)
             when score1 then 'score1'
             when score2 then 'score2'
             when score3 then 'score3'
        end) as high_score_name,
       greatest(score1, score2, score3) as high_score
from t;

这篇关于对值进行排名以确定最高值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！