Оператор SQL для возврата IP-адресов с двумя конкретными протоколами

Question

У меня есть CSV-файл длиной чуть более полумиллиона строк и шириной в десять столбцов.

Мне нужно извлечь общие IP-адреса, которые имеют два конкретных протокола (UDP и IGMP) и отфильтровать все другие IP-адреса, которые имеют только один связанный протокол. Вот пример данных в моей таблице:

Итак, этот запрос будет запущен и выведет:

Он возвращает IP/ сервер, который имеет ОБА UDP и IGMP, а НЕ сервер, который имеет только UDP. Как я могу это сделать? Это должно быть выполнено против 510 000+ линий с около 11 000 уникальных IP-адресов.

Answer 1

Одним из методов является использование оконных функций:

select t.*
from (select t.*,
             sum(case when protocol = 'UDP' then 1 else 0 end) over (partition by sourceIP) as num_udp,
             sum(case when protocol = 'IGMP' then 1 else 0 end) over (partition by sourceIP) as num_igmp
      from table t
     ) t
where num_udp > 0 and num_igmp > 0;

РЕДАКТИРОВАТЬ:

Если вам нужны только эти протоколы:

select t.*
from (select t.*,
             sum(case when protocol = 'UDP' then 1 else 0 end) over (partition by sourceIP) as num_udp,
             sum(case when protocol = 'IGMP' then 1 else 0 end) over (partition by sourceIP) as num_igmp,
             sum(case when protocol not in ('UDP', 'IGMP') then 1 else 0 end) over (partition by sourceIP) as num_other
      from table t
     ) t
where num_udp > 0 and num_igmp > 0 and num_other = 0;

Если вы просто хотите отфильтровать протоколы, то добавьте where protocol in ('UDP', 'IGMP'),

Answer 2

Выберите * из таблицы, где находится SourceIP (выберите DestIP из таблицы, где Protocol = 'IGMP')

Это предполагает, что если запись IGMP существует, то UDP также должен существовать. (Я не очень дешевый ответ)

Answer 3

Я считаю, что Гордон уже дал хороший ответ, используя оконные функции... Другой альтернативой может быть...

Select * from [tablename]
where SourceIP in
(

Select t2.SourceIP,count(SourceIP) from 
    (
      Select SourceIP,Protocol,count(*) from [tablename] where Protocol in ('UDP','IGMP') group by SourceIP,Protocol
    ) as t2 
group by SourceIP having count(SourceIP)=2

)

Примечание: я еще не запускал этот запрос...