列出连续记录范围的有效方法

jgovgodb 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(266)

我有一张这样的table：

CREATE TABLE `cn` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `type` int(3) unsigned NOT NULL,
    `number` int(10) NOT NULL,
    `desc` varchar(64) NOT NULL,
    `datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB
``` `number` 通常是但不一定是唯一的。
表的大部分由具有连续属性的行组成 `number` 条目。
例如
101010、101011、101012等。
我一直在试图找到一种有效的方法来列出连续数字的范围，这样我就可以很容易地找出数字“丢失”的地方。我想做的是列出开始编号、结束编号和连续行的数量。因为可以有重复的，我用 `SELECT DISTINCT(number)` 以避免重复。
我运气不太好——这类问题大多涉及日期，很难一概而论。一个查询是永远执行的，所以这是不允许的。这个答案有点接近，但不完全正确。它使用一个 `CROSS JOIN` ，这听起来像是一个灾难配方当你有数百万的记录。
最好的办法是什么？有些答案使用连接，我对性能方面持怀疑态度。现在只有50000行，但几天内将有数百万条记录，因此每一盎司的性能都很重要。
我想到的最后一个问题是： `SELECT DISTINCT(number) FROM cn WHERE type = 1 GROUP BY [consecutive...] ORDER BY number ASC`

sql mysql window-functions mariadb gaps-and-islands

来源：https://stackoverflow.com/questions/62263166/efficient-way-to-list-ranges-of-consecutive-records

1条答案

按热度按时间

0dxa2lsx1#

这是一个缺口和孤岛问题。你可以用 row_number() 以及 number 界定群体；通过差异的变化来识别差距：

select type, min(number) first_number, max(number) last_number, count(*) no_records
from (
    select cn.*, row_number() over(order by number) rn
    from cn
    where type = 1
) c
group by type, number - rn

注意：mysql 8.0和mariadb 10.3以后的版本都提供窗口函数。
在早期版本中，可以模拟 row_number() 使用会话变量：

select type, min(number) first_number, max(number) last_number, count(*) no_records
from (
    select c.*, @rn := @rn + 1 rn
    from (select * from cn where type = 1 order by number) c
    cross join (select @rn := 0) r
) c
group by number - rn

赞(0）回复(0）举报 2021-07-26

我来回答

列出连续记录范围的有效方法

1条答案

相关问题

热门标签

最新问答