Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Среднее значение часа / 13 сообщений из 13, страница 1 из 1
13.10.2016, 16:25
    #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:32
    #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:37
    #39326463
fortress
fortress
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Среднее значение часа
limit 6 - просто для примера, чтобы можно было правильность результата оценить на пальцах.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2016, 16:40
    #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:44
    #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:45
    #39326474
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Среднее значение часа
fortressДля некоторых событий из логов я решил оценить в какое время суток они обычно появляются.Я бы для этого предложил строить распределение по часам, дням недели, дням месяца и т.п. Вдруг, например, там несколько пиков в течение суток или, например, по понедельникам.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2016, 16:46
    #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:49
    #39326478
fortress
fortress
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Среднее значение часа
miksoftfortressДля некоторых событий из логов я решил оценить в какое время суток они обычно появляются.Я бы для этого предложил строить распределение по часам, дням недели, дням месяца и т.п. Вдруг, например, там несколько пиков в течение суток или, например, по понедельникам.

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

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

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

Если переходить к самой задаче, то он примерно в следующем. Есть сервис к которому подключаются периодически. Ротуер логирует факты подключения в rsyslog. Я решил проверить, в какое примерно время происходит подключение. Также могу добавить что подключаются примерно раз в сутки и одно подключение состоит из нескольких соединений, каждое из которых логируется (TCP SYN).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2016, 11:16
    #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, 12:06
    #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
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Среднее значение часа / 13 сообщений из 13, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 554ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1831317]:
 0ms
get settings:
 6ms
get forum list:
 22ms
check forum access:
 5ms
check topic access:
 5ms
track hit:
 199ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 64ms
get tp. blocked users:
 1ms
others: 242ms
total:  554ms
x
x
Закрыть


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