Прежде чем перейти к статье, хочу вам представить, экономическую онлайн игру Brave Knights, в которой вы можете играть и зарабатывать. Регистируйтесь, играйте и зарабатывайте!
Привет, Хабр!
Для аналитиков, владение SQL — это база. И от познаний в SQL зачастую зависит, отправит ли вам компания, где вы собеседуетесь - оффер.
В статье мы обсудим три области вопросов, которые могут встретиться на собеседованиях по SQL. А в конце рассмотрим три задачки.
Как оптимизировать SQL запросы?
Очень частый вопрос. Выделим три основных вида оптимизации.
Индексация
Индексация работает по принципу книжного указателя, позволяя БД быстро найти нужную строку без необходимости просмотра всех записей.
CREATE INDEX idx_customer_name ON customers (name);
Индекс поможет ускорить выполнение запросов, которые фильтруют или сортируют данные по столбцу name
в таблице customers
. Например, запрос:
SELECT * FROM customers WHERE name = 'Иван Иванов';
будет выполняться значительно быстрее с индексом, так как базе данных не нужно будет читать всю таблицу для поиска нужных значений.
Партиционирование
Партиционирование позволяет разделить таблицу на меньшие, более управляемые части.
CREATE TABLE sales (
sale_id INT AUTO_INCREMENT,
sale_date DATE,
amount DECIMAL(10, 2),
PRIMARY KEY (sale_id)
) PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1992),
PARTITION p2 VALUES LESS THAN (1993),
...
);
Таблица sales
разделена на партиции по годам продаж. Это позволяет выполнить запросы, ограниченные определенным годом, намного быстрее, так как SQL-сервер сможет обратиться непосредственно к нужной партиции.
Нормализация
Нормализация — процесс организации данных в БД, т.е устранение избыточности и зависимостей.
Перед нормализацией:
CREATE TABLE orders (
order_id INT,
customer_name VARCHAR(100),
customer_address VARCHAR(255),
order_date DATE
);
После нормализации:
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
address VARCHAR(255)
);
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Информация о клиенте вынесена в отдельную таблицу, что уменьшает дублирование данных и упрощает их обновление.
Про джоины
Работа с JOINs — это основа для многих сложных аналитических задач. JOINы позволяют объединять данные из разных таблиц для получения комплексных отчетов и анализа данных.
INNER JOIN
Допустим, нужно получить список всех заказов с информацией о клиентах.
SELECT orders.order_id, customers.name, orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.order_date > '2021-01-01';
Запрос объединяет таблицы orders
и customers
по customer_id
для получения информации о заказах, сделанных после определенной даты.
LEFT JOIN
Нужно выявить клиентов, которые не делали заказов.
SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;
LEFT JOIN
используется для объединения данных о клиентах с данными о заказах. В результат включаются все клиенты, но заказы присутствуют только у тех, кто что-то заказывал. Фильтр по orders.order_id IS NULL
позволяет идентифицировать клиентов без заказов.
RIGHT JOIN
Необходимо получить список всех товаров, которые были заказаны, включая информацию о заказах.
SELECT products.product_name, orders.order_id
FROM products
RIGHT JOIN orders ON products.product_id = orders.product_id;
RIGHT JOIN
аналогичен LEFT JOIN
, но в этом случае в результат попадут все заказы, и если какие-то товары не были в заказах, то соответствующие поля product_name
будут заполнены как NULL.
FULL OUTER JOIN
Интегрируем данные о клиентах и заказах для полного отчета.
SELECT customers.name, orders.order_id
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;
FULL OUTER JOIN
объединяет LEFT
и RIGHT JOIN
, включая все строки из обеих таблиц. Если совпадений нет, соответствующие поля заполняются NULLами.
CROSS JOIN
Нужно сгенерировать все возможные пары продуктов для анализа кросс-продаж.
SELECT A.product_name, B.product_name
FROM products A
CROSS JOIN products B
WHERE A.product_id != B.product_id;
CROSS JOIN
создает декартово произведение двух таблиц, что мастхев для анализа всех возможных комбинаций элементов из двух таблиц. В данном случае генерируются все возможные пары продуктов, за исключением пар с одинаковыми продуктами.
Оконные функции
Оконные функции позвояляют выполнять сложные вычисления, такие как кумулятивные суммы, движущиеся средние
и ранжирование, не агрегируя при этом строки в единую группу.
ROW_NUMBER() для уникальной нумерации строк
Например, нужно присвоить уникальный порядковый номер каждому заказу клиента по дате заказа.
SELECT customer_id, order_id, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS order_sequence
FROM orders;
ROW_NUMBER()
присваивает уникальный порядковый номер каждому заказу в пределах каждого клиента, упорядоченный по дате заказа.
RANK() и DENSE_RANK() для ранжирования данных
Ранжировать продажи сотрудников в рамках их отделов.
SELECT department_id, employee_id, sales_amount,
RANK() OVER (PARTITION BY department_id ORDER BY sales_amount DESC) AS sales_rank,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY sales_amount DESC) AS dense_sales_rank
FROM sales_records;
RANK()
присваивает ранг продажам сотрудников в каждом отделе, пропуская ранги при одинаковых значениях, в то время как DENSE_RANK()
делает то же самое, но без пропусков рангов.
Кумулятивная сумма с использованием SUM()
Нужен расчет кумулятивной суммы продаж по дням.
SELECT order_date, daily_sales,
SUM(daily_sales) OVER (ORDER BY order_date) AS cumulative_sales
FROM sales_by_date;
SUM()
с оконной спецификацией OVER
позволяет рассчитать кумулятивную сумму продаж, суммируя значения daily_sales
начиная с начала набора данных до текущей строки.
Вычисление скользящего среднего
Вычисляем 7-дневное скользящее среднего объема продаж.
SELECT order_date, sales_amount,
AVG(sales_amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_sales
FROM sales_records;
Юзаем AVG()
с оконной функцией, заданной для расчета среднего значения sales_amount
за текущий и предыдущие 6 дней.
Работа с подзапросами и CTE
Подзапросы и CTE улучшают структуру запросов.
Использование подзапросов
Находим клиентов, которые тратят больше среднего по всем клиентам.
SELECT customer_id, total_spent
FROM (
SELECT customer_id, SUM(amount) AS total_spent
FROM purchases
GROUP BY customer_id
) AS customer_spending
WHERE total_spent > (
SELECT AVG(total_spent) FROM (
SELECT SUM(amount) AS total_spent
FROM purchases
GROUP BY customer_id
) AS average_spending
);
Здесь внутренний запрос (подзапрос) используется для расчета общих трат каждого клиента, а внешний запрос сравнивает эти траты со средним значением, также полученным через подзапрос.
CTE для разделения сложных задач
Проведем анализ цепочки поставок для определения замедления доставки.
WITH SupplierData AS (
SELECT supplier_id, region, COUNT(*) AS num_orders
FROM orders
GROUP BY supplier_id, region
),
RegionalDelays AS (
SELECT region, AVG(delay) AS avg_delay
FROM deliveries
GROUP BY region
)
SELECT sd.supplier_id, sd.region, sd.num_orders, rd.avg_delay
FROM SupplierData sd
JOIN RegionalDelays rd ON sd.region = rd.region
WHERE rd.avg_delay > 10;
В запросе два CTE SupplierData
и RegionalDelays
используются для сегментации данных по поставщикам и региональным задержкам. Эти выражения затем объединяются для анализа.
Рекурсивные CTE
Генерируем серии дат.
WITH RECURSIVE DateSeries AS (
SELECT '2021-01-01' AS date
UNION ALL
SELECT DATE_ADD(date, INTERVAL 1 DAY)
FROM DateSeries
WHERE date < '2021-01-31'
)
SELECT *
FROM DateSeries;
Рекурсивный CTE в этом запросе создает последовательность дат, что юзабельно для заполнения пропущенных дат в временных рядах или для создания календарей.
Перейдем к задачам
Первая задача
Нужно разработать запрос для идентификации пользователей, которые никогда не испытывали скуку (Bored) в любой рекламной кампании, но испытали восторг (Excited) в последней кампании.
Предполагается наличие таблицы campaigns
, которая содержит поля user_id
, campaign_id
, impression
, и timestamp
. Поле impression
указывает на эмоциональную реакцию пользователя ("Bored" или "Excited"), а timestamp
фиксирует время реакции.
Для начала нужно будет сделать фильтр пользователей, которые никогда не были Bored, это можно выполнить, исключив из выборки тех пользователей, у которых хотя бы один раз была реакция Bored.
Далее нужно проидентифицировать пользователей, чья последняя реакция была Excited, для этого сначала найдем последнюю кампанию каждого пользователя, а затем проверим, что их последняя реакция была Excited.
WITH LastImpression AS (
SELECT user_id, MAX(timestamp) AS LastTime
FROM campaigns
GROUP BY user_id
),
ExcitedLast AS (
SELECT l.user_id
FROM LastImpression l
JOIN campaigns c ON l.user_id = c.user_id AND l.LastTime = c.timestamp
WHERE c.impression = 'Excited'
),
NeverBored AS (
SELECT user_id
FROM campaigns
GROUP BY user_id
HAVING SUM(CASE WHEN impression = 'Bored' THEN 1 ELSE 0 END) = 0
)
SELECT n.user_id
FROM NeverBored n
JOIN ExcitedLast e ON n.user_id = e.user_id;
CTE LastImpression
создает временное представление, содержащее user_id
и время последнего взаимодействия для каждого пользователя.
CTE ExcitedLast
выбирает пользователей, чья последняя реакция была Excited, используя данные из LastImpression
.
CTE NeverBored
:выбирает пользователей, которые никогда не испытывали скуку. HAVING
фильтр здесь проверяет, что суммарное количество Bored впечатлений равно 0.
Финальный SELECT соединяет пользователей из NeverBored
и ExcitedLast
для получения идентификаторов тех, кто никогда не испытывал скуку, но был восторжен в последней кампании.
Вторая задача
Напишите запрос для нахождения клиента с наибольшей общей стоимостью заказов за период с февраля 2019 по май 2024 года, суммируя затраты за каждый день, если клиент сделал несколько заказов в один день
Есть таблица orders
со следующими колонками:
customer_id
- идентификатор клиентаorder_date
- дата заказаorder_cost
- стоимость заказа
Делаем запрос:
SELECT customer_id, SUM(order_cost) AS total_cost
FROM (
SELECT customer_id, order_date, SUM(order_cost) AS order_cost
FROM orders
WHERE order_date BETWEEN '2019-02-01' AND '2024-05-31'
GROUP BY customer_id, order_date
) AS daily_orders
GROUP BY customer_id
ORDER BY total_cost DESC
LIMIT 1;
daily_orders
: запрос фильтрует заказы, включая только те, что были сделаны с 1 февраля по 31 мая 2024 года.
Далее запрос группирует заказы по customer_id
и order_date
, суммируя стоимость заказов, сделанных в один день. Это сделано для того, что если клиент сделал несколько заказов в один день, их стоимости будут суммированы.
После того как стоимости заказов за каждый день подсчитаны, внешний запрос суммирует эти дневные стоимости для каждого клиента, чтобы получить общую стоимость заказов каждого клиента за указанный период.
Внешний запрос также сортирует результаты по убыванию общей стоимости заказов и выбирает клиента с наибольшей общей стоимостью заказов, используя LIMIT 1
для возвращения только одного записи с макс. стоимостью.
Третья задача
Напишите запрос для определения трех отделов с наибольшим числом сотрудников, где более 10% сотрудников получают заработную плату выше $100,000.
Предполагается, что есть две таблицы: employees
с колонками employee_id
, department_id
, salary
и departments
с колонками department_id
, department_name
.
WITH SalaryStats AS (
SELECT
department_id,
COUNT(*) AS total_employees,
SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) AS high_earners
FROM employees
GROUP BY department_id
),
QualifiedDepartments AS (
SELECT
s.department_id,
d.department_name,
s.total_employees,
s.high_earners,
(s.high_earners * 100.0 / s.total_employees) AS percent_high_earners
FROM SalaryStats s
JOIN departments d ON s.department_id = d.department_id
WHERE (s.high_earners * 100.0 / s.total_employees) > 10
)
SELECT
department_id,
department_name,
total_employees,
high_earners,
percent_high_earners
FROM QualifiedDepartments
ORDER BY total_employees DESC
LIMIT 3;
CTE SalaryStats
считает общее количество сотрудников в каждом отделе и определяет количество сотрудников с зарплатой выше $100,000, после группирует данные по department_id
.
CTE QualifiedDepartments
присоединяет информацию о названии отдела из таблицы departments
, а после вычисляет процент сотрудников с зарплатой выше $100,000 в каждом отделе и фильтрует отделы, где этот процент больше 10.
Выборка данных выводит данные о квалифицированных отделах, упорядоченные по убыванию общего числа сотрудников и ограничивает вывод тремя отделами с наибольшим числом сотрудников.
Какой вопрос по SQL на ваших собеседованиях когда-либо вводил вас в заблуждение? Было бы интересно узнать в ваших комментариях, если вопрос будет интересным — добавим его во вторую часть статьи.
Больше практических навыков по SQL и не только вы можете получить в рамках практических онлайн-курсов по аналитике от экспертов отрасли.