时间戳

首页 > 解决方案 > 查找“它们之间没有任何内容”的相关行

sql - 查找“它们之间没有任何内容”的相关行

问题描述

我有一张这样的桌子：

CREATE TABLE mytable (
  id INT(10) auto_increment PRIMARY KEY,
  from DATE(10) NOT NULL,
  before DATE(10) NULL,
  reference_id INT(10) NOT NULL,
)

所以有些行引用了另一个表（带有reference_id）。这些引用具有适用的日期范围 ( from/ before)。对于每个reference_id条目，可能有许多条目通常没有间隙：

id | from       | before     | reference_id
-------------------------------------------
1  | 2019-03-01 | 2019-03-05 | 5
5  | 2019-03-05 | 2019-03-09 | 5
8  | 2019-03-09 | NULL       | 5

（它们之间可能有其他reference_ids 的条目。）一个条目从前一个结束的地方开始。现在我想找到它们之间有差距的所有条目，其中from比before前面的要晚。例如（行2、列的变化from）：

id | from       | before     | reference_id
-------------------------------------------
1  | 2019-03-01 | 2019-03-05 | 5
5  | 2019-03-06 | 2019-03-09 | 5
8  | 2019-03-09 | NULL       | 5

第 2 行from比第 1 行晚一天before，这是一个差距。问题：对于第 3 行和第 1 行也是如此，但不应将它们视为结果，因为它们之间还有另一行。

我想出的是：

SELECT *
FROM mytable mt1
INNER JOIN mytable mt2 ON mt1.reference_id = mt2.reference_id AND mt1.id != mt2.id
WHERE mt1.before IS NOT NULL
  AND mt1.from < mt2.from
  AND DATE_ADD(mt1.before, INTERVAL 1 DAY) = mt2.from
  AND NOT EXISTS(SELECT * FROM mytable mt3 WHERE mt3.id BETWEEN mt1.id AND mt2.id)

然而，这个 (the EXISTS) 非常慢。有一个更好的方法吗？

[编辑]查询刚刚完成，虽然我肯定会期待一些结果，但我没有得到任何结果。所以它不仅慢而且不正确。[/edit]

执行计划：

1,PRIMARY,mt1,ALL,"mytable_48d78c2b,mytable_261384ee,mytable_849034da",,,,3313021,Using where
1,PRIMARY,mt2,ref,"mytable_48d78c2b,mytable_849034da",mytable_849034da,4,db.mt1.reference_id,1,Using index condition; Using where
2,DEPENDENT SUBQUERY,mt3,index,PRIMARY,mytable_48d78c2b,3,,3313021,Using where; Using index

标签： sqlmariadb

解决方案

（请注意，from对于列名，这是一个非常糟糕的选择，因为它是保留关键字。但是，它是有效的，所以我们开始了。）

假设没有重叠，您可以使用lag函数查找before上一行的：lag(before, 1) over (partition by reference_id order by before) as previous_before。从那里开始，如果很容易检查是否有间隙，如果from > previous_before

select
    `from`, before, `reference_id`
  , `from` > lag(before, 1) over (partition by reference_id order by before) as has_gap
from mytable

它的lag作用是找到前一行的before值，前一行由order by窗口函数的子句确定。如果from和之前相同，则没有间隙。

此查询为您提供在它们之前有间隙的行，您可以以相同的方式使用前导函数来获取在它们之后有间隙的行。

请注意，MariaDB 仅从 10.2.2 开始具有窗口函数。

推荐阅读