SQL QUERY对表进行排序后合并连续的相同值

Question

假设我们有这个表

id |begin | end | location
1  |    5 |  10 | MALL A
2  |    1 |   3 | MALL B
3  |   13 |  17 | MALL A
4  |   21 |  25 | MALL C
5  |   36 |  38 | MALL D
6  |   31 |  33 | MALL D
7  |   26 |  29 | MALL F
8  |   40 |  45 | MALL D

然后我们按照asc开始列对表格进行排序。因此，我们有这个表

id |begin | end | location
2  |    1 |   3 | MALL B
1  |    5 |  10 | MALL A
3  |   13 |  17 | MALL A
4  |   21 |  25 | MALL C
7  |   26 |  29 | MALL F
6  |   31 |  33 | MALL D
5  |   36 |  38 | MALL D
8  |   40 |  45 | MALL D

我想要一张这样的桌子。(连续位置相同的行将被合并(

begin | end | location
1 |   3 | MALL B
5 |  17 | MALL A
21 |  25 | MALL C
26 |  29 | MALL F
31 |  45 | MALL D

我该如何做到这一点？

我想我可以使用RANK((，然后根据秩值对其进行分组。但我没能来。我想这是因为桌子还没有先排序。

如果你想在SQL上制作表格，我会提供这些SQL语法来创建它

CREATE TABLE `t` (
`id` int NOT NULL,
`begin` int DEFAULT NULL,
`end` int DEFAULT NULL,
`location` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO t (id, begin, end, location) VALUES (1, 5,10, 'A');
INSERT INTO t (id, begin, end, location) VALUES (2, 1,3, 'B');
INSERT INTO t (id, begin, end, location) VALUES (3, 13,17, 'A');
INSERT INTO t (id, begin, end, location) VALUES (4, 21,25, 'C');
INSERT INTO t (id, begin, end, location) VALUES (5, 36,38, 'D');
INSERT INTO t (id, begin, end, location) VALUES (6, 31,33, 'D');
INSERT INTO t (id, begin, end, location) VALUES (7, 26,29, 'F');
INSERT INTO t (id, begin, end, location) VALUES (8, 40,45, 'D');

Answer 1

这是一种间隙和孤岛问题。在这种情况下，可以使用lag()来确定行应该在不同组中的位置。然后使用累积和来定义组和聚合：

select location, min(begin), max(end)
from (select t.*,
sum(case when prev_location = location then 0 else 1 end) over (order by begin) as grp
from (select t.*,
lag(location) over (order by begin) as prev_location
from t
) t
) t
group by grp, location;

事实上，因为您不关心末尾和以下开头之间的间隙，所以可以使用行数的简单差异：

select location, min(begin), max(end)
from (select t.*,
row_number() over (order by begin) as seqnum,
row_number() over (partition by location order by begin) as seqnum_2
from t
) t
group by location, (seqnum - seqnum_2);

这有点难以解释，但如果你查看子查询的结果，你会发现当位置相同时，两个row_number()之间的差异是如何恒定的。