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

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

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