Помогите решить задачку / MySQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Помогите решить задачку

12 сообщений из 12, страница 1 из 1

Помогите решить задачку

#39942766

Timson4eg

Гость

В sql не силён, так что прошу помощи у вас.
Если маршрутизатор, который по netFlow сливает информацию о проходящем трафике на коллектор, который в свою очередь заливает все это в базу MySQL...
Вот таким запросом я считаю общий входящий и исходящий трафик.

SELECT (select ROUND (SUM(bytes)/1024/1024,2) FROM acct WHERE ip_dst LIKE '172.16.%' and stamp_updated like '2020-04-01%') as Входящий, (select ROUND (SUM(bytes)/1024/1024,2) FROM acct WHERE ip_src LIKE '172.16.%' and stamp_updated like '2020-04-01%') as Исходящий;
Получает вот так:

+------------------+--------------------+
| Входящий | Исходящий |
+------------------+--------------------+
| 59330.20 | 8787.16 |
+------------------+--------------------+

Хотелось ты получить такой же выхлоп, но только по каждому IP адресу... Перекопал уже много информации, но никак не могу сформировать правильный запрос.

...

Рейтинг:

0 / 0

01.04.2020, 11:16

| Ответить | Цитировать | Написать

Помогите решить задачку

#39942793

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

В студию:

- Структуру таблицы в формате CREATE TABLE
- Пример наполнения в формате INSERT INTO
- Требуемый результат для именно таких данных

Скрипты - обернуть тегом SRC. Таблицу - тегом Table (поля разделять запятыми). Перед публикацией проверить (кнопка "Просмотр").

...

Рейтинг:

0 / 0

01.04.2020, 12:48

| Ответить | Цитировать | Написать

Помогите решить задачку

#39942828

Timson4eg

Гость

Прошу прощение за не правильное оформление поста.

Вот что имеется:
Структура:

create table acct (
mac_src CHAR(17) NOT NULL,
mac_dst CHAR(17) NOT NULL,
ip_src CHAR(45) NOT NULL,
ip_dst CHAR(45) NOT NULL,
src_port INT(2) UNSIGNED NOT NULL,
dst_port INT(2) UNSIGNED NOT NULL,
ip_proto CHAR(6) NOT NULL,
packets INT UNSIGNED NOT NULL,
bytes BIGINT UNSIGNED NOT NULL,
stamp_inserted DATETIME NOT NULL,
stamp_updated DATETIME,
PRIMARY KEY (mac_src, mac_dst, ip_src, ip_dst, src_port, dst_port, ip_proto, stamp_inserted)
);

Примера наполнения у меня к сожалению нет, т.к. это делается не скрптом, а программным комплекосом так сказать...
Из того, что у меня получилось, это только посчитать общий входящий и исходящий трафик вот таким запросом:

Код: sql

SELECT  (select ROUND (SUM(bytes)/1024/1024,2) FROM acct WHERE ip_dst LIKE '172.16.%' and stamp_updated like '2020-04-01%') as Входящий,

Код: sql

 (select ROUND (SUM(bytes)/1024/1024,2) FROM acct WHERE ip_src LIKE '172.16.%' and stamp_updated like '2020-04-01%') as Исходящий;

Входящий Исходящий 89045.23 17348.44

На выходе хотелось бы иметь сумму трафика по адресам, которые присутствуют в колонке ip_dst.

Адрес Входящий Исходящий 172.16.0.1 1234545 123111 172.16.0.2 2345505 123222 172.16.0.3 7234555 123333

...

Рейтинг:

0 / 0

01.04.2020, 13:41

| Ответить | Цитировать | Написать

Помогите решить задачку

#39942850

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

Timson4eg

PRIMARY KEY (mac_src, mac_dst, ip_src, ip_dst, src_port, dst_port, ip_proto, stamp_inserted)

Ну ни хрена ж себе...

Timson4eg

На выходе хотелось бы иметь сумму трафика по адресам, которые присутствуют в колонке ip_dst.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

SELECT ip, ROUND(SUM(traf_out)/1024/1024, 2) traf_out, ROUND(SUM(traf_in)/1024/1024, 2) traf_in
FROM ( SELECT ip_dst ip, 0 traf_out, SUM(bytes) traf_in
       FROM acct 
       WHERE ip_dst LIKE '172.16.%' 
         AND DATE(stamp_updated) = '2020-04-01'
     UNION ALL 
       SELECT ip_src, SUM(bytes), 0 
       FROM acct 
       WHERE ip_src LIKE '172.16.%' 
         AND DATE(stamp_updated) = '2020-04-01'
     ) total
GROUP BY ip

...

Рейтинг:

0 / 0

01.04.2020, 14:18

| Ответить | Цитировать | Написать

Помогите решить задачку

#39942852

miksoft

Участник

Сообщения: 36 746

Рейтинг: 0 / 0

Akina

Timson4eg

PRIMARY KEY (mac_src, mac_dst, ip_src, ip_dst, src_port, dst_port, ip_proto, stamp_inserted)

Ну ни хрена ж себе...

Ну если бы были адекватные типы данных полей, то было бы нормально. А так - да...

...

Рейтинг:

0 / 0

01.04.2020, 14:26

| Ответить | Цитировать | Написать

Помогите решить задачку

#39942869

Timson4eg

Гость

Матерится...

#1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'pmacct.acct.ip_dst'; this is incompatible with sql_mode=only_full_group_by

...

Рейтинг:

0 / 0

01.04.2020, 14:54

| Ответить | Цитировать | Написать

Помогите решить задачку

#39942884

Timson4eg

Гость

Отключил sql_mode="NO_ENGINE_SUBSTITUTION"

Но выхлоп всеравно не тот :(

ip traf_out traf_in172.16.0.1 12345 0172.16.0.35 0 5678

...

Рейтинг:

0 / 0

01.04.2020, 15:48

| Ответить | Цитировать | Написать

Помогите решить задачку

#39942888

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

А, ну да, группировка в подзапросах-то...

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

SELECT ip, ROUND(SUM(traf_out)/1024/1024, 2) traf_out, ROUND(SUM(traf_in)/1024/1024, 2) traf_in
FROM ( SELECT ip_dst ip, 0 traf_out, SUM(bytes) traf_in
       FROM acct 
       WHERE ip_dst LIKE '172.16.%' 
         AND DATE(stamp_updated) = '2020-04-01'
       GROUP BY ip
     UNION ALL 
       SELECT ip_src, SUM(bytes), 0 
       FROM acct 
       WHERE ip_src LIKE '172.16.%' 
         AND DATE(stamp_updated) = '2020-04-01'
       GROUP BY ip
     ) total
GROUP BY ip

...

Рейтинг:

0 / 0

01.04.2020, 15:51

| Ответить | Цитировать | Написать

Помогите решить задачку

#39942897

Timson4eg

Гость

Теперь другая ошибка :(
#1054 - Unknown column 'ip' in 'group statement'

...

Рейтинг:

0 / 0

01.04.2020, 16:11

| Ответить | Цитировать | Написать

Помогите решить задачку

#39942912

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

SELECT ip, ROUND(SUM(traf_out)/1024/1024, 2) traf_out, ROUND(SUM(traf_in)/1024/1024, 2) traf_in
FROM ( SELECT ip_dst ip, 0 traf_out, SUM(bytes) traf_in
       FROM acct 
       WHERE ip_dst LIKE '172.16.%' 
         AND DATE(stamp_updated) = '2020-04-01'
       GROUP BY ip_dst 
     UNION ALL 
       SELECT ip_src, SUM(bytes), 0 
       FROM acct 
       WHERE ip_src LIKE '172.16.%' 
         AND DATE(stamp_updated) = '2020-04-01'
       GROUP BY ip_src
     ) total
GROUP BY ip

...

Рейтинг:

0 / 0

01.04.2020, 16:34

| Ответить | Цитировать | Написать

Помогите решить задачку

#39942953

Timson4eg

Гость

Спасибо огромное! Очень помогли!

Хотел еще поинтересоваться, что означают нули в коде?
0 traf_out и SUM(bytes), 0

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

SELECT ip, ROUND(SUM(traf_out)/1024/1024, 2) traf_out, ROUND(SUM(traf_in)/1024/1024, 2) traf_in
FROM ( SELECT ip_dst ip, 0 traf_out, SUM(bytes) traf_in
       FROM acct 
       WHERE ip_dst LIKE '172.16.%' 
         AND DATE(stamp_updated) = '2020-04-01'
       GROUP BY ip_dst 
     UNION ALL 
       SELECT ip_src, SUM(bytes), 0 
       FROM acct 
       WHERE ip_src LIKE '172.16.%' 
         AND DATE(stamp_updated) = '2020-04-01'
       GROUP BY ip_src
     ) total
GROUP BY ip

...

Рейтинг:

0 / 0

01.04.2020, 18:04

| Ответить | Цитировать | Написать

Помогите решить задачку

#39942971

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

Timson4eg

Хотел еще поинтересоваться, что означают нули в коде?

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

...

Рейтинг:

0 / 0

01.04.2020, 19:19

| Ответить | Цитировать | Написать

12 сообщений из 12, страница 1 из 1

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Помогите решить задачку

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

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

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=47&msg=39942766&tid=1828662]:	0ms
get settings:	7ms
get forum list:	13ms
check forum access:	3ms
check topic access:	3ms
track hit:	247ms
get topic data:	9ms
get forum data:	2ms
get page messages:	42ms
get tp. blocked users:	1ms
others:	297ms

total:	624ms