sql - Hive - 跨列/多行的最大值

Question

我的数据如下所示：

ip, category, a_score, b_score, c_score
1.2.3.4, X, 0, 5, 0
1.2.3.4, X, 3, 0, NULL
1.2.3.4, Y, 1, NULL, NULL
1.2.3.4, Y, 0, 2, 0
1.2.3.4, Z, 1, 0, 9
1.2.3.4, Z, 0, 2, 0

每个 IP 最多可以有 8 个类别和每个类别最多 3 个有效分数（a_score、b_score、c_score）。

对于每个 IP，我需要在该类别的所有分数中找到每个类别的最高分数。

所以在这种情况下，我会得到

1.2.3.4, X, 5
1.2.3.4, Y, 2
1.2.3.4, Z, 9

即，类别 X 的 a/b/c 分数的最大值是第一行的 c_score；Y 类的 a/b/c 分数的最大值是第二行的 b_score；类别 Z 的 a/b/c 分数的最大值是第一行的 c_score。

我尝试了一些东西

select ip, category, greatest(max(a_score), max(b_score), max(c_score)) from mytable where ip = '1.2.3.4' group by ip, category;

但这仅返回 NULL。

我该怎么办？TIA。

标签： sqlhive

Answer 1

coalesce与0（零）一起使用：

select ip, category, 
     max(greatest(coalesce(a_score,0), coalesce(b_score,0), coalesce(c_score,0))) max_score
  from mytable 
 where ip = '1.2.3.4' 
group by ip, category;

Rextester 演示