sql - 甲骨文:选择并行条目

Question

我正在寻找在相对较大的表中进行相对复杂查询的最有效方法。

概念是:

• 我有一个表，其中包含可以相互并行运行的阶段的记录
• 记录量超过 500 万(并且还在增加)
• 时间段从大约 5 年前开始
• 由于性能原因，此选择可以应用于最近 3 个月的 300.000 条记录(仅当物理上不可能对整个表执行此操作时)
• Oracle 版本:11g

数据样本如下

Table Phases (ID, START_TS, END_TS, PRIO)

1  10:00:00 10:20:10 10
2  10:05:00 10:10:00 11
3  10:05:20 10:15:00 9
4  10:16:00 10:25:00 8
5  10:24:00 10:45:15 1
6  10:26:00 10:30:00 10
7  10:27:00 10:35:00 15
8  10:34:00 10:50:00 5
9  10:50:00 10:55:00 20
10 10:55:00 11:00:00 15

上面可以看到信息当前是如何存储的(当然还有其他几列是不相关的信息)。

有两个需求(或需要解决的问题)

1. 如果我们将所有阶段的持续时间相加，结果是上述数据所代表的一个多小时。 (阶段之间可能存在漏洞，因此采用第一个 start_ts 和最后一个 end_ts 是不够的)。
2. 数据应以一种形式显示，即每次哪个阶段与哪个阶段并行以及哪个阶段具有最高优先级，如下面的预期 View 所示

这里很容易区分每次的最高优先级阶段 (HIGHEST_PRIO)，将它们的持续时间相加将得到实际的总持续时间。

View V_Parallel_Phases (ID, START_TS, END_TS, PRIO, HIGHEST_PRIO) 
-> Optional Columns: Part_of_ID / Runs_Parallel

1  10:00:00 10:05:20 10 True  (--> Part_1 / False)
1  10:05:20 10:15:00 10 False (--> Part_2 / True)
2  10:05:00 10:10:00 11 False (--> Part_1 / True)
3  10:05:20 10:15:00  9 True  (--> Part_1 / True)
1  10:15:00 10:16:00 10 True  (--> Part_3 / True)
1  10:16:00 10:20:10 10 False (--> Part_4 / True)
4  10:16:00 10:24:00  8 True  (--> Part_1 / True)
4  10:24:00 10:25:00  8 False (--> Part_2 / True)
5  10:24:00 10:45:15  1 True  (--> Part_1 / True)
6  10:26:00 10:30:00 10 False (--> Part_1 / True)
7  10:27:00 10:35:00 15 False (--> Part_1 / True)
8  10:34:00 10:45:15  5 False (--> Part_1 / True)
8  10:45:15 10:50:00  5 True  (--> Part_2 / True)
9  10:50:00 10:55:00 20 True  (--> Part_2 / False)
10 10:55:00 11:00:00 15 True  (--> Part_2 / False)

不幸的是，我不知道进行此查询的有效方法。当前的解决方案是在生成大型报告的工具中以编程方式进行上述计算，但完全失败了。从这个计算之前需要 30 秒，现在需要 10 多分钟，而不考虑阶段的优先级..

然后我想到将此代码转换成 sql 中的任何一个:a) View b) 物化 View c) 一个表，我会偶尔用一个过程填充它(取决于所需的持续时间)。

PS:我知道 oracle 有一些可以处理复杂查询的分析函数，但我不知道哪些函数实际上可以帮助我解决当前的问题。

提前致谢!

Answer 1

这是一个不完整的答案，但在继续之前我需要知道这种方法是否可行。我相信可以完全用 SQL 来完成，但我不确定性能如何。

首先找出所有有转换的时间点:

CREATE VIEW Events AS
    SELECT START_TS AS TS
      FROM Phases
    UNION
    SELECT END_TS AS TS
      FROM Phases
;

然后从这些时间点创建(开始，结束)元组:

CREATE VIEW Segments AS
     SELECT START.TS AS START_TS,
            MIN(END.TS) AS END_TS
      FROM Events AS START
      JOIN Events AS END
     WHERE START.TS < END.TS
;

从这里开始，剩下的事情应该相当简单。这是一个查询，列出了给定段中的段和所有事件阶段:

  SELECT *
    FROM Segments
    JOIN Phases
   WHERE Segments.START_TS BETWEEN Phases.START_TS AND Phases.END_TS
     AND Segments.END_TS BETWEEN Phases.START_TS AND Phases.END_TS
ORDER BY Segments.START_TS
;

剩下的可以通过子选择和一些聚合来完成。

| START_TS |   END_TS | ID | START_TS |  END_TS  | PRIO |
|----------|----------|----|----------|----------|------|
| 10:00:00 | 10:05:00 |  1 | 10:00:00 | 10:20:10 |   10 |
| 10:05:00 | 10:05:20 |  1 | 10:00:00 | 10:20:10 |   10 |
| 10:05:00 | 10:05:20 |  2 | 10:05:00 | 10:10:00 |   11 |
| 10:05:20 | 10:10:00 |  1 | 10:00:00 | 10:20:10 |   10 |
| 10:05:20 | 10:10:00 |  2 | 10:05:00 | 10:10:00 |   11 |
| 10:05:20 | 10:10:00 |  3 | 10:05:20 | 10:15:00 |    9 |
| 10:10:00 | 10:15:00 |  1 | 10:00:00 | 10:20:10 |   10 |
| 10:10:00 | 10:15:00 |  3 | 10:05:20 | 10:15:00 |    9 |
| 10:15:00 | 10:16:00 |  1 | 10:00:00 | 10:20:10 |   10 |
| 10:16:00 | 10:20:10 |  1 | 10:00:00 | 10:20:10 |   10 |
| 10:16:00 | 10:20:10 |  4 | 10:16:00 | 10:25:00 |    8 |
| 10:20:10 | 10:24:00 |  4 | 10:16:00 | 10:25:00 |    8 |
| 10:24:00 | 10:25:00 |  4 | 10:16:00 | 10:25:00 |    8 |
| 10:24:00 | 10:25:00 |  5 | 10:24:00 | 10:45:15 |    1 |
| 10:25:00 | 10:26:00 |  5 | 10:24:00 | 10:45:15 |    1 |
| 10:26:00 | 10:27:00 |  5 | 10:24:00 | 10:45:15 |    1 |
| 10:26:00 | 10:27:00 |  6 | 10:26:00 | 10:30:00 |   10 |
| 10:27:00 | 10:30:00 |  5 | 10:24:00 | 10:45:15 |    1 |
| 10:27:00 | 10:30:00 |  6 | 10:26:00 | 10:30:00 |   10 |
| 10:27:00 | 10:30:00 |  7 | 10:27:00 | 10:35:00 |   15 |
| 10:30:00 | 10:34:00 |  5 | 10:24:00 | 10:45:15 |    1 |
| 10:30:00 | 10:34:00 |  7 | 10:27:00 | 10:35:00 |   15 |
| 10:34:00 | 10:35:00 |  8 | 10:34:00 | 10:50:00 |    5 |
| 10:34:00 | 10:35:00 |  5 | 10:24:00 | 10:45:15 |    1 |
| 10:34:00 | 10:35:00 |  7 | 10:27:00 | 10:35:00 |   15 |
| 10:35:00 | 10:45:15 |  5 | 10:24:00 | 10:45:15 |    1 |
| 10:35:00 | 10:45:15 |  8 | 10:34:00 | 10:50:00 |    5 |
| 10:45:15 | 10:50:00 |  8 | 10:34:00 | 10:50:00 |    5 |
| 10:50:00 | 10:55:00 |  9 | 10:50:00 | 10:55:00 |   20 |
| 10:55:00 | 11:00:00 | 10 | 10:55:00 | 11:00:00 |   15 |

这里有一个 SQL fiddle 演示了整个过程:

http://sqlfiddle.com/#!9/d801b/2