Поднимите свои навыки кодирования SQL на новый уровень с помощью этих трех простых приемов.
вступление
Добро пожаловать в последнюю часть серии. В предыдущих двух частях мы создали прочную основу для базовых и расширенных SQL-запросов и выполнили их с помощью SSMS и демонстрационной базы данных AdventureWorks. В этой заключительной части мы рассмотрим набор навыков, которые выведут ваш SQL на новый уровень. Они не менее важны, если не более, чем просто умение писать на SQL, поскольку именно они превратят ваши запросы в точный, легко читаемый воспроизводимый актив!
Часть 3
- QA тестирование ваших SQL-запросов
- Оптимизация SQL-запросов
- Стандарты и лучшие практики кодирования для науки о данных
- План обучения
👇 Ссылки на части 1 и 2
1. Тестирование QA ваших SQL-запросов
Распространенное заблуждение, которого я также твердо придерживался в начале своей карьеры, состоит в том, что хорошие специалисты по обработке и анализу данных настолько хорошо умеют кодировать SQL, что никогда не делают ошибок. На самом деле все ошибаются. Даже хирурги и пилоты склонны к ошибкам. Частота ошибок будет разной в зависимости от опыта, но, в конце концов, у всех есть выходной день или дни.
К счастью, существуют контрмеры, позволяющие свести к минимуму такие ошибки. Они называются контрольные списки! Это не новая идея и не уникальная для специалистов по данным. В авиакомпаниях каждый пилот выполняет один и тот же набор основных тестов перед вылетом или при посадке самолета. Точно так же есть шаги, которые вы можете предпринять, чтобы проверить, что ваш SQL-запрос не возвращает неверных результатов (т. е. безопасен для отправления). Ниже я выделил три основных.
1.1 Уникальность результатов
Первый пункт в контрольном списке SQL — уникальность ваших результатов. В большинстве ваших запросов как специалисту по данным вам придется объединять несколько таблиц, чтобы получить желаемый результат. Когда между двумя таблицами существует отношение «один ко многим», объединенная таблица будет иметь дубликаты. Таким образом, в каждом запросе, который вы запускаете, убедитесь, что вы проверили уровень (т. е. столбец), на котором ваша результирующая объединенная таблица уникальна.
WITH orders as( -- unique at the SalesOrderID level SELECT * FROM Sales.SalesOrderHeader ), orders_detail as ( -- unique at the SalesOrderDetailID level SELECT * FROM Sales.SalesOrderDetail ) -- unique at the SalesOrderDetailID level SELECT * /* SELECT COUNT(*), COUNT(DISTINCT SalesOrderDetailID)*/ FROM orders o INNER JOIN orders_detail ol ON o.SalesOrderID = ol.SalesOrderID
В приведенном выше примере вы можете видеть, что я закомментировал строку 14, которую я использовал для проверки уровня моих результатов. Этот простой запрос сравнивает результат COUNT(*) с COUNT(DISTINCT column) и, если они совпадают, проверяет уникальность таблицы на этом уровне. Как видите, я добавил комментарий, указывающий на уникальность таблиц в результате и CTE. Эта практика также облегчит присоединение к столам на нужном уровне!
1.2 Вычисляемые столбцы
Одна из самых сложных частей ваших запросов — вычисляемые столбцы. Они подвержены как логическим, так и вычислительным ошибкам. Иногда проблема может заключаться в том, что столбец имеет значения NULL или что выбранная вами функция ранжирования не работает должным образом. В других случаях некоторые из ваших условий могут перекрываться в операторе CASE, что приводит к неправильным результатам.
- Выборочная проверка результатов (особенно для сложных случаев), используя пару случайно выбранных записей.
- Всегда используйте предложение ELSE в операторе CASE, чтобы пометить несовпадающие записи.
В приведенном выше примере проблема заключается в том, что в данных для столбца CurrencyRateID были значения NULL. Они были неправильно добавлены в группу «Высокий» в запросе правой панели, но правильно помечены с помощью инструкции ELSE для несоответствующих условий на левой панели.
1.3 Сравнение с исходными данными
Наконец, всегда полезно сравнить результаты верхнего уровня вашего запроса с информационной панелью компании. Это может быть так же просто, как отфильтровать ваши результаты на определенную дату и сравнить их с результатами на панели инструментов (например, количество заказов, общие расходы и т. д.). Эта часть особенно полезна для перепроверки того, что вы не пропустили необходимый фильтр работоспособности, например, исключение учетных записей тестировщиков или активности ботов.
2. Оптимизация SQL-запросов
Для целей этого раздела я создал новую таблицу, объединив несколько раз таблицу SalesOrderHeader. Новая таблица называется SalesOrderDetail_Compiled и содержит 65 миллионов строк.
2.1 Ограничение количества записей в CTE
Несмотря на то, что это кажется очень простым и понятным, по моему опыту обучения аналитиков, это наиболее часто пропускаемый шаг. Глядя на приведенный ниже пример, вы можете видеть, что мы значительно сократили время выполнения за счет агрегирования результатов в CTE и последующего их объединения (правая панель), а не получения всех строк, объединения и последующего агрегирования (левая панель).
2.2 Избегайте использования COUNT DISTINCT
Ключевое слово DISTINCT — одно из самых затратных в SQL. Это имеет смысл, так как для получения четких результатов SQL должен сканировать каждую строку столбца. Конечно, бывают случаи, когда COUNT(column) и COUNT(DISTINCT column) дадут один и тот же результат (т. е. когда столбец является первичным ключом). В таких случаях очевидной рекомендацией будет опустить ключевое слово DISTINCT.
Но что можно сделать со столбцами, имеющими дубликаты? В приведенном ниже примере мы создали новый двоичный столбец на правой панели. Записи, которые не совпали в левом соединении, будут иметь NULL и будут помечены как 0, а не NULL помечены как 1. Затем мы можем взять среднее значение этого столбца. Поскольку мы пометили 1 совпадающие записи и 0 остальные, их среднее значение даст нам тот же результат, что и соотношение различных счетчиков на левой панели. Мы надеемся, что это станет более ясным, глядя на снимок ниже.
В SQL Server вы также можете использовать индексы для предварительного сканирования определенных столбцов, что повышает производительность при использовании ключевого слова DISTINCT.
2.3. Функции, обернутые вокруг предложений JOIN и WHERE
Столкнувшись с функциями, обернутыми вокруг столбцов в предложении WHERE или JOIN, подумайте о том, чтобы переписать запрос, чтобы столбцы можно было оставить чистыми. Причина, по которой это увеличивает время обработки, заключается в том, что SQL должен вычислять функцию для каждого значения столбца, а затем фильтровать результаты.
2.4 Использование статистики оперативных запросов в SSMS
SSMS также предоставляет очень хороший инструмент «Живая статистика запросов». Вы можете найти его на панели инструментов, и если он выбран, он создаст новую вкладку в ваших результатах, которая будет демонстрировать ваш запрос в режиме реального времени во время его выполнения и предоставит вам статистику для каждого узла для выявления узких мест в вашем коде.
3. Стандарты кодирования данных и лучшие практики
Каждый раз, когда вы пишете фрагмент кода SQL, который сохраняется в вашей кодовой базе (вместе с другими сценариями SQL), вы либо создаете актив, либо пассив. Ресурс — это сценарий, который легко понять и запустить повторно. Напротив, пассив будет сценарием, для понимания которого требуется слишком много времени; вам не терпится закончить его и перейти к чему-то другому, а иногда вы даже можете выбросить его в мусорное ведро и начать с нуля (поскольку это будет проще, чем работать над существующим сценарием).
Вот что такое воспроизводимый код. Плохо иметь надстройку, которую мы можем использовать, когда есть свободное время. Речь идет о написании кода таким образом, чтобы сделать его воспроизводимым активом. Актив, который повысит ценность вашей работы как в настоящем, так и в будущем; для себя и других членов команды! Итак, что мы можем сделать, чтобы создать активы, а не пассивы?
3.1 Комментарии
Комментарии нужны для того, чтобы помочь вам в настоящем и будущем (и всем, кто будет работать над вашим кодом) быстро понять его:
- Вы можете использовать двойное тире, чтобы начать однострочный комментарий в большинстве СУБД.
- Прокомментируйте уровень, на котором ваши CTE и основной результат уникальны.
- Добавляйте комментарии для объяснения сложных вычисляемых полей.
- Добавьте комментарии, чтобы объяснить любую логику в вашем запросе, которая может потребовать уточнения в будущем (условия фильтрации и т. д.).
3.2 Форматирование
В отличие от тестирования вашего кода, при форматировании нет набора необходимых шагов, которые должен выполнить каждый. Цель состоит в том, чтобы упростить ваш код и сделать его легко читаемым и понятным. Любое форматирование, достигающее этой цели, приемлемо, если оно непротиворечиво. Ниже вы можете увидеть формат, который я использую.
- Используйте заглавные буквы для ключевых слов и предложений (WHERE, DISTINCT и т. д.).
- Используйте одиночный отступ табуляции для столбцов и SELECT в CTE
- Используйте CTE вместо подзапросов
- Используйте пробелы для разделения таких элементов, как предложения, предикаты и выражения в ваших запросах.
3.3 Модульность (CTE)
Использование CTE вместо подзапросов также является хорошим шагом к улучшению воспроизводимости вашего кода. В приведенном ниже примере показан код на левой панели (с использованием подзапросов, без комментариев и без форматирования) и его воспроизводимый аналог на правой панели.
4. План обучения
И вот мы подошли к заключительному разделу нашей серии из трех частей! 🚀🚀
Самая большая проблема в моем путешествии по SQL заключалась в том, чтобы найти то, что мне нужно было знать, и попрактиковаться в этом. Я оставил серию самодостаточной, так как у вас будет все необходимое, чтобы начать практиковаться позже. Это не означает, что мы рассмотрели все функции или все предложения и функциональные возможности SQL, но вместо этого мы рассмотрели все, что необходимо для начала вашего путешествия. Если на вашем пути вам нужно найти другую функцию, которую мы не рассмотрели, я надеюсь, что к настоящему времени у вас есть прочная основа, чтобы ловить эту рыбу самостоятельно. Есть также много бесплатных ресурсов, доступных в Интернете для ваших специальных нужд, но мои личные фавориты — W3schools и SQLServerTutorial.Net.
Для ваших практических занятий я также рекомендую следующее:
- Найдите в Интернете вопросы для собеседования по SQL и попробуйте решить их с помощью AdventureWorks2019 и SSMS.
- Практикуйте свой SQL хотя бы пару часов в неделю в течение месяца или двух, чтобы вы начали подсознательно думать о написании на SQL.
Я надеюсь, что вы нашли эту статью полезной и получили удовольствие от запросов!
Если у вас есть какие-либо вопросы или вам нужна дополнительная помощь, пожалуйста, не стесняйтесь комментировать ниже, и я отвечу быстро.
Оставайтесь на связи
Если вам нравится читать эту статью и вы хотите узнать больше, не забудьте подписаться, чтобы получать мои истории прямо на ваш почтовый ящик.
По ссылке ниже вы также можете найти бесплатное пошаговое руководство в формате PDF по выполнению кластерного анализа клиентов в реальном бизнес-сценарии с использованием методов обработки данных и лучших практик в R.
