Capturing consecutive year ranges with break in hive

user_13644685 0
Viewed 162

I am trying to write a query in hive to return a data with the year range if they are consecutive years along with the gap year, if there are gaps between the years.

I am trying to get my head around it, but can't seem to find the logic to achieve the results. How does hive logic work for this. Please help.

Input

group_no            year 
1111                2003
1111                2004
1111                2005
1111                2008
1111                2010
1111                2011
1111                2012
2222                2015
3333                2014
3333                2015
3333                2017
3333                2019
4444                2010
4444                2012

Output:

group_no year
1111    [2003-2005,2008,2010-2012]
2222    [2015]
3333    [2014-2015,2017,2019]
4444    [2010,2012]
hiveql gaps-and-islands hive window-functions sql
2 Answers

This is a gaps and islands problem, where you want to group together rows having the same group_no and whose years are consecutive.

Here is an approach using window functions: the idea is to use the difference between row_number() and year to build the groups. You can then aggregate once for each group of adjacent record, and finally aggregate by group_no.

select 
    group_no, 
    collect_list(
        case when min_year <> max_year 
            then concat(min_year, '-', max_year)
            else min_year
        end
    ) year
from (
    select group_no, min(year) min_year, max(year) max_year
    from (
        select  t.*, row_number() over(partition by group_no order by year) rn
        from mytable t
    ) t
    group by group_no, year - rn
) t
group by group_no

I am unsure whether hive supports order by in collect_list() as an aggregate function - it seems like it does when used as a window function though, so this might be better:

select distinct 
    group_no, 
    collect_list(
        case when min_year <> max_year 
            then concat(min_year, '-', max_year)
            else min_year
        end
    ) over(
        partition by group_no 
        order by min_year
        rows between unbounded preceding and unbounded following
    ) year
from (
    select group_no, min(year) min_year, max(year) max_year
    from (
        select  t.*, row_number() over(partition by group_no order by year) rn
        from mytable t
    ) t
    group by group_no, year - rn
) t
user_106767160

New range starts when (year - prev_year) > 1 or (prev_year is NULL), you can take current year as first year for new range. Assign first_year to all rows then calculate last_year for each group (group_no, first_year).

    with my_data as(
    select stack(14,
    1111, 2003,
    1111, 2004,
    1111, 2005,
    1111, 2008,
    1111, 2010,
    1111, 2011,
    1111, 2012,
    2222, 2015,
    3333, 2014,
    3333, 2015,
    3333, 2017,
    3333, 2019,
    4444, 2010,
    4444, 2012  
    ) as (group_no, year)
    )   

select group_no, array_sort(collect_list(case when first_year=last_year then first_year else concat(first_year,'-',last_year) end)) as year
from
(--calculate last_year
select s.group_no, s.first_year, max(year) last_year      
from
(
select group_no, year, 
       --New range starts when (year - prev_year) > 1 or (prev_year is NULL)
       --Calculate first_year for every row
       max(case when (year - prev_year) = 1 then NULL else year end) over(partition by group_no order by year rows between unbounded preceding and current row ) first_year
  from
(
select d.*,
       lag(year) over(partition by group_no order by year) prev_year
  from my_data d
)s  
)s
group by s.group_no, s.first_year
)s
group by group_no 
order by group_no

Result:

group_no  year
1111  ["2003-2005","2008","2010-2012"]
2222  ["2015"]
3333  ["2014-2015","2017","2019"]
4444  ["2010","2012"]
user_27003440
Related