两列上的间隙和孤岛-如果列a连续，列b相同

umuewwlo 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(496)

我有一张table如下：

CREATE TABLE `table` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `cc` int(3) unsigned NOT NULL,
    `number` int(10) NOT NULL,
    `name` varchar(64) NOT NULL,
    `datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB

dbms是Debian9.1上的Mariadb10.1.26。我试着让它列出连续数字的范围。通过以下查询，我可以完成以下任务：

SELECT min(number) first_number, max(number) last_number, count(*) AS no_records FROM (
    SELECT c.*, @rn := @rn + 1 rn
    from (SELECT number FROM table WHERE cc = 1 GROUP BY number ORDER BY number) AS c
    CROSS JOIN (SELECT @rn := 0) r
) c
GROUP BY number - rn ORDER BY number ASC

但是，如果我希望根据附加列中的值将项目聚在一起，这就行不通了。假设我只想在项目的值为 name 都是一样的。假设这是我的数据：

INSERT INTO `table` (`id`, `cc`, `number`, `name`) VALUES
(1, 1, 12, 'Hello'),
(2, 1, 2, 'Apple'),
(3, 1, 3, 'Bean'),
(4, 1, 10, 'Hello'),
(5, 1, 11, 'Hello'),
(6, 1, 1, 'Apple'),
(7, 1, 14, 'Deer'),
(8, 1, 14, 'Door'),
(9, 1, 15, 'Hello'),
(10, 1, 17, 'Hello'),

我想得到这样一份报告：

first  last   count  name
1      2      2      Apple
3      3      1      Bean
10     12     3      Hello
14     14     1      Deer
14     14     1      Door
15     15     1      Hello
17     17     1      Hello

换句话说，除了对连续的项进行分组之外，当这些组的值 name 不同(换言之，如果所有项目都是连续的并且具有相同的精确值，则这些项目只能一起在一个岛中 name ). 最接近（也不是很接近）我来了，是这样做的：

SELECT min(number) first_number, max(number) last_number, count(*) AS no_records FROM (
    SELECT c.*, @rn := @rn + 1 rn
    from (SELECT number FROM table WHERE cc = 1 GROUP BY number, name ORDER BY number) AS c
    CROSS JOIN (SELECT @rn := 0) r
) c
GROUP BY number - rn, name ORDER BY number ASC

不过，这样做行不通，结果是它似乎将一个名字的第一次出现返回为 first 最后一次出现是 last ，与 no_records 因为他们的人数不同，这肯定是不对的。
我觉得这个问题可能有关联，但我还没弄清楚，当我试着把它调整到我的table上时，它只是做了一个简单的 SELECT * 或多或少。我需要对我的查询进行哪些修改才能使其正常工作？
请记住：
项目可以按任何顺序插入
数字可以重复
名字可以重复，不一定是连续的

sql mysql mariadb gaps-and-islands

来源：https://stackoverflow.com/questions/62284967/gaps-and-islands-on-2-columns-if-column-a-consecutive-and-column-b-identical

2条答案

按热度按时间

uurity8g1#

您的查询没有什么可更改的。你基本上需要选择 name 以及 number 在子查询中按相同顺序排序。然后你可以分组 name, number - rn 在外部查询中。

SELECT
    min(number) first_number,
    max(number) last_number,
    count(*) AS no_records,
    name
FROM (
    SELECT c.*, @rn := @rn + 1 rn
    from (
        SELECT name, number
        FROM `table`
        WHERE cc = 1
        ORDER BY name, number
        LIMIT 99999999999999999
    ) AS c
    CROSS JOIN (SELECT @rn := 0) r
) c
GROUP BY name, number - rn 
ORDER BY first_number ASC, name ASC;

结果：

first_number  last_number  no_records  name
           1            2           2  Apple
           3            3           1  Bean
          10           12           3  Hello
          14           14           1  Deer
          14           14           1  Door
          15           15           1  Hello
          17           17           1  Hello

db<>小提琴
我通常反对以这种方式使用会话变量。原因是这些解决方案依赖于内部实现，可能会被版本更新或设置更改破坏。例如：一旦mariadb决定在子查询中不加限制地忽略order by子句。这就是为什么我包括一个巨大的限制。
我还替换了 number 与 first_number 在外部ORDERBY子句中，以避免只使用\u full \u group \u by模式出现问题。
生成行号的一种更稳定的方法是在临时表中使用aoto\u increment列：

drop temporary table if exists tmp_tbl;

create temporary table tmp_tbl (
  rn int unsigned auto_increment primary key,
  name varchar(64) not null,
  number int not null
);

insert into tmp_tbl (name, number)
  select name, number
  from `table`
  order by name, number;

最后的select查询与上面的外部查询相同：

SELECT
    min(number) first_number,
    max(number) last_number,
    count(*) AS no_records,
    name
FROM tmp_tbl
GROUP BY name, number - rn 
ORDER BY first_number ASC, name ASC;

db<>小提琴
在较新的版本（从mariadb 10.2开始）中，您可以使用 ROW_NUMBER() 窗口函数：

SELECT
    min(number) first_number,
    max(number) last_number,
    count(*) AS no_records,
    name
FROM (
    SELECT
        name,
        number,
        row_number() OVER (ORDER BY name, number) as rn
    FROM `table`
    WHERE cc = 1
) c
GROUP BY name, number - rn 
ORDER BY first_number ASC, name ASC;

db<>小提琴

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

wgx48brx2#

你的例子不是一个空白和孤岛的问题。如果它代表了您的实际问题，您可以使用聚合：

select min(number), max(number), count(*), name
from t
group by name;

我这么说是因为没有窗口功能，间隙和孤岛更具挑战性。这就引出了一个问题：为什么不使用mariadb的最新版本。不管怎样，10.1的寿命结束时间是今年10月。
编辑：
作为一个缺口和孤岛，这有点棘手，因为每个名称都必须单独处理。诀窍是使用 row_number() 带分区：

select name, min(number), max(number), count(*)
from (select t.*,
             row_number() over (partition by name order by number) as seqnum
      from t
     ) t
group by name, (number - seqnum);

如果名称有相邻的数字，并减去一个连续值，则结果是常量。例如：

Name   Number Seq  Diff
Hello    10    1    9
Hello    11    2    9
Hello    12    3    9
Hello    15    4   11

这个 diff 标识要聚合的组。
哦，我忘了这是一个即将过时的mariadb版本：

select name, min(number), max(number), count(*)
from (select t.*,
             (select count(*)
               from `table` t2
               where t2.name = t.name and t2.number <= t.number
              ) as seqnum
      from `table` t
     ) t
group by name, (number - seqnum);

为了提高性能，您需要一个索引 (name, number) . 除非名称的行数超过几百行，否则性能应该是合理的。
这是一把小提琴。

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

我来回答

两列上的间隙和孤岛-如果列a连续，列b相同

2条答案

相关问题

热门标签

最新问答