如何从Hive Hue中排除零的7列中获取最小值和最大值

Question

我有一个有9列的表格。下面是它的结构

​

​

我需要一行中不包括零的列的最小和最大值。下面是所需的表结构

​

​

如果您看到列min和max，则min是不包括零的特定行中7个cols (col1到col7)的最小值，max是该行7个cols (col1到col7)中的最大值。

请帮助我在hive (色调)中完成此操作。

Answer 1

可以使用least和greatest获取最小和最大值，使用when删除0。

select *,
    least(
        case when col1 != 0 then col1 else 99999999 end,
        case when col2 != 0 then col2 else 99999999 end,
        case when col3 != 0 then col3 else 99999999 end,
        case when col4 != 0 then col4 else 99999999 end,
        case when col5 != 0 then col5 else 99999999 end,
        case when col6 != 0 then col6 else 99999999 end,
        case when col7 != 0 then col7 else 99999999 end,
    ) as `Min`
    greatest(
        case when col1 != 0 then col1 else -99999999 end,
        case when col2 != 0 then col2 else -99999999 end,
        case when col3 != 0 then col3 else -99999999 end,
        case when col4 != 0 then col4 else -99999999 end,
        case when col5 != 0 then col5 else -99999999 end,
        case when col6 != 0 then col6 else -99999999 end,
        case when col7 != 0 then col7 else -99999999 end
    ) as `Max`
from mytable

Answer 2

您可以使用显式case表达式：

select (case when col1 <> 0 and col1 >= col2 and col1 >= col3 and col1 > =col4 and col1 >= col5 and col1 >= col6 and col1 >= col7
             then col1
             . . .
        end)

这需要大量的打字。这可能表明您的数据存储不正确--您应该为每列单独的行。您可以通过扩展数据并重新编目来模拟此过程：

select name, state, col1, col2, col3, col4, col5, col6, col7,
       min(case when col1 <> 0 then col1 end) as min_value,
       max(case when col1 <> 0 then col1 end) as max_value,
from ((select t.*, col1 as col from t) union all
      (select t.*, col2 as col from t) union all
      (select t.*, col3 as col from t) union all
      (select t.*, col4 as col from t) union all
      (select t.*, col5 as col from t) union all
      (select t.*, col6 as col from t) union all
      (select t.*, col7 as col from t)
     ) x
group by name, state, col1, col2, col3, col4, col5, col6, col7;