30 SQL-запросов, объяснённых через их эквиваленты Pandas

В мире, где с 1974 года доминирует SQL, в 2008 году появился Pandas, предлагающий привлекательные функции, такие как встроенная визуализация и гибкая обработка данных. Он быстро стал популярным инструментом для исследования данных, затмив собой SQL.

Но не обманывайте себя, SQL по-прежнему держит свои позиции. Это второй по востребованности и третий по скорости роста язык для Data science (см. здесь ). Таким образом, в то время как Pandas привлекает всеобщее внимание, SQL остаётся жизненно важным навыком для любого специалиста по данным.

Давайте узнаем, как легко выучить SQL, если вы уже знаете Pandas.

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

В частности, мы будем напрямую запускать SQL-запросы в Jupyter Notebook без дополнительных шагов. Всё, что нам нужно сделать, это установить пакет ipython-sql с помощью pip:

После установки запустите новый сеанс Jupyter и выполните эту команду в блокноте:

и всё готово!

Чтобы проиллюстрировать, как работают основные операторы SQL, мы будем использовать базу данных Chinook SQLite, которая имеет 11 таблиц.

Чтобы загрузить набор данных и его 11 таблиц как отдельные переменные в нашу среду, мы можем запустить:

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

Допустимые пути всегда должны начинаться с префикса sqlite:/// для баз данных SQLite. Выше мы подключаемся к базе данных, хранящейся в папке «data» текущего каталога. Если вы хотите передать абсолютный путь, префикс должен начинаться с четырех косых черт:sqlite:////

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

Первое, что мы всегда делаем в Pandas, — это используем функцию .head() для первого взгляда на данные. Давайте узнаем, как это сделать в SQL:

Первое ключевое слово в приведённом выше запросе — SELECT. Оно эквивалентно оператору скобок в Pandas, где мы выбираем определённые столбцы. Но за ключевым словом SELECT следует * (звездочка). * — оператор SQL, который выбирает всё (все строки и столбцы) из таблицы, указанной после ключевого слова FROM. LIMIT используется для минимизации возвращаемого результата. Таким образом, приведённый выше запрос эквивалентен функции df.head().

Если вы не хотите выбирать все столбцы, вы можете указать одно или несколько имён столбцов после ключевого слова SELECT:

Эквивалентная операция Pandas:

Другое полезное ключевое слово в SQL — DISTINCT. Добавление этого ключевого слова перед именем любого столбца возвращает его уникальные значения:

Комментарии в SQL пишутся с двойным дефисом.

Точно так же, как у Pandas есть атрибут .shape для своих DataFrames, у SQL есть функция COUNT для отображения количества строк в таблице:

Более полезной информацией будет подсчёт количества уникальных значений в определённом столбце. Мы можем сделать это, добавив ключевое слово DISTINCT в COUNT:

Просто смотреть и считать строки может любой. Давайте посмотрим, как мы можем фильтровать строки на основе условий.

Во-первых, давайте посмотрим на песни, которые стоят больше доллара:

Условные операторы записываются в предложении WHERE, которое всегда следует после FROM и перед ключевыми словами LIMIT. Использование условий очень похоже на то, как мы это делаем в Pandas, но я осмелюсь сказать, что версия SQL более читабельна.

Вы также можете использовать функцию COUNT при использовании условных выражений. Например, давайте посмотрим количество песен с ценой от 1 до 10 долларов:

Выше мы связали два условия логическим оператором AND. Аналогично используются и другие логические операторы (OR, NOT).

Теперь давайте посмотрим все счета-фактуры, в которых в качестве города выставления счётов указан Париж или Берлин:

Оператор равенства в SQL требует только одного знака «=» (равно). Оператор неравенства представлен операторами '!=' или '<>':

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

Если бы мы хотели выбрать счета для пяти городов, нам пришлось бы написать пять связанных условий. К счастью, SQL поддерживает аналогичный оператор IN, поэтому .isin() нам не нужно:

Список значений после IN следует указывать в виде кортежа, а не списка. Вы также можете отменить условие с помощью ключевого слова NOT:

Другой распространённой операцией фильтрации числовых столбцов является выбор значений в пределах диапазона. Для этого можно использовать ключевое слово BETWEEN, которое эквивалентно pd.Series.between():

В каждом источнике данных есть пропущенные значения, и базы данных не являются исключением. Точно так же, как есть несколько способов исследовать отсутствующие значения в Pandas, существуют определённые ключевые слова, которые проверяют наличие нулевых значений в SQL. Следующий запрос подсчитывает количество строк с пропущенными значениями в BillingState:

Вы можете добавить ключевое слово NOT между IS и NULL, чтобы отбросить пропущенные значения определённого столбца:

В предложении WHERE мы отфильтровали столбцы на основе точных текстовых значений. Но часто нам может понадобиться отфильтровать текстовые столбцы на основе шаблона. В Pandas и чистом Python мы использовали бы регулярные выражения для сопоставления с образцом, которые очень эффективны, но для освоения регулярных выражений требуется время.

В качестве альтернативы SQL предлагает подстановочный знак '%' в качестве заполнителя для соответствия любому символу 0 или более раз. Например, строка «gr%» соответствует «greet», «groom», «greed» и «%ex%» соответствует любому тексту с «ex» в середине и т. д. Давайте посмотрим, как использовать её с SQL:

Приведённый выше запрос находит все песни, начинающиеся с «B». Строка, содержащая подстановочный знак, должна идти после ключевого слова LIKE.

Теперь давайте найдём все песни, в названии которых есть слово «beautiful»:

Вы также можете использовать другие логические операторы рядом с LIKE:

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

Также возможно выполнять основные арифметические операции над столбцами. Эти операции называются агрегатными функциями в SQL, и наиболее распространёнными из них являются AVG, SUM, MIN, MAX. Их функциональность должна быть ясна из их названий:

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

Вы можете так же легко комбинировать агрегатные функции с условными операторами, используя предложения WHERE:

Также можно использовать арифметические операторы, такие как +, -, *, / в столбцах и простые числа. При использовании со столбцами операция выполняется поэлементно:

Одно замечание об арифметических операциях: если вы выполняете операции только с целыми числами, SQL считает, что вы ожидаете целое число в качестве ответа:

Вместо того, чтобы возвращать 3,33…, результат равен 3. Чтобы получить результат с плавающей запятой, вы должны использовать в запросе хотя бы одно число с плавающей запятой или использовать все числа с плавающей запятой, чтобы быть в безопасности:

Используя эти знания, рассчитаем среднюю продолжительность песни в минутах:

Если вы обратите внимание на приведённый выше столбец, его имя написано как «запрос, используемый для создания этого столбца ». Из-за этого поведения использование длинных вычислений, таких как определение стандартного отклонения или дисперсии столбца, может быть проблемой, поскольку имя столбца будет таким же большим, как и сам запрос.

Чтобы избежать этого, SQL допускает использование псевдонимов, подобно тому, как операторы импорта имеют псевдонимы в Python. Например:

Использование ключевого слова as после одного элемента в операторе SELECT сообщает SQL, что мы используем псевдоним. Вот ещё примеры:

Вы можете так же легко использовать псевдонимы для столбцов с длинными именами.

Так же, как у Pandas есть метод sort_values, SQL поддерживает упорядочение столбцов через предложение ORDER BY. Передача имени столбца после предложения сортирует результаты в порядке возрастания:

Упорядочим таблицу треков по возрастанию имени композитора. Обратите внимание, что оператор ORDER BY всегда должен стоять после предложения WHERE. Также можно передать два или более столбца в ORDER BY:

Вы также можете изменить порядок, передав ключевое слово DESC после имени каждого столбца:

Приведённый выше запрос возвращает три столбца после упорядочения UnitPrice и Compose в порядке убывания и имени в порядке возрастания ( ключевое слово ASC по умолчанию).

Одной из самых мощных функций Pandas является расширение groupby. Вы можете использовать его, чтобы преобразовать таблицу практически в любую форму, которую вы хотите. Его очень близкий родственник в SQL - GROUP BY, который можно использовать для достижения той же функциональности. Например, следующий запрос подсчитывает количество песен в каждом жанре:

Разница между GROUP BY в SQL и groupby в Pandas заключается в том, что SQL не позволяет выбирать столбцы, которые не были указаны в предложении GROUP BY. Например, добавление дополнительного свободного столбца в приведённый выше запрос приводит к ошибке:

Однако вы можете выбрать столько столбцов в операторе SELECT, сколько хотите, если вы используете для них какой-либо тип агрегатной функции:

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

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

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

По умолчанию SQL не разрешает условную фильтрацию с использованием агрегатных функций в предложении WHERE. Например, мы хотим выбрать только те жанры, в которых количество песен превышает 100. Попробуем сделать это с предложением WHERE:

Правильный способ фильтрации строк на основе результатов агрегатных функций — использование предложения HAVING:

Предложение HAVING обычно используется с GROUP BY. Всякий раз, когда вы хотите отфильтровать строки с помощью агрегатных функций, используйте предложение HAVING!

К настоящему времени вы должны были понять, насколько мощным может быть SQL и насколько более читабельным он становится по сравнению с Pandas. Несмотря на то, что мы узнали тонну, мы едва лишь поцарапали поверхность.

Для практических задач я рекомендую Data Lemur или LeetCode, если вы любите приключения.