从不规则日期中查找每小时的百分比

Question

我有一个如下表，每个条目在给定时间的状态发生变化。 状态可以重复，因为其他列有子状态信息。

如何获取每种状态的时间百分比（例如，小时）？

NAME STATUS_CHANGE_TIME  STATUS
foo  15-MAY-11 18:52     A
foo  15-MAY-11 18:38     A
foo  15-MAY-11 18:33     B
foo  15-MAY-11 16:53     A
foo  15-MAY-11 16:47     B
foo  15-MAY-11 13:37     A
foo  15-MAY-11 13:33     C
foo  15-MAY-11 10:23     C
foo  15-MAY-11 10:17     A
foo  ...

期望回报：

HH24  STATUS  PERCENT    
10  ...
11    C       100        (No entries; last change was to C)
12    C       100        ""                       ""
13    C        62
13    A        38        (From C to A at :37 with 23 mins left; 23/60 ~ 38%)
14    A       100
15    A       100
16    A        90        (= A for first 47 minutes, then for another 7)
16    B        10        (16:53 - 16:47 = 6 minutes or 10% of an hour)
17    A       100
18 ... etc.

Answer 1

好问题，这是一个有趣的挑战！

您需要一个辅助表来存储每个时间段（在本例中为小时），然后在状态更新重叠的地方连接到它。 LEAD() 可以获取下一个状态条目来检查它是什么时候，而 GREATEST() 和 LEAST() 可以计算出当前时间适用于每小时状态的开始/结束。

当然，通过示例更容易解释这一点。这是所需的 HOURS 表：

SQL> CREATE TABLE hours (HOUR NUMBER(2), start_m date, end_m date);

Table created.

SQL> BEGIN
  2      FOR i IN 0..23 LOOP
  3          INSERT INTO hours VALUES(i, to_date(lpad(i, 2, '0')||':00:00', 'HH24:MI:SS')
  4                                    , to_date(lpad(i, 2, '0')||':59:59', 'HH24:MI:SS'));
  5      END loop;
  6      COMMIT;
  7  END;
  8  /

PL/SQL procedure successfully completed.

以下只是您问题中的测试数据的总体情况。

SQL> CREATE TABLE status_updates (NAME VARCHAR2(3), status_change_time DATE, status CHAR(1));

Table created.

SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 18:52', 'DD-MON-RR HH24:MI:SS'), 'A');

1 row created.

SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 18:38', 'DD-MON-RR HH24:MI:SS'), 'A');

1 row created.

SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 18:33', 'DD-MON-RR HH24:MI:SS'), 'B');

1 row created.

SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 16:53', 'DD-MON-RR HH24:MI:SS'), 'A');

1 row created.

SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 16:47', 'DD-MON-RR HH24:MI:SS'), 'B');

1 row created.

SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 13:37', 'DD-MON-RR HH24:MI:SS'), 'A');

1 row created.

SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 13:33', 'DD-MON-RR HH24:MI:SS'), 'C');

1 row created.

SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 10:23', 'DD-MON-RR HH24:MI:SS'), 'C');

1 row created.

SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 10:17', 'DD-MON-RR HH24:MI:SS'), 'A');

1 row created.

SQL> commit;

Commit complete.

现在这是获取所需百分比的 select 语句。

SELECT t.NAME, t.HOUR, t.status, sum(round((status_end_h-start_status_h)*24*100)) per_cent
FROM   (
    SELECT A.NAME
    ,      A.status
    ,      A.status_change_time
    ,      A.next_change_time
    ,      b.HOUR
    ,      greatest(status_change_time, trunc(status_change_time)+(b.start_m-trunc(b.start_m))) start_status_h
    ,      least(next_change_time, trunc(next_change_time)+(b.end_m-trunc(b.end_m))) status_end_h
    FROM   (
        SELECT NAME
        ,      status
        ,      status_change_time
        ,      lead(status_change_time) OVER (ORDER BY NAME, status_change_time) next_change_time
        FROM   status_updates
    ) A, hours b
    WHERE  TO_CHAR(b.start_m, 'HH24:MI:SS') BETWEEN TO_CHAR(A.status_change_time, 'HH24:MI:SS') AND TO_CHAR(A.next_change_time, 'HH24:MI:SS')
    OR     TO_CHAR(b.end_m, 'HH24:MI:SS') BETWEEN TO_CHAR(A.status_change_time, 'HH24:MI:SS') AND TO_CHAR(A.next_change_time, 'HH24:MI:SS')
    OR    (TO_CHAR(A.status_change_time, 'HH24:MI:SS') BETWEEN TO_CHAR(b.start_m, 'HH24:MI:SS') AND TO_CHAR(b.end_m, 'HH24:MI:SS')
    AND    TO_CHAR(A.next_change_time, 'HH24:MI:SS') BETWEEN TO_CHAR(b.start_m, 'HH24:MI:SS') AND TO_CHAR(b.end_m, 'HH24:MI:SS'))
) t
GROUP BY t.NAME, t.HOUR, t.status
ORDER BY t.HOUR;

NAM       HOUR S   PER_CENT                                                     
--- ---------- - ----------                                                     
foo         10 A         10                                                     
foo         10 C         62                                                     
foo         11 C        100                                                     
foo         12 C        100                                                     
foo         13 A         38                                                     
foo         13 C         62                                                     
foo         14 A        100                                                     
foo         15 A        100                                                     
foo         16 A         90                                                     
foo         16 B         10                                                     
foo         17 A        100                                                     

NAM       HOUR S   PER_CENT                                                     
--- ---------- - ----------                                                     
foo         18 A         78                                                     
foo         18 B          8                                                     

13 rows selected.