Как писать модульный, читаемый SQL с помощью именуемых наборов результатов и функций

1656623646 kak pisat modulnyj chitaemyj sql s pomoshhyu imenuemyh naborov rezultatov

Лак Лакшмонан

Мой профессиональный путь в компьютерах включал C++, затем Java, а теперь Python. SQL остается, в лучшем случае, на иностранном языке. Поэтому для собственного ума я перевел некоторые из моих лучших практик программирования в SQL. В частности, заявление WITH было моим другом.

NNhuWHnTuY2hkZmbrzZY1qw1PoqpsDazq--W
Если вы пишете модульный, читаемый SQL, у вас будет время для долгих поездок на велосипеде в выходные

Для демонстрации я использую общедоступный набор данных о лондонских велосипедах в Google BigQuery. Скажем, мы хотим узнать, арендуют ли велосипеды на более продолжительный срок в выходные дни.

1. Константы, а не жестко закодированные числа

Хорошим первым шагом является определение констант, которые мы будем использовать в моем запросе (см. полный запрос):

#standardsqlWITH constants AS (  SELECT  600 AS SHORT_DUR,         1800 AS LONG_DUR,         ['Sun', 'Mon', 'Tues', 'Wed', 'Thurs', 'Fri', 'Sat'] AS daysofweek),

Здесь я определяю поездки продолжительностью менее 10 минут как «короткие», а поездки продолжительностью более 30 минут – как «длительные». Обратите внимание, как, предопределив эти константы вперед, я могу облегчить попытку разных чисел. Использование именуемых констант также сделает запрос гораздо более читабельным.

2. Именуемые наборы результатов

Другое дело, которое вы хотите сделать, чтобы увеличить читабельность, – это разложить запрос на именуемые наборы результатов. Вместо того, чтобы писать запросы и подзапросы и подсчитывать скобки, я часто использую операторы WITH. Как и функции в таких языках как C++ или Python, именуемые наборы результатов позволяют как повторное использование, так и логическое разделение.

Сначала я определяю запрос, чтобы получить нужные поля, и называю этот набор результатов как bikeshare (полный запрос):

bikeshare AS (  SELECT    IF(duration < SHORT_DUR, 1, 0) AS short_ride,    IF(duration > LONG_DUR,  1, 0) AS long_ride,    daysofweek[ORDINAL(EXTRACT(DAYOFWEEK FROM start_date))] AS dayofweek  FROM `bigquery-public-data.london_bicycles.cycle_hire`, constants)

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

3. Функции SQL

Вы можете разложить сложные запросы с помощью ключевого слова WITH и создать именуемые наборы результатов. Но как насчет сложного разбора? В фрагменте выше строка извлекает день недели и индексирует в дней недели массив не читается, не правда ли? И по всей вероятности, что это то, что вы захотите в другом месте.

Используйте функцию SQL, чтобы вы могли повторно использовать это выражение:

CREATE TEMPORARY FUNCTION dayOfWeek(ts TIMESTAMP,                                     days ARRAY<STRING>) AS(  days[ORDINAL(EXTRACT(DAYOFWEEK FROM ts))]);

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

bikeshare AS (  SELECT    IF(duration < SHORT_DUR, 1, 0) AS short_ride,    IF(duration > LONG_DUR,  1, 0) AS long_ride,    dayOfWeek(start_date, daysofweek) AS dayofweek  FROM `bigquery-public-data.london_bicycles.cycle_hire`, constants)

Сама простота

После того, как мы назвали константы и назвали наборы результатов, окончательный запрос сам по себе простота:

SELECT   dayofweek,  SUM(short_ride)/COUNT(short_ride) AS frac_short_rides,  SUM(long_ride)/COUNT(long_ride)  AS frac_long_rides,  COUNT(short_ride) AS num_all_ridesFROM  bikeshareGROUP BY  dayofweekORDER BY frac_long_rides DESC

Вот полный запрос и полученный результат:

3IL2XIQaZ4oQV485yTgWj6QSgupIYfXsNXzT

Будние дни предназначены для скорых коротких поездок, а выходные – для долгих, медленных поездок. Имеет полный смысл!

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *