Дата-инженер контролирует и анализирует большие наборы данных. SQL — мощный инструмент выполнения запросов и манипулирования данными, и для создания эффективных решений здесь имеется немало продвинутых функциональных средств. Рассмотрим ряд важных для дата-инженеров тем SQL, иллюстрируя их примерами применения набора данных.
Пример набора данных
Имеется три таблицы:
product_tableс данными различных продуктов — название, цена, категория;category_tableс данными о категориях продуктов;order_tableс данными о заказах: заказанный продукт, его количество, дата заказа.
Вот схема каждой таблицы:
product_table +----+----------------------+-------+-------------+ | id | Название | Цена | category_id | +----+----------------------+-------+-------------+ | 1 | iPhone 13 | 1000 | 2 | | 2 | Galaxy S21 | 800 | 2 | | 3 | Pixel 6 | 700 | 2 | | 4 | MacBook Air | 1000 | 4 | | 5 | Surface Pro 8 | 1200 | 4 | | 6 | iPad Pro | 800 | 3 | | 7 | iPad Mini | 400 | 9 | | 8 | Smart TV | 800 | 1 | | 9 | Home Theater System | 600 | 1 | | 10 | Galaxy S21 Ultra | 1400 | 2 | | 11 | iPhone 13 Pro Max | 1300 | 2 | | 12 | Pixel 6 Pro | 1000 | 2 | +----+----------------------+-------+-------------+ category_table +----+-------------+ | id | Название | +----+-------------+ | 1 | Электроника | | 2 | Телефоны | | 3 | Планшеты | | 4 | Ноутбуки | | 5 | Бытовая техника | | 6 | Одежда | | 7 | Спорттовары | | 8 | Игрушки | | 9 | Аксессуары | +----+-------------+ order_table +----+------------+-------------+------------+ | id | product_id | Количество | Дата | +----+------------+-------------+------------+ | 1 | 1 | 2 | 2022-03-28 | | 2 | 2 | 1 | 2022-03-28 | | 3 | 3 | 3 | 2022-03-29 | | 4 | 4 | 1 | 2022-03-30 | | 5 | 5 | 2 | 2022-03-31 | | 6 | 6 | 3 | 2022-03-31 | | 7 | 7 | 2 | 2022-03-31 | | 8 | 8 | 1 | 2022-03-31 | | 9 | 9 | 1 | 2022-03-31 | +--------------------------------------------+
Фильтрация данных
Предложение WHERE — это важный SQL-функционал для фильтрования данных по конкретным условиям. WHERE, которым определяется условие включения данных в результаты, добавляется в конце оператора SELECT.
Пример. Чтобы получить все продукты дороже 1000 $, применяем WHERE для их фильтрации по цене, в результаты включаются только продукты дороже 1000 $:
SELECT *
FROM product_table
WHERE price > 1000;
Возвращаемый результат:
+----+---------------------+-------+-------------+
| id | Название | Цена | category_id |
+----+---------------------+-------+-------------+
| 5 | Surface Pro 8 | 1200 | 4 |
| 10 | Galaxy S21 Ultra | 1400 | 2 |
| 11 | iPhone 13 Pro Max | 1300 | 2 |
+----+---------------------+-------+-------------+
Объединения
Чтобы иметь полную картину о данных нескольких таблиц, эти таблицы объединяют. Для этого в SQL имеются внутренние, левые, правые и полные внешние объединения.
Пример. Чтобы получить список всех заказов и название заказанного продукта, с помощью объединения в SQL объединяем таблицы: в одной данные о заказе, в другой — о продукте:
SELECT order_table.*, product_table.name
FROM order_table
INNER JOIN product_table ON order_table.product_id = product_table.id;
Возвращаемый результат:
+----+------------+----------+-------------+---------------------+
| id | product_id | Количество | Дата | Название |
+----+------------+----------+-------------+---------------------+
| 1 | 1 | 2 | 2022-03-28 | iPhone 13 |
| 2 | 2 | 1 | 2022-03-28 | Galaxy S21 |
| 3 | 3 | 3 | 2022-03-29 | Pixel 6 |
| 4 | 4 | 1 | 2022-03-30 | MacBook Air |
| 5 | 5 | 2 | 2022-03-31 | Surface Pro 8 |
| 6 | 6 | 3 | 2022-03-31 | iPad Pro |
| 7 | 7 | 2 | 2022-03-31 | iPad Mini |
| 8 | 8 | 1 | 2022-03-31 | Smart TV |
| 9 | 9 | 1 | 2022-03-31 | Home Theater System |
+----+------------+----------+-------------+---------------------+
Подзапросы
В SQL подзапрос — это запрос, который пишется внутри другого запроса. Подзапросами выполняются операции посложнее, а также фильтруются данные по результатам других запросов.
Пример. Чтобы получить список всех продуктов, заказанных хотя бы раз, внутри основного запроса пишем подзапрос с критериями для фильтрации данных. Результат подзапроса — все ID заказанных хотя бы раз продуктов — включается в условие фильтра основного запроса. В итоге получаем все данные о продуктах, соответствующих критериям подзапроса, со списком всех заказанных хотя бы раз товаров:
SELECT *
FROM product_table
WHERE id IN (
SELECT DISTINCT product_id
FROM order_table
);
Возвращаемый результат:
+----+---------------------+-------+-------------+
| id | Название | Цена | category_id |
+----+---------------------+-------+-------------+
| 1 | iPhone 13 | 999 | 2 |
| 2 | Galaxy S21 | 1099 | 2 |
| 3 | Pixel 6 | 899 | 2 |
| 4 | MacBook Air | 1199 | 3 |
| 5 | Surface Pro 8 | 1200 | 4 |
| 6 | iPad Pro | 799 | 4 |
| 7 | iPad Mini | 499 | 4 |
| 8 | Smart TV | 899 | 1 |
| 9 | Home Theater System | 799 | 1 |
+----+---------------------+-------+-------------+
Оконные функции
Оконными функциями в SQL вычисляются нарастающие итоги, скользящие средние и т. д. по группе связанных друг с другом строк.
Пример. Чтобы рассчитать нарастающий итог по дате заказа каждого продукта в одном запросе, применяем оконную функцию к подмножеству, называемому окном, в которое включаются все строки конкретного продукта, связанные друг с другом по заданным критериям:
SELECT name, quantity, SUM(quantity) OVER (
PARTITION BY product_id
ORDER BY date
) AS running_total
FROM order_table
INNER JOIN product_table ON order_table.product_id = product_table.id
ORDER BY product_id, date;
Возвращаемый результат:
+--------------+----------+---------------+
| Название | Количество | running_total |
+--------------+----------+---------------+
| iPhone 13 | 2 | 2 |
| iPhone 13 | 1 | 3 |
| Galaxy S21 | 1 | 1 |
| Pixel 6 | 3 | 3 |
| MacBook Air | 1 | 1 |
| Surface Pro 8| 2 | 2 |
| iPad Pro | 3 | 3 |
| iPad Mini | 2 | 2 |
| Smart TV | 1 | 1 |
| Home Theater | 1 | 1 |
+--------------+----------+---------------+
Обобщенные табличные выражения
Обобщенные табличные выражения — это временно именованные результирующие наборы, применяемые в одиночном операторе SQL для создания сложных запросов разбиением их на мелкие, более управляемые части. Эти выражения особенно полезны для рекурсивных запросов и тех, которым требуются множественные объединения.
Обобщенное табличное выражение создается указанием имени результирующего набора и оператора SQL, в котором возвращаются желаемые результаты. Затем этот результирующий набор применяется в основном запросе, как если бы это была таблица.
Пример. Чтобы найти общий доход по каждой категории, обобщенным табличным выражением высчитываем его с помощью подзапроса по каждому продукту и объединяем с таблицей категорий. Так получим общий доход по каждой категории продуктов в одном запросе:
WITH product_revenue AS (
SELECT product_id, SUM(quantity * price) AS revenue
FROM order_table
INNER JOIN product_table ON order_table.product_id = product_table.id
GROUP BY product_id
)
SELECT category_table.name, SUM(product_revenue.revenue) AS total_revenue
FROM category_table
INNER JOIN product_table ON category_table.id = product_table.category_id
INNER JOIN product_revenue ON product_table.id = product_revenue.product_id
GROUP BY category_table.name;
Возвращаемый результат:
+------------+---------------+
| Название | total_revenue |
+------------+---------------+
| Электроника| 6094 |
| Home | 1598 |
+------------+---------------+
В предложении WITH определяется обобщенное табличное выражение product_revenue, которым высчитывается общий доход по каждому продукту. Затем, чтобы высчитать общий доход по каждой категории, обобщенное табличное выражение объединяется в основном запросе с таблицами продуктов и категорий.
Материализованные представления
Материализованные представления, как и обычные, создаются на основе SQL-запросов, но подобны предварительно вычисляемым сводным данным, сохраняемым в виде физических таблиц. Данные в материализованных представлениях вычисляются и сохраняются заранее, поэтому запросы в них быстрее, результаты мгновенные — без необходимости вычислять данные снова и снова. Материализованными представлениями ускоряются сложные запросы, агрегирование данных для отчетов и предоставление высокопроизводительного источника данных для инструментов бизнес-аналитики.
Пример. Чтобы рассчитать общий доход по каждому проданному за последний месяц продукту, создаем материализованное представление, в котором это значение вычисляется и сохраняется в виде физической таблицы. Больше не нужно каждый раз вычислять его, просто выполняем запрос в материализованное представление:
CREATE MATERIALIZED VIEW product_monthly_revenue AS
SELECT product_id, SUM(quantity * price) AS monthly_revenue
FROM order_table
INNER JOIN product_table ON order_table.product_id = product_table.id
WHERE order_date BETWEEN CURRENT_DATE - INTERVAL '1 month' AND CURRENT_DATE
GROUP BY product_id;
В этом материализованном представлении высчитывается и ежемесячное значение, результаты сохраняются в физической таблице product_monthly_revenue, получаем их с помощью запроса:
SELECT product_table.name, product_monthly_revenue.monthly_revenue
FROM product_table
INNER JOIN product_monthly_revenue ON product_table.id = product_monthly_revenue.product_id;
Возвращаемый результат:
+----------------------+----------------+
| Название | monthly_revenue|
+----------------------+----------------+
| Ноутбук | 1200 |
| Смартфон | 800 |
| Наушники | 300 |
+----------------------+----------------+
Материализованные представления обновляются по расписанию или вручную: предварительно вычисляемые результаты обновляются, изменения отражаются на базовых данных. Поэтому материализованные представления — мощный инструмент дата-инженеров, которым ускоряется предоставление высокопроизводительных источников данных для инструментов бизнес-аналитики.
Индексирование
Индексирование — это способ ускорения запросов к базе данных за счет создания в таблицах индексов. Индексы подобны оглавлению книги: с ними нужная информация находится быстро.
Благодаря созданию в таблице индекса, компонентом database engine быстро и без поиска по всей таблице находятся строки, соответствующие условию запроса, значительно повышается производительность запросов, особенно в больших таблицах.
Пример. Найдем в большой — на миллионы строк — таблице заказов orders все заказы, размещенные конкретным покупателем:
SELECT *
FROM orders
WHERE customer_id = 12345;
Не будь индекса в столбце customer_id, для поиска искомых строк пришлось бы сканировать компонентом database engine всю таблицу orders. С индексом они находятся быстро, запрос значительно ускоряется:
CREATE INDEX customer_id_idx ON orders (customer_id);
Индексы создаются в одном или нескольких столбцах, уникальными или неуникальными. Уникальными индексами гарантируется, что в таблице нет двух строк с одинаковыми значениями для индексированных столбцов, а вот с неуникальными индексами возможны дубли.
Еще индексы бывают кластеризованными или некластеризованными. Первыми определяется физический порядок данных в таблице, во вторых хранится отдельная таблица записей индекса, которыми указывается на данные в таблице.
Несмотря на значительное повышение производительности запросов, имеется у индексов и минус — накладные расходы. Индексы занимают дисковое пространство, ими замедляются операции изменения данных, например вставки INSERT, обновления UPDATE и удаления DELETE. Поэтому важно тщательно продумать, какие столбцы индексировать и как оптимизировать индексы в конкретной ситуации.
Другие продвинутые темы
В SQL имеется много других продвинутых тем для дата-инженеров.
- Партиционирование — разбиение больших таблиц на разделы поменьше для упрощения управления и ускорения запросов.
- Компрессия — сжатие данных для экономии дискового пространства и повышения производительности запросов.
- Оптимизация запросов — применение планов запроса, статистики, подсказок и других техник для оптимизации производительности запросов.
- Блокировка и конкурентность — управление несколькими одновременными сеансами базы данных для обеспечения целостности данных и предотвращения конфликтов.
- Хранимые процедуры и функции — предварительно скомпилированный код базы данных, повторно используемый в нескольких запросах.
- Безопасность и аутентификация — защита конфиденциальных данных и предоставление доступа к базе данных только авторизованным пользователям.
Если не всеми этими темами овладеть, то хотя бы добиться глубокого их понимания — вот задача дата-инженера, чтобы стать эффективнее и лучше использовать информационные ресурсы организации.
Заключение
SQL — мощный язык, применяемый дата-инженерами для извлечения информации из больших наборов данных. Мы рассмотрели ряд продвинутых тем SQL: фильтрацию и агрегирование данных, объединение таблиц, использование подзапросов и оконных функций. Освоив их, дата-инженеры станут эффективнее в анализе и визуализации данных, повышая качество принимаемых в организациях решений.
Читайте также:
- SQL: загадки на сообразительность
- SQL — язык программирования? 10 аргументов “за” и “против”
- Выбор между SQL и NoSQL: ACID и CAP, схема и транзакции
Читайте нас в Telegram, VK и Дзен
Перевод статьи Vishal Barvaliya: Advanced SQL topics for data engineers
