SQL 获取定长连续子序列
有一张表 savior，它的表结构及数据如下：
CREATE TABLE `savior` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`status` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8
id status
------ --------
1 1
2 1
3 0
4 0
5 0
6 1
7 0
8 0
9 0
10 0
11 0
12 1
13 1
14 0
15 0
savior 表有两个字段，id 是主键，设置了自动递增；status 表示状态，它只有 0/1 两种状态。
要求：从 savior 表中获取状态为 0 的 id，并且这些 id 能够组成长度为 3 的连续子序列。
比如，id = 3、4、5 的数据，它们的状态为 0，且它们构成的序列长度正好为 3。满足这个规则的还有（7,8,9）、（8,9,10）、（9,10,11）构成的序列。
最终期望能获取的结果：
subseq
--------
3~5
7~9
8~10
9~11
在历史的文章里有提到过判断连续子序列的方法，就是根据目标字段的排序规则生成序号，目标字段减去它对应的序号得到的的结果相同的数据则说明它们是连续的子序列。
请看演示：
SELECT
id,
rn,
id - rn AS rs
FROM
(SELECT
id,
row_number () over (
ORDER BY id) AS rn
FROM
savior
WHERE STATUS = 0) t ;
id rn rs
------ ------ --------
3 1 2
4 2 2
5 3 2
7 4 3
8 5 3
9 6 3
10 7 3
11 8 3
14 9 5
15 10 5
id 为 3 ~ 5 是一个连续子序列，7 ~ 11 是一个连续子序列，14 ~ 15 是一个连续子序列。
由于我们只要获取长度为 3 的子序列，根据判断连续子序列的规则，反过来说，如果一组数据是连续子序列，那么目标字段和它对应的序号分别加上固定的值，目标字段得到的结果和新序号的差值仍和做加法操作前保持一致。
比如，在 rs = 2 的子序列中，id = 3 和 rn = 1 分别加上 2，得到新的 id = 5 和 rn = 3，5 - 3 仍是 2 。
因此，可以将这个固定值作为定长子序列的长度参照（子序列的长度 = 固定值 + 1）。在这个需求里，这个固定值取值 2 。
WITH cte AS
(SELECT
*,
row_number() over (
ORDER BY id) AS rn
FROM
savior
WHERE STATUS = 0)
SELECT
CONCAT_WS('~', a.id, b.id) AS subseq
FROM
cte a
INNER JOIN cte b
ON a.id + 2 = b.id
AND a.rn + 2 = b.rn
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2020-12-08，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
