ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Среднее значение часа
13 сообщений из 13, страница 1 из 1
Среднее значение часа
    #39326445
fortress
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fortress
Участник
Имеется таблица в mysql для rsyslog, в ней есть таблица systemevents:
 
Код: 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.
mysql> show columns from systemevents;
+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| ID                 | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| CustomerID         | bigint(20)       | YES  |     | NULL    |                |
| ReceivedAt         | datetime         | YES  |     | NULL    |                |
| DeviceReportedTime | datetime         | YES  |     | NULL    |                |
| Facility           | smallint(6)      | YES  |     | NULL    |                |
| Priority           | smallint(6)      | YES  |     | NULL    |                |
| FromHost           | varchar(60)      | YES  |     | NULL    |                |
| Message            | text             | YES  |     | NULL    |                |
| NTSeverity         | int(11)          | YES  |     | NULL    |                |
| Importance         | int(11)          | YES  |     | NULL    |                |
| EventSource        | varchar(60)      | YES  |     | NULL    |                |
| EventUser          | varchar(60)      | YES  |     | NULL    |                |
| EventCategory      | int(11)          | YES  |     | NULL    |                |
| EventID            | int(11)          | YES  |     | NULL    |                |
| EventBinaryData    | text             | YES  |     | NULL    |                |
| MaxAvailable       | int(11)          | YES  |     | NULL    |                |
| CurrUsage          | int(11)          | YES  |     | NULL    |                |
| MinUsage           | int(11)          | YES  |     | NULL    |                |
| MaxUsage           | int(11)          | YES  |     | NULL    |                |
| InfoUnitID         | int(11)          | YES  |     | NULL    |                |
| SysLogTag          | varchar(60)      | YES  |     | NULL    |                |
| EventLogType       | varchar(60)      | YES  |     | NULL    |                |
| GenericFileName    | varchar(60)      | YES  |     | NULL    |                |
| SystemID           | int(11)          | YES  |     | NULL    |                |
| processid          | varchar(60)      | NO   |     |         |                |
| checksum           | int(11) unsigned | NO   |     | 0       |                |
+--------------------+------------------+------+-----+---------+----------------+



Для некоторых событий из логов я решил оценить в какое время суток они обычно появляются.
Например, имеется 6 строк:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
mysql> select receivedat from systemevents where syslogtag = 'rsyslogd:' limit 6;
+---------------------+
| receivedat          |
+---------------------+
| 2015-05-20 19:15:33 |
| 2015-05-20 19:15:33 |
| 2015-05-20 19:21:02 |
| 2015-05-21 06:25:02 |
| 2015-05-22 06:25:04 |
| 2015-05-23 06:25:06 |
+---------------------+
6 rows in set (0.01 sec)


Мне нужно получить среднее время суток, когда происходят эти события (пример не очень удачный, т.к. данные разбросаны сильно и среднее ничего толком не скажет).
Вот что первое пришло в голову:
Код: sql
1.
2.
3.
4.
5.
6.
7.
mysql> select avg(hour(receivedat)) from systemevents where syslogtag = 'rsyslogd:' limit 6;
+-----------------------+
| avg(hour(receivedat)) |
+-----------------------+
|                6.1222 |
+-----------------------+
1 row in set (0.08 sec)


Я полагал, что hour вернет значение часа как целое число, и дальше по нему должно посчитаться среднее и должно получиться 12:30 (примерно).
Думаю, может hour возвращает не целое, ок, пробую так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
mysql> select avg(cast(hour(receivedat) as unsigned)) from systemevents where syslogtag = 'rsyslogd:' limit 6;
+-----------------------------------------+
| avg(cast(hour(receivedat) as unsigned)) |
+-----------------------------------------+
|                                  6.1222 |
+-----------------------------------------+
1 row in set (0.07 sec)


Результат не изменился. Видимо имеет место быть игра теней особенность работы с типами дата-время.
Поиск в интернете пока навел на нечто такое (тут должна получиться среднее дата-время):
Код: sql
1.
2.
3.
4.
5.
6.
7.
mysql> select from_unixtime(avg(unix_timestamp(receivedat))) from systemevents where syslogtag = 'rsyslogd:' limit 6;
+------------------------------------------------+
| from_unixtime(avg(unix_timestamp(receivedat))) |
+------------------------------------------------+
| 2016-01-21 08:06:37                            |
+------------------------------------------------+
1 row in set (0.07 sec)


Вроде бы тоже на правду не очень похоже.
Мне хочется странного или действиельно получить среднее значение времени не тривиальная задача?
...
Рейтинг: 0 / 0
13.10.2016, 16:25
| Ответить | Цитировать | Написать  
Среднее значение часа
    #39326455
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
fortress,

avg - это арифметическое среднее, sum / count. Поэтому avg дробные значения может возвращать.

Но уточнить другое хочу:
Код: sql
1.
select avg(hour(receivedat)) from systemevents where syslogtag = 'rsyslogd:' limit 6;


вот этот limit 6 остался по ошибке с прошлого запроса или вы предполагаете, что будут выбраны данные только 6 каких-то первых-попавшихся строк?
...
Рейтинг: 0 / 0
13.10.2016, 16:32
| Ответить | Цитировать | Написать  
Среднее значение часа
    #39326463
fortress
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fortress
Участник
limit 6 - просто для примера, чтобы можно было правильность результата оценить на пальцах.
...
Рейтинг: 0 / 0
13.10.2016, 16:37
| Ответить | Цитировать | Написать  
Среднее значение часа
    #39326470
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
fortresslimit 6 - просто для примера, чтобы можно было правильность результата оценить на пальцах.Ну чтобы пальцы при каждом запуске были одни и те же, то надо как-то так:
Код: sql
1.
select receivedat from systemevents where syslogtag = 'rsyslogd:' order by receivedat limit 6

Код: sql
1.
select avg(hour(receivedat)) from (select receivedat from systemevents where syslogtag = 'rsyslogd:' order by receivedat limit 6)

Т.е. чтобы контрольные данные и результат из одних и тех же записей брались, а не из разных.
...
Рейтинг: 0 / 0
13.10.2016, 16:40
| Ответить | Цитировать | Написать  
Среднее значение часа
    #39326473
fortress
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fortress
Участник
Для определенности возьмем данные из упорядоченного набора строк:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
mysql> select (receivedat) from systemevents where syslogtag = 'rsyslogd:' order by receivedat desc limit 6;
+---------------------+
| receivedat          |
+---------------------+
| 2016-10-13 06:25:02 |
| 2016-10-12 06:25:01 |
| 2016-10-11 06:25:01 |
| 2016-10-10 06:25:02 |
| 2016-10-09 06:25:05 |
| 2016-10-09 06:25:05 |
+---------------------+
6 rows in set (0.08 sec)

mysql> select from_unixtime(avg(unix_timestamp(receivedat))) from systemevents where syslogtag = 'rsyslogd:' order by receivedat desc limit 6;
+------------------------------------------------+
| from_unixtime(avg(unix_timestamp(receivedat))) |
+------------------------------------------------+
| 2016-01-21 08:06:37                            |
+------------------------------------------------+
1 row in set (0.07 sec)
...
Рейтинг: 0 / 0
13.10.2016, 16:44
| Ответить | Цитировать | Написать  
Среднее значение часа
    #39326474
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
fortressДля некоторых событий из логов я решил оценить в какое время суток они обычно появляются.Я бы для этого предложил строить распределение по часам, дням недели, дням месяца и т.п. Вдруг, например, там несколько пиков в течение суток или, например, по понедельникам.
...
Рейтинг: 0 / 0
13.10.2016, 16:45
| Ответить | Цитировать | Написать  
Среднее значение часа
    #39326475
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
fortress
Код: sql
1.
select from_unixtime(avg(unix_timestamp(receivedat))) from systemevents where syslogtag = 'rsyslogd:' order by receivedat desc limit 6

Это не так работает, как вы думаете. LIMIT сработает после вычисления среднего, а не до.
...
Рейтинг: 0 / 0
13.10.2016, 16:46
| Ответить | Цитировать | Написать  
Среднее значение часа
    #39326478
fortress
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fortress
Участник
miksoftfortressДля некоторых событий из логов я решил оценить в какое время суток они обычно появляются.Я бы для этого предложил строить распределение по часам, дням недели, дням месяца и т.п. Вдруг, например, там несколько пиков в течение суток или, например, по понедельникам.

Так пожалуй даже правильней будет для решения конечной задачи, разве что иногда час может оказатся слишком большим прмежутком времени.
...
Рейтинг: 0 / 0
13.10.2016, 16:49
| Ответить | Цитировать | Написать  
Среднее значение часа
    #39326489
fortress
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fortress
Участник
[/src][/quot]Это не так работает, как вы думаете. LIMIT сработает после вычисления среднего, а не до.[/quot]

Спасибо что указали, повод заглянуть в документацию.
...
Рейтинг: 0 / 0
13.10.2016, 16:56
| Ответить | Цитировать | Написать  
Среднее значение часа
    #39326583
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
fortressпример не очень удачный, т.к. данные разбросаны сильно и среднее ничего толком не скажетПример-то как раз ОЧЕНЬ удачный. Он заставляет задуматься над тем, насколько правильно (как по мне - так ни на грамм) поставлена задача.

Я бы рекомендовал для начала попробовать следующее - для каждой записи посчитать количество записей в течение, скажем, 1 часа после этой записи (надеюсь, дискретность в 1 час - достаточна?), или там от минус получаса до плюс получаса. И взять среднее для того часового промежутка, где количество максимально...
...
Рейтинг: 0 / 0
13.10.2016, 18:59
| Ответить | Цитировать | Написать  
Среднее значение часа
    #39326843
fortress
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fortress
Участник
Akina, да, возможно.

Если переходить к самой задаче, то он примерно в следующем. Есть сервис к которому подключаются периодически. Ротуер логирует факты подключения в rsyslog. Я решил проверить, в какое примерно время происходит подключение. Также могу добавить что подключаются примерно раз в сутки и одно подключение состоит из нескольких соединений, каждое из которых логируется (TCP SYN).
...
Рейтинг: 0 / 0
14.10.2016, 10:32
| Ответить | Цитировать | Написать  
Среднее значение часа
    #39328051
fortress
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fortress
Участник
Получился такой запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select date(ReceivedAt),sec_to_time(avg(time_to_sec(ReceivedAt))),left(Message,7)
from systemevents 
where 
SysLogTag like 'fire%' 
and Message like '%dev%'
and ReceivedAt between '2016-10-13' and '2016-10-18'
group by 1,3
order by ReceivedAt desc;


Проблема была в том, как Mysql приводит datetime (и time) к целому числу:
Код: sql
1.
2.
3.
select current_time(),current_time()+0;
result:
'11:14:02', '111402.000000'


Для операций поиска среднего такой способ приведения к целому числу не подходит. Для это лучше подойдут функции time_to_sec() и sec_to_time(). Они переводят время в целое количество секунд. Имея столбец таких значений можно для них оценить среднее и преобразовать его обратно к типу время.

Код: sql
1.
select from_unixtime(avg(unix_timestamp(receivedat))) from systemevents where syslogtag = 'rsyslogd:' order by receivedat


Этот запрос корректно работает именно с datetime.
...
Рейтинг: 0 / 0
17.10.2016, 11:16
| Ответить | Цитировать | Написать  
Среднее значение часа
    #39328092
fortress
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fortress
Участник
fortress
Код: sql
1.
select from_unixtime(avg(unix_timestamp(receivedat))) from systemevents where syslogtag = 'rsyslogd:' order by receivedat


Этот запрос корректно работает именно с datetime.

Точнее не так. Этот приём тоже можно использовать в моем случае, но при условии, что есть группировка по дате. Иначе результат тоже будет корректный, но менее полезный.
Вот, например, видно что результат будет аналогичный:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
select 
    date(ReceivedAt),
    sec_to_time(avg(time_to_sec(ReceivedAt))),
    time(from_unixtime(avg(unix_timestamp(ReceivedAt)))),
    min(time(receivedat)),
    max(time(receivedat)),
    count(receivedat),
    substring_index(substring_index(substring_index(Message,',',4),',',-1),':',1) as ip,
    left(Message,7)
from systemevents 
where 
SysLogTag like 'fire%' 
and fromhost='192.168.105.2'
and Message like '%dev%'
and ReceivedAt between '2016-10-01' and '2016-10-18'
group by 1,7,8
order by ReceivedAt desc;
result:
'2016-10-17', '08:52:06', '08:52:06', '08:47:22', '08:57:45', '7', ' 185.34.152.157', ' dev1'
'2016-10-17', '08:42:22', '08:42:22', '08:38:56', '08:46:48', '5', ' 185.34.152.157', ' dev2'
'2016-10-14', '08:57:12', '08:57:12', '08:53:47', '09:01:44', '7', ' 185.34.152.157', ' dev1'
'2016-10-14', '08:48:32', '08:48:32', '08:40:40', '08:53:33', '12', ' 185.34.152.157', ' dev2'
'2016-10-13', '12:29:33', '12:29:33', '12:29:25', '12:29:41', '2', ' 185.34.152.157', ' dev2'
...
Рейтинг: 0 / 0
17.10.2016, 12:06
| Ответить | Цитировать | Написать  
13 сообщений из 13, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Среднее значение часа
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&tid=1831317]:
 0ms
get settings:
 10ms
get forum list:
 20ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 182ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 49ms
get tp. blocked users:
 1ms
others: 209ms
total:  491ms
созд. / загр.: 491ms
x
x
Закрыть


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