Задача # 13. Разбор задачи по SQL: Поиск пассажиров с полными тёзками

Вывести имена людей, у которых есть полный тёзка среди пассажиров

SQL — это универсальный язык для взаимодействия с базами данных, который должен знать каждый аналитик, разработчик и дата-инженер. На собеседованиях кандидатам часто предлагают решить практические задачи: написать эффективные запросы, оптимизировать их и предложить решение для конкретных бизнес-кейсов. Давай разберём распространённые типы SQL-задач и эффективные подходы к их решению.

Предыдущее задание:

Постановка задачи

Требуется вывести имена пассажиров, у которых есть хотя бы один полный тёзка (человек с точно таким же именем) в базе данных. Результирующая таблица должна содержать одно поле:

• name — имя пассажира (только для тех, у кого есть тёзки)

Анализ структуры базы данных

Для решения задачи нам понадобится таблица:

• Passenger — содержит информацию о пассажирах:
  name — полное имя пассажира (единственное поле, нужное для решения)
  Другие поля (не требуются для этой задачи)

Детальный разбор решения

Решение для MySQL/PostgreSQL

SELECT name
FROM Passenger
GROUP BY name
HAVING COUNT(*) >= 2;

Пошаговое объяснение:

1. FROM Passenger — выбираем данные из таблицы пассажиров
2. GROUP BY name — группируем записи по полному имени
3. HAVING COUNT(*) >= 2 — фильтруем группы, оставляя только те имена, которые встречаются 2 и более раз
4. SELECT name — выводим только имена (без количества)

Ключевые аспекты решения

1. Различие между WHERE и HAVING

• WHERE фильтрует строки до группировки
• HAVING фильтрует результаты после группировки
• В данной задаче важно использовать именно HAVING, так как фильтрация выполняется по результату агрегации

2. Использование COUNT(*)

• COUNT(*) подсчитывает все строки в группе
• Можно использовать COUNT(name) — результат будет одинаковым, так как GROUP BY уже выполнен по name
• Условие >= 2 означает "два и более одинаковых имени"

3. Особенности группировки

• GROUP BY name объединяет все записи с одинаковыми именами
• Для каждого уникального имени создаётся одна группа
• В SELECT можно указывать только столбцы из GROUP BY или агрегатные функции

Альтернативные варианты решения

1. С использованием подзапроса

SELECT DISTINCT p1.name
FROM Passenger p1
WHERE EXISTS (
SELECT 1
FROM Passenger p2
WHERE p1.name = p2.name
AND p1.id != p2.id
);

2. С явным выводом количества тёзок

SELECT name, COUNT(*) AS name_count
FROM Passenger
GROUP BY name
HAVING COUNT(*) >= 2
ORDER BY name_count DESC;

3. С использованием оконных функций (PostgreSQL)

SELECT DISTINCT name
FROM (
SELECT name, COUNT(*) OVER (PARTITION BY name) AS name_count
FROM Passenger
) counted
WHERE name_count >= 2;

Оптимизация запроса

Для больших таблиц:

1. Создать индекс на поле name
2. Для MySQL использовать FORCE INDEX при необходимости
3. Для PostgreSQL рассмотреть использование HASH для группировки

Частые ошибки

1. Использование WHERE вместо HAVING для фильтрации по COUNT
2. Забыть условие >= 2 (вернёт все имена)
3. Включение лишних столбцов в SELECT без агрегации
4. Использование DISTINCT без необходимости (GROUP BY уже устраняет дубликаты)

Дополнительные соображения

1. В реальной системе могут быть проблемы с однофамильцами (если name содержит ФИО)
2. Можно учитывать регистр (добавить LOWER(name) при группировке)
3. Для больших баз данных можно добавить лимит на количество результатов

Заключение

Данная задача демонстрирует:

1. Правильное использование GROUP BY и HAVING
2. Особенности работы с агрегатными функциями
3. Эффективные способы поиска дубликатов в SQL

🔑 Итоговое решение:

SELECT name
FROM Passenger
GROUP BY name
HAVING COUNT(*) >= 2;

Дополнительный вариант с сортировкой:

SELECT name
FROM Passenger
GROUP BY name
HAVING COUNT(*) >= 2
ORDER BY name;