Создания сложного запроса по таблице (MySQL)

[Serg79]

Есть таблица хранящая данные о трафике, структура таблицы следующая:

Код:

create table traffic
(
        /* хранит время выгрузки */
        date datetime not null,
        /* IP источника (0 - клиент, -1 - не используется)  */
        srcIp int unsigned not null,
        /* IP назначения (0 - клиент, -1 - не используется) */
        dstIp int unsigned not null,
        /* порт источника */
        srcPort int not null,
        /* порт назначения */
        dstPort int not null,
        /* протокол (1 - tcp, 2 - udp, 3 - icmp, 4 - igmp, -1 - unkn) */
        protoID tinyint not null,
        /* количество данных */
        bytes int not null,
        /* полный размер */
        psize int not null, 
        primary key (date, srcIp, dstIp, srcPort, dstPort, protoID),
        index (bytes), index (psize)
) type = MyISAM;

Вот как это выглядит:
mysql> SELECT * FROM traffic LIMIT 5;
+---------------------+------------+------------+---------+---------+---------+----------+----------+
| date                | srcIp      | dstIp      | srcPort | dstPort | protoID | bytes    | psize    |
+---------------------+------------+------------+---------+---------+---------+----------+----------+
| 2009-04-22 09:16:37 | 3232269169 | 3232269311 |       0 |     694 |       2 | 12363688 | 14337688 |
| 2009-04-22 09:16:37 | 3232269176 | 3232269311 |       0 |     694 |       2 | 12299337 | 14273281 |
| 2009-04-22 09:16:37 | 3232269325 | 3232269567 |       0 |     137 |       2 |  3945350 |  6154746 |
| 2009-04-22 09:16:37 | 3232240919 | 3232241011 |       0 |      22 |       1 |  2729744 |  3899848 |
| 2009-04-22 09:16:37 | 3232241011 | 3232240919 |      22 |       0 |       1 |  2266208 |  3367464 |
+---------------------+------------+------------+---------+---------+---------+----------+----------+
5 rows in set (0.00 sec)

Чтобы мне подсчитать количество 'TCP' трафика по каждому IP я формирую следующий запрос:
mysql> SELECT DISTINCT srcIp, SUM(bytes) AS bytesTCP, SUM(psize) AS psizeTCP
-> FROM traffic
-> WHERE protoID = 1
-> GROUP BY srcIp
-> ORDER BY psizeTCP DEST
-> LIMIT 5;
+------------+------------+------------+
| srcIp      | bytesTCP   | psizeTCP   |
+------------+------------+------------+
| 3232241011 | 3464714668 | 3597061424 |
| 3232240655 |  452955261 |  472668361 |
| 3232240790 |  148848479 |  154294187 |
| 1596266742 |   51989241 |   61140709 |
| 3232240919 |   22654422 |   33675174 |
+------------+------------+------------+
5 rows in set (0.01 sec)

Чтобы подсчитать то же самое только по 'UDP' трафику в значении WHERE protoID = 1 надо подставить номер протокола 'UDP', то есть заменить '1' на '2'. Подсчет других протоколов так же ничем не отличается.

Вопрос вот в чем, как мне составить запрос, что бы на выходе я получил значения по всем протоколам, то есть, что бы на выходе я имел следующую таблицу:
+-------+----------+----------+----------+----------+-----------+-----------+-----------+-----------+
| srcIp | bytesTCP | psizeTCP | bytesUDP | psizeUDP | bytesICMP | psizeICMP | bytesFULL | psizeFULL |
+-------+----------+----------+----------+----------+-----------+-----------+-----------+-----------+

Или это не возможно и для каждого протокола придется формировать новый запрос?

[Serg79]

Решил все проблемы вот такой запрос:
-----------------------
mysql> select distinct srcIp,
-> sum(if(protoID=1,bytes,0)) as bTCP,
-> sum(if(protoID=1,psize,0)) as pTCP,
-> sum(if(protoID=2,bytes,0)) as bUDP,
-> sum(if(protoID=2,psize,0)) as pUDP,
-> sum(if(protoID=3,bytes,0)) as bICMP,
-> sum(if(protoID=3,psize,0)) as pICMP,
-> sum(if(protoID=4,bytes,0)) as bIGMP,
-> sum(if(protoID=4,psize,0)) as pIGMP,
-> sum(if(protoID=-1,bytes,0)) as bUNKN,
-> sum(if(protoID=-1,psize,0)) as pUNKN,
-> sum(bytes) as bFULL,
-> sum(psize) as pFULL
-> from traffic
-> where srcIp != 0
-> group by srcIp
-> order by pFULL desc
-> limit 10;
-----------------------

Я конечно знал что 'SQL' и реляционные базы данных это сила, но что да такой степени, я даже и не догадывался!!!

[PooH]

если у тебя group by srcIp, то distinct  не нужен.

и еще такой вопрос - зачем тебе примари кеу?
(например, будет ошибка при вставке, если пройдет два запроса в одно и тоже время)

[RXL]

Serg79, при известном числе протокол — это решение подходит, но если их будет N, то нужно будет делать:

Код:

SELECT srcIp, protoID, SUM(bytes), SUM(psize)
FROM traffic
GROUP BY srcIp, protoID

Кстати, у тебя совершенно ненужное слово кругом - DISTINCT. Какой смысл его тут использовать?
При "GROUP BY" DISTINCT допустим внутри агрегатных функций, но не на всю строку.

[Serg79]

Да, 'DISTINC' действительно не нужен, только что проверил. А 'DISTINC' сюда затисался еще вот на этом этапе: select distinct srcIp from traffic where protoID = 1;. Опыта в этой области у меня еще маловато.

и еще такой вопрос - зачем тебе примари кеу?
(например, будет ошибка при вставке, если пройдет два запроса в одно и тоже время)

Да, я уже сталкивался с тем что ключи одинаковые. Но в рабочей системе этого происходить не должно, т.к. при выгрузке трафика из снифера он сразу же загружается в базу и время в поле 'date datetime not null' равно именно времени загрузки трафика в базу а не его выгрузки из снифера. Хотя все равно эти конфликты не есть гуд, так как на конечный результат запросов по таблице, одинаковые поля в ключе не влияют.

Это связано с тем, что при проектировании базы и таблиц я еще не совсем представлял себе какие запросы я буду гонять по ней и как будет происходить загрузка. Так что, ее структура будет еще изменяться в процессе разработки.

[RXL]

В рамках рассмотренных запросов индексы полностью бесполезные. Возможно, тут вообще индексы не нужны.
Serg79, попробуй поэкспериментировать с неуникальными индексами с участием srcIp и pFULL. Проверять нужно так: EXPLAIN SELECT ...