sql - 使用 SQL 在列中查找缺失的订单

Question

给定下表“订单”:

+--------------------------+
| id|order|country|cstm_id |
+--------------------------+
| 1 |O-1  |DE     |1       |
| 4 |O-2  |AT     |2       |
| 5 |O-3  |DE     |2       |
| 6 |O-5  |AT     |2       |
| 8 |O-6  |DE     |3       |
| 10|O-10 |DE     |3       |
| 11|O-11 |AT     |3       |
| 12|O-123|DE     |1       |
+--------------------------+

id 列和 order 列都缺少值。

我成功地使用这段代码找到了 id 中的缺失值:

select start, stop from (
  select m.id + 1 as start,
    (select min(id) - 1 from orders as x where x.id > m.id) as stop
  from orders as m
    left outer join orders as r on m.id = r.id - 1
  where r.id is null
) as x
where stop is not null;

结果如愿:

+------------+
| start|stop |
+------------+
| 2    |3    |
| 7    |7    |
| 9    |9    |
+------------+

但是我遇到了如何对顺序列执行相同操作的障碍，因为序列是非数字的。

Answer 1

您的第一个查询可以使用窗口函数来简化(并且很可能它也会更快):

select id + 1 as start, 
       next_id - 1 as stop
from (
  select id, 
         lead(id) over w as next_id,
         lead(id) over w - id as diff
  from orders
  window w as (order by id)
) t
where diff > 1;

在清除非数字部分后，可以对“order”值使用相同的方法:

with clean_ids as (
  select regexp_replace("order", '[^0-9]', '', 'g')::int as order_id
  from orders
) 
select order_id + 1 as start, 
       next_id - 1 as end
from (
  select order_id, 
         lead(order_id) over w as next_id,
         lead(order_id) over w - order_id as diff
  from clean_ids
  window w as (order by order_id)
) t
where diff > 1;

在线示例:https://rextester.com/NTIEG85119