SQL - 연속된 ID로 행 압축

SQL - 연속된 ID로 행 압축

저는 많은 데이터가 있는 mariadb 10.5 InnoDB 테이블을 가지고 있습니다.각 테이블 행에는 다음과 같은 ID가 있습니다.

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

보시다시피, ID가 모두 외부 데이터 소스에서 비롯되고 데이터베이스가 부분적으로 불완전하기 때문에 연속적인 것은 아닙니다.하지만, 그들 중 많은 것들이 연속적입니다.

수천 개의 ID가 있기 때문에 행당 하나의 ID를 가진 이러한 유형의 표현은 어떤 ID가 존재하고 어떤 ID가 존재하지 않는지에 대한 간략한 개요를 얻기에 적합하지 않습니다.

대신 다음과 같은 연속적인 ID 범위를 나열하는 SQL 요청을 하고 싶습니다.

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

또는 다음을 수행합니다.

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

SQL을 사용하여 이를 달성하려면 어떻게 해야 합니까?

여러 솔루션이 있을 수 있으며, 그 중 하나는 WINDOW 함수 DENSE_RANK()를 사용하는 것입니다.

샘플 표:

예를 들어, 다음과 같은 공백이 있는 테이블 t1(idint)이 있습니다.

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 |
+-------------------+-------------------+

언급URL : https://stackoverflow.com/questions/73864321/sql-condense-rows-with-consecutive-ids