Данные: сущности, связи и SQL для аналитика

Сущности и атрибуты

Система хранит данные о чём-то: гостях, заказах, напитках, баллах. Каждое такое «что-то» — сущность, а её свойства — атрибуты:

Для каждого атрибута аналитик уточняет:

• тип — число, текст, дата, да/нет;
• обязательность — может ли быть пустым. Телефон гостя — обязателен, имя — можно не указывать;
• правила — формат телефона, баланс не может быть отрицательным, статус только из списка.

Связи

Сущности связаны между собой:

• один-ко-многим — у одного гостя много заказов, у заказа один гость. В таблице заказов хранится guest_id — ссылка на гостя;
• многие-ко-многим — в заказе много напитков, напиток входит во много заказов. Такую связь хранят через промежуточную сущность позиция заказа: заказ, напиток, количество, цена на момент заказа;
• один-к-одному — встречается реже: например, гость и его настройки уведомлений.

Цену стоит хранить в позиции заказа, а не брать из справочника напитков: если капучино подорожает, старые заказы не должны пересчитаться.

ER-диаграмма

ER-диаграмма (сущность — связь) рисует всё это схемой: прямоугольники-сущности с атрибутами и линии-связи с пометками «1» и «много».

Гость 1 ──── * Заказ 1 ──── * Позиция заказа * ──── 1 Напиток

По этой схеме разработчики проектируют таблицы базы данных, а аналитик проверяет, что учтены все данные из требований.

SQL для аналитика

Аналитику не нужно проектировать базу как разработчику, но нужно уметь самому проверить данные: сколько гостей с баллами, правильно ли посчитаны суммы, есть ли «сломанные» записи. Для этого нужен SQL — язык запросов к базам данных.

Учебные таблицы guests (гости) и orders (заказы). Гости с баллами по убыванию:

SELECT name, points
FROM guests
WHERE points > 0
ORDER BY points DESC;

name  | points
Анна  | 120
Иван  | 40

Сколько заказов и на какую сумму у каждого гостя:

SELECT g.name, COUNT(o.id) AS orders, SUM(o.total) AS spent
FROM guests g
JOIN orders o ON o.guest_id = g.id
GROUP BY g.name
ORDER BY spent DESC;

name  | orders | spent
Анна  | 3      | 940
Иван  | 1      | 180

В базе есть ещё гостья Мария без заказов, но её в результате нет: JOIN оставляет только тех, у кого есть пара в другой таблице. Чтобы увидеть всех, нужен LEFT JOIN — он покажет Марию с нулём заказов.

Подробно SQL разобран в курсе «SQL с нуля»: выборки, условия, группировка и соединение таблиц.

SELECT g.name, COUNT(o.id) AS orders
FROM guests g
LEFT JOIN orders o ON o.guest_id = g.id
GROUP BY g.id, g.name
ORDER BY orders DESC;

name  | orders
Анна  | 3
Иван  | 1
Мария | 0