Продвинутые темы SQL для дата-инженеров

Дата-инженер контролирует и анализирует большие наборы данных. Рассмотрим ряд важных для дата-инженеров тем SQL, иллюстрируя их примерами применения набора данных.

Дата-инженер контролирует и анализирует большие наборы данных. 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 | 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: фильтрацию и агрегирование данных, объединение таблиц, использование подзапросов и оконных функций. Освоив их, дата-инженеры станут эффективнее в анализе и визуализации данных, повышая качество принимаемых в организациях решений.