sql - 获取 Hive 中一行具有最大值的列

Question

我有一个场景，我需要从三列中选择行中的最大值，有一个名为 Greatest 的函数，但它在我的 Hive 0.13 版本中不起作用。

请建议更好的方法来完成它。

示例表:

+---------+------+------+------+
| Col1    | Col2 | Col3 | Col4 |
+---------+------+------+------+
| Group A | 1    | 2    | 3    |
+---------+------+------+------+
| Group B | 4    | 5    | 1    |
+---------+------+------+------+
| Group C | 4    | 2    | 1    |
+---------+------+------+------+

预期结果:

+---------+------------+------------+
| Col1    | output_max | max_column |
+---------+------------+------------+
| Group A | 3          | Col4       |
+---------+------------+------------+
| Group B | 5          | col3       |
+---------+------------+------------+
| Group C | 4          | col2       |
+---------+------------+------------+

Answer 1

select  col1
       ,tuple.col1                as output_max
       ,concat('Col',tuple.col2)  as max_column

from   (select  Col1
               ,sort_array(array(struct(Col2,2),struct(Col3,3),struct(Col4,4)))[2] as tuple
        from    t
        ) t
;

---

sort_array(Array)
Sorts the input array in ascending order according to the natural ordering of the array elements and returns it (as of version 0.9.0). https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

---

hive> select  col1
    >        ,tuple.col1                as output_max
    >        ,concat('Col',tuple.col2)  as max_column
    >     
    > from   (select  Col1
    >                ,sort_array(array(struct(Col2,2),struct(Col3,3),struct(Col4,4)))[2] as tuple
    >         from    t
    >         ) t
    > ;
OK
Group A 3   Col4
Group B 5   Col3
Group C 4   Col2