SQL стал намного проще для людей, которые любят Pandas
Мотивация
В мире, где с 1974 года доминирует SQL, в 2008 году появился Pandas, предлагающий привлекательные функции, такие как встроенная визуализация и гибкая обработка данных. Он быстро стал популярным инструментом для исследования данных, затмив собой SQL.
Но не обманывайтесь, SQL по-прежнему держит свои позиции. Это второй по востребованности и третий по скорости роста язык для науки о данных (см. здесь). Таким образом, в то время как Pandas привлекает всеобщее внимание, SQL остается жизненно важным навыком для любого специалиста по данным.
Давайте узнаем, как легко выучить SQL, если вы уже знаете Pandas.
Подключение к базе данных
Настройка рабочей области SQL и подключение к образцу базы данных может быть настоящей головной болью. Во-первых, вам нужно установить предпочитаемый вариант SQL (PostgreSQL, MySQL и т. д.) и загрузить SQL IDE. Выполнение их здесь отвлекло бы нас от цели статьи, поэтому мы воспользуемся ярлыком.
В частности, мы будем напрямую запускать SQL-запросы в Jupyter Notebook без дополнительных шагов. Все, что нам нужно сделать, это установить пакет ipython-sql с помощью pip:
pip install ipython-sql
После установки запустите новый сеанс Jupyter и выполните эту команду в блокноте:
%load_ext sql
и все готово!
Чтобы проиллюстрировать, как работают основные операторы SQL, мы будем использовать базу данных Chinook SQLite, которая имеет 11 таблиц.
Чтобы загрузить набор данных и его 11 таблиц как отдельные переменные в нашу среду, мы можем запустить:
%sql sqlite:///data/chinook.db
Оператор начинается со встроенной магической команды %sql, которая сообщает интерпретатору ноутбука, что мы будем запускать команды SQL. За ним следует путь, по которому находится загруженная база данных Chinook.
Допустимые пути всегда должны начинаться с префикса sqlite:/// для баз данных SQLite. Выше мы подключаемся к базе данных, хранящейся в папке «data» текущего каталога. Если вы хотите передать абсолютный путь, префикс должен начинаться с четырех косых черт — sqlite:////.
Если вы хотите подключиться к другой разновидности базы данных, вы можете обратиться к этой отличной статье.
Первый взгляд на таблицы
Первое, что мы всегда делаем в Pandas, — это используем функцию .head() для первого просмотра данных. Давайте узнаем, как это сделать в SQL:
Первое ключевое слово в приведенном выше запросе — SELECT. Это эквивалентно оператору скобок в Pandas, где мы выбираем определенные столбцы. Но за ключевым словом SELECT следует * (звездочка). * — это оператор SQL, который выбирает все (все строки и столбцы) из таблицы, указанной после ключевого слова FROM. LIMIT используется для минимизации возвращаемого результата. Таким образом, приведенный выше запрос эквивалентен функции df.head().
Если вы не хотите выбирать все столбцы, вы можете указать одно или несколько имен столбцов после ключевого слова SELECT:
Эквивалентная операция Pandas:
tracks[['Name', 'Composer', 'UnitPrice']].head(10)
Другое полезное ключевое слово в SQL — DISTINCT. Добавление этого ключевого слова перед именем любого столбца возвращает его уникальные значения:
Комментарии в SQL пишутся с двойным дефисом.
Подсчет количества строк
Точно так же, как Pandas имеет атрибут .shape в своих DataFrames, SQL имеет функцию COUNT для отображения количества строк в таблице:
%%sql SELECT COUNT(*) FROM tracks
Более полезной информацией будет подсчет количества уникальных значений в определенном столбце. Мы можем сделать это, добавив ключевое слово DISTINCT в COUNT:
Фильтрация результатов с помощью предложений WHERE
Просто смотреть и считать строки довольно хромой. Давайте посмотрим, как мы можем фильтровать строки на основе условий.
Во-первых, давайте посмотрим на песни, которые стоят больше доллара:
Условные операторы записываются в предложении WHERE, которое всегда следует после FROM и перед ключевыми словами LIMIT. Использование условий очень похоже на то, как мы это делаем в Pandas, но я осмелюсь сказать, что версия SQL более читабельна.
Вы также можете использовать функцию COUNT при использовании условных выражений. Например, давайте посмотрим, сколько песен стоит от 1 до 10 долларов:
Выше мы связали два условия логическим оператором AND. Аналогично используются и другие логические операторы (ИЛИ, НЕ).
Теперь давайте посмотрим на все счета, в которых в качестве города выставления счетов указан Париж илиБерлин:
Для оператора равенства в SQL требуется только один знак "=" (равно). Оператор неравенства представлен операторами ‘!=’ или ‘‹›’:
Упрощенная фильтрация с помощью BETWEEN и IN
Подобные условные операторы используются очень часто, и их запись с помощью простых логических значений становится громоздкой. Например, в Pandas есть функция .isin(), которая проверяет, принадлежит ли значение списку значений.
Если бы мы хотели выбрать счета для пяти городов, нам пришлось бы написать пять связанных условий. К счастью, SQL поддерживает аналогичный оператор IN, такой как .isin(), поэтому нам не нужно:
Список значений после IN следует указывать в виде кортежа, а не списка. Вы также можете отменить условие с помощью ключевого слова NOT:
Другой распространенной операцией фильтрации числовых столбцов является выбор значений в пределах диапазона. Для этого можно использовать ключевое слово BETWEEN, эквивалентное pd.Series.between():
Проверка на нули
В каждом источнике данных есть пропущенные значения, и базы данных не являются исключением. Точно так же, как есть несколько способов исследовать отсутствующие значения в Pandas, существуют определенные ключевые слова, которые проверяют наличие нулевых значений в SQL. Следующий запрос подсчитывает количество строк с пропущенными значениями в BillingState:
Вы можете добавить ключевое слово NOT между IS и NULL, чтобы отбросить пропущенные значения определенного столбца:
Лучшее сопоставление строк с LIKE
В предложении WHERE мы отфильтровали столбцы на основе точных текстовых значений. Но часто нам может понадобиться отфильтровать текстовые столбцы на основе шаблона. В Pandas и чистом Python мы использовали бы регулярные выражения для сопоставления с образцом, которые очень эффективны, но для освоения регулярных выражений требуется время.
В качестве альтернативы SQL предлагает подстановочный знак «%» в качестве заполнителя для соответствия любому символу 0 или более раз. Например, строка «gr%» соответствует словам «greet», «groom», «greed» и «%ex%» соответствует любому тексту с «ex» в середине и т. д. Давайте посмотрим, как использовать ее с SQL:
Приведенный выше запрос находит все песни, начинающиеся с «B». Строка, содержащая подстановочный знак, должна идти после ключевого слова LIKE.
Теперь давайте найдем все песни, в названии которых есть слово «красивая»:
Вы также можете использовать другие логические операторы рядом с LIKE:
В SQL есть много других подстановочных знаков, которые имеют разные функции. Вы можете увидеть полный список и их использование здесь.
Агрегатные функции в SQL
Также возможно выполнять основные арифметические операции над столбцами. Эти операции называются агрегатными функциями в SQL, и наиболее распространенными из них являются AVG, SUM, MIN, MAX. Их функциональность должна быть ясна из их названий:
Агрегатные функции дают только один результат для столбца, в котором вы их использовали. Это означает, что вы не можете выполнять агрегирование по одному столбцу и выбирать другие неагрегированные столбцы:
Вы можете так же легко комбинировать агрегатные функции с условными операторами, используя предложения WHERE:
Также можно использовать арифметические операторы, такие как +, -, *, / в столбцах и простые числа. При использовании со столбцами операция выполняется поэлементно:
Одно замечание об арифметических операциях: если вы выполняете операции только с целыми числами, SQL считает, что вы ожидаете целое число в качестве ответа:
%%sql SELECT 10 / 3
Вместо того, чтобы возвращать 3,33…, результат равен 3. Чтобы получить результат с плавающей запятой, вы должны использовать в запросе хотя бы одно число с плавающей запятой или использовать все числа с плавающей запятой, чтобы быть в безопасности:
%%sql SELECT 10.0 / 3.0
Используя эти знания, рассчитаем среднюю продолжительность песни в минутах:
Если вы обратите внимание на приведенный выше столбец, его имя написано как «запрос, использованный для создания этого столбца». Из-за этого поведения использование длинных вычислений, таких как определение стандартного отклонения или дисперсии столбца, может быть проблемой, поскольку имя столбца будет таким же большим, как и сам запрос.
Чтобы избежать этого, SQL допускает использование псевдонимов, подобно тому, как операторы импорта имеют псевдонимы в Python. Например:
Использование ключевого слова as после одного элемента в операторе SELECT сообщает SQL, что мы используем псевдоним. Вот еще примеры:
Вы можете так же легко использовать псевдонимы для столбцов с длинными именами.
Упорядочивание результатов в SQL
Так же, как в Pandas есть метод sort_values, SQL поддерживает упорядочение столбцов с помощью предложения ORDER BY. Передача имени столбца после предложения сортирует результаты в порядке возрастания:
Упорядочим таблицу треков по возрастанию имени композитора. Обратите внимание, что оператор ORDER BY всегда должен стоять после предложения WHERE. Также можно передать два или более столбца в ORDER BY:
Вы также можете изменить порядок, передав ключевое слово DESC после каждого имени столбца:
Приведенный выше запрос возвращает три столбца после упорядочения UnitPrice и Compose в порядке убывания и имени в порядке возрастания (ASC — ключевое слово по умолчанию).
Группировка в SQL
Одной из самых мощных функций Pandas является функция groupby. Вы можете использовать его, чтобы преобразовать стол практически в любую форму, которую вы хотите. Его очень близкий родственник в SQL — предложение GROUP BY можно использовать для достижения той же функциональности. Например, следующий запрос подсчитывает количество песен в каждом жанре:
Разница между GROUP BY в SQL и groupby в Pandas заключается в том, что SQL не позволяет выбирать столбцы, которые не были указаны в предложении GROUP BY. Например, добавление дополнительного свободного столбца в приведенный выше запрос приводит к ошибке:
Однако вы можете выбрать столько столбцов в операторе SELECT, сколько хотите, если вы используете для них какой-либо тип агрегатной функции:
Приведенный выше запрос включает почти все темы, которые мы изучили до этого момента. Мы группируем как по идентификатору альбома, так и по идентификатору жанра, и для каждой группы мы рассчитываем среднюю продолжительность и цену песни. Мы также эффективно используем псевдонимы.
Мы можем сделать запрос еще более мощным, упорядочив его по средней продолжительности и количеству жанров:
Обратите внимание на то, как мы используем псевдонимы агрегатных функций в предложении ORDER BY. После того как вы назовете столбец или результат агрегатной функции псевдонимом, вы сможете ссылаться на них только по псевдониму для остальной части запроса.
Использование условных выражений с HAVING
По умолчанию SQL не разрешает условную фильтрацию с использованием агрегатных функций в предложении WHERE. Например, мы хотим выбрать только те жанры, в которых количество песен превышает 100. Попробуем сделать это с предложением WHERE:
Правильный способ фильтрации строк на основе результатов агрегатных функций — использование предложения HAVING:
Предложение HAVING обычно используется с GROUP BY. Всякий раз, когда вы хотите отфильтровать строки с помощью агрегатных функций, используйте предложение HAVING!
Все изображения созданы мной лично.
Краткое содержание
К настоящему времени вы должны были понять, насколько мощным может быть SQL и насколько более читабельным он становится по сравнению с Pandas. Несмотря на то, что мы узнали тонну, мы едва поцарапали поверхность.
Для практических задач я рекомендую Data Lemur или LeetCode, если вы любите приключения.
Понравилась эта статья и, скажем прямо, ее причудливый стиль написания? Представьте себе, что у вас есть доступ к десяткам таких же, написанных блестящим, обаятельным, остроумным автором (кстати, это я :).
Всего за 4,99 $ членства вы получите доступ не только к моим историям, но и к сокровищнице знаний от лучших и самых ярких умов на Medium. А если вы воспользуетесь моей реферальной ссылкой, то получите мою сверхновую благодарность и виртуальную пятерку за поддержку моей работы.
