SQL: запросы и аналитика

Все темы Data Scientist

Подтемы:

• Основные концепции SQL
• Фильтрация строк
• JOIN Операторы и операции с таблицами
• Оконные функции
• PIVOT Таблицы
• Лайфкодинг SQL

Для чего используется LIKE?

Оператор LIKE в SQL используется для выполнения поиска в текстовых данных с использованием шаблонов. Он позволяет выбирать строки, которые соответствуют определенному шаблону, который может содержать специальные символы, такие как % (заменяющий любое количество символов) и _ (заменяющий один символ). Это полезно для поиска строк с определенными паттернами, например, имена, которые начинаются или заканчиваются определенной последовательностью символов, или содержат определенные символы в середине строки.

Что такое обобщенное табличное выражение (CTE) и как оно используется?

CTE представляет именованный результат запроса, заданный через WITH и доступный внутри одной SQL-команды. Он помогает разбить сложный запрос на части и поддерживает рекурсивные вычисления. CTE не гарантирует ускорение или однократное вычисление: материализация и встраивание в основной запрос зависят от СУБД и плана.

Что представляют собой значения null и NaN, и в чем заключается их различие? Каким образом следует обрабатывать данные с такими типами?

Значения NULL и NaN используются для представления отсутствующих или неопределенных данных.

NULL:

• В SQL, NULL используется для обозначения отсутствия значения.

• Он не является нулевым значением, а скорее указывает на отсутствие какого-либо значения.

• NULL не равен ни одному другому значению, даже самому себе.

NaN (Not a Number):

• NaN используется в числовых вычислениях, чтобы указать на неопределенный результат или ошибку.

• Он обычно возникает при выполнении некорректных математических операций, таких как деление на ноль или попытка получить квадратный корень отрицательного числа.

Различие между NULL и NaN заключается в их контексте использования: NULL применяется в SQL для обозначения отсутствия значения, в то время как NaN используется в числовых вычислениях для обозначения неопределенного результата.

В чем разница query и key?

В контексте SQL, термины "query" и "key" относятся к разным понятиям:

1. Query (Запрос): Это инструкция или набор инструкций, написанных на языке SQL, которые позволяют выполнить операции с данными в базе данных. Запросы могут включать выборку данных, их вставку, обновление, удаление и множество других операций. Например, SELECT * FROM users; — это запрос на выборку всех данных из таблицы пользователей.

2. Key (Ключ): Это атрибут или набор атрибутов в таблице, который помогает SQL-системе быстро и эффективно организовать, доступить и поддерживать целостность данных. Существуют различные типы ключей:

   • Primary Key (Первичный ключ): Уникально идентифицирует каждую запись в таблице.

   • Foreign Key (Внешний ключ): Обеспечивает ссылочную целостность между двумя таблицами.

   • Unique Key (Уникальный ключ): Гарантирует, что все значения в столбце уникальны.

Таким образом, "query" это команды для работы с данными, а "key" — это элементы структуры базы данных, обеспечивающие управление и целостность данных.

В чем разница между операторами DELETE и TRUNCATE?

Между двумя этими операторами, есть основная разница:

• DELETE удаляет строки в таблице, одну за другой, и поддерживает транзакции и триггеры.

• TRUNCATE удаляет все строки без WHERE. Возможность отката и работа триггеров зависят от СУБД: в PostgreSQL TRUNCATE транзакционен и может быть отменён через ROLLBACK.

В каком логическом порядке SQL выполняет FROM, JOIN, WHERE и SELECT?

Логически запрос обрабатывается так: FROM и JOIN, затем WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. Поэтому alias из SELECT обычно недоступен в WHERE, а фильтр по агрегату ставят в HAVING. Физический план может переставлять операции, если сохраняется результат. Порядок полезен для понимания области видимости и гранулярности, но не описывает фактический алгоритм исполнения. Особенности alias и LIMIT зависят от диалекта.

Чем отличаются WHERE и HAVING?

WHERE и HAVING - это два разных оператора в SQL для фильтрации данных, применяемых в разных контекстах:

WHERE:

• WHERE используется для фильтрации строк до группировки или агрегации данных.

• Он применяется к отдельным строкам и определяет, какие строки будут включены в результат запроса.

• WHERE применяется к строкам до выполнения агрегации.

HAVING:

• HAVING используется для фильтрации результатов агрегации данных после выполнения группировки.

• Он применяется к группам строк, сгруппированным по определенным критериям, и определяет, какие группы будут включены в результат запроса.

• HAVING применяется к результатам агрегации после выполнения группировки.

Перечислите типы JOINов, дайте характеристику каждому.

В SQL существует несколько типов операторов JOIN:

1. INNER JOIN:

   • Возвращает только те строки, которые имеют совпадения в обоих таблицах по указанным условиям.

   • Если в обеих таблицах нет совпадений, строки не возвращаются.

2. LEFT JOIN (или LEFT OUTER JOIN):

   • Возвращает все строки из левой таблицы (первой в запросе), а также строки из правой таблицы, которые имеют совпадения с условием JOIN.

   • Если в правой таблице нет совпадений, возвращается NULL для столбцов правой таблицы.

3. RIGHT JOIN (или RIGHT OUTER JOIN):

   • Возвращает все строки из правой таблицы (второй в запросе), а также строки из левой таблицы, которые имеют совпадения с условием JOIN.

   • Если в левой таблице нет совпадений, возвращается NULL для столбцов левой таблицы.

4. FULL JOIN (или FULL OUTER JOIN):

   • Возвращает все строки из обеих таблиц, совпадающие и несовпадающие по условию JOIN.

   • Если нет совпадений, возвращаются NULL значения для недостающих столбцов.

5. CROSS JOIN:

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

В одной таблице 2 строчки, в другой 3, сколько минимум строк будет при иннер и лефт джойне? А сколько максимум вне зависимости от джойна?

При выполнении INNER JOIN минимальное количество строк равно 0, если нет совпадений между строками обеих таблиц.

При выполнении LEFT JOIN минимальное количество строк равно количеству строк в левой таблице, т.е. 2 строки, так как левые таблицы строки всегда включаются, даже если нет совпадений.

Максимум строк (вне зависимости от типа JOIN): 2*3=6.

Какие существуют оконные функции?

Оконные функции в SQL - это функции, которые выполняют вычисления на группах строк, называемых окнами, в пределах результата запроса. Они позволяют выполнять агрегатные функции (например, суммирование, подсчет, вычисление среднего значения) и аналитические функции (например, вычисление ранжирования, отступов, смещений) с учетом порядка и разбиения данных на окна. Оконные функции обычно используются вместе с ключевым словом OVER, которое определяет окно, над которым будет выполняться функция.

Какие оконные функции ранжирования вы знаете в SQL?

Некоторые из распространенных оконных функций ранжирования в SQL:

1. ROW_NUMBER(): Присваивает каждой строке уникальный числовой ранг в пределах заданного окна, начиная с 1 и увеличиваясь на 1 для каждой следующей строки.

2. RANK(): Присваивает каждой строке ранг в пределах заданного окна. Если несколько строк имеют одинаковые значения, им присваивается одинаковый ранг, при этом следующий ранг увеличивается на количество строк, имеющих предыдущий ранг.

3. DENSE_RANK(): Подобно функции RANK(), но не допускает разрывов в последовательности рангов. Если несколько строк имеют одинаковые значения, им присваиваются одинаковые ранги, но следующий ранг не увеличивается на количество строк с предыдущим рангом.

4. NTILE(n): Делит строки раздела на n групп с максимально близким числом строк и возвращает номер группы.

Как ROW_NUMBER, RANK и DENSE_RANK нумеруют пять строк, если среди значений есть совпадения?

Для значений ORDER BY 10, 20, 20, 30, 40 результаты будут следующими.

1. ROW_NUMBER(): 1, 2, 3, 4, 5. Номера уникальны; порядок строк с одинаковым значением без дополнительного ключа не определён.

2. RANK(): 1, 2, 2, 4, 5. Равные значения получают одинаковый ранг, после них остаётся пропуск.

3. DENSE_RANK(): 1, 2, 2, 3, 4. Равные значения получают одинаковый ранг, пропусков нет.

Знакомы ли с оконными функциями? С рекурсивными подзапросами? Что знаете из этого?

Да, я знаком с оконными функциями и рекурсивными подзапросами в SQL.

Оконные функции позволяют выполнять вычисления на группах строк, называемых “окнами”, внутри результирующего набора строк. Они часто используются для агрегации данных в пределах группы строк или для анализа временных рядов.

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

Расскажите про PIVOT таблицы.

PIVOT в SQL - это операция, которая позволяет преобразовывать строки данных в столбцы. Обычно это используется для трансформации результатов запроса, где значения из одного столбца (обычно строковые значения) становятся заголовками столбцов. Это позволяет легче анализировать данные, особенно в случае агрегированных результатов. PIVOT в SQL использует агрегирующую функцию, такую как SUM или MAX, чтобы сгруппировать данные по определенным критериям. Пример использования PIVOT может выглядеть так:

SELECT *
FROM (
    SELECT category, year, sales
    FROM sales_table
) AS src
PIVOT (
    SUM(sales)
    FOR year IN ([2019], [2020], [2021])
) AS piv;

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

Задача №1

Пусть имеем таблицу с 3 столбцами: id, name и age, которая содержит 100 млн строк.
Допустим, неизвестно, является ли id первичным ключом таблицы.
Хотим проверить, так ли это. Каким простым запросом можно сделать такую валидацию?
Допустим, нужно добавить в эту таблицу столбец с количеством раз, которое каждое имя (поле name)
встречается в таблице. Как это сделать? Допустим, нужно определить возраст,
который чаще всего встречается в таблице.

Как это сделать?

Решение:

• Этот запрос проверяет отсутствие NULL и повторов в текущих данных id. Наличие ограничения PRIMARY KEY проверяют отдельно в метаданных схемы.

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.

• Чтобы добавить столбец с количеством раз, которое каждое имя встречается в таблице, можно использовать следующий запрос:

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 в таблицу и затем обновляет его значения, подсчитывая количество строк с каждым именем.

• Чтобы определить возраст, который чаще всего встречается в таблице, можно использовать следующий запрос:

SELECT age
FROM your_table_name
GROUP BY age
ORDER BY COUNT(*) DESC
LIMIT 1;

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

Задача №2

Для каждого клиента вычислить средний интервал между заказами в днях, используя следующие таблицы:
Таблица order содержит следующие столбцы:
- order_id: уникальный идентификатор заказа.
- date_time: дата и время заказа.
- amount: сумма заказа.
- customer_id: идентификатор клиента, связанный с заказом.
Таблица customer содержит следующие столбцы:
- customer_id: уникальный идентификатор клиента.
- age: возраст клиента.
Для каждого клиента необходимо вычислить средний интервал между его заказами в днях.

Для вычисления среднего интервала между заказами для каждого клиента, мы можем сделать следующее:

1. Сначала для каждого клиента упорядочим заказы по date_time и order_id и найдём предыдущий заказ с помощью LAG.

2. Вычислим длительность между соседними заказами в секундах и переведём в дни.

3. Наконец, мы сгруппируем результаты по customer_id и вычислим среднее значение интервала для каждого клиента.

Вот как это можно сделать в 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. При одинаковом времени заказов учитывается нулевой интервал.

Задача №3

Предложить 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

Решение:

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;

Задача №4

-- Вот условие задания:
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. Под последней неделей понимаем календарную неделю с последней покупкой каждого клиента.

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;

Задача №5

Есть две таблицы А=10 строк В=50 строк какое максимальное и минимальное количество строк получим при A left join B

При выполнении операции left join (или left outer join) в SQL, результат будет содержать все строки из таблицы A, даже если в таблице B нет соответствующих строк.

Таким образом, минимальное количество строк в результирующей таблице будет равно количеству строк в таблице A, то есть 10 строк.

Максимум составляет 10 × 50 = 500 строк, если каждая строка A соответствует каждой строке B по условию соединения.