如何在 PostgreSQL 中查找序列中的间隙? [英] How Do I Find a Gap In a Sequence in PostgreSQL?

问题描述

我有一个带有 id 和 number_col 的项目表.在 Stackoverflow 上有很多关于寻找 ID 差距的问题，但我正在寻找的是数字差距.例如，如果我有 3 个项目:

<前>身份证 |number_col================1 |11 |21 |4

我需要一个返回 id = 1, number_col = 3 作为缺失值的 SQL 查询.查询应该查看每个 id 的 number_col 中的最大值.由于在我的示例中 4 是 id 1 的最大 number_col 值，因此它不应返回 1, 5 缺失.

解决方案

如果你想要缺失值的范围，那么:

选择 number_col + 1 作为 first_missing，(next_nc - 1) 作为 last_missingfrom (select t.*,lead(number_col) over (partition by id order by number_col) 作为 next_nc从T) t其中 next_nc <>number_col + 1

I have a table of items with an id and a number_col. There are plenty of questions on Stackoverflow for finding gaps in IDs, but what I'm looking for is a gap in the number. For example, if I have 3 items:

id | number_col
===============
1  | 1
1  | 2
1  | 4

I need a SQL query that returns id = 1, number_col = 3 as missing. The query should look at the max value in number_col for each id. Since 4 is the max number_col value for id 1 in my example, it should not return that 1, 5 is missing.

解决方案

If you want ranges of missing values, then:

select number_col + 1 as first_missing, (next_nc - 1) as last_missing
from (select t.*, lead(number_col) over (partition by id order by number_col) as next_nc
      from t
     ) t
where next_nc <> number_col + 1

这篇关于如何在 PostgreSQL 中查找序列中的间隙?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！