SQL进阶技巧：如何对货场剩余货位进行查询统计？

会飞的一十六 2024-12-11

点击上方【蓝色】字体关注我们

“每一个选择的背后，都隐藏着我们不得不放弃的另一种可能。那种感觉就像是站在岔路口，眼睁睁地看着另一条路上的风景渐渐模糊，而自己踏上的这条路却充满未知的阴霾，你永远不知道那些放弃的风景会不会是一生的遗憾。”

01 场景描述

这是在做一个大型货场租赁系统时遇到的问题，在计算货场剩余存储空间时，不仅仅需要知道哪些货位是空闲的，还要能够判断出哪些货位之间是连续的。因为在新货物入场时，可以判断这些货物是否可以堆放在一起，而不是放在不连续的多个货位上，这样更便于管理，并且在出货时也更加迅速。

假设这个货场共有100个货位，现在已存放货物的货位是1、2、3、4、87、89、99、100，则剩余空位是5～86、88、90～98。数据库的设计方式一般有两种：一种是在表中为每个货位建一条记录，类似表1所示的结构设计；另一种设计方式是仅将存放有货物的货位号放在表中，也就是存货情况表中仅有货位编号列，存放1、2、3、4、87、89、99、100这几个数值。

存货情况表

相对于大型数据库而言，第一种架构设计对于查询语句编写方面会更方便一些。如果数据需要驻留在一些手持设备上，多数开发人员会更喜欢第二种架构设计，因为它能够节省宝贵的存储空间。尤其是当表的数据量非常大时，这种设计方式更能显示出它的优势。这里我们将以第二种架构设计方式来演示查询的创建方法，下面是创建示例表的语句。

create table freights as
select 1 id,1 numb
from dual
union all
select 2 id, 2 numb
from dual
union all
select 3 id,3 numb
from dual
union all
select 4 id,4 numb
from dual
union all
select 5 id,87 numb
from dual
union all
select 6 id,89 numb
from dual
union all
select 7 id,99 numb
from dual
union all
select 8 id,100 numb
from dual

问题1：查找剩余空位区间和剩余空位编号？

问题2：查找已用货位区间？

02 剩余空位区间及剩余货位统计分析

要查找剩余空位区间，就是要找出表2所示的数值范围。

要找出这些区间的开始和结束编号，需要在间断之前的值加1，在下一组编号开始之间的值减1。例如，表中的5～86是在4的基础上加1、在87的基础上减1得来的。

每一货位号的下一货位号

可以看出，只要找出n2-n1大于1的货位组，并在n1上加1，在n2上减1，就可以得到表19-8所示的剩余空位区间。参考下面的SQL语句。

select id,
       n1 + 1 f_st,
       n2 - 1 f_et
from (select id,
             numb                          n1,
             lead(numb) over (order by id) n2
      from FREIGHTS
     ) t
where n2 - n1 > 1
;

方法2：自关联形式实现



SELECT n1.Numb + 1      f_st,
       MIN(n2.Numb - 1) f_et
FROM Freights n1
         JOIN Freights n2
              ON n2.Numb > n1.Numb
GROUP BY n1.Numb
HAVING (n1.Numb + 1) < MIN(n2.Numb);

如果希望返回的不是剩余空位区间，而是剩余空位编号，则需要建立一个全部的货位编号表。下面的语句使用了递归CTE循环来建立1～100的货位编号。



WITH numbs(n) AS
         (
             SELECT 1 AS n from dual
             UNION ALL
             SELECT n + 1
             FROM numbs
             WHERE n < 100
         )
SELECT n
FROM numbs;

只要全部货位编号在Freights表中不存在，则表示该货位号没有使用，参考下面的语句。

WITH numbs(n) AS
         (
             SELECT 1 AS n
             from dual
             UNION ALL
             SELECT n + 1
             FROM numbs
             WHERE n < 100
         )
SELECT n
FROM Numbs
WHERE n NOT IN (SELECT Numb FROM Freights)

如果不需要返回全部的空货位号，而是几个的话，可以使用下面的查询语句。

SELECT numb, rn, (numb - rn) AS available_Numb
FROM (SELECT Numb, ROW_NUMBER() OVER (ORDER BY numb) rn
      FROM Freights) t
WHERE rn <> numb

NUMB	RN	AVAILABLE_NUMB
87	5	82
89	6	83
99	7	92
100	8	92

03 查找已用货位区间

现在已经使用的货位是1、2、3、4、87、89、99、100，已用货位区间即1～4、87～87、89～89、99～100。这些区间实际上是一组连续编号中的最小值和最大值，如1～4是货位1、2、3、4中的最小值和最大值。

上面的问题实际上是经典的连续性问题，我们按照连续性问题的通用处理方法来处理

select flg
     , min(n1) f_st
     , max(n1) f_ed
from (select id,
             n1,
             n2,
            --当状态一致时记为0，状态变化时记为1，相同状态的累计值不变，会持续状态开始前的值。
             sum(case when n1 - n2 <= 1 then 0 else 1 end) over (order by id) flg
      from (
               select id,
                      numb                         n1,
                      lag(numb) over (order by id) n2
               from FREIGHTS
           ) t
     ) t


group by flg
order by flg

FLG	F_ST	F_ED
1	1	4
2	87	87
3	89	89
4	99	100

04 小结

问题拓展：如果货位的占用时间有重叠，该如何处理？

假设存在一个名为warehouse
的表，其中包含space_id
（货位编号）、occupied_start_time
（占用开始时间）和occupied_end_time
（占用结束时间）这几个关键列来记录货位的占用情况。当然，实际情况中可能还有其他列，如货位大小、位置等信息，但对于查询未被占用的货位数量来说，上述三个列是重点关注的对象。
数据示例：
| space_id | occupied_start_time | occupied_end_time |
| 1 | '2024-01-01 08:00:00' | '2024-01-02 17:00:00' |
| 2 | NULL | NULL |
| 3 | '2024-01-03 10:00:00' | '2024-01-03 14:00:00' |

识别重叠情况

时间区间表示法：将货位的占用时间看作一个时间区间，用起始时间（）和结束时间（）来表示，例如一个货位从时刻开始被占用，到时刻结束占用，那么这个时间区间可以表示为。

重叠的判定条件：当两个时间区间和满足以下条件之一时，就存在重叠。

（第二个区间的起始时间在第一个区间内）
（第二个区间的结束时间在第一个区间内）
（第一个区间的起始时间在第二个区间内）
（第一个区间的结束时间在第二个区间内）

方法一：自连接和条件判断

思路
：通过将表与自身进行连接，比较每两个占用时间区间是否存在重叠。

查询示例

       SELECT w1.space_id, w1.occupied_start_time, w1.occupied_end_time,
              w2.space_id, w2.occupied_start_time, w2.occupied_end_time
       FROM warehouse_spaces w1
       JOIN warehouse_spaces w2
       ON w1.space_id < w2.space_id
       AND (
           (w1.occupied_start_time <= w2.occupied_start_time AND w1.occupied_end_time >= w2.occupied_start_time)
           OR (w1.occupied_start_time <= w2.occupied_end_time AND w1.occupied_end_time >= w2.occupied_end_time)
           OR (w2.occupied_start_time <= w1.occupied_start_time AND w2.occupied_end_time >= w1.occupied_start_time)
           OR (w2.occupied_start_time <= w1.occupied_end_time AND w2.occupied_end_time >= w1.occupied_end_time)
       );

解释：

JOIN
子句中的w1.space_id < w2.space_id
是为了避免同一个货位的时间区间与自身比较，同时也避免重复比较（例如，比较了和后，就不需要再比较和）。
AND
后面的条件部分就是根据前面提到的重叠判定条件来判断两个时间区间是否重叠。
这个查询会返回存在占用时间重叠的货位对以及它们的占用时间区间。

方法2：使用窗口函数（适用于部分支持高级窗口函数特性的数据库）

思路

首先，通过窗口函数计算每个货位与其他货位在时间上的重叠情况。可以使用排序和范围窗口来比较相邻货位的时间区间，标记出重叠的货位。

     SELECT space_id, occupied_start_time, occupied_end_time,
            MAX(CASE 
                    WHEN LAG(occupied_end_time) OVER (ORDER BY occupied_start_time) >= occupied_start_time 
                    THEN 1 
                    ELSE 0 
                END) OVER (ORDER BY occupied_start_time) AS overlap_flag
     FROM warehouse_spaces;

解释

LAG(occupied_end_time) OVER (ORDER BY occupied_start_time)
用于获取前一个货位的占用结束时间。
然后通过比较前一个货位的占用结束时间和当前货位的占用开始时间（LAG(occupied_end_time) OVER (ORDER BY occupied_start_time) >= occupied_start_time
），如果满足条件，则将overlap_flag
设为 1，表示有重叠；否则设为 0。
最后，通过MAX
函数在窗口内（按照占用开始时间排序的窗口）计算重叠标志，这样可以处理连续的重叠情况。