Два продвинутых SQL-метода, которые могут значительно улучшить ваши запросы

SQL — это основа для каждого специалиста по работе с данными. Неважно, являетесь ли вы аналитиком данных, специалистом по данным или инженером по обработке данных, вам необходимо иметь четкое представление о том, как писать чистые и эффективные SQL-запросы.

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

В одной из прошлых моих статей мы узнали, что такое SQL, рассмотрели некоторые основные команды SQL, такие как SELECT, FROM и WHERE, охватывающие большинство основных запросов, с которыми вы столкнетесь при использовании SQL.

Но что произойдет, если этих простых команд недостаточно? Что произойдет, если нужные вам данные требуют более надежного подхода к запросу?

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

Чтобы помочь нам изучить эти методы, мы будем использовать онлайн-редактор SQL под названием DB Fiddle (настроен на SQLite v3.39) и набор данных о продолжительности поездки на такси, полученный из Google Cloud (лицензия NYC Open Data).

Подготовка данных

Если вам не интересно узнать, как я подготовил набор данных, не стесняйтесь пропустить этот раздел и вставить следующий код в скрипт БД для создания схемы:

CREATE TABLE taxi (
  id varchar,
  vendor_id integer,
  pickup_datetime datetime,
  dropoff_datetime datetime,
  trip_seconds integer,
  distance float
);

INSERT INTO taxi 
VALUES
('id2875421', 2, '2016-03-14 17:24:55', '2016-03-14 17:32:30', 455, 0.93), 
('id2377394', 1, '2016-06-12 00:43:35', '2016-06-12 00:54:38', 663, 1.12), 
('id3858529', 2, '2016-01-19 11:35:24', '2016-01-19 12:10:48', 2124, 3.97), 
('id3504673', 2, '2016-04-06 19:32:31', '2016-04-06 19:39:40', 429, 0.92), 
('id2181028', 2, '2016-03-26 13:30:55', '2016-03-26 13:38:10', 435, 0.74), 
('id0801584', 2, '2016-01-30 22:01:40', '2016-01-30 22:09:03', 443, 0.68), 
('id1813257', 1, '2016-06-17 22:34:59', '2016-06-17 22:40:40', 341, 0.82), 
('id1324603', 2, '2016-05-21 07:54:58', '2016-05-21 08:20:49', 1551, 3.55), 
('id1301050', 1, '2016-05-27 23:12:23', '2016-05-27 23:16:38', 255, 0.82), 
('id0012891', 2, '2016-03-10 21:45:01', '2016-03-10 22:05:26', 1225, 3.19), 
('id1436371', 2, '2016-05-10 22:08:41', '2016-05-10 22:29:55', 1274, 2.37), 
('id1299289', 2, '2016-05-15 11:16:11', '2016-05-15 11:34:59', 1128, 2.35), 
('id1187965', 2, '2016-02-19 09:52:46', '2016-02-19 10:11:20', 1114, 1.16), 
('id0799785', 2, '2016-06-01 20:58:29', '2016-06-01 21:02:49', 260, 0.62), 
('id2900608', 2, '2016-05-27 00:43:36', '2016-05-27 01:07:10', 1414, 3.97), 
('id3319787', 1, '2016-05-16 15:29:02', '2016-05-16 15:32:33', 211, 0.41), 
('id3379579', 2, '2016-04-11 17:29:50', '2016-04-11 18:08:26', 2316, 2.13), 
('id1154431', 1, '2016-04-14 08:48:26', '2016-04-14 09:00:37', 731, 1.58), 
('id3552682', 1, '2016-06-27 09:55:13', '2016-06-27 10:17:10', 1317, 2.86), 
('id3390316', 2, '2016-06-05 13:47:23', '2016-06-05 13:51:34', 251, 0.81), 
('id2070428', 1, '2016-02-28 02:23:02', '2016-02-28 02:31:08', 486, 1.56), 
('id0809232', 2, '2016-04-01 12:12:25', '2016-04-01 12:23:17', 652, 1.07), 
('id2352683', 1, '2016-04-09 03:34:27', '2016-04-09 03:41:30', 423, 1.29), 
('id1603037', 1, '2016-06-25 10:36:26', '2016-06-25 10:55:49', 1163, 3.03), 
('id3321406', 2, '2016-06-03 08:15:05', '2016-06-03 08:56:30', 2485, 12.82), 
('id0129640', 2, '2016-02-14 13:27:56', '2016-02-14 13:49:19', 1283, 2.84), 
('id3587298', 1, '2016-02-27 21:56:01', '2016-02-27 22:14:51', 1130, 3.77), 
('id2104175', 1, '2016-06-20 23:07:16', '2016-06-20 23:18:50', 694, 2.33), 
('id3973319', 2, '2016-06-13 21:57:27', '2016-06-13 22:12:19', 892, 1.57), 
('id1410897', 1, '2016-03-23 14:10:39', '2016-03-23 14:49:30', 2331, 6.18);

После запуска SELECT * from taxi вы должны получить результирующую таблицу, которая выглядит следующим образом:

Для людей, которым интересно, как на самом деле появилась эта таблица, я отфильтровал данные до первых 30 строк и оставил только те столбцы, которые вы видите выше. Что касается поля distance, я вычислил ортодромическое расстояние между координатами посадки и высадки (широта и долгота).

Ортодромическое расстояние — это кратчайшее расстояние между двумя точками на сфере, так что на самом деле это оказывается заниженной оценкой реального расстояния, проехавшего такси. Однако для целей того, что мы делаем сегодня, мы можем пока проигнорировать это.

Формулу для расчета ортодромического расстояния можно найти здесь . Теперь вернемся к SQL.

Обобщённое табличное выражение (CTE)

Обобщённое табличное выражение (CTE) — это временная таблица, которую вы возвращаете в запросе. Вы можете думать об этом как о запросе внутри запроса. Они помогают не только разбивать ваши запросы на более читаемые фрагменты, но и создавать новые запросы на основе определённого CTE.

Чтобы продемонстрировать это, предположим, что мы хотим проанализировать поездки на такси с разбивкой по часам дня и отфильтровать поездки, которые были совершены в период с января по март 2016 года.

SELECT CAST(STRFTIME('%H', pickup_datetime) AS INT) AS hour_of_day, 
       trip_seconds,
       distance
FROM taxi
WHERE pickup_datetime > '2016-01-01'
  AND pickup_datetime < '2016-04-01'
ORDER BY hour_of_day;

Достаточно легко! Давайте сделаем ещё один шаг вперед!

Предположим, теперь мы хотим вычислить количество поездок и среднюю скорость для каждого часа. Именно здесь мы можем использовать CTE, чтобы сначала получить временную таблицу, подобную той, которую мы обсуждали выше, а затем выполнить запрос для подсчета количества поездок и средней скорости по часам за день.

Вы можете определить CTE с помощью операторов WITH и AS.

WITH relevantrides AS
(
SELECT CAST(STRFTIME('%H', pickup_datetime) AS INT) AS hour_of_day, 
       trip_seconds,
       distance
FROM taxi
WHERE pickup_datetime > '2016-01-01'
  AND pickup_datetime < '2016-04-01'
ORDER BY hour_of_day
)
SELECT hour_of_day,
       COUNT(1) as num_trips,
       ROUND(3600 * SUM(distance) / SUM(trip_seconds), 2) as avg_speed
FROM relevantrides
GROUP BY hour_of_day
ORDER BY hour_of_day;

Альтернативой использованию CTE является простое помещение временной таблицы в выражение FROM (см. код ниже), что даст такой же результат. 

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

SELECT hour_of_day,
       COUNT(1) as num_trips,
       ROUND(3600 * SUM(distance) / SUM(trip_seconds), 2) as avg_speed
FROM (
 SELECT CAST(STRFTIME('%H', pickup_datetime) AS INT) AS hour_of_day, 
           trip_seconds,
           distance
 FROM taxi
 WHERE pickup_datetime > '2016-01-01'
    AND pickup_datetime < '2016-04-01'
 ORDER BY hour_of_day
)
GROUP BY hour_of_day
ORDER BY hour_of_day;

В результате видно, что такси, как правило, движутся медленнее (более низкая средняя скорость) в час пик, из-за плотного движения, потому, что люди едут на работу и обратно.

Оконные функции

Оконная функция в SQL – функция, которая работает с выделенным набором строк (окном, партицией) и выполняет вычисление для этого набора строк в отдельном столбце. 

Чтобы полностью понять, как работают оконные функции, давайте сначала используем предложение GROUP BY.

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

SELECT CAST(STRFTIME('%m', pickup_datetime) AS INT) AS month,
       COUNT(1) AS trip_count,
       ROUND(SUM(distance), 3) AS total_distance,
       ROUND(AVG(distance), 3) AS avg_distance,
       MIN(distance) AS min_distance,
       MAX(distance) AS max_distance
FROM taxi
GROUP BY month;

В приведённом выше примере мы вычислили количество, сумму, среднее, минимальное и максимальное расстояние за каждый отдельный месяц. Обратите внимание, что наша исходная таблица такси с 30 строками теперь уменьшилась до шести строк, по одной для каждого отдельного месяца.

Итак, что на самом деле происходит за кулисами запроса? Во-первых, SQL сгруппировал все 30 строк исходной таблицы по месяцам. Затем он применил соответствующие расчеты на основе значений за каждый отдельный месяц.

Возьмем в качестве примера январь. В датасете есть две поездки, которые произошли в январе месяце, с расстоянием 3,97 и 0,68 км соответственно. Мы вычислили расстояние, сумму, среднее, минимальное и максимальное на основе этих двух значений. Далее процесс повторяется для других месяцев, пока в конце концов мы не получим результат, который, показан выше.

Теперь держите эту мысль, пока мы начинаем исследовать, как работают оконные функции. Существует три категории оконных функций: агрегатные функции, ранжирующие функции и навигационные функции. Мы рассмотрим примеры каждой из них.

Агрегатные функции

Мы уже видели агрегатные функции в нашем предыдущем примере. Агрегатные функции включают в себя такие функции, как count, sum, avg, min и max.

Оконные функции отличаются от GROUP BY, количеством строк в конечном выводе. В частности, мы увидели, что после агрегирования по месяцам в нашей конечной таблице осталось всего шесть строк (по одной строке для каждого отдельного месяца).

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

Синтаксис для выполнения оконной функции: OVER(PARTITION BY ...). Вы можете думать о ней, как об предложении GROUP BY из нашего предыдущего примера.

Давайте посмотрим, как это работает на практике:

WITH aggregate AS
(
SELECT id,
       pickup_datetime,
       CAST(STRFTIME('%m', pickup_datetime) AS INT) AS month, 
       distance
FROM taxi
)
SELECT *,
       COUNT(1) OVER(PARTITION BY month) AS trip_count,
       ROUND(SUM(distance) OVER(PARTITION BY month), 3) AS total_month_distance,
       ROUND(AVG(distance) OVER(PARTITION BY month), 3) AS avg_month_distance,
       MIN(distance) OVER(PARTITION BY month) AS min_month_distance,
       MAX(distance) OVER(PARTITION BY month) AS max_month_distance
FROM aggregate;

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

Вы заметите, что значения после агрегирования не изменились, а просто отображаются в виде повторяющихся строк в таблице. Например, первые две строки (январь) содержат те же значения количества поездок, общего месячного расстояния, среднего месячного расстояния, минимального месячного расстояния и максимального месячного расстояния, что и раньше. То же самое относится и к другим месяцам.

Оконные функции полезны тем, что они позволяют нам сравнить каждое значение строки с агрегированным значением. С ними мы можем легко сравнить расстояние в каждой строке со среднемесячным значением, минимальным и максимальным значением и так далее.

Ранжирующие функции

Другим типом оконной функции являются ранжирующие функции. Как следует из названия, они ранжируют группу строк на основе совокупного поля.

WITH ranking AS
(
SELECT id,
       pickup_datetime,
       CAST(STRFTIME('%m', pickup_datetime) AS INT) AS month, 
       distance
FROM taxi
)
SELECT *,
       RANK() OVER(ORDER BY distance DESC) AS overall_rank,
       RANK() OVER(PARTITION BY month ORDER BY distance DESC) AS month_rank
FROM ranking
ORDER BY pickup_datetime;

В приведённом выше примере у нас есть два столбца рейтинга: один для общего рейтинга (от 1 до 30) и один для ежемесячного рейтинга, оба в порядке убывания.

Чтобы указать порядок при ранжировании, вам нужно будет использовать оператор ORDER BY внутри OVER.

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

Навигационные функции

И последнее, но не менее важное: у нас есть навигационные функции.

Навигационная функция присваивает значение на основе значения в строке, отличной от текущей строки. Некоторые общие функции навигации включают FIRST_VALUE, LAST_VALUE, LEAD и LAG.

SELECT id,
       pickup_datetime,
       distance,
       LAG(distance) OVER(ORDER BY pickup_datetime) AS prev_distance,
       LEAD(distance) OVER(ORDER BY pickup_datetime) AS next_distance
FROM taxi
ORDER BY pickup_datetime;

В приведенных выше примерах мы использовали функцию LAG для возврата значения предыдущей строки и функцию LEAD для возврата значения последующей строки. Обратите внимание, что первая строка столбца отставания равна нулю, тогда как последняя строка столбца опережения тоже равна нулю.

SELECT id,
       pickup_datetime,
       distance,
       LAG(distance, 2) OVER(ORDER BY pickup_datetime) AS prev_distance,
       LEAD(distance, 2) OVER(ORDER BY pickup_datetime) AS next_distance
FROM taxi
ORDER BY pickup_datetime;

Аналогично, мы также можем смещать функции LEAD и LAG, т.е. начинать с определенного индекса или позиции. Когда для смещения установлено значение два, вы можете видеть, что первые две строки столбца отставания равны нулю, и последние две строки столбца опережения тоже равны нулю.

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

Подводя итог, CTE — это временная таблица или запрос внутри запроса. Они используются для разделения запросов на более читаемые фрагменты. С другой стороны, оконные функции выполняют агрегирование групп строк и возвращают результаты для каждой строки в исходной таблице.

Если вы хотите улучшить понимание этих методов, я настоятельно рекомендую вам начать применять их в своих SQL-запросах либо на работе, либо при решении задач на собеседовании, либо просто поэкспериментировав со случайными наборами данных. Практика делает совершенным, не так ли?