Where condition on collect_set in hive

user_10078766 0
Viewed 332

Is there a way where I can filter out non consecutive numbers in the collect_set output in hive? See the example below, the highlighted row does not have numbers consecutive and I want such outputs only enter image description here

This number may be [5,6,9] or [4,5,7,8,] I would want to filter all such rows from a hive query where the values within the array are non-consecutive.

hiveql hive arrays sql
1 Answers

Explode array, compare each element with next one to find non-consecutive, count such occurrences and filter.

Demo:

set hive.on.master=true;
with your_data as (
    select  array (5,6,9) as myarray
    union all
    select  array (1,2,3,4,5,6) as myarray
    union all
    select  array (0) as myarray
    union all
    select  array (4,5,7,8) as myarray
    union all
    select  array (0,1) as myarray
    union all
    select  array (0,1,3) as myarray
)

select myarray--, sum(non_consecutive)
from
(
select myarray,
       case when lead(value) over(partition by myarray order by pos )-value != 1 then 1 else 0 end non_consecutive
  from your_data d
       lateral view posexplode(myarray) e as pos, value
)s
group by myarray    
having sum(non_consecutive)>0
;

Result:

myarray
[0,1,3]
[4,5,7,8]
[5,6,9]

If you have initially exploded data, then just apply the same logic before collect_set: calculate lead value (you should know what to put in order by), compare with current value, then aggregate sets and at the same time aggregate non_consecutive and filter.

user_27003440
Related