ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния
7 сообщений из 7, страница 1 из 1
помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния
    #39384151
evgenius_b
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
evgenius_b
Участник
Всем привет.
Есть набор датчиков (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, 13:56
| Ответить | Цитировать | Написать  
помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния
    #39384212
Фотография env
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
env
Участник
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, 14:41
| Ответить | Цитировать | Написать  
помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния
    #39384274
evgenius_b
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
evgenius_b
Участник
env,
ага, спасибо, почти то, что нужно. В предложенном решении показывается длительность каждого интервала в состоянии false, осталось только просуммировать полученные значения.
Я правильно понял принцип - сначала в дополнительном столбце grp проставляем номер группы следующих друг за другом смен состояний по каждому девайсу, затем суммируем по номеру группы и девайсу?
...
Рейтинг: 0 / 0
13.01.2017, 15:31
| Ответить | Цитировать | Написать  
помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния
    #39384362
Фотография env
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
env
Участник
evgenius_b,

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

и поищи по форуму start_of_group, т.к. два row_number не всегда дадут корректную группу
...
Рейтинг: 0 / 0
13.01.2017, 17:01
| Ответить | Цитировать | Написать  
помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния
    #39384397
evgenius_b
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
evgenius_b
Участник
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
13.01.2017, 17:19
| Ответить | Цитировать | Написать  
помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния
    #39385444
evgenius_b
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
evgenius_b
Участник
Всем спасибо, особенно 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
16.01.2017, 14:20
| Ответить | Цитировать | Написать  
7 сообщений из 7, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / помогите средствами вендоронезависимого ANSI SQL-99 посчитать длительность состояния
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&tid=1886644]:
 0ms
get settings:
 7ms
get forum list:
 10ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 154ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 35ms
get tp. blocked users:
 1ms
others: 242ms
total:  462ms
созд. / загр.: 462ms
x
x
Закрыть


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