oracle中的小时状态报告

Question

我在oracle表中有详细的服务停机时间数据，如何获得服务的小时可用性报告

| Service Id |    Start Time    |  End Time        |
|   s-1      | 2016-01-30 21:20 | 2016-01-31 01:30 |
|   s-2      | 2016-01-30 18:12 | 2016-01-30 20:15 |

我必须使用sql查询如下所示。我想要一个sql，它产生的结果类似于下面的输出

s-1的可用性

.
.
| 2016-01-30 20:00 | 100% |
| 2016-01-30 21:00 |  33% |
| 2016-01-30 22:00 |   0% |
| 2016-01-30 23:00 |   0% |
| 2016-01-31 00:00 |   0% |
| 2016-01-31 01:00 |  50% |
| 2016-01-31 02:00 | 100% |
.

对于s-2也是如此。

注意:我正在使用hibernate、spring和java来编写报告。但是查看sql以生成相同的报告。

Answer 1

假设您在(Un)可用性表中没有重叠，那么：

Oracle安装

CREATE TABLE Unavailability( Service_id, start_time, end_time ) AS
SELECT 'S-1', TO_DATE( '2016-01-30 21:20', 'YYYY-MM-DD HH24:MI' ), TO_DATE( '2016-01-31 01:30', 'YYYY-MM-DD HH24:MI' ) FROM DUAL UNION ALL
SELECT 'S-2', TO_DATE( '2016-01-30 18:12', 'YYYY-MM-DD HH24:MI' ), TO_DATE( '2016-01-30 20:15', 'YYYY-MM-DD HH24:MI' ) FROM DUAL;

查询

WITH Times ( lower_bound, upper_bound ) AS (
  SELECT TO_DATE( '2016-01-30 12', 'YYYY-MM-DD HH24' ), TO_DATE( '2016-01-31 12', 'YYYY-MM-DD HH24' ) FROM DUAL
),
Services ( service_id ) AS (
  SELECT 'S-1' FROM DUAL UNION ALL
  SELECT 'S-2' FROM DUAL
),
Hours ( hr )AS (
  SELECT lower_bound + ( LEVEL - 1 ) / 24
  FROM   times
  CONNECT BY lower_bound + (LEVEL - 1) / 24 <= upper_bound
)
SELECT s.service_id,
       h.hr AS time,
       COALESCE(
         ( SELECT 100 - SUM( LEAST( u.end_time, h.hr + 1/24 ) - GREATEST( start_time, h.hr ) ) * 2400
           FROM   Unavailability u
           WHERE  u.start_time < h.hr + 1/24
           AND    u.end_time   > h.hr
           AND    u.service_id = s.service_id
         ),
         100
       ) AS availability
FROM   Services s
       CROSS JOIN
       Hours h
ORDER BY 1, 2;

输出

SERVICE_ID TIME                AVAILABILITY
---------- ------------------- ------------
S-1        2016-30-01 12:00:00          100 
S-1        2016-30-01 13:00:00          100 
S-1        2016-30-01 14:00:00          100 
S-1        2016-30-01 15:00:00          100 
S-1        2016-30-01 16:00:00          100 
S-1        2016-30-01 17:00:00          100 
S-1        2016-30-01 18:00:00          100 
S-1        2016-30-01 19:00:00          100 
S-1        2016-30-01 20:00:00          100 
S-1        2016-30-01 21:00:00   33.3333333 
S-1        2016-30-01 22:00:00            0 
S-1        2016-30-01 23:00:00            0 
S-1        2016-31-01 00:00:00            0 
S-1        2016-31-01 01:00:00           50 
S-1        2016-31-01 02:00:00          100 
S-1        2016-31-01 03:00:00          100 
S-1        2016-31-01 04:00:00          100 
S-1        2016-31-01 05:00:00          100 
S-1        2016-31-01 06:00:00          100 
S-1        2016-31-01 07:00:00          100 
S-1        2016-31-01 08:00:00          100 
S-1        2016-31-01 09:00:00          100 
S-1        2016-31-01 10:00:00          100 
S-1        2016-31-01 11:00:00          100 
S-1        2016-31-01 12:00:00          100 
S-2        2016-30-01 12:00:00          100 
S-2        2016-30-01 13:00:00          100 
S-2        2016-30-01 14:00:00          100 
S-2        2016-30-01 15:00:00          100 
S-2        2016-30-01 16:00:00          100 
S-2        2016-30-01 17:00:00          100 
S-2        2016-30-01 18:00:00           20 
S-2        2016-30-01 19:00:00            0 
S-2        2016-30-01 20:00:00           75 
S-2        2016-30-01 21:00:00          100 
S-2        2016-30-01 22:00:00          100 
S-2        2016-30-01 23:00:00          100 
S-2        2016-31-01 00:00:00          100 
S-2        2016-31-01 01:00:00          100 
S-2        2016-31-01 02:00:00          100 
S-2        2016-31-01 03:00:00          100 
S-2        2016-31-01 04:00:00          100 
S-2        2016-31-01 05:00:00          100 
S-2        2016-31-01 06:00:00          100 
S-2        2016-31-01 07:00:00          100 
S-2        2016-31-01 08:00:00          100 
S-2        2016-31-01 09:00:00          100 
S-2        2016-31-01 10:00:00          100 
S-2        2016-31-01 11:00:00          100 
S-2        2016-31-01 12:00:00          100