SQL之定位连续区间的起始位置和结束位置--HQL面试题45【拼多多】

目录

0 题目

1 分析

2 小结

---

0 题目

表:Logs
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| log_id        | int     |
+---------------+---------+
id 是上表的主键。
上表的每一行包含日志表中的一个 ID。
后来一些 ID 从 Logs 表中删除。编写一个 SQL 查询得到 Logs 表中的连续区间的开始数字和结束数字。

将查询表按照 start_id 排序。

查询结果格式如下面的例子：

Logs 表：
+------------+
| log_id     |
+------------+
| 1          |
| 2          |
| 3          |
| 7          |
| 8          |
| 10         |
+------------+

结果表：
+------------+--------------+
| start_id   | end_id       |
+------------+--------------+
| 1          | 3            |
| 7          | 8            |
| 10         | 10           |
+------------+--------------+
结果表应包含 Logs 表中的所有区间。
从 1 到 3 在表中。
从 4 到 6 不在表中。
从 7 到 8 在表中。
9 不在表中。
10 在表中。

1 分析

（1）建表，插入数据

create table logs as 
select 1 as log_id
union all
select 2 as log_id
union all
select 3 as log_id
union all
select 7 as log_id
union all
select 8 as log_id
union all
select 10 as log_id


hive> select * from logs;
OK
1
2
3
7
8
10
Time taken: 0.055 seconds, Fetched: 6 row(s)

（2） 分析

采用重分组的思路将连续的数据分到一个组里面，然后取分组里的最大最小值即可

SQL如下：

select min(log_id) as start_id
      ,max(log_id) as end_id
from(
     select log_id
            ,sum(if(diff>1,1,0)) over(order by log_id) as flg
     from(
           select log_id
                  ,log_id - lag(log_id,1,log_id) over(order by log_id) as diff
           from logs
     ) t

) m
group by flg

计算结果如下：

--------------------------------------------------------------------------------
OK
1	3
7	8
10	10
Time taken: 20.621 seconds, Fetched: 3 row(s)

具体中间结果如下：

第一步：获取lag值，构建差值

select log_id
      ,log_id - lag(log_id,1,log_id) over(order by log_id) as diff
from logs



OK
1     0
2     1
3     1
7     4
8     1
10     2

第二步：根据计算的差值，构建分组条件

select log_id
      ,sum(if(diff>1,1,0)) over(order by log_id) as flg
from(
     select log_id
           ,log_id - lag(log_id,1,log_id) over(order by log_id) as diff
     from logs
) t

结果如下：

--------------------------------------------------------------------------------
OK
1	0
2	0
3	0
7	1
8	1
10	2
Time taken: 8.975 seconds, Fetched: 6 row(s)

2 小结

本题分析了一种定位连续区间起始和结束的方法，利用重分组方法能够快速解决此问题。关于重分组方法请参考前面几篇文章。

欢迎关注石榴姐公众号"我的SQL呀"，关注我不迷路