Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния / 7 сообщений из 7, страница 1 из 1
13.01.2017, 13:56
    #39384151
evgenius_b
evgenius_b
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния
Всем привет.
Есть набор датчиков (device_id) и результаты измерения их статусов (dev_status) в заданное время (timestamp) (см.пример в таблице ниже).

timestampdevice_iddev_status05/11/2016 16:301ok05/11/2016 17:001ok05/11/2016 17:301err05/11/2016 18:001err05/11/2016 18:301ok05/11/2016 19:001ok05/11/2016 19:301err05/11/2016 20:001ok05/11/2016 20:301ok05/11/2016 16:002ok05/11/2016 16:302err05/11/2016 17:002ok05/11/2016 17:302ok05/11/2016 18:002err05/11/2016 18:302ok05/11/2016 19:002err05/11/2016 19:302err05/11/2016 20:002err05/11/2016 20:302ok

Мне надо в отдельном столбце получить сколько времени прошло с момента перехода девайса в состояние "err" из состояния "ok".
При этом, если девайс из состояния "err" снова перешел в состояние "ok", то счетчик обнуляется.

Мой план решения (просьба не пинать очень уж сильно):
1) в дополнительном столбце (err_cntr_state) проставить флаги старта счетчика состояния "ашипка" (переходы dev_status из состояния "ok" в "err") и его остановки (переходы dev_status из состояния "err" в "ok").
2) в другом дополнительном столбце (err_cntr_value) нарастающим итогом вывести требуемую длительность состояния "ашипка".
Т.е. получить нечто вроде такого:

timestampdevice_iddev_statuserr_cntr_stateerr_cntr_value05/11/2016 16:301ok05/11/2016 17:001ok05/11/2016 17:301errstart005/11/2016 18:001errstop00:3005/11/2016 18:301ok05/11/2016 19:001ok05/11/2016 19:301err05/11/2016 20:001ok05/11/2016 20:301ok05/11/2016 16:002ok05/11/2016 16:302err05/11/2016 17:002ok05/11/2016 17:302ok05/11/2016 18:002err05/11/2016 18:302ok05/11/2016 19:002errstart005/11/2016 19:302err00:3005/11/2016 20:002errstop01:0005/11/2016 20:302ok

Вопросы:
1) С п.1. из плана я справился:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select t.*
, case when lag(t.dev_status) over (partition by t.device_id order by t.timestamp) = 'true' and t.dev_status = 'false' --previous state = true but current = false
        and lead(t.dev_status) over (partition by t.device_id order by t.timestamp) != 'true' --to filter out single starts
        then 'start' 
        when lead(t.dev_status) over (partition by t.device_id order by t.timestamp) = 'true' and t.dev_status = 'false' --previous state = true but current = false
        and lag(t.dev_status) over (partition by t.device_id order by t.timestamp) != 'true' --to filter out single stops
        then 'stop' end err_cntr_state
from t
order by 2,1


но на реализации п.2. потух. Прошу помощи.
2) Есть основания полагать, что выбранный вариант решения не вполне изящен с т.з. реальных профи SQL. Подскажите пожалуйста, по каким ключевым словам копать в верном направлении. На самом деле кручу данные на вертике, т.о. некоторые аналитические функции доступны, а если буду хорошо себя вести, то наши dba еще и права на создание процедур и функций моей схеме дадут :).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.01.2017, 14:41
    #39384212
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния
evgenius_b,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
with t as (select 1 dev_id, 'true' dev_status, cast('2017-01-01 00:00:00' as datetime) dt union all
           select 1 dev_id, 'false' dev_status, cast('2017-01-01 00:00:01' as datetime) dt union all
           select 1 dev_id, 'false' dev_status, cast('2017-01-01 00:00:10' as datetime) dt union all
           select 1 dev_id, 'false' dev_status, cast('2017-01-01 00:01:00' as datetime) dt union all
           select 1 dev_id, 'true' dev_status, cast('2017-01-01 00:01:01' as datetime) dt ),
     tg as (select t.*, 
                   row_number()    over (partition by dev_id order by dt) - 
                   row_number()    over (partition by dev_id, dev_status order by dt) grp
            from t)      
select tg.*, 
       case when dev_status = 'false' then dt - nvl(lag(dt) over (partition by dev_id, grp order by dt),dt) end
from tg



Что-то такое?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.01.2017, 15:31
    #39384274
evgenius_b
evgenius_b
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния
env,
ага, спасибо, почти то, что нужно. В предложенном решении показывается длительность каждого интервала в состоянии false, осталось только просуммировать полученные значения.
Я правильно понял принцип - сначала в дополнительном столбце grp проставляем номер группы следующих друг за другом смен состояний по каждому девайсу, затем суммируем по номеру группы и девайсу?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.01.2017, 16:50
    #39384362
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния
evgenius_b,

попробуй привести через два with ... select ... набор входных данных и ожидаемый результат, возможно я не правильно понял твою задачу
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.01.2017, 17:01
    #39384375
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния
env,

и поищи по форуму start_of_group, т.к. два row_number не всегда дадут корректную группу
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.01.2017, 17:19
    #39384397
evgenius_b
evgenius_b
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния
env,

Извиняюсь, оракла под рукой нет, поэтому пишу псевдокод, не помню как оформить время в минутах в столбце err_cntr_value:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
select to_date('05/11/2016 16:00', 'dd/mm/yyyy dd24:mi') as timestamp, 1 as device_id, 'ok' as dev_status, null as err_cntr_value from dual union all
select to_date('05/11/2016 16:30', 'dd/mm/yyyy dd24:mi') as timestamp, 1 as device_id, 'ok' as dev_status, null from dual union all
select to_date('05/11/2016 17:00', 'dd/mm/yyyy dd24:mi') as timestamp, 1 as device_id, 'err' as dev_status, '00:00' from dual union all
select to_date('05/11/2016 17:30', 'dd/mm/yyyy dd24:mi') as timestamp, 1 as device_id, 'err' as dev_status, '00:30' from dual union all
select to_date('05/11/2016 18:00', 'dd/mm/yyyy dd24:mi') as timestamp, 1 as device_id, 'err' as dev_status, '01:00' from dual union all
select to_date('05/11/2016 18:30', 'dd/mm/yyyy dd24:mi') as timestamp, 1 as device_id, 'ok' as dev_status, null from dual union all
select to_date('05/11/2016 19:00', 'dd/mm/yyyy dd24:mi') as timestamp, 1 as device_id, 'err' as dev_status, '00:00' from dual union all
select to_date('05/11/2016 19:30', 'dd/mm/yyyy dd24:mi') as timestamp, 1 as device_id, 'ok' as dev_status, null from dual union all
select to_date('06/11/2016 20:00', 'dd/mm/yyyy dd24:mi') as timestamp, 1 as device_id, 'ok' as dev_status, null from dual union all
select to_date('05/11/2016 16:00', 'dd/mm/yyyy dd24:mi') as timestamp, 2 as device_id, 'ok' as dev_status, null from dual union all
select to_date('05/11/2016 16:30', 'dd/mm/yyyy dd24:mi') as timestamp, 2 as device_id, 'err' as dev_status, '00:00' from dual union all
select to_date('05/11/2016 17:00', 'dd/mm/yyyy dd24:mi') as timestamp, 2 as device_id, 'ok' as dev_status, null from dual union all
select to_date('05/11/2016 17:30', 'dd/mm/yyyy dd24:mi') as timestamp, 2 as device_id, 'ok' as dev_status, null from dual union all
select to_date('05/11/2016 18:00', 'dd/mm/yyyy dd24:mi') as timestamp, 2 as device_id, 'err' as dev_status, '00:00' from dual union all
select to_date('05/11/2016 18:30', 'dd/mm/yyyy dd24:mi') as timestamp, 2 as device_id, 'ok' as dev_status, null from dual union all
select to_date('05/11/2016 19:00', 'dd/mm/yyyy dd24:mi') as timestamp, 2 as device_id, 'err' as dev_status, '00:00' from dual union all
select to_date('05/11/2016 19:30', 'dd/mm/yyyy dd24:mi') as timestamp, 2 as device_id, 'err' as dev_status, '00:30' from dual union all
select to_date('06/11/2016 20:00', 'dd/mm/yyyy dd24:mi') as timestamp, 2 as device_id, 'ok' as dev_status, null from dual union all
select to_date('06/11/2016 20:30', 'dd/mm/yyyy dd24:mi') as timestamp, 2 as device_id, 'ok' as dev_status, null from dual



В столбце err_cntr_value выводится нарастающим итогом время работы в режиме err (ед.измерения часы, но можно любое, смогу перевести), но только в случаях, когда эти ошибки смежные, т.е. следуют друг за другом. в Других случаях (после возврата из состояния err в состояние ок) таймер обнуляется.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.01.2017, 14:20
    #39385444
evgenius_b
evgenius_b
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния
Всем спасибо, особенно env!
Поиск по форуму start_of_group помог корректно пронумеровать каждую группу изменений состояния каждого девайса в течение дня. Затем для каждой группы с меткой error вычислил нужное время (максимальный timestamp - минимальный timestamp).

Получилось что-то вроде этого (код для вертики):

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
with t as (
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '16' hour + interVal '30' minute as timestamp, 1 as device_id, 'true' as dev_status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '17' hour + interVal '00' minute as timestamp, 1 as device_id, 'true' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '17' hour + interVal '30' minute as timestamp, 1 as device_id, 'false' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '18' hour + interVal '00' minute as timestamp, 1 as device_id, 'false' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '18' hour + interVal '30' minute as timestamp, 1 as device_id, 'true' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '19' hour + interVal '00' minute as timestamp, 1 as device_id, 'true' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '19' hour + interVal '30' minute as timestamp, 1 as device_id, 'false' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '20' hour + interVal '00' minute as timestamp, 1 as device_id, 'true' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '20' hour + interVal '30' minute as timestamp, 1 as device_id, 'true' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '16' hour + interVal '00' minute as timestamp, 2 as device_id, 'true' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '16' hour + interVal '30' minute as timestamp, 2 as device_id, 'false' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '17' hour + interVal '00' minute as timestamp, 2 as device_id, 'true' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '17' hour + interVal '30' minute as timestamp, 2 as device_id, 'true' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '18' hour + interVal '00' minute as timestamp, 2 as device_id, 'false' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '18' hour + interVal '30' minute as timestamp, 2 as device_id, 'true' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '19' hour + interVal '00' minute as timestamp, 2 as device_id, 'false' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '19' hour + interVal '30' minute as timestamp, 2 as device_id, 'false' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '20' hour + interVal '00' minute as timestamp, 2 as device_id, 'false' as status from dual union all
select to_date('05/11/2016', 'DD/MM/YYYY')::timestamp + interVal '20' hour + interVal '30' minute as timestamp, 2 as device_id, 'true' as status from dual
)

, t1 as (
select tt.timestamp
, tt.device_id, tt.dev_status, max(rn) over (partition by device_id, dev_status order by timestamp) max_rn
from (
select t.*
, case when lag(dev_status) over (partition by device_id order by timestamp) <> dev_status then row_number() over (partition by device_id order by timestamp)
     when row_number() over (partition by device_id order by timestamp) = 1 then 1 
        else null 
        end rn 
from t
) tt
)
select t1.*, min(timestamp) over (partition by trunc(timestamp), device_id, dev_status, max_rn) st
, max(timestamp) over (partition by trunc(timestamp), device_id, dev_status, max_rn) et
, max(timestamp) over (partition by trunc(timestamp), device_id, dev_status, max_rn) -
   min(timestamp) over (partition by trunc(timestamp), device_id, dev_status, max_rn) err_cntr_value
from t1
order by 2,1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния / 7 сообщений из 7, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 486ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1886644]:
 0ms
get settings:
 8ms
get forum list:
 11ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 174ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 44ms
get tp. blocked users:
 1ms
others: 235ms
total:  486ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]