Как узнать время, в течение которого параметр имел определенное значение по журналу событий?

Question

[Евгений Д.]

Есть таблица журнала событий изменения метрик-триггеров (включено/выключено) со следующей структурой:

Table `log`:
+-------------+----------+--------------------------+
| metric_type | int      | Тип метрики              |
+-------------+----------+--------------------------+
| date_change | datetime | Дата изменения состояния |
+-------------+----------+--------------------------+
| state       | tinyint  | Состояние (вкл./выкл.)   |
+-------------+----------+--------------------------+

В таблице регистрируется состояние неких метрик. Причем регистрация происходит только в момент изменения метрики.
Пример данных: метрика "1" была в состоянии "1" с 2017-04-27 22:00 до 2017-06-02 10:00:

+-------------+---------------------+-------+
| metric_type | date_change         | state |
+-------------+---------------------+-------+
| 1           | 2017-04-20 10:00:00 | 0     |
+-------------+---------------------+-------+
| 1           | 2017-04-27 22:00:00 | 1     |
+-------------+---------------------+-------+
| 1           | 2017-06-02 10:00:00 | 0     |
+-------------+---------------------+-------+

Как с помощью SQL можно почитать сколько времени находилась метрика в состоянии "1" в произвольно заданный диапазон времени?

Answer 1

[V Sh.]

Вам нужно присоединить эту таблицу к себе самой, но со сдвигом на одну строчку. Вот корявое, но рабочее решение на ORACLE:

select t1.metric_type, t1.state, (t2.date_change - t1.date_change) as diff
from
(
select rownum as n, metric_type, state, date_change
from
(
select *
from someTable
order by metric_type, date_change
)) t1
left join (select rownum as n, metric_type, state, date_change
from
(
select *
from someTable
order by metric_type, date_change
)) t2 on (t1.n = t2.n+1 and t1.metric_type = t2.metric_type)