提问人：l0o0 提问时间：6/13/2023 最后编辑：l0o0 更新时间：6/18/2023 访问量：33
Hive SQL：查找最长的连续序列起点和终点
Hive SQL : Find the longest continuous sequence start and end point
问：
我在下面有一个示例数据集
| 编号 | 罗维德 | 地位 |
|---|---|---|
| 1 | 1 | 2 |
| 1 | 2 | 1 |
| 1 | 3 | 1 |
| 1 | 4 | 1 |
| 1 | 5 | 2 |
| 1 | 6 | 1 |
| 1 | 7 | 1 |
| 1 | 8 | 2 |
| 2 | 1 | 1 |
| 2 | 2 | 1 |
| 2 | 3 | 1 |
| 2 | 4 | 1 |
| 3 | 1 | 1 |
| 3 | 2 | 2 |
| 3 | 3 | 1 |
| 3 | 4 | 1 |
| 3 | 5 | 1 |
rowid是 ID 自动增加的。我需要找到按 id 分区的 rowid 的最长连续序列，然后找到此序列的开始和结束 rowid。
此示例数据的例外输出
| 编号 | start_id | end_id |
|---|---|---|
| 1 | 2 | 4 |
| 2 | 1 | 4 |
| 3 | 3 | 5 |
更新： 我找到了如何找到第一点和最后一点。 找到终点
-- Find the end point
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
;
-- Find the start point
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
;
答：
0赞
Junhua.xie
6/13/2023
#1
对于这个问题，用 sql 获得结果很复杂。
我有一些想法与你分享。
第 1 步，获取最大连续序列计数。
select t1.id, max(counter) as max_counter from(
select t.id,gap, count(1) as counter from (
select id, rowid,rowid - row_number() over(partition by id order by rowid) as gap from table1
where status = 1)t group by id, gap)t1 group BY t1.id
结果如下
id, max_counter
1,3
2,4
3,3
下一步需要使用结果来连接这个sql的中间结果。 希望有人可以补充这个 answser。
评论
0赞
l0o0
6/18/2023
谢谢你的回答。我学会了如何获得序列的第一点和最后一点。该帖子已更新。
评论