基于Hive SQL查找各id分组下最长连续rowid序列的起止点

示例数据集 #

其中rowid按id自增，需查找各id分组内最长连续rowid序列的起止rowid。

预期输出 #

更新：起止点查找方法 #

已找到查找起止点的方法，SQL代码如下：

-- 查找结束点
select * from (select * from tbl where status = 1) t1 
left join (select * from tbl where status = 1) t2 on t1.id = t2.id and t1.rowid = t2.rowid + 1
where t2.rowid is null
;
-- 查找起始点
select * from (select * from tbl where status = 1) t1 
left join (select * from tbl where status = 1) t2 on t1.id = t2.id and t1.rowid = t2.rowid - 1
where t2.rowid is null
;

内容的提问来源于stack exchange，提问作者l0o0