Таблица как визуализация — Продвинутая аналитика на SQL

• Таблица
  • Компоненты таблицы
• Отношения таблиц
  • Один к одному
  • Один ко многим
  • Многие ко многим
• Проектирование таблиц
• Агрегация таблицы продаж
• Выводы

Таблицы — необходимый компонент баз данных. Чтобы построить аналитику, мы часто агрегируем данные из таблиц и строим графики. Но иногда анализ самой таблицы без графиков оказывается удобнее.

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

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

Таблица

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

Таблица содержит информация о разных пиццериях. В ней есть следующие столбцы:

• Название — это название пиццерии
• Доставка — есть у пиццерии доставка или нет
• Начало работы — с какого времени открыта пиццерия

Вся информация в таблице — это параметры разных пиццерий, то есть смысл этой таблицы. Смысл называется сущностью — это то, о чем хранится информация в таблице.

Для таблиц как сущностей есть такое правило: одна таблица — одна сущность. Например, в таблице Users хранится только информация о пользователях, в таблице Payments — о платежах, а в таблице выше — о пиццериях.

Таблицы в базах данных могут быть связаны друг с другом. Такие базы называются реляционными.

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

Например, таблица Users хранит информацию о пользователе — его ФИО, адрес электронной почты и другие его параметры. Но каждый пользователь совершает какой-то платеж, и эти платежи хранятся в таблице Payments.

Таблицы Users и Payments связаны. Такие связи называются отношениями между сущностями. Язык SQL — это основной инструмент работы с реляционными базами данных.

У таблиц в реляционных базах данных есть разные компоненты. Их использование является одним из необходимых условий реляционной модели, потому что они позволяют связывать разные сущности. Рассмотрим их подробнее.

Компоненты таблицы

Обязательные компоненты таблиц такие:

• Столбец
• Строка
• Домен
• Ключ

Столбец в таблицах еще называют колонкой, *полем** или атрибутом. Атрибут таблицы выражает какой-то один тип информации о сущности. Например, в таблице Users может храниться поле Full name, и оно содержит информацию о ФИО пользователя. Поле User address будет хранить адрес пользователя. В колонке Название в таблице пиццерий мы видим название конкретной пиццерии.

Строки мы еще зовем записью или кортежем. Одна строка — это одна единица сущности. В таблице Users одна запись — это один пользователь, в Payments — это один платеж, в Пиццерии — одна пиццерия.

При создании таблицы в базе данных мы обязательно указываем тип данных информации в поле. Домен поля — это и есть тип данных столбца. К примеру, поле Full name в таблице Users может содержать строку и ничего кроме строки.

Вот примеры доменов полей:

• Строка
• Целое число
• Число с плавающей точкой и прочее

Мы уже упомянули, что таблицы в реляционных базах данных связаны. Чтобы связать две таблицы, мы используем специальное поле, которое называется ключ. В таблице Users и Payments такой ключ — это колонка UserID. Мы связываем эти две таблицы с помощью оператора join.

Ключ, который является уникальным для всей таблицы, — это первичный ключ. В таблице Users каждый пользователь имеет свой уникальный UserID, и два пользователя не могут иметь один UserID. Но в таблице Payments один пользователь может совершить несколько платежей, поэтому бывают строки с одинаковыми UserID.

В то же время первичный ключ PaymentID для этой таблицы бывает только уникальным, поскольку одна строка — это один платеж, и они не могут повторяться.

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

Отношения таблиц

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

• Один к одному
• Один ко многим
• Многие ко многим

Рассмотрим каждый из этих типов.

Один к одному

Представим, что у нас есть база данных пользователей интернет-магазина. Посмотрим на таблицу Users, в которой есть информация о пользователях:

users

В этой таблице мы видим три поля:

• UserID — ID покупателя
• Full name — имя и фамилия покупателя
• Email — адрес электронной почты

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

Чтобы решить эту задачу просто, мы создадим новую таблицу Users discount card, в которой будет всего два поля:

• ID покупателя
• Наличие у покупателя скидочной карты

При этом, чтобы не вписывать наличие карты у одного и того же покупателя несколько раз, мы сделаем поле UserID уникальным.

Посмотрим на такую таблицу:

users_discount_card

Таблица содержит только UserID и Discount card — есть у пользователя скидочная карта или нет. Теперь эту таблицу легко связать с таблицей Users по UserID с помощью SQL-функции join и получить для каждого пользователя наличие у него скидочной карты. При этом нам не нужно вносить в Users новое поле.

Такие отношения между таблицами называются один к одному. В таком типе отношений только одному пользователю соответствует только одна метка наличия карты.

Один ко многим

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

Для этого мы создадим новую таблицу Phones:

users

В этой таблице есть три поля:

• PhoneID — ID номера телефона
• UserID — ID покупателя, которому принадлежит телефон
• Phone — номер телефона

При этом PhoneID должен быть в таблице уникален, но, как мы видим, UserID повторяется. Это значит, что у одного покупателя есть несколько номеров.

Многие ко многим

Чтобы понять отношение многие ко многим, рассмотрим еще одну таблицу Products:

products

В таблице содержится список товаров магазина — ID товара и наименование. Мы хотим связать, какие товары какой покупатель приобрел. При этом один покупатель может купить много разных товаров, но и один и тот же товар могут купить разные люди. Таблица-отношение между таблицами Users и Products будет выглядеть так:

users_products

В этой таблице мы видим, что пользователь с ID = 1 купил товары с ID 10 и 5, но товар 10 купили два пользователя: с ID = 1 и ID = 6. Такие отношения и есть «многие ко многим».

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

Проектирование таблиц

Есть такие виды проектирования баз данных:

• Концептуальное моделирование
• Логическое моделирование
• Физическое моделирование

Концептуальное проектирование заключается в том, что мы описываем, какие у нас будут сущности в базе данных и отношения. Сущности, как мы говорили выше — это отдельные таблицы. Отношения — какие сущности и как будут связаны друг с другом: «один к одному», «один ко многим» или «многие ко многим». То, о чем мы говорили выше, — это концептуальное проектирование.

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

Физическое моделирование уже учитывает проектирование на уровне особенностей каждой отдельной базы данных: PostgreSQL, MySQL. При физическом моделировании мы выбираем, какие запросы нам важны больше всего: чтение из таблицы или запись в таблицу, и производим тестирование на пропускную способность.

Как правило, разные типы моделирования используются при проектировании одной и той же базы данных:

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

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

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

Агрегация таблицы продаж

Рассмотрим базу данных table. В ней есть одна таблица sales, которая содержит информацию о покупках в магазине. Посмотрим на эту таблицу:

sales

В этой таблице мы видим информацию о покупателях и купленных товарах.

Мы хотим определить из таблицы топ-5 самых прибыльных товаров. Наименование товара указано в поле product_name, а прибыль — в sales. Напишем SQL-запрос:

SELECT
    product_name,
    SUM(sales)
FROM sales
GROUP BY product_name
ORDER BY SUM(sales) DESC
LIMIT 5;

Ссылка на таблицу

В этом запросе мы произвели агрегацию по товарам и посчитали суммарную прибыль по всем товарам. Мы использовали ORDER BY SUM(sales) DESC, чтобы отсортировать товары по убыванию прибыли. LIMIT 5 позволяет отобрать только пять верхних строк таблицы.

Так мы нашли топ-5 самых прибыльных товаров. При этом самый удобный и наглядный способ решить нашу задачу — это просто таблица, без построения диаграмм.

Выводы

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

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

Самостоятельная работа

Для самостоятельной работы возьмите базу данных из урока. Найдите топ-10 покупателей, которые потратили больше всего денег в этом магазине.