编写 Oracle 函数以获取某个事件的时间（如果该事件不在另一个事件中）

Question

让我解释一下我想要什么：有些组件可能会由于某些原因而失败，并且它们有优先级。现在，我想要的是，如果一个事件发生并且其优先级较低，我会将其开始时间和结束时间传递给函数，看看是否还有其他那段时间存在的高优先级组成部分。如果有，则有四种情况：

1. 如果低优先级事件在高优先级事件的计时，则时间为 0。

2. 如果事件的结束时间处于高优先级，但开始时间在高优先级之外，则为事件外部的时间。

3. 如果事件的开始时间处于高优先级，但结束时间在高优先级之外，则为事件外部的时间。

4. start 和 end 都出局意味着高优先级出现在低优先级事件之间。

示例：假设每个事件都具有高优先级。我正在发送开始时间和结束时间，例如 12:41:01 和 12:49:01

component  start time  end time  
1          12:40:01    12:50:01   result will be 0 because it's between the start & end
2          12:40:01    12:48:01   result will be 1 minute
3          12:43:01    12:50:01   result will be 2 minutes
4          12:43:01    12:44:01   result will be 7 minutes

我希望该函数以秒为单位向我发送时间。我必须一直将它与每个组件进行比较，并且我不知道如何在仅函数而不是过程中执行此操作。

Answer 1

鉴于下表定义

CREATE TABLE EVENT_OCCURRENCE
  (COMPONENT        NUMBER PRIMARY KEY,
   PRIORITY         VARCHAR2(6) NOT NULL
     CHECK(PRIORITY IN ('HIGH', 'MEDIUM', 'LOW')),
   START_TIME       DATE NOT NULL,
   END_TIME         DATE NOT NULL);

和以下数据，

INSERT INTO EVENT_OCCURRENCE (COMPONENT, PRIORITY, START_TIME, END_TIME)
  VALUES (1, 'HIGH', TRUNC(SYSDATE) + INTERVAL '0 12:40:01' DAY TO SECOND, TRUNC(SYSDATE) + INTERVAL '0 12:50:01' DAY TO SECOND);

INSERT INTO EVENT_OCCURRENCE (COMPONENT, PRIORITY, START_TIME, END_TIME)
  VALUES (2, 'HIGH', TRUNC(SYSDATE) + INTERVAL '0 12:45:01' DAY TO SECOND, TRUNC(SYSDATE) + INTERVAL '0 12:48:01' DAY TO SECOND);

INSERT INTO EVENT_OCCURRENCE (COMPONENT, PRIORITY, START_TIME, END_TIME)
  VALUES (3, 'HIGH', TRUNC(SYSDATE) + INTERVAL '0 12:39:01' DAY TO SECOND, TRUNC(SYSDATE) + INTERVAL '0 12:46:01' DAY TO SECOND);

INSERT INTO EVENT_OCCURRENCE (COMPONENT, PRIORITY, START_TIME, END_TIME)
  VALUES (4, 'HIGH', TRUNC(SYSDATE) + INTERVAL '0 12:38:01' DAY TO SECOND, TRUNC(SYSDATE) + INTERVAL '0 12:55:01' DAY TO SECOND);

以下过程将不会给出您要求的结果，但考虑到您的解释有点缺乏细节，我认为这是您所能期望的最好结果。它应该为您提供一些起点来获得您认为想要的东西：

CREATE OR REPLACE PROCEDURE PRINT_INTERSECTING_OCCURRENCES(dtEvent_start IN DATE,
                                                           dtEvent_end IN DATE) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('dtEvent_start=' || TO_CHAR(dtEvent_start, 'DD-MON-YYYY HH24:MI:SS') ||
                       '   dtEvent_end=' || TO_CHAR(dtEvent_end, 'DD-MON-YYYY HH24:MI:SS'));

  FOR aRow IN (SELECT E.*,
                      CASE
                        WHEN dtEvent_start >= E.START_TIME 
                             AND dtEvent_end <= E.END_TIME 
                        THEN
                          0
                        WHEN dtEvent_start <  E.START_TIME
                            AND dtEvent_end BETWEEN E.START_TIME AND E.END_TIME 
                        THEN
                          (E.START_TIME - dtEvent_start) * (24 * 60 * 60)
                        WHEN dtEvent_start BETWEEN E.START_TIME AND E.END_TIME 
                             AND dtEvent_end > E.END_TIME 
                       THEN
                          (dtEvent_end - E.END_TIME) * (24 * 60 * 60)
                    WHEN dtEvent_start >  E.END_TIME
                        OR dtEvent_end < E.START_TIME  
                    THEN
                         (dtEvent_end - dtEvent_start) * (24 * 60 * 60)
                    ELSE
                          ((E.START_TIME - dtEvent_start) * (24 * 60 * 60))
                             + ((dtEvent_end - E.END_TIME) * (24 * 60 * 60))
                      END AS TIME_DIFF
                 FROM EVENT_OCCURRENCE E
                 WHERE E.PRIORITY = 'HIGH')
  LOOP
    DBMS_OUTPUT.PUT_LINE('COMPONENT=' || aRow.COMPONENT ||
                         '  PRIORITY=' || aRow.PRIORITY ||
                         '  START_TIME=' || TO_CHAR(aRow.START_TIME, 'DD-MON-YYYY HH24:MI:SS') ||
                         '  END_TIME=' || TO_CHAR(aRow.END_TIME, 'DD-MON-YYYY HH24:MI:SS') ||
                         '  TIME_DIFF=' || aRow.TIME_DIFF);
  END LOOP;
END PRINT_INTERSECTING_OCCURRENCES;

分享和享受。

Answer 2

该描述似乎没有意义，但我认为这就是您可能正在寻找的内容（假设 start_time_in 和 end_time_in 是输入变量的名称）。

select component, ((start_time - least(start_time, start_time_in)) + (greatest(end_time, end_time_in) - end_time)) * 24*60*60 seconds_outside_window   
from table