I want to group by a column and then select random rows from another column. In Presto, there's arbitrary.
E.g. my query is:
SELECT a, arbitrary(b)
FROM foo
GROUP BY a
How do I do this in Hive?
Edit:
By "random", I meant "arbitrary". It could just be the first row every time.
1 Answer
You can use the below logic to get your required result in Hive. Provide a row_number to rand(b) and choose any row_number you want. Every time it will return a random value from column b.
select a, b
from (
select a, b,row_number() over( partition by a order by rand(b) asc) rn from foo
)a
where rn=1
group by a, b;
