sql - 如何根据范围获取连续的批号？

Question

我有一个这样的表:

LotNumber
1065
1026
95092
95090
95089
95088
85087
95086
95085
95084
95083
95082
95081

我的 SQL 查询应该如何根据范围(第一个匹配项)返回连续的批号。

例如返回范围内的 3，(95090, 95089, 95088)返回范围内的 5 (95086, 95085, 95084, 95083, 95082)

Answer 1

这是间隙和孤岛问题的复杂变体，因为:

• 你想要一个组中的所有元素(不仅仅是开始和结束)
• 只需要第一组中满足条件的元素

以下查询符合您的预期:

select lotNumber
from (
    select 
        lotNumber,
        cnt,
        dense_rank() over(order by grp) rn
    from (
        select 
            lotNumber,
            grp,
            count(*) over(partition by grp) cnt
        from (
            select 
                lotNumber,
                sum(case when lotNumber = lagLotNumber - 1 then 0 else 1 end) 
                    over(order by id) grp
            from (
                select
                    id,
                    lotNumber, 
                    lag(lotNumber) over(order by id) lagLotNumber
                from mytable
            ) t
        ) t
    ) t
    where cnt >= 3
) t
where rn = 1

子句where cnt >= 3可以用来控制连续数的目标长度。

Demo on DB Fiddle

| lotnumber |
| --------- |
| 95090     |
| 95089     |
| 95088     |

---

逐步解释

首先，只有 存在可用于对记录进行排序的列(在 sql 表中，记录默认情况下是无序的)才能解决此问题。我假设存在这样一个列并称为 id。这是数据集:

| lotnumber | id  |
| --------- | --- |
| 1065      | 1   |
| 1026      | 2   |
| 95092     | 3   |
| 95090     | 4   |
| 95089     | 5   |
| 95088     | 6   |
| 85087     | 7   |
| 95086     | 8   |
| 95085     | 9   |
| 95084     | 10  |
| 95083     | 11  |
| 95082     | 12  |
| 95081     | 13  |

1) 第一步包括恢复每条记录的先前 lotNumber。为此，我们使用 lag()。

select
    id,
    lotNumber, 
    lag(lotNumber) over(order by id) lagLotNumber
from mytable

| id  | lotnumber | laglotnumber |
| --- | --------- | ------------ |
| 1   | 1065      |              |
| 2   | 1026      | 1065         |
| 3   | 95092     | 1026         |
| 4   | 95090     | 95092        |
...

2) 然后，我们使用累积和将批号连续的记录分组。当两条记录不连续时，新的一组开始:

select 
    lotNumber,
    sum(case when lotNumber = lagLotNumber - 1 then 0 else 1 end) 
        over(order by id) grp
from (
    ... above query ...
) t

| lotnumber | grp |
| --------- | --- |
| 1065      | 1   |
| 1026      | 2   |
| 95092     | 3   |
| 95090     | 4   |
| 95089     | 4   |
| 95088     | 4   |
| 85087     | 5   |
| 95086     | 6   |
| 95085     | 6   |
| 95084     | 6   |
| 95083     | 6   |
| 95082     | 6   |
| 95081     | 6   |

3) 下一步包括计算我们在每个组中有多少条记录，使用窗口计数

select 
    lotNumber,
    grp,
    count(*) over(partition by grp) cnt
from (
    ... above query ...
) t;

| lotnumber | grp | cnt |
| --------- | --- | --- |
| 1065      | 1   | 1   |
| 1026      | 2   | 1   |
| 95092     | 3   | 1   |
| 95090     | 4   | 3   |
| 95089     | 4   | 3   |
| 95088     | 4   | 3   |
| 85087     | 5   | 1   |
| 95086     | 6   | 6   |
| 95085     | 6   | 6   |
| 95084     | 6   | 6   |
| 95083     | 6   | 6   |
| 95082     | 6   | 6   |
| 95081     | 6   | 6   |

4) 有了手边的这些信息，我们现在可以过滤至少具有目标连续记录数的组。同时，我们按批号升序对组进行排序。过滤条件where cnt >= 3可以根据需要改变，以控制连续记录的目标条数。

在这里，我们有两组至少有 3 个连续的数字:

select 
    lotNumber,
    cnt,
    dense_rank() over(order by grp) rn
from (
    ... above query ...
) t
where cnt >= 3;

| lotnumber | cnt | rn  |
| --------- | --- | --- |
| 95090     | 3   | 1   |
| 95089     | 3   | 1   |
| 95088     | 3   | 1   |
| 95086     | 6   | 2   |
| 95085     | 6   | 2   |
| 95084     | 6   | 2   |
| 95083     | 6   | 2   |
| 95082     | 6   | 2   |
| 95081     | 6   | 2   |

5) 最后一步是过滤每个组中的顶部记录。

select lotNumber
from (
    ... above query ...
) t
where rn = 1

| lotnumber |
| --------- |
| 95090     |
| 95089     |
| 95088     |