--- title: 'SQL: запросы и аналитика' questionDates: q-transfer-0067: '2026-10-01' seo: description: >- Тема «SQL: запросы и аналитика» для собеседования Data Scientist. Для чего используется LIKE? Что такое обобщенное табличное выражение (CTE) и как оно используется? title: 'SQL: запросы и аналитика — Data Scientist' --- [Все темы Data Scientist](/prep/data-scientist) Подтемы: - [Основные концепции SQL](#q-14bee738d69b81eb8357c1464e2caf2a) - [Фильтрация строк](#q-14bee738d69b8141b16df5efcd91df56) - [JOIN Операторы и операции с таблицами](#q-14bee738d69b81188f41e82082e45ead) - [Оконные функции](#q-14bee738d69b81d9bd9bdc8899d4d0e6) - [PIVOT Таблицы](#q-14bee738d69b81f3876dc7c2c9992982) - [Лайфкодинг SQL](#q-14bee738d69b81019777c47c5b66b79e) ## Для чего используется LIKE? [#q-14bee738d69b81eb8357c1464e2caf2a] Оператор LIKE в SQL используется для выполнения поиска в текстовых данных с использованием шаблонов. Он позволяет выбирать строки, которые соответствуют определенному шаблону, который может содержать специальные символы, такие как % (заменяющий любое количество символов) и \_ (заменяющий один символ). Это полезно для поиска строк с определенными паттернами, например, имена, которые начинаются или заканчиваются определенной последовательностью символов, или содержат определенные символы в середине строки. :::note[Ссылки для изучения] 1. [Оператор LIKE в SQL](https://tproger.ru/articles/like-sql) ::: --- ## Что такое обобщенное табличное выражение (CTE) и как оно используется? [#q-14bee738d69b81d7b00bed3b4a263732] CTE представляет именованный результат запроса, заданный через WITH и доступный внутри одной SQL-команды. Он помогает разбить сложный запрос на части и поддерживает рекурсивные вычисления. CTE не гарантирует ускорение или однократное вычисление: материализация и встраивание в основной запрос зависят от СУБД и плана. :::note[Ссылки для изучения] 1. [Обобщенное табличное выражение в SQL](https://sql-academy.org/ru/guide/operator-with) ::: --- ## Что представляют собой значения null и NaN, и в чем заключается их различие? Каким образом следует обрабатывать данные с такими типами? [#q-14bee738d69b811898d8c8c88c7ccc19] Значения NULL и NaN используются для представления отсутствующих или неопределенных данных. #### NULL: - В SQL, NULL используется для обозначения отсутствия значения. - Он не является нулевым значением, а скорее указывает на отсутствие какого-либо значения. - NULL не равен ни одному другому значению, даже самому себе. #### NaN (Not a Number): - NaN используется в числовых вычислениях, чтобы указать на неопределенный результат или ошибку. - Он обычно возникает при выполнении некорректных математических операций, таких как деление на ноль или попытка получить квадратный корень отрицательного числа. Различие между NULL и NaN заключается в их контексте использования: NULL применяется в SQL для обозначения отсутствия значения, в то время как NaN используется в числовых вычислениях для обозначения неопределенного результата. :::note[Ссылки для изучения] 1. [NULL в SQL: неопределённость и проверки IS NULL](https://postgrespro.ru/docs/postgresql/current/functions-comparison) 1. [NaN и inf при арифметике массивов NumPy](https://miptstats.github.io/courses/python/05_numpy.html) ::: --- ## В чем разница query и key? [#q-14bee738d69b81acbb1ec3c13de6d360] В контексте SQL, термины "query" и "key" относятся к разным понятиям: {/* prettier-ignore */} 1. Query (Запрос): Это инструкция или набор инструкций, написанных на языке SQL, которые позволяют выполнить операции с данными в базе данных. Запросы могут включать выборку данных, их вставку, обновление, удаление и множество других операций. Например, SELECT * FROM users; — это запрос на выборку всех данных из таблицы пользователей. 1. Key (Ключ): Это атрибут или набор атрибутов в таблице, который помогает SQL-системе быстро и эффективно организовать, доступить и поддерживать целостность данных. Существуют различные типы ключей: - Primary Key (Первичный ключ): Уникально идентифицирует каждую запись в таблице. - Foreign Key (Внешний ключ): Обеспечивает ссылочную целостность между двумя таблицами. - Unique Key (Уникальный ключ): Гарантирует, что все значения в столбце уникальны. Таким образом, "query" это команды для работы с данными, а "key" — это элементы структуры базы данных, обеспечивающие управление и целостность данных. :::note[Ссылки для изучения] 1. [SELECT: запрос данных в SQL](https://postgrespro.ru/docs/postgresql/current/sql-select) 1. [Ключи и ограничения: PRIMARY KEY, FOREIGN KEY и UNIQUE](https://postgrespro.ru/docs/postgresql/current/ddl-constraints) ::: --- ## В чем разница между операторами DELETE и TRUNCATE? [#q-14bee738d69b81d09252dbd6b8e2eb8e] Между двумя этими операторами, есть основная разница: - DELETE удаляет строки в таблице, одну за другой, и поддерживает транзакции и триггеры. - TRUNCATE удаляет все строки без WHERE. Возможность отката и работа триггеров зависят от СУБД: в PostgreSQL TRUNCATE транзакционен и может быть отменён через ROLLBACK. :::note[Ссылки для изучения] 1. [DELETE: удаление строк по условию в PostgreSQL](https://postgrespro.ru/docs/postgresql/current/sql-delete) 1. [TRUNCATE в PostgreSQL: опустошение, триггеры и откат](https://postgrespro.ru/docs/postgresql/current/sql-truncate) ::: --- ## В каком логическом порядке SQL выполняет FROM, JOIN, WHERE и SELECT? [#q-transfer-0067] Логически запрос обрабатывается так: `FROM` и `JOIN`, затем `WHERE`, `GROUP BY`, `HAVING`, `SELECT`, `DISTINCT`, `ORDER BY`, `LIMIT`. Поэтому alias из `SELECT` обычно недоступен в `WHERE`, а фильтр по агрегату ставят в `HAVING`. Физический план может переставлять операции, если сохраняется результат. Порядок полезен для понимания области видимости и гранулярности, но не описывает фактический алгоритм исполнения. Особенности alias и `LIMIT` зависят от диалекта. :::note[Ссылки для изучения] 1. [Логический порядок SELECT: FROM, WHERE, группировка, SELECT и сортировка в PostgreSQL](https://postgrespro.ru/docs/postgresql/18/sql-select) ::: --- ## Чем отличаются WHERE и HAVING? [#q-14bee738d69b8141b16df5efcd91df56] WHERE и HAVING - это два разных оператора в SQL для фильтрации данных, применяемых в разных контекстах: #### WHERE: - WHERE используется для фильтрации строк до группировки или агрегации данных. - Он применяется к отдельным строкам и определяет, какие строки будут включены в результат запроса. - WHERE применяется к строкам до выполнения агрегации. #### HAVING: - HAVING используется для фильтрации результатов агрегации данных после выполнения группировки. - Он применяется к группам строк, сгруппированным по определенным критериям, и определяет, какие группы будут включены в результат запроса. - HAVING применяется к результатам агрегации после выполнения группировки. :::note[Ссылки для изучения] 1. [Оператор HAVING в SQL](https://sql-academy.org/ru/guide/operator-having) 1. [Оператор WHERE в SQL](https://sql-academy.org/ru/guide/conditional-where-operator) ::: --- ## Перечислите типы JOINов, дайте характеристику каждому. [#q-14bee738d69b81188f41e82082e45ead] В SQL существует несколько типов операторов JOIN: {/* prettier-ignore */} 1. INNER JOIN: - Возвращает только те строки, которые имеют совпадения в обоих таблицах по указанным условиям. - Если в обеих таблицах нет совпадений, строки не возвращаются. 1. LEFT JOIN (или LEFT OUTER JOIN): - Возвращает все строки из левой таблицы (первой в запросе), а также строки из правой таблицы, которые имеют совпадения с условием JOIN. - Если в правой таблице нет совпадений, возвращается NULL для столбцов правой таблицы. 1. RIGHT JOIN (или RIGHT OUTER JOIN): - Возвращает все строки из правой таблицы (второй в запросе), а также строки из левой таблицы, которые имеют совпадения с условием JOIN. - Если в левой таблице нет совпадений, возвращается NULL для столбцов левой таблицы. 1. FULL JOIN (или FULL OUTER JOIN): - Возвращает все строки из обеих таблиц, совпадающие и несовпадающие по условию JOIN. - Если нет совпадений, возвращаются NULL значения для недостающих столбцов. 1. CROSS JOIN: - Возвращает декартово произведение всех строк из обеих таблиц, то есть каждая строка из одной таблицы объединяется со всеми строками из другой таблицы. :::note[Ссылки для изучения] 1. [Соединения SQL: типы JOIN и число строк результата](https://postgrespro.ru/docs/postgresql/18/queries-table-expressions#QUERIES-JOIN) ::: --- ## В одной таблице 2 строчки, в другой 3, сколько минимум строк будет при иннер и лефт джойне? А сколько максимум вне зависимости от джойна? [#q-14bee738d69b818a93f1f8b6c4b72cd7] При выполнении INNER JOIN минимальное количество строк равно 0, если нет совпадений между строками обеих таблиц. При выполнении LEFT JOIN минимальное количество строк равно количеству строк в левой таблице, т.е. 2 строки, так как левые таблицы строки всегда включаются, даже если нет совпадений. Максимум строк (вне зависимости от типа JOIN): 2\*3=6. :::note[Ссылки для изучения] 1. [Соединения SQL: типы JOIN и число строк результата](https://postgrespro.ru/docs/postgresql/18/queries-table-expressions#QUERIES-JOIN) ::: --- ## Какие существуют оконные функции? [#q-14bee738d69b81d9bd9bdc8899d4d0e6] Оконные функции в SQL - это функции, которые выполняют вычисления на группах строк, называемых окнами, в пределах результата запроса. Они позволяют выполнять агрегатные функции (например, суммирование, подсчет, вычисление среднего значения) и аналитические функции (например, вычисление ранжирования, отступов, смещений) с учетом порядка и разбиения данных на окна. Оконные функции обычно используются вместе с ключевым словом OVER, которое определяет окно, над которым будет выполняться функция. :::note[Ссылки для изучения] 1. [Оконные функции: OVER, ранжирование и смещения](https://postgrespro.ru/docs/postgresql/18/functions-window) ::: --- ## Какие оконные функции ранжирования вы знаете в SQL? [#q-14bee738d69b81e687a3d7a4141ad0f2] Некоторые из распространенных оконных функций ранжирования в SQL: 1. ROW_NUMBER(): Присваивает каждой строке уникальный числовой ранг в пределах заданного окна, начиная с 1 и увеличиваясь на 1 для каждой следующей строки. 1. RANK(): Присваивает каждой строке ранг в пределах заданного окна. Если несколько строк имеют одинаковые значения, им присваивается одинаковый ранг, при этом следующий ранг увеличивается на количество строк, имеющих предыдущий ранг. 1. DENSE_RANK(): Подобно функции RANK(), но не допускает разрывов в последовательности рангов. Если несколько строк имеют одинаковые значения, им присваиваются одинаковые ранги, но следующий ранг не увеличивается на количество строк с предыдущим рангом. 1. NTILE(n): Делит строки раздела на n групп с максимально близким числом строк и возвращает номер группы. :::note[Ссылки для изучения] 1. [ROW_NUMBER, RANK, DENSE_RANK и NTILE](https://postgrespro.ru/docs/postgresql/18/functions-window) ::: --- ## Как ROW_NUMBER, RANK и DENSE_RANK нумеруют пять строк, если среди значений есть совпадения? [#q-14bee738d69b81278ca5e8ba45934eeb] Для значений ORDER BY 10, 20, 20, 30, 40 результаты будут следующими. 1. ROW_NUMBER(): 1, 2, 3, 4, 5. Номера уникальны; порядок строк с одинаковым значением без дополнительного ключа не определён. 1. RANK(): 1, 2, 2, 4, 5. Равные значения получают одинаковый ранг, после них остаётся пропуск. 1. DENSE_RANK(): 1, 2, 2, 3, 4. Равные значения получают одинаковый ранг, пропусков нет. :::note[Ссылки для изучения] 1. [Оконные функции: OVER, ранжирование и смещения](https://postgrespro.ru/docs/postgresql/18/functions-window) ::: --- ## Знакомы ли с оконными функциями? С рекурсивными подзапросами? Что знаете из этого? [#q-14bee738d69b811b82edec4937585e75] Да, я знаком с оконными функциями и рекурсивными подзапросами в SQL. Оконные функции позволяют выполнять вычисления на группах строк, называемых "окнами", внутри результирующего набора строк. Они часто используются для агрегации данных в пределах группы строк или для анализа временных рядов. Рекурсивные подзапросы позволяют ссылаться на самих себя внутри SQL запроса. Они часто используются для выполнения итеративных вычислений, обхода древовидных структур данных или генерации последовательностей чисел. :::note[Ссылки для изучения] 1. [Оконные функции: OVER, ранжирование и смещения](https://postgrespro.ru/docs/postgresql/18/functions-window) 1. [WITH RECURSIVE: рекурсивные SQL-запросы](https://postgrespro.ru/docs/postgresql/current/queries-with) ::: --- ## Расскажите про PIVOT таблицы. [#q-14bee738d69b81f3876dc7c2c9992982] PIVOT в SQL - это операция, которая позволяет преобразовывать строки данных в столбцы. Обычно это используется для трансформации результатов запроса, где значения из одного столбца (обычно строковые значения) становятся заголовками столбцов. Это позволяет легче анализировать данные, особенно в случае агрегированных результатов. PIVOT в SQL использует агрегирующую функцию, такую как SUM или MAX, чтобы сгруппировать данные по определенным критериям. Пример использования PIVOT может выглядеть так: ```sql SELECT * FROM ( SELECT category, year, sales FROM sales_table ) AS src PIVOT ( SUM(sales) FOR year IN ([2019], [2020], [2021]) ) AS piv; ``` Этот запрос преобразует строки с данными по категориям и годам в столбцы, где каждый столбец представляет сумму продаж для определенного года, сгруппированного по категории. :::note[Ссылки для изучения] 1. [PIVOT в SQL Server: синтаксис и сводная таблица](https://learn.microsoft.com/ru-ru/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver17) ::: --- ## Задача №1 [#q-14bee738d69b81019777c47c5b66b79e] ```text Пусть имеем таблицу с 3 столбцами: id, name и age, которая содержит 100 млн строк. Допустим, неизвестно, является ли id первичным ключом таблицы. Хотим проверить, так ли это. Каким простым запросом можно сделать такую валидацию? Допустим, нужно добавить в эту таблицу столбец с количеством раз, которое каждое имя (поле name) встречается в таблице. Как это сделать? Допустим, нужно определить возраст, который чаще всего встречается в таблице. Как это сделать? ``` Решение: - Этот запрос проверяет отсутствие NULL и повторов в текущих данных id. Наличие ограничения PRIMARY KEY проверяют отдельно в метаданных схемы. ```sql SELECT COUNT(*) AS total_rows, COUNT(id) AS non_null_ids, COUNT(DISTINCT id) AS distinct_ids FROM your_table_name; ``` Если все три значения равны, в текущих данных id нет NULL и повторов. Это ещё не означает, что в схеме объявлен PRIMARY KEY. - Чтобы добавить столбец с количеством раз, которое каждое имя встречается в таблице, можно использовать следующий запрос: ```sql ALTER TABLE your_table_name ADD COLUMN name_count INT; UPDATE your_table_name SET name_count = (SELECT COUNT(*) FROM your_table_name t2 WHERE t2.name = your_table_name.name); ``` Этот запрос добавляет новый столбец name_count в таблицу и затем обновляет его значения, подсчитывая количество строк с каждым именем. - Чтобы определить возраст, который чаще всего встречается в таблице, можно использовать следующий запрос: ```sql SELECT age FROM your_table_name GROUP BY age ORDER BY COUNT(*) DESC LIMIT 1; ``` Этот запрос группирует строки по возрасту, считает количество строк для каждого возраста, затем сортирует результаты по убыванию количества строк и выбирает самый часто встречающийся возраст. :::note[Ссылки для изучения] 1. [COUNT(\*), COUNT(column) и COUNT(DISTINCT): строки, NULL и уникальные значения](https://postgrespro.ru/docs/postgresql/18/sql-expressions#SYNTAX-AGGREGATES) 1. [GROUP BY, агрегаты, ORDER BY и LIMIT для поиска наиболее частых значений](https://postgrespro.ru/docs/postgresql/18/sql-select) ::: --- ## Задача №2 [#q-14bee738d69b81eaaf7eed76f8707425] ```text Для каждого клиента вычислить средний интервал между заказами в днях, используя следующие таблицы: Таблица order содержит следующие столбцы: - order_id: уникальный идентификатор заказа. - date_time: дата и время заказа. - amount: сумма заказа. - customer_id: идентификатор клиента, связанный с заказом. Таблица customer содержит следующие столбцы: - customer_id: уникальный идентификатор клиента. - age: возраст клиента. Для каждого клиента необходимо вычислить средний интервал между его заказами в днях. ``` Для вычисления среднего интервала между заказами для каждого клиента, мы можем сделать следующее: 1. Сначала для каждого клиента упорядочим заказы по date_time и order_id и найдём предыдущий заказ с помощью LAG. 1. Вычислим длительность между соседними заказами в секундах и переведём в дни. 1. Наконец, мы сгруппируем результаты по customer_id и вычислим среднее значение интервала для каждого клиента. Вот как это можно сделать в SQL: ```sql WITH intervals AS ( SELECT customer_id, TIMESTAMPDIFF(SECOND, LAG(date_time) OVER ( PARTITION BY customer_id ORDER BY date_time, order_id ), date_time) / 86400.0 AS interval_days FROM `order` ) SELECT c.customer_id, AVG(i.interval_days) AS avg_interval_days FROM customer c LEFT JOIN intervals i ON i.customer_id = c.customer_id GROUP BY c.customer_id; ``` Запрос для MySQL 8 вычисляет интервалы между соседними заказами и усредняет их по клиенту. Для клиентов с нулём или одним заказом средний интервал равен NULL. При одинаковом времени заказов учитывается нулевой интервал. :::note[Ссылки для изучения] 1. [LAG: предыдущее значение в упорядоченном разделе и NULL для первой строки (PostgreSQL)](https://postgrespro.ru/docs/postgresql/18/functions-window) ::: --- ## Задача №3 [#q-14bee738d69b815db1e4f973d6711ce6] ```text Предложить SQL запрос для оценки качества данных в динамике по заполненности. В таблице table есть отчётные даты report_dt на конец месяца, номера договоров id не Null. Названия полей по которым анализируем пропуски: feature1, feature2. Вид таблицы: table(report_dt, id, feature1, feature2) Пример строки: '2024-01-31', 457294, 1, Null '2024-02-28', 784934, Null, 3 ``` Решение: ```sql SELECT report_dt, COUNT(id) AS total_records, COUNT(feature1) AS filled_feature1, COUNT(feature2) AS filled_feature2, 1.0 * COUNT(feature1) / COUNT(*) AS feature1_completion_rate, 1.0 * COUNT(feature2) / COUNT(*) AS feature2_completion_rate FROM "table" GROUP BY report_dt ORDER BY report_dt; ``` :::note[Ссылки для изучения] 1. [COUNT(column) и COUNT(\*): заполненные значения и общее число строк](https://postgrespro.ru/docs/postgresql/18/sql-expressions#SYNTAX-AGGREGATES) 1. [GROUP BY и ORDER BY: расчёт показателей отдельно для каждой отчётной даты](https://postgrespro.ru/docs/postgresql/18/sql-select) ::: --- ## Задача №4 [#q-14bee738d69b815a87e5d5fe4e0dd173] ```sql -- Вот условие задания: CREATE TABLE shop ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, city VARCHAR(255) NOT NULL ); CREATE TABLE cheque ( uid UUID NOT NULL DEFAULT gen_random_uuid(), created_at TIMESTAMP NOT NULL DEFAULT now(), "sum" DECIMAL(10, 2) NOT NULL, shop_id INT NOT NULL, customer_id BIGINT NOT NULL, PRIMARY KEY (uid), FOREIGN KEY (shop_id) REFERENCES shop (id) ); -- Вот само задание, которое нужно решить: -- Вывести средний чек для покупателя за последнюю неделю, -- когда у него была покупка: -- покупатель - средний чек - дата последней покупки. ``` Решение для PostgreSQL. Под последней неделей понимаем календарную неделю с последней покупкой каждого клиента. ```sql WITH last_dates AS ( SELECT customer_id, MAX(created_at) AS last_purchase_date FROM cheque GROUP BY customer_id ) SELECT c.customer_id, AVG(c."sum") AS average_check, d.last_purchase_date FROM cheque c JOIN last_dates d ON c.customer_id = d.customer_id WHERE c.created_at >= date_trunc('week', d.last_purchase_date) AND c.created_at < date_trunc('week', d.last_purchase_date) + INTERVAL '1 week' GROUP BY c.customer_id, d.last_purchase_date; ``` :::note[Ссылки для изучения] 1. [date_trunc и арифметика интервалов: календарные границы недели в PostgreSQL](https://postgrespro.ru/docs/postgresql/18/functions-datetime#FUNCTIONS-DATETIME-TRUNC) ::: --- ## Задача №5 [#q-14bee738d69b81b2aa56d9b5a566045c] ```text Есть две таблицы А=10 строк В=50 строк какое максимальное и минимальное количество строк получим при A left join B ``` Решение: При выполнении операции left join (или left outer join) в SQL, результат будет содержать все строки из таблицы A, даже если в таблице B нет соответствующих строк. Таким образом, минимальное количество строк в результирующей таблице будет равно количеству строк в таблице A, то есть 10 строк. Максимум составляет 10 × 50 = 500 строк, если каждая строка A соответствует каждой строке B по условию соединения. :::note[Ссылки для изучения] 1. [Число строк при JOIN: декартово произведение и сохранение левой таблицы в LEFT JOIN](https://postgrespro.ru/docs/postgresql/18/queries-table-expressions#QUERIES-JOIN) :::