hive求连续段的最后一个数及每段的个数

需求

数据准备

create table temp_id_list_0209
(
    id int
) row format delimited
    fields terminated by '\t'
    stored as orc
    tblproperties ("orc.compress" = "snappy");

insert into temp_id_list_0209
values (1),
       (2),
       (4),
       (5),
       (6),
       (9),
       (10),
       (11),
       (12)
;
select * from temp_id_list_0209;

实现思路

第一步

select id,
       lag(id, 1, id) over (order by id)      df,
       id - lag(id, 1, id) over (order by id) diff
from temp_id_list_0209;

第二步

select id, sum(case when diff > 1 then 1 else 0 end) over (order by id) as grep
from (select id,
             id - lag(id, 1, id) over (order by id) as diff
      from temp_id_list_0209) i;

第三步

select grp, max(id) last_id, count(id) cnt
from (select id, sum(if(diff > 1, 1, 0)) over (order by id) as grp
      from (select id, id - lag(id, 1, id) over (order by id) as diff
            from temp_id_list_0209) m) n
group by grp;