Задача # 17. Разбор задачи по SQL: Анализ расходов семьи за 2005 год

Определить, сколько потратил в 2005 году каждый из членов семьи. В результирующей выборке не выводите тех членов семьи, которые ничего не потратили.

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

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

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

Требуется определить сумму расходов каждого члена семьи в 2005 году, исключив из результатов тех, кто не совершал трат. Результирующая таблица должна содержать:

• member_name — имя члена семьи
• status — статус в семье
• costs — общая сумма расходов за год

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

Для решения задачи потребуются две таблицы:

1. FamilyMembers — содержит информацию о членах семьи:
   member_id — идентификатор члена семьи (PK)
   member_name — имя (нужно для вывода)
   status — статус (нужно для вывода)
2. Payments — содержит информацию о платежах:
   family_member — ID члена семьи (FK)
   date — дата платежа (фильтр по 2005 году)
   unit_price — цена за единицу
   amount — количество единиц

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

Оптимальное решение

SELECT
fm.member_name,
fm.status,
SUM(p.unit_price * p.amount) AS costs
FROM
FamilyMembers fm
JOIN
Payments p ON fm.member_id = p.family_member
WHERE
EXTRACT(YEAR FROM p.date) = 2005
GROUP BY
fm.member_name, fm.status
HAVING
SUM(p.unit_price * p.amount) > 0
ORDER BY
costs DESC;

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

1. FROM FamilyMembers fm — начинаем с таблицы членов семьи
2. JOIN Payments p — соединяем с таблицей платежей по ID члена семьи
3. WHERE EXTRACT(YEAR FROM p.date) = 2005 — фильтруем платежи только за 2005 год
4. GROUP BY fm.member_name, fm.status — группируем по имени и статусу
5. HAVING SUM(p.unit_price * p.amount) > 0 — исключаем членов семьи без трат
6. SELECT — выбираем:
   fm.member_name — имя члена семьи
   fm.status — статус
   SUM(p.unit_price * p.amount) AS costs — сумма расходов
7. ORDER BY costs DESC — сортируем по убыванию суммы расходов

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

1. Фильтрация по году

Для извлечения года из даты используются:

• В MySQL: YEAR(p.date) = 2005
• В PostgreSQL: EXTRACT(YEAR FROM p.date) = 2005
• Альтернативно: p.date BETWEEN '2005-01-01' AND '2005-12-31'

2. Расчет суммы расходов

• unit_price * amount — стоимость каждой покупки
• SUM() — агрегатная функция для подсчета общей суммы
• HAVING SUM(...) > 0 гарантирует исключение членов семьи без трат

3. Группировка данных

• Группировка по двум полям: member_name и status
• Важно включать все неагрегированные поля из SELECT в GROUP BY

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

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

SELECT
fm.member_name,
fm.status,
(SELECT SUM(p.unit_price * p.amount)
FROM Payments p
WHERE p.family_member = fm.member_id
AND EXTRACT(YEAR FROM p.date) = 2005
) AS costs
FROM
FamilyMembers fm
WHERE
EXISTS (
SELECT 1
FROM Payments p
WHERE p.family_member = fm.member_id
AND EXTRACT(YEAR FROM p.date) = 2005
)
ORDER BY
costs DESC;

2. С CTE (Common Table Expression)

WITH member_costs AS (
SELECT
family_member,
SUM(unit_price * amount) AS total
FROM
Payments
WHERE
EXTRACT(YEAR FROM date) = 2005
GROUP BY
family_member
HAVING
SUM(unit_price * amount) > 0
)
SELECT
fm.member_name,
fm.status,
mc.total AS costs
FROM
FamilyMembers fm
JOIN
member_costs mc ON fm.member_id = mc.family_member
ORDER BY
mc.total DESC;

3. С фильтрацией даты через диапазон

SELECT
fm.member_name,
fm.status,
SUM(p.unit_price * p.amount) AS costs
FROM
FamilyMembers fm
JOIN
Payments p ON fm.member_id = p.family_member
WHERE
p.date BETWEEN '2005-01-01' AND '2005-12-31'
GROUP BY
fm.member_name, fm.status
HAVING
SUM(p.unit_price * p.amount) > 0
ORDER BY
costs DESC;

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

1. Индексы:
   На Payments(family_member, date) — для быстрого поиска платежей
   На Payments(date) — если часто фильтруем по дате
   На FamilyMembers(member_id) — для соединения
2. Для больших таблиц:
   Использовать материализованные представления
   Рассмотреть предварительную агрегацию данных
   Добавить LIMIT для ограничения результатов

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

1. Забыть включить status в GROUP BY
2. Использовать WHERE вместо HAVING для фильтрации по SUM
3. Неправильный формат даты в условиях фильтрации
4. Отсутствие сортировки или неправильный порядок сортировки

Дополнительные возможности

1. Добавление средней суммы покупки

SELECT
fm.member_name,
fm.status,
SUM(p.unit_price * p.amount) AS total_costs,
AVG(p.unit_price * p.amount) AS avg_payment,
COUNT(*) AS payments_count
FROM
FamilyMembers fm
JOIN
Payments p ON fm.member_id = p.family_member
WHERE
EXTRACT(YEAR FROM p.date) = 2005
GROUP BY
fm.member_name, fm.status
HAVING
SUM(p.unit_price * p.amount) > 0
ORDER BY
total_costs DESC;

2. Анализ по месяцам

SELECT
fm.member_name,
EXTRACT(MONTH FROM p.date) AS month,
SUM(p.unit_price * p.amount) AS monthly_costs
FROM
FamilyMembers fm
JOIN
Payments p ON fm.member_id = p.family_member
WHERE
EXTRACT(YEAR FROM p.date) = 2005
GROUP BY
fm.member_name, EXTRACT(MONTH FROM p.date)
HAVING
SUM(p.unit_price * p.amount) > 0
ORDER BY
fm.member_name, month;

3. Ранжирование членов семьи по расходам

SELECT
member_name,
status,
costs,
RANK() OVER (ORDER BY costs DESC) AS rank
FROM (
SELECT
fm.member_name,
fm.status,
SUM(p.unit_price * p.amount) AS costs
FROM
FamilyMembers fm
JOIN
Payments p ON fm.member_id = p.family_member
WHERE
EXTRACT(YEAR FROM p.date) = 2005
GROUP BY
fm.member_name, fm.status
HAVING
SUM(p.unit_price * p.amount) > 0
) t
ORDER BY
rank;

Заключение

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

1. Технику соединения таблиц
2. Фильтрацию данных по временному периоду
3. Группировку и агрегацию данных
4. Условия фильтрации после группировки

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

SELECT member_name,

status,

SUM(unit_price * amount) AS costs

FROM FamilyMembers fm

JOIN Payments ps ON fm.member_id = ps.family_member

WHERE YEAR(DATE) = 2005

GROUP BY member_name,

status;