How to get min and max from 7 columns in Hive Hue excluding zeros

Question

I have a table which has 9 columns. Below is the structure of it

I need the min and max of these columns for a row excluding zeros. Below is the required table structure

If you see the columns min and max, min is minimum of 7 cols (col1 to col7) in a particular row excluding zero and max is the maximum of the 7 cols (col1 to col7) for that row.

Please help me to accomplish this in hive (hue).

Answer 1

You can use least and greatest to get the min and max, and use when to remove 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