Базы данных. Примеры

Базы данных. Примеры

 

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

 

Интернет-магазин

 

Интернет-магазин продаёт товары. В типовой базе данных магазина есть таблицы с покупателями, товарами и заказами. Таблица с заказами объединяет информацию о товарах и покупателях. Когда в одном заказе несколько товаров, используют дополнительную таблицу «состав заказа».

Помните магазин вина из четвертого урока? Ниже — дополненная версия структуры данных этого магазина. В прямоугольниках — таблицы. Оранжевые линии — общие поля, по которым можно таблицы объединять с помощью джоинов.

Вы уже работали с таблицами users и orders и помните, что их можно объединить по полям users.id = orders.user_id.

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

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

 

Наборы вина хранятся в таблице sets. Она состоит из четырёх столбцов:

  • id — уникальный номер набора;
  • name — название, например, «Праздничная депрессия № 2» или «Застолье в подвале»;
  • retail_price — отпускная цена набора;
  • purchase_price — себестоимость набора.

 

Информация о марках вина хранится в таблице bottlesсо столбцами:

  • id — уникальный номер марки;
  • name — название, например, «Всплеск белладонны» или «Душа амбиций»;
  • color — цвет вина: белое, красное, розовое;
  • bubbles — признак игристости (1 — игристое, 0 — обычное);
  • country — из какой страны вино;
  • stock — сколько бутылок в наличии;
  • retail_price — отпускная цена бутылки вина;
  • purchase_price — себестоимость бутылки вина.

 

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

  • set_id — номер набора
  • bottle_id — номер бутылки

Чтобы узнать названия бутылок, входящих в набор «Застолье в подвале», нужно объединить наборы с бутылками через sets_bottles. Вот как это сделать:

 

SELECT

DISTINCT bottles.name

FROM

sets

INNER JOIN sets_bottles

ON sets.id = sets_bottles.set_id

INNER JOIN bottles

ON sets_bottles.bottle_id = bottles.id

WHERE

sets.name = “Застолье в подвале”

 

На заметку: с помощью джоинов можно объединять сколько угодно таблиц.

 

Новый оператор: DISTINCT указывает, что нужно вывести список уникальных значений, без повторов.

 

Теперь свяжем наборы с заказами. Напомню, что за раз клиенты порой покупают несколько наборов. Поэтому заказы и наборы связывают аналогичным образом — через специальную таблицу orders_sets с полями:

  • order_id — номер заказа;
  • set_id — номер набора.

 

Например, вы хотите узнать, сколько раз был куплен набор «Застолье в подвале». Объедините ordersorders_setsи sets:

 

SELECT

count(DISTINCT order_id)

FROM

orders

INNER JOIN orders_sets

ON orders.id = orders_sets.order_id

INNER JOIN sets

ON orders_sets.set_id = sets.id

WHERE

sets.name = “Застолье в подвале”

 

Добавим кунг-фу и посчитаем продажи конкретной бутылки, например, «Души амбиций»:

 

SELECT

count(DISTINCT order_id)

FROM

orders

INNER JOIN orders_sets

ON orders.id = orders_sets.order_id

INNER JOIN sets

ON orders_sets.set_id = sets.id

INNER JOIN sets_bottles

ON sets.id = sets_bottles.set_id

INNER JOIN bottles

ON sets_bottles.bottle_id = bottles.id

WHERE

bottles.name = “Душа амбиций”

 

Таблицы типа orders_sets и sets_bottles называются джоин-таблицами. Их создают, чтобы описывать связи многих элементов из нескольких таблиц.

 

В базе данных интернет-магазина в первую очередь ищите таблицы с информацией о покупателях, заказах и товарах, например usersи orders.

Затем ищите джоин-таблицы, описывающие связи между ними типа orders_setssets_bottles.

 

Маркетплейс

 

Маркетплейс сам по себе ничего не продаёт, он объединяет продавцов и покупателей, помогает им общаться. Например, сервисы такси типа Гетт и Убера — маркетплейсы, объединяющие водителей такси с одной стороны и пассажиров с другой.

База данных такого сервиса выглядит примерно так:

Объясню, что происходит, когда вы заказываете такси.

Вы (user) открываете приложение, выбираете адрес и кликаете «заказать». В этот момент в таблице ordersпоявляется заказ с полями:

  • id — уникальный номер заказа;
  • created_at — дата и время создания;
  • user_id — ваш уникальный номер;
  • pickup_address — адрес, откуда вы едете;
  • dropoff_address — адрес пункта назначения;
  • status — текущий статус заказа («в поиске»), потом изменится на «выполнен» или «провален»;
  • driver_id — уникальный номер вашего водителя;
  • price — стоимость поездки.

Чтобы найти водителя, система создаёт «предложение» в таблице offers, и рассылает его ближайшим свободным таксистам. Чтобы понять, какой свободный водитель находится недалеко от вас, система смотрит в таблицуdriver_activity, в которую раз в минуту записываются географические координаты водителей на линии и их статус: «свободен» или «занят»:

  • driver_id — номер водителя;
  • time — дата и время измерения;
  • status — текущий статус водителя, «свободен» или «занят»;
  • latitudelongitude — географические координаты водителя.

Так выглядит таблица offers с заказами клиентов:

  • id — уникальный номер предложения о поездке;
  • order_id — номер заказа, к которому относится предложение;
  • driver_id — номер водителя, получившего предложение;
  • сreated_at — дата и время создания предложения;
  • status — статус предложения: «принято или «отклонено».

Когда водитель соглашается вас отвезти, в поле driver_idв таблице orders появляется номер водителя, а вы на экране телефона видите, как он к вам едет.

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

Чтобы это проверить, можно посчитать, сколько предложений в таблице offers было за каждый час в пересчёте на количество водителей на линии:

 

SELECT

offers_per_hour.hour as hour,
offers_created / active_drivers as offers_per_driver

FROM

(SELECT

hour(created_at) as hour,
count(*) as offers_created

FROM offers
GROUP BY hour) offers_per_hour
INNER JOIN
(SELECT

hour(time) as hour,
count(id) as active_drivers

FROM driver_activity
WHERE

status = ‘active’

GROUP BY hour) active_drivers

ON offers_per_hour.hour = active_drivers.hour

 

Вложенные запросы

В разделе FROM вы указываете таблицы, из которых хотите получить данные.

Любой SELECT-запрос возвращает таблицу с данными. Можно писать такие запросы в разделе FROM:

SELECT

FROM

(SELECT… FROM… WHERE…)

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

 

Схожую структуру вы найдёте в любом маркетплейсе: онлайн-аукционе Ибей, службах доставки Фудфокс и Деливери Клаб, купонном сервисе вроде Групона.

 

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

 

Сервисы по подписке

 

Сервисы по подписке анализировать одно удовольствие, ведь структура данных там проще, чем в других видах бизнеса.

В качестве примера разберём сервис по подписке для просмотра кино и сериалов типа Амедатеки.

По традиции, сначала ищем таблицы, описывающие пользователей (users) и товары. Только в этот раз вместо товаров — тарифные планы (plans). Пользователи связаны с тарифными планами с помощью джоин-таблицыpayments.

Конечно, в базе есть ещё таблица с фильмами, наверняка сохраняется история просмотров, комментариев и предпочтений. Но на оплату это не влияет: цена не зависит от количества просмотренных фильмов.

У Амедиатеки есть четыре основных тарифа: на 30, 90, 180 и 360 дней. Цены меняются от 590 рублей за 30 дней до 3599 рублей — за 360 дней.

Представьте, что их финансовый отдел прислал свежую идею: «А давайте узнаем, какой тариф приносит больше всего денег, а остальные отключим?». Ваша задача — посчитать, сколько приносит каждый тарифный план в пересчёте на пользователя.

Вот как это сделать:

 

SELECT

plans.name,
sum(payments.sum) as total_revenue
sum(payments.sum)/count(DISTINCT user_id) as revenue_per_user

FROM

users
INNER JOIN payments
ON users.id = payments.user_id
INNER JOIN plans
ON payments.plan_id = plans.id

WHERE

DATEDIFF(CURRENT_DATE(),users.registration_date) >= 365
— в анализ включим только пользователей,
— зарегистрировавшихся более 1 года назад

GROUP BY

1

 

Новые операторы:
CURRENT_DATE() возвращает сегодняшнюю дату в формате ГГГГ-ММ-ДД.
DATEDIFF() берёт две даты и возвращает разницу в днях.

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

И ещё: в разделе GROUP BY я вместо названия поля указал цифру 1. Бигквери читает это, как «сгруппируй по первому полю». Бигквери посмотрит в начало запроса, возьмёет plans.name и сгруппирует по нему. Такой синтаксис работает не во всех базах данных. Но он удобный. Аналогично работает в ORDER BY.

 

Ещё пара примеров

 

Фримиум-игры. Например, «Ворлд оф танкс». Пользователи гоняют на танках, стреляют друг в друга и иногда за виртуальную валюту покупают бонусы типа пушки побольше, снарядов помощнее или брони потолще.

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

Поисковые системы и социальные сети. Например, Вконтакте, Фейсбук, Яндекс или Гугл. На сайт заходят люди, чтобы найти полезную информацию, поболтать или запостить фотку своего кота. Эти люди — пользователи.

Но платят деньги не они, а рекламодатели. Товар в данном случае — внимание пользователей. Рекламодатели соревнуются между собой за возможность показать рекламу.

 

Что дальше

 

Я надеюсь, вы уже начали замечать закономерности в структуре запросов и данных. Вам легче читать и писать запросы. А значит, вы готовы переходить дальше, к использованию их на практике.

Мы заканчиваем раздел «Данные» и переходим к их анализу.