ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / выборка диапазонов из списка дат
8 сообщений из 8, страница 1 из 1
выборка диапазонов из списка дат
    #39358943
2Garin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
2Garin
Гость
привет всем
есть мониторинг нескольких процессов, который пишет лог - включена система или нет, для этого создана вот такая табличка (MySQL 5.7):
Код: sql
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE `log` (
  `log_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `system_id` varchar(255) NOT NULL,
  `date` datetime DEFAULT CURRENT_TIMESTAMP,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;



в неё раз в минуту пишется статус по системам, в результате имеем примерно такой набор данных:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
1	12	2016-11-23 05:43:01	0
2	12	2016-11-23 05:44:02	1
3	12	2016-11-23 05:45:01	1
4	12	2016-11-23 05:46:02	1
5	12	2016-11-23 05:47:01	0
6	12	2016-11-23 05:48:01	0
7	12	2016-11-23 05:49:01	0
8	12	2016-11-23 05:50:01	0
9	12	2016-11-23 05:51:01	1
10	12	2016-11-23 05:52:01	1
11	12	2016-11-23 05:53:02	1
12	12	2016-11-23 05:54:01	1
13	12	2016-11-23 05:55:01	1
14	12	2016-11-23 05:56:01	0
15	12	2016-11-23 05:57:01	0



как можно выбрать диапазоны активности, т.е. в примере это:
2016-11-23 05:44:02 - 2016-11-23 05:46:02
и
2016-11-23 05:51:01 - 2016-11-23 05:55:01

единственная идея у меня - это добавить отдельное поле для номера группы и триггер на insert, и смотреть что было ранее 0 или 1, а в новое поле писать номер группы, и потом уже брать первую и последнюю запись в группе

но наверн жеж можно и просто select-ом достать?
...
Рейтинг: 0 / 0
01.12.2016, 21:58
| Ответить | Цитировать | Написать  
выборка диапазонов из списка дат
    #39358960
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
2Garinно наверн жеж можно и просто select-ом достать?
Можно. Но на большом объёме данных всё встанет колом...

2Garinидея у меня - это добавить отдельное поле для номера группы и триггер на insert, и смотреть что было ранее 0 или 1, а в новое поле писать номер группы, и потом уже брать первую и последнюю запись в группеНе очень идея... какой смысл делать наполовину? лучше уж сразу получать готовый список таких периодов, с посчитанными началом и концом. Сотвори таблицу этих своих "диапазонов активности", и в триггере добавляй (если период начался) или обновляй (если продолжается) запись.
...
Рейтинг: 0 / 0
01.12.2016, 22:33
| Ответить | Цитировать | Написать  
выборка диапазонов из списка дат
    #39358973
2Garin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
2Garin
Гость
идея в принципе хорошая, по поводу того, чтобы диапазон сразу писать, подумаю
данных действительно получается много, при ежеминутном мониторинге, разовая выборка максимум будет за месяц, но всёже это тоже совсем не мало

тут есть проблемка: по хорошему нужно совсем каждую запись анализировать, на предмет того, что между ними разница не более минуты, т.к. обеспечить 100% аптайм мониторинга не получится, и если "свет моргнул", мы никогда не узнаем что в течении часа запись не велась, т.к. в диапазоне это не отразится
для этого можно попробовать выбрать только активные записи, и рассчитывать разницу между каждой, но это тоже совсем как не оптимально
...
Рейтинг: 0 / 0
01.12.2016, 22:58
| Ответить | Цитировать | Написать  
выборка диапазонов из списка дат
    #39358983
2Garin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
2Garin
Гость
хотя если мониторинг какое то время не велся, как раз можно считать разницу во времени между записями, и если она скажем больше минуты - закрывать старую старым временем, и открывать новую...
...
Рейтинг: 0 / 0
01.12.2016, 23:16
| Ответить | Цитировать | Написать  
выборка диапазонов из списка дат
    #39358984
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Не вижу проблемы. Просто в триггере берём из таблицы самую "свежую запись" и считаем разницу от неё. Если там более полутора минут, то действуем так, будто начался новый период.

Альтернативный вариант - расширить таблицу периодов, введя туда ещё и поле статуса (активный/неактивный/неизвестный).
...
Рейтинг: 0 / 0
01.12.2016, 23:23
| Ответить | Цитировать | Написать  
выборка диапазонов из списка дат
    #39359279
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
2Garin ,

для MS SQL решение довольно простое, неоднократно предлагавшееся ранее - перенумеровать все строки последовательно и перенумеровать строки внутри групп с одинаковым статусом, затем вычесть второй номер из первого - получим инвариант периода непрерывности.
Далее, например, группировка по инвариантам с отсечение ненужных диапазонов
В MySQL не хочу возиться с переменнымим для нумерации строк, извините.
Но как вариант - вполне допустимо, особенно если отсечь по индекс-фильтру только требуемый диапазон строк таблицы
 
решение в MS SQL
Код: 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.
;with log as (
  select * from( values
    (1, 12, '2016-11-23 05:43:01', 0),
    (2, 12, '2016-11-23 05:44:02', 1),
    (3, 12, '2016-11-23 05:45:01', 1),
    (4, 12, '2016-11-23 05:46:02', 1),
    (5, 12, '2016-11-23 05:47:01', 0),
    (6, 12, '2016-11-23 05:48:01', 0),
    (7, 12, '2016-11-23 05:49:01', 0),
    (8, 12, '2016-11-23 05:50:01', 0),
    (9, 12, '2016-11-23 05:51:01', 1),
    (10, 12, '2016-11-23 05:52:01', 1),
    (11, 12, '2016-11-23 05:53:02', 1),
    (12, 12, '2016-11-23 05:54:01', 1),
    (13, 12, '2016-11-23 05:55:01', 1),
    (14, 12, '2016-11-23 05:56:01', 0),
    (15, 12, '2016-11-23 05:57:01', 0)
  )values_log(log_id,system_id,date,status))
, renum as (
  select *
    , row_number()over(order by log_id)
    - row_number()over(partition by status order by log_id)idd
  from log 
  )
select
    min(l.log_id)log_np
  , min(l.date)time_np
  , max(l.log_id)log_kp
  , max(l.date)time_kp 
from renum l
group by l.idd
having min(l.status)=1
order by l.idd


log_nptime_nplog_kptime_kp22016-11-23 05:44:0242016-11-23 05:46:0292016-11-23 05:51:01132016-11-23 05:55:01

 
таблица с двойной нумерацией
log_idsystem_iddatestatusnppnpsidd1122016-11-23 05:43:0101105122016-11-23 05:47:0105236122016-11-23 05:48:0106337122016-11-23 05:49:0107438122016-11-23 05:50:01085314122016-11-23 05:56:010146815122016-11-23 05:57:01015782122016-11-23 05:44:0212113122016-11-23 05:45:0113214122016-11-23 05:46:0214319122016-11-23 05:51:01194510122016-11-23 05:52:011105511122016-11-23 05:53:021116512122016-11-23 05:54:011127513122016-11-23 05:55:0111385
...
Рейтинг: 0 / 0
02.12.2016, 13:12
| Ответить | Цитировать | Написать  
выборка диапазонов из списка дат
    #39360078
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
Cygapb-007В MySQL не хочу возиться с переменнымим для нумерации строк, извините.


...а чё извинятся то... не хочешь делать на переменных - делай по другому...

...на переменных -- достаточно легко и прямолинейно.

Сама по себе задача -- курсорная -- решение требует значения
из "предыдшей" строчки.

СКЛ -- как язык работы с сетами, НЕ
расчитан на расчеты "предыдуших" строк.
Конечно можно на каждой строчке делать подселект для
нахождения ближайней предыдушей... ну успехов...чё...
...
Рейтинг: 0 / 0
04.12.2016, 17:24
| Ответить | Цитировать | Написать  
выборка диапазонов из списка дат
    #39372939
2Garin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
2Garin
Гость
оставлю готовое решение для истории:
 
MySQL
Код: 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.
CREATE TABLE IF NOT EXISTS `period` (
  `period_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `system_id` varchar(255) NOT NULL,
  `period_start` datetime DEFAULT NULL,
  `period_end` datetime DEFAULT NULL,
  PRIMARY KEY (`period_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER //
CREATE TRIGGER `set_period`
AFTER INSERT ON `log`
FOR EACH ROW
  BEGIN
    IF NEW.status = 1
    THEN
      SET @PERIOD_ID = (SELECT period_id
                        FROM period
                        WHERE user_id = NEW.user_id AND period_end >= NEW.date - INTERVAL 90 SECOND
                        ORDER BY period_id DESC
                        LIMIT 1);

      IF (ISNULL(@PERIOD_ID))
      THEN
        INSERT INTO period (user_id, period_start, period_end) VALUES (NEW.user_id, NEW.date, NEW.date);
      ELSE
        UPDATE period
        SET period_end = (NEW.date)
        WHERE period_id = @PERIOD_ID;
      END IF;
    END IF;
  END;//
DELIMITER ;


...
Рейтинг: 0 / 0
22.12.2016, 14:00
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / выборка диапазонов из списка дат
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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