Search code examples
hivehiveql

Efficiently take min of a column with other column as the key

I recently transitioned over from using Presto to Hive. I have the following scenario. Column A, B, C. I want to aggregate on A and find the value of B for which the value of C is minimized. In presto you can something like this as SELECT A, min_by(B, C) from <TABLE> GROUP BY A Now I want to do the same thing in Hive. But unfortunately I couldn't find a UDF similar to this anywhere in the documentation. Now I know I can do the following

SELECT A, COALESCE(B, 0)
from <TABLE> as primary
JOIN (
SELECT A, MIN(C) as C FROM <TABLE> GROUP BY A 
) secondary
ON primary.A = secondary.A AND primary.C = secondary.C\
GROUP BY A

I have 2 problems with this solution

  1. It's not concise at all.
  2. It's not efficient either. I am doing an extra subquery resulting and an extra aggregation and an extra JOIN. It would be nice to have a first class aggregation support for such a function.

Is there a way to achieve what I am trying to do without writing your custom UDF ?

Solution

  • Join works slower than analytic functions, try this approach without join, and table will be scanned only once:

    select s.*
from
(
SELECT A, COALESCE(B, 0) as B, C,
      min(C) over (partition by A) as min_C
from <TABLE> as primary
)s
where s.C=s.min_C;

    If you need min(C) to be calculated by more group columns, add them to the partition BY clause.