ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Запрос для получения длительности состояния датчика
9 сообщений из 9, страница 1 из 1
Запрос для получения длительности состояния датчика
    #39708156
Borris
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Borris
Гость
В базу пишется время и состояние датчика 1 или 0
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
   
 probe           time       value
Датчик1 2018-09-26 08:26:00   1
Датчик1 2018-09-26 08:25:00   1
Датчик1 2018-09-26 08:24:00   1
Датчик1 2018-09-26 08:23:00   1
Датчик1 2018-09-26 08:22:00   0
Датчик1 2018-09-26 08:21:00   0
Датчик1 2018-09-26 08:20:00   0
Датчик1 2018-09-26 08:19:00   1
Датчик1 2018-09-26 08:18:00   1
Датчик1 2018-09-26 08:17:00   0



Последнее полученное состояние считается текущим. Каким запросом мне посчитать как долго он находится в этом состоянии?
Чтобы получилось нечто
Код: sql
1.
2.
 probe   duration
Датчик1      3
...
Рейтинг: 0 / 0
26.09.2018, 08:53
| Ответить | Цитировать | Написать  
Запрос для получения длительности состояния датчика
    #39708175
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lonepsycho
Участник
Borris,

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

Код: 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.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
WITH vals AS (
  SELECT 
    v.probe,
    v.time,
    v.value
  FROM (
    VALUES 
      ('Датчик1'::TEXT, '2018-09-26 08:26:00'::TIMESTAMP,   1::INTEGER),
      ('Датчик1', '2018-09-26 08:25:00',   1),
      ('Датчик1', '2018-09-26 08:24:00',   1),
      ('Датчик1', '2018-09-26 08:23:00',   1),
      ('Датчик1', '2018-09-26 08:22:00',   0),
      ('Датчик1', '2018-09-26 08:21:00',   0),
      ('Датчик1', '2018-09-26 08:20:00',   0),
      ('Датчик1', '2018-09-26 08:19:00',   1),
      ('Датчик1', '2018-09-26 08:18:00',   1),
      ('Датчик1', '2018-09-26 08:17:00',   0)
  ) AS v(probe, "time", "value")
),
curr_val AS (
  SELECT DISTINCT ON (v.probe)
    v.probe,
    v.time,
    v.value
  FROM vals AS v
  ORDER BY
    v.probe,
    v.time DESC
)
SELECT 
  c.probe,
  t.time_from,
  c.time AS time_to,
  c.value,
  c.time - t.time_from AS duration 
FROM
  curr_val AS c
  LEFT JOIN LATERAL
  (
    SELECT
      min(v.time) AS time_from
    FROM
      vals AS v
    WHERE
      v.probe = c.probe AND
      v.time > (
        SELECT
          max(vi.time)
        FROM
          vals AS vi
        WHERE
          vi.time < c.time AND
          vi.probe = c.probe AND
          vi.value != c.value
        )
  ) AS t
  ON TRUE
...
Рейтинг: 0 / 0
26.09.2018, 09:34
| Ответить | Цитировать | Написать  
Запрос для получения длительности состояния датчика
    #39708178
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lonepsycho
Участник
Borris,

и да, надо подкорректировать чтобы отрабатывался вариант когда от датчика получено лишь одно значение.
...
Рейтинг: 0 / 0
26.09.2018, 09:36
| Ответить | Цитировать | Написать  
Запрос для получения длительности состояния датчика
    #39708196
Borris
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Borris
Гость
LonepsychoBorris,

и да, надо подкорректировать чтобы отрабатывался вариант когда от датчика получено лишь одно значение. Спасибо!
а в принципе если одно значение то наверное и правильно что длительность Null
...
Рейтинг: 0 / 0
26.09.2018, 09:55
| Ответить | Цитировать | Написать  
Запрос для получения длительности состояния датчика
    #39708358
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lonepsycho
Участник
Borris,

это уже вам решать, логика вам известна, но по моему это не правильно.
...
Рейтинг: 0 / 0
26.09.2018, 12:22
| Ответить | Цитировать | Написать  
Запрос для получения длительности состояния датчика
    #39708631
Владимир Затуливетер
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Затуливетер
Участник
Вот вариант через lateral, но начальное время берется от предыдущего статуса устройства.
Код: 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.
41.
42.
43.
44.
45.
46.
47.
with cteData 
as (
  select *
  from (
    values
      ('Датчик1'::text, '2018-09-26 08:26:00'::timestamp,   1::integer),
      ('Датчик1', '2018-09-26 08:25:00',   1),
      ('Датчик1', '2018-09-26 08:24:00',   1),
      ('Датчик1', '2018-09-26 08:23:00',   1),
      ('Датчик1', '2018-09-26 08:22:00',   0),
      ('Датчик1', '2018-09-26 08:21:00',   0),
      ('Датчик1', '2018-09-26 08:20:00',   0),
      ('Датчик1', '2018-09-26 08:19:00',   1),
      ('Датчик1', '2018-09-26 08:18:00',   1),

      ('Датчик2', '2018-09-26 08:50:00',   0),
      ('Датчик2', '2018-09-26 08:40:00',   0),
      ('Датчик2', '2018-09-26 08:30:00',   0),
      ('Датчик2', '2018-09-26 08:25:00',   1),
      ('Датчик2', '2018-09-26 08:20:00',   1),
      ('Датчик2', '2018-09-26 08:15:00',   0)
  ) as t(probe_id, time, state)
),
cteProbs ( probe_id )
as (
              select 'Датчик1'
    union all select 'Датчик2'
)
select p.probe_id, lst.state as last_state, prev.time as prev_time, lst.time as last_time
from cteProbs p
    -- получаем последнее состояние устройства 
    cross join lateral ( 
        select d.state, d.time 
        from cteData d 
        where d.probe_id = p.probe_id 
        order by d.time desc 
        limit 1
    ) lst
    -- поучаем время когда устройство было в другом состоянии
    cross join lateral (
        select d.time
        from cteData d
        where d.probe_id = p.probe_id 
          and d.state = case lst.state when 1 then 0 else 1 end
        order by time desc 
        limit 1
    ) prev


Если принципиально иметь стартовое время в текущем статусе, то тогда придется еще раз дернуть таблицу:
Код: 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.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
with cteData 
as (
  select *
  from (
    values
      ('Датчик1'::text, '2018-09-26 08:26:00'::timestamp,   1::integer),
      ('Датчик1', '2018-09-26 08:25:00',   1),
      ('Датчик1', '2018-09-26 08:24:00',   1),
      ('Датчик1', '2018-09-26 08:23:00',   1),
      ('Датчик1', '2018-09-26 08:22:00',   0),
      ('Датчик1', '2018-09-26 08:21:00',   0),
      ('Датчик1', '2018-09-26 08:20:00',   0),
      ('Датчик1', '2018-09-26 08:19:00',   1),
      ('Датчик1', '2018-09-26 08:18:00',   1),

      ('Датчик2', '2018-09-26 08:50:00',   0),
      ('Датчик2', '2018-09-26 08:40:00',   0),
      ('Датчик2', '2018-09-26 08:30:00',   0),
      ('Датчик2', '2018-09-26 08:25:00',   1),
      ('Датчик2', '2018-09-26 08:20:00',   1),
      ('Датчик2', '2018-09-26 08:15:00',   0)
  ) as t(probe_id, time, state)
),
cteProbs ( probe_id )
as (
              select 'Датчик1'
    union all select 'Датчик2'
)
select p.probe_id, lst.state as last_state, strt.time as prev_time, lst.time as last_time
from cteProbs p
    -- получаем последнее состояние устройства 
    cross join lateral ( 
        select d.state, d.time 
        from cteData d 
        where d.probe_id = p.probe_id 
        order by d.time desc 
        limit 1
    ) lst
    -- поучаем время когда устройство было в другом состоянии
    cross join lateral (
        select d.time
        from cteData d
        where d.probe_id = p.probe_id 
          and d.state = case lst.state when 1 then 0 else 1 end
        order by time desc 
        limit 1
    ) prev
    -- поучаем время когда устройство начало быть в текущем состоянии
    cross join lateral (
        select d.time
        from cteData d
        where d.probe_id = p.probe_id 
          and d.state = lst.state
          and d.time >= prev.time
        order by time asc 
        limit 1
    ) strt



на таблице [cteData] должен быть индекс
Код: sql
1.
create index idx_data__probe_id__time__state on data (probe_id, time, state)
...
Рейтинг: 0 / 0
26.09.2018, 16:36
| Ответить | Цитировать | Написать  
Запрос для получения длительности состояния датчика
    #39709048
p2.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
p2.
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select probe, max(time) - min(time) dura
from (
  select probe, time, value, 
    row_number() over(partition by probe order by time desc) r1,
    row_number() over(partition by probe, value order by time desc) r2
  from таблица
) t
where r1=r2
group by probe;
...
Рейтинг: 0 / 0
27.09.2018, 09:56
| Ответить | Цитировать | Написать  
Запрос для получения длительности состояния датчика
    #39709230
Владимир Затуливетер
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Затуливетер
Участник
p2.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select probe, max(time) - min(time) dura
from (
  select probe, time, value, 
    row_number() over(partition by probe order by time desc) r1,
    row_number() over(partition by probe, value order by time desc) r2
  from таблица
) t
where r1=r2
group by probe;



Оно может и красиво и лаконично, но требует скана всей таблицы, что в свою очередь будет очень тормознуто на больших объемах данных.
...
Рейтинг: 0 / 0
27.09.2018, 12:55
| Ответить | Цитировать | Написать  
Запрос для получения длительности состояния датчика
    #39709319
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Владимир Затуливетер,

я бы думал (по постановке), что "датчик находится в текущем состоянии" на время now()

но почему-то все считают что вне бд времени не существует
...
Рейтинг: 0 / 0
27.09.2018, 14:35
| Ответить | Цитировать | Написать  
9 сообщений из 9, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Запрос для получения длительности состояния датчика
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39708358&tid=1995570]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 48ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 46ms
get tp. blocked users:
 1ms
others: 267ms
total:  404ms
созд. / загр.: 404ms
x
x
Закрыть


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