Объединение таблиц: INNER JOIN, LEFT JOIN

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

Зачем объединять таблицы в антифроде?

Представьте: у вас есть таблица с транзакциями (сумма, дата, статус) и таблица с данными пользователей (ID, регион, дата регистрации). Чтобы понять, какие пользователи из какого региона совершают подозрительные транзакции, вам нужно связать эти таблицы по общему идентификатору, например, user_id.

Объединение таблиц позволяет:

• Собрать полную картину: Получить всю нужную информацию из разных источников в одном месте.
• Обогатить данные: Добавить контекст к существующим данным (например, к транзакции — данные о пользователе или устройстве).
• Провести глубокий анализ: Найти сложные паттерны мошенничества, которые не видны при анализе отдельных таблиц.

INNER JOIN: только совпадения

INNER JOIN (внутреннее соединение) — самый распространённый тип объединения. Он возвращает только те строки, для которых есть совпадения в обеих таблицах по заданному условию.

Возьмём таблицы transactions и users.

Таблица transactions:

transaction_id	user_id	amount	status
101	1	1500	completed
102	2	300	pending
103	1	2500	completed
104	3	500	declined
105	4	1000	completed

Таблица users:

user_id	region	registration_date
1	Moscow	2023-01-15
2	St. Petersburg	2023-02-20
3	Kazan	2023-03-10

Чтобы получить транзакции вместе с информацией о пользователях, используем INNER JOIN:

SELECT
    t.transaction_id,
    t.amount,
    t.status,
    u.region,
    u.registration_date
FROM
    transactions AS t -- Псевдоним 't' для удобства
INNER JOIN
    users AS u ON t.user_id = u.user_id; -- Условие объединения по user_id

Результат INNER JOIN:

transaction_id	amount	status	region	registration_date
101	1500	completed	Moscow	2023-01-15
102	300	pending	St. Petersburg	2023-02-20
103	2500	completed	Moscow	2023-01-15
104	500	declined	Kazan	2023-03-10

Обратите внимание: транзакция с transaction_id = 105 (пользователь user_id = 4) не попала в результат. Почему? Потому что пользователя с user_id = 4 нет в таблице users. INNER JOIN требует совпадения в обеих таблицах.

Совет: Всегда используйте псевдонимы (например, AS t, AS u) для таблиц при объединении. Это делает запросы читабельнее и помогает избежать путаницы, если в разных таблицах есть столбцы с одинаковыми названиями.

LEFT JOIN: всё из левой таблицы

LEFT JOIN (левое соединение) возвращает все строки из левой таблицы (той, что указана первой после FROM), а также совпадающие строки из правой. Если совпадений в правой таблице нет, то для столбцов из правой таблицы будут значения NULL.

Это полезно, когда вам нужны все записи из одной таблицы, даже если для них нет соответствий в другой. Например, вы хотите увидеть все транзакции и, если есть, добавить информацию о пользователе. Если нет — оставить поля пользователя пустыми.

Используем те же таблицы transactions и users.

SELECT
    t.transaction_id,
    t.amount,
    t.status,
    u.region,
    u.registration_date
FROM
    transactions AS t
LEFT JOIN
    users AS u ON t.user_id = u.user_id;

Результат LEFT JOIN:

transaction_id	amount	status	region	registration_date
101	1500	completed	Moscow	2023-01-15
102	300	pending	St. Petersburg	2023-02-20
103	2500	completed	Moscow	2023-01-15
104	500	declined	Kazan	2023-03-10
105	1000	completed	NULL	NULL

Здесь транзакция с transaction_id = 105 (пользователь user_id = 4) включена в результат. Но поля region и registration_date для неё содержат NULL, так как соответствующего пользователя в таблице users не нашлось.

Важно: Выбор между INNER JOIN и LEFT JOIN зависит от вашей задачи. Нужны только полные совпадения? Используйте INNER JOIN. Нужны все записи из одной таблицы, независимо от совпадений во второй? Используйте LEFT JOIN.

Практическое задание

Представьте, что вы антифрод-исследователь в банке и расследуете подозрительные операции. У вас есть две таблицы:

1. card_transactions: информация о транзакциях по картам.
   • transaction_id (ID транзакции)
   • card_number (номер карты)
   • amount (сумма)
   • transaction_date (дата транзакции)
   • merchant_id (ID продавца)
2. merchants: информация о продавцах.
   • merchant_id (ID продавца)
   • merchant_name (название продавца)
   • merchant_category (категория продавца, например, "электроника", "продукты", "онлайн-казино")

Задание 1: Напишите SQL-запрос, который выведет все транзакции по картам вместе с названиями и категориями продавцов, только если информация о продавце доступна.

Задание 2: Напишите SQL-запрос, который выведет все транзакции по картам, и если есть информация о продавце, добавит её. Если информации о продавце нет, поля merchant_name и merchant_category должны быть NULL.

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

В следующем разделе мы перейдём к основам Python и работе с Jupyter Notebook. Это позволит вам обрабатывать данные ещё эффективнее и автоматизировать многие рутинные задачи антифрод-исследователя.