mariadb - SQL - 压缩具有连续 ID 的行

Question

我有一个包含大量数据的 mariadb 10.5 InnoDB 表。每个表行都有一个像这样的 id:

ext_id    <other data>
--
1
2
4
5
6
8
9

如您所见，ID 并非都是连续的，因为它们来自外部数据源并且数据库部分不完整。然而，其中许多是连续的。

因为有成千上万的 id，这种每行一个 id 的表示不适合快速了解哪些 id 存在，哪些不存在。

我想执行一个 SQL 请求，而不是像这样列出连续 ID 的范围:

id
--
1-2
4-6
8-9

或者，或者:

id_start    id_end
--------    ------
1           2
4           6
8           9

如何使用 SQL 实现此目的？

Answer 1

可能有几种解决方案，其中之一是使用 WINDOW 函数 DENSE_RANK() .

示例表:

例如，我们有一个表 t1(id int) 有一些间隙:

CREATE TABLE t1 (id int);
INSERT INTO t1 SELECT seq FROM seq_1_to_4;
INSERT INTO t1 SELECT seq FROM seq_7_to_13;
INSERT INTO t1 SELECT seq FROM seq_22_to_25;

DENSE_RANK() 作为组标识符

通过从 ID 中减去 DENSE_RANK 返回的值，我们得到一个组标识符。

SELECT id, id - dense_rank() over (order by id) AS result FROM t1;
+------+--------+
| id   | result |
+------+--------+
|    1 |      0 |
|    2 |      0 |
|    3 |      0 |
|    4 |      0 |
|    7 |      2 |
|    8 |      2 |
|    9 |      2 |
|   10 |      2 |
|   11 |      2 |
|   12 |      2 |
|   13 |      2 |
|   22 |     10 |
|   23 |     10 |
|   24 |     10 |
|   25 |     10 |
+------+--------+

子查询:

现在使用以前的查询作为子查询并按结果分组。

SELECT min(sequences.id), max(sequences.id) from (select id, id - dense_rank() over (order by id) as result from t1) as sequences group by result;
+-------------------+-------------------+
| min(sequences.id) | max(sequences.id) |
+-------------------+-------------------+
|                 1 |                 4 |
|                 7 |                13 |
|                22 |                25 |
+-------------------+-------------------+