Oracle 选择与他人有一定 “距离” 的记录

你好，汤姆，

我们有一张有预约的表。像这样每5分钟有一个约会，可能会有漏洞，因为已经进行了一些约会

create table AM_HILFSTABELLE
(
  amht_num1  NUMBER,
  amht_dat1  DATE
);

insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 1, to_date('08.06.2020 08:00:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 2, to_date('08.06.2020 08:05:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 3, to_date('08.06.2020 08:10:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 4, to_date('08.06.2020 08:15:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 5, to_date('08.06.2020 08:20:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 6, to_date('08.06.2020 08:25:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 7, to_date('08.06.2020 08:35:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 8, to_date('08.06.2020 08:40:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 9, to_date('08.06.2020 08:45:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 110, to_date('08.06.2020 08:50:00','DD.MM.YYYY HH24:MI:SS'));
复制

我们只想向用户显示彼此之间至少15分钟的约会
在这种情况下
08:00
08:15:
08:35 (已经采取08:30，所以从最后一次开始20分钟)
08:50 (距离最后一个15分钟)
我们将这些约会写在一个临时表中，我们向用户展示。

Ww用光标和循环解决了它。
是否可以在没有循环的情况下使用select执行此操作，这样我们就可以避免pl/sql开销并直接向用户显示结果

问候
安德烈亚斯

谢谢你的耐心。我忘记了这个，今天发现了它...抱歉。

首先，我将提取每行传递的分钟

SQL> select a.*,
  2         nvl(amht_dat1 - lag(amht_dat1) over ( order by amht_num1),0)*1440 mins
  3  from am_hilfstabelle a;

 AMHT_NUM1 AMHT_DAT1                 MINS
---------- ------------------- ----------
         1 08/06/2020 08:00:00          0
         2 08/06/2020 08:05:00          5
         3 08/06/2020 08:10:00          5
         4 08/06/2020 08:15:00          5
         5 08/06/2020 08:20:00          5
         6 08/06/2020 08:25:00          5
         7 08/06/2020 08:35:00         10
         8 08/06/2020 08:40:00          5
         9 08/06/2020 08:45:00          5
       110 08/06/2020 08:50:00          5

10 rows selected.
复制

现在，我可以将这些分钟用作运行总数，当它达到15分钟时会重置。在12c中，match_regnize是完美的，但是在11.2上，我们可以使用模型 (不友好)

SQL> with t as
  2  (
  3  select a.*,
  4         nvl(amht_dat1 - lag(amht_dat1) over ( order by amht_num1),0)*1440 mins
  5  from am_hilfstabelle a
  6  )
  7  select s, e, d, mins, sm from t
  8   model dimension by(row_number() over(order by amht_num1) rn)
  9         measures(amht_dat1 d, amht_num1 s, amht_num1 e, mins, mins sm)
 10         rules(sm[rn > 1] =
 11                 case when (sm[cv() - 1] + mins[cv()]) > 15 or mins[cv()] > 15
 12                      then 0 -- mins[cv()]
 13                      else sm[cv() - 1] + mins[cv()]
 14                  end,
 15               s[rn > 1] =
 16                 case when(sm[cv() - 1] + mins[cv()]) > 15 or mins[cv()] > 15
 17                      then s[cv()]
 18                      else s[cv() - 1]
 19                  end);

         S          E D                         MINS         SM
---------- ---------- ------------------- ---------- ----------
         1          1 08/06/2020 08:00:00          0          0
         1          2 08/06/2020 08:05:00          5          5
         1          3 08/06/2020 08:10:00          5         10
         1          4 08/06/2020 08:15:00          5         15
         5          5 08/06/2020 08:20:00          5          0
         5          6 08/06/2020 08:25:00          5          5
         5          7 08/06/2020 08:35:00         10         15
         8          8 08/06/2020 08:40:00          5          0
         8          9 08/06/2020 08:45:00          5          5
         8        110 08/06/2020 08:50:00          5         10

10 rows selected.
复制

你可以看到逻辑分组形式 ....“S” (正在启动amht_num1) 运行4个时隙 (行)，然后我们在2个插槽中切换到S = 5，依此类推。现在可以分组了

SQL> with t as
  2  (
  3  select a.*,
  4         nvl(amht_dat1 - lag(amht_dat1) over ( order by amht_num1),0)*1440 mins
  5  from am_hilfstabelle a
  6  )
  7  select s, min(d), max(e), max(sm)
  8  from (
  9    select s, e, d, mins, sm from t
 10     model dimension by(row_number() over(order by amht_num1) rn)
 11           measures(amht_dat1 d, amht_num1 s, amht_num1 e, mins, mins sm)
 12           rules(sm[rn > 1] =
 13                   case when (sm[cv() - 1] + mins[cv()]) > 15 or mins[cv()] > 15
 14                        then 0 -- mins[cv()]
 15                        else sm[cv() - 1] + mins[cv()]
 16                    end,
 17                 s[rn > 1] =
 18                   case when(sm[cv() - 1] + mins[cv()]) > 15 or mins[cv()] > 15
 19                        then s[cv()]
 20                        else s[cv() - 1]
 21                    end)
 22  )
 23  group by s order by s;

         S MIN(D)                  MAX(E)    MAX(SM)
---------- ------------------- ---------- ----------
         1 08/06/2020 08:00:00          4         15
         5 08/06/2020 08:20:00          7         15
         8 08/06/2020 08:40:00        110         10

SQL>
复制