MySQL - как объединить данные из двух таблиц, используя значение одного поля в качестве фильтра для подсчета значений в другом?
Я ломал голову над этим, и решение, вероятно, настолько простое, но я просто не могу его понять, искал повсюду и не могу найти решение этой конкретной проблемы.
У меня есть две таблицы - одна содержит список IP-соединений, например:
Connections_Table
src dst
192.168.1.1 1.2.3.4
192.168.1.1 2.2.2.2
192.168.1.1 3.3.3.3
192.168.1.1 4.4.4.4
Другая таблица содержит список IP-адресов, например:
Bad_Ip_Addresses_Table
ip
7.8.9.4
3.2.1.4
77.8.99.4
2.2.2.2
18.7.9.8
Вот где я не могу найти, как построить этот запрос ... Я пытаюсь создать таблицу, которая показывает src и dst из первой таблицы, и содержит ли вторая таблица dst из первой таблицы. Другими словами:
Results_Table
src dst match
192.168.1.1 1.2.3.4 0
192.168.1.1 2.2.2.2 1
192.168.1.1 3.3.3.3 0
192.168.1.1 4.4.4.4 0
Вот, вероятно, самая большая загвоздка: я видел сообщения о SO, где решение включает создание таблицы и триггеров. Я не могу этого сделать - это будет оператор SQL AWS Kinesis Analytics:
Connections_Table загружается в реальном времени, а Bad_Ip_Addresses_Table - это CSV, загруженный из AWS S3. Для каждой принятой строки мне нужно выполнить SQL-запрос для CSV, чтобы определить, находится ли dst ip в CSV.
Какие-либо предложения?
@GordonLinoff Это оператор SQL AWS Kinesis Analytics, использующий входной файл S3 в качестве справочных данных, которые можно запрашивать с помощью операторов SQL для обогащения потока Kinesis.
Ответы 2
Вы можете использовать левое соединение и считать
select a.src, a.dst, count(b.ip)
from Connections_Table a
left join Bad_Ip_Addresses_Table b on a.dst = b.ip
group by a.src, a.dst
- В MySQL вы можете
Left JoinотConnections_TableдоBad_Ip_Addresses_Table, так что учитываются все значенияdstизConnections_Table(независимо от того, существует ли соответствующая строка или нет). - Затем вы можете
Group Byнаsrcиdst; и используйте функциюCount()для подсчета совпадений. Обратите внимание, чтоCount(null) = 0; поэтому несоответствующие строки вернут 0 (поскольку после левого соединения будут значенияnull).
В MySQL попробуйте следующий запрос:
SELECT
ct.src,
ct.dst,
COUNT(biat.ip) AS match
FROM
Connections_Table AS ct
LEFT JOIN Bad_Ip_Addresses_Table AS biat ON biat.ip = ct.dst
GROUP BY ct.src, ct.dst
@nukalov рад помочь :)
Я запутался. Это MySQL или Kinesis? Если не MySQL, удалите этот тег (и, возможно, SQL тоже).