Задача # 20. Разбор задачи по SQL: Сколько и кто из семьи потратил на развлечения (entertainment). Вывести статус в семье, имя, сумму

SQL (Structured Query Language) важен, потому что он позволяет эффективно управлять базами данных. С его помощью можно:

• Хранить и организовывать большие объемы структурированной информации.
• Быстро извлекать, фильтровать и сортировать данные с помощью запросов.
• Изменять данные (добавлять, обновлять, удалять) безопасно и точно.
• Автоматизировать процессы и интегрировать данные между разными системами.
• Анализировать информацию для принятия решений в бизнесе, науке и IT.

Без SQL работа современных приложений, сайтов и аналитических систем была бы невозможна.

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

Разбор задачи по SQL: Анализ расходов семьи на развлечения

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

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

• status — статус в семье (например, "мать", "отец", "ребенок")
• member_name — имя члена семьи
• costs — суммарные затраты на развлечения (с использованием AS costs)

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

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

1. FamilyMembers — информация о членах семьи:
   member_id — идентификатор (PK)
   member_name — имя (нужно для вывода)
   status — статус (нужно для вывода)
2. Payments — информация о покупках:
   family_member — ID члена семьи (FK)
   good — ID товара/услуги (FK)
   amount — количество
   unit_price — цена за единицу
3. Goods — справочник товаров/услуг:
   good_id — идентификатор (PK)
   type — категория (FK к GoodTypes)
4. GoodTypes — типы товаров:
   good_type_id — идентификатор (PK)
   good_type_name — название типа (фильтр по 'entertainment')

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

Оптимальное решение (MySQL/PostgreSQL)

SELECT
fm.status,
fm.member_name,
SUM(ps.amount * ps.unit_price) AS costs
FROM
FamilyMembers fm
JOIN
Payments ps ON fm.member_id = ps.family_member
JOIN
Goods gs ON ps.good = gs.good_id
JOIN
GoodTypes gt ON gs.type = gt.good_type_id
WHERE
gt.good_type_name = 'entertainment'
GROUP BY
fm.status, fm.member_name
ORDER BY
costs DESC;

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

1. Соединение таблиц:
   Связываем членов семьи с их покупками
   Затем соединяем с товарами и их категориями
2. Фильтрация:
   WHERE gt.good_type_name = 'entertainment' — только развлечения
3. Агрегация:
   SUM(ps.amount * ps.unit_price) — расчет суммы по каждой покупке
   GROUP BY fm.status, fm.member_name — группировка для каждого члена семьи
4. Сортировка:
   ORDER BY costs DESC — сначала самые большие траты

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

1. Многотабличное соединение

• Используется цепочка из трех JOIN для связи четырех таблиц
• Важные условия соединения:
  fm.member_id = ps.family_member
  ps.good = gs.good_id
  gs.type = gt.good_type_id

2. Расчет суммы затрат

• amount * unit_price — стоимость каждой покупки
• SUM() — агрегация по всем покупкам категории
• AS costs — обязательное именование столбца

3. Особенности фильтрации

• Точное совпадение по названию категории
• В реальной системе стоит учитывать:
  Регистр (LOWER/UPPER)
  Возможные подкатегории развлечений

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

1. С подзапросом

SELECT
fm.status,
fm.member_name,
(SELECT SUM(ps.amount * ps.unit_price)
FROM Payments ps
JOIN Goods gs ON ps.good = gs.good_id
JOIN GoodTypes gt ON gs.type = gt.good_type_id
WHERE gt.good_type_name = 'entertainment'
AND ps.family_member = fm.member_id
) AS costs
FROM
FamilyMembers fm
WHERE
EXISTS (
SELECT 1
FROM Payments ps
JOIN Goods gs ON ps.good = gs.good_id
JOIN GoodTypes gt ON gs.type = gt.good_type_id
WHERE gt.good_type_name = 'entertainment'
AND ps.family_member = fm.member_id
)
ORDER BY
costs DESC;

2. С CTE (для PostgreSQL)

WITH EntertainmentCosts AS (
SELECT
ps.family_member,
SUM(ps.amount * ps.unit_price) AS total
FROM
Payments ps
JOIN
Goods gs ON ps.good = gs.good_id
JOIN
GoodTypes gt ON gs.type = gt.good_type_id
WHERE
gt.good_type_name = 'entertainment'
GROUP BY
ps.family_member
)
SELECT
fm.status,
fm.member_name,
ec.total AS costs
FROM
FamilyMembers fm
JOIN
EntertainmentCosts ec ON fm.member_id = ec.family_member
ORDER BY
ec.total DESC;

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

Индексы:sqlCopyDownloadCREATE INDEX idx_good_types_name ON GoodTypes(good_type_name);
CREATE INDEX idx_goods_type ON Goods(type);
CREATE INDEX idx_payments_good ON Payments(good);
CREATE INDEX idx_payments_member ON Payments(family_member);

1. Для больших таблиц:
   Использовать LIMIT для ограничения результатов
   Рассмотреть материализованные представления для часто запрашиваемых категорий

Дополнительный анализ

1. Добавление количества покупок

SELECT
fm.status,
fm.member_name,
SUM(ps.amount * ps.unit_price) AS costs,
COUNT(*) AS purchases_count
FROM
FamilyMembers fm
JOIN
Payments ps ON fm.member_id = ps.family_member
JOIN
Goods gs ON ps.good = gs.good_id
JOIN
GoodTypes gt ON gs.type = gt.good_type_id
WHERE
gt.good_type_name = 'entertainment'
GROUP BY
fm.status, fm.member_name
ORDER BY
costs DESC;

2. Анализ по видам развлечений

SELECT
fm.status,
fm.member_name,
gs.good_name,
SUM(ps.amount * ps.unit_price) AS costs
FROM
FamilyMembers fm
JOIN
Payments ps ON fm.member_id = ps.family_member
JOIN
Goods gs ON ps.good = gs.good_id
JOIN
GoodTypes gt ON gs.type = gt.good_type_id
WHERE
gt.good_type_name = 'entertainment'
GROUP BY
fm.status, fm.member_name, gs.good_name
ORDER BY
fm.member_name, costs DESC;

Заключение

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

1. Сложные многотабличные соединения
2. Фильтрацию по категориям товаров/услуг
3. Группировку и агрегацию данных
4. Сортировку результатов анализа

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

SELECT
fm.status,
fm.member_name,
SUM(ps.amount * ps.unit_price) AS costs
FROM
FamilyMembers fm
JOIN
Payments ps ON fm.member_id = ps.family_member
JOIN
Goods gs ON ps.good = gs.good_id
JOIN
GoodTypes gt ON gs.type = gt.good_type_id
WHERE
gt.good_type_name = 'entertainment'
GROUP BY
fm.status, fm.member_name
ORDER BY
costs DESC;