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

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

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

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

https://t.me/sqlhub – изучение sql в одном канале

Подключение к базе данных

Настройка рабочей области 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:

%%sql

SELECT * FROM customers
LIMIT 5

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

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

%%sql

SELECT Name, Composer, UnitPrice
FROM tracks
LIMIT 10

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

tracks[['Name', 'Composer', 'UnitPrice']].head(10)

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

%%sql

SELECT DISTINCT FirstName
FROM employees

-- equivalent to `.unique()`

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

Подсчёт количества строк

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

%%sql

SELECT COUNT(*) FROM tracks

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

%%sql 

SELECT COUNT(DISTINCT FirstName) FROM employees

-- equivalent to `employees['FirstName'].nunique()`

Фильтрация результатов с помощью предложений WHERE

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

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

%%sql

SELECT * FROM tracks
WHERE UnitPrice > 1.0
LIMIT 10

--tracks[tracks['UnitPrice' > 1.0]]

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

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

%%sql

SELECT COUNT(*) FROM tracks
WHERE UnitPrice > 1.0 AND UnitPrice < 10

-- tracks[(tracks['UnitPrice'] > 1) & (tracks['UnitPrice'] < 10)]

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

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

%%sql

SELECT BillingAddress, BillingCity, Total
FROM invoices
WHERE BillingCity = 'Paris' OR BillingCity = 'Berlin'
LIMIT 5

--invoices[(invoices['BillingCity'] == 'Paris') |
--         (invoices['BillingCity'] == 'Berlin')]

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

%%sql

SELECT BillingAddress, BillingCity, Total
FROM invoices
WHERE BillingCity != 'Paris' AND BillingCity <> 'Berlin'
LIMIT 5

Упрощённая фильтрация с помощью BETWEEN и IN

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

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

%%sql

SELECT * 
  FROM invoices
 WHERE BillingCity IN ('Berlin', 'Paris', 'New York', 'Boston', 'London')
 LIMIT 5
    
--invoices[invoices['BillingCity'].isin(
--      ('Berlin', 'Paris', 'New York', 'Boston', 'London')
--)]

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

%%sql

SELECT * 
  FROM invoices
 WHERE BillingCity NOT IN ('Berlin', 'Paris', 'New York', 'Boston', 'London')
 LIMIT 5
    
--invoices[~invoices['BillingCity'].isin(
--    ('Berlin', 'Paris', 'New York', 'Boston', 'London')
--)]

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

%%sql

SELECT BillingCity, Total
FROM invoices
WHERE Total BETWEEN 5 AND 15
LIMIT 5

--invoices[invoices['Total'].between(5, 15)]

Проверка на нули

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

%%sql

SELECT COUNT(*) FROM
invoices
WHERE BillingState IS NULL

--invoices['BillingState'].isnull().sum()

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

%%sql

SELECT InvoiceDate, BillingCountry 
FROM invoices
WHERE Total IS NOT NULL
LIMIT 10

Лучшее сопоставление строк с LIKE

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

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

%%sql

SELECT Name, Composer, UnitPrice 
  FROM tracks
 WHERE Name LIKE 'B%'
 LIMIT 5

--tracks[tracks['Name'].str.startswith('B')]

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

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

%%sql

SELECT Name, Composer, UnitPrice
  FROM tracks
 WHERE Name LIKE '%beautiful%'

--tracks[tracks['Name'].str.contains('beautiful')]

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

%%sql

SELECT Name, Composer, UnitPrice
  FROM tracks
 WHERE (Name LIKE 'F%') AND (UnitPrice > 1.0)

--tracks[(tracks['Name'].str.startswith('F')) & (tracks['UnitPrice'] > 1.0)]

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

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

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

%%sql 

SELECT SUM(Total), MAX(Total), 
       MIN(Total), AVG(Total)
FROM invoices

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

%%sql

SELECT AVG(Total), BillingCity, BillingAddress
FROM invoices;

-- the result will be a single row because
-- of the presence of aggregate functions

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

%%sql

SELECT AVG(Total), BillingCity
FROM invoices
WHERE BillingCity = 'Paris'

-- invoices[invoices['BillingCity']]['Total'].mean()

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

%%sql

SELECT SUM(Total) / COUNT(Total)
FROM invoices

-- finding the mean manually

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

%%sql

SELECT 10 / 3

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

%%sql

SELECT 10.0 / 3.0

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

%%sql

SELECT Milliseconds / 1000.0 / 60.0
FROM tracks
LIMIT 10

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

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

%%sql

SELECT SUM(Total) as sum_total
FROM invoices

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

%%sql

SELECT SUM(Total) as sum_total,
       MAX(Total) as max_total,
       MIN(Total) as min_total,
       SUM(Total) / COUNT(Total) as mean_total
FROM invoices

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

Упорядочивание результатов в SQL

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

%%sql

SELECT Name, Composer, UnitPrice
FROM tracks
WHERE Composer <> 'None'
ORDER BY Composer
LIMIT 10

-- tracks.sort_values('Compose')[['Name', 'Compose', 'UnitPrice']]

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

%%sql

SELECT Name, Composer, UnitPrice
FROM tracks
WHERE Composer <> 'None'
ORDER BY UnitPrice, Composer, Name
LIMIT 10

-- tracks.sort_values(['UnitPrice', 'Composer', 'Name'])\
-- [['Name', 'Compose', 'UnitPrice']]

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

%%sql

SELECT Name, Composer, UnitPrice
FROM tracks
WHERE Composer != 'None'
ORDER BY UnitPrice DESC, Composer DESC, Name ASC
LIMIT 10

-- tracks.sort_values(['UnitPrice', 'Composer', 'Name'])\
-- [['Name', 'Compose', 'UnitPrice']]

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

Группировка в SQL

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

%%sql

SELECT GenreID, COUNT(*) as genre_count
FROM tracks
GROUP BY GenreId
LIMIT 10

-- tracks.groupby('GenreId')['GenreID'].count()

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

%%sql

SELECT Name, GenreID, COUNT(*) as genre_count
FROM tracks
GROUP BY GenreId

syntax error at or near "GROUP"
LINE 1: GROUP BY GenreId;

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

%%sql

SELECT GenreId, AlbumId, 
       COUNT(*) as genre_count,
       AVG(Milliseconds) / 1000.0 / 60.0 as avg_duration,
       AVG(UnitPrice) as avg_price
  FROM tracks
 GROUP BY GenreId, AlbumId
 LIMIT 10

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

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

%%sql

SELECT GenreId, AlbumId, 
       COUNT(*) as genre_count,
       AVG(Milliseconds) / 1000.0 / 60.0 as avg_duration,
       AVG(UnitPrice) as avg_price
  FROM tracks
 GROUP BY GenreId, AlbumId
 ORDER BY avg_duration DESC, genre_count DESC
 LIMIT 10

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

Использование условных выражений с HAVING

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

%%sql

SELECT GenreId
FROM tracks
GROUP BY GenreId
WHERE COUNT(GenreId) > 10

syntax error at or near "WHERE"
LINE 1: where COUNT(GenreId) > 10

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

%%sql

SELECT GenreId
FROM tracks
GROUP BY GenreId
HAVING COUNT(GenreId) > 100

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

Краткое содержание

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

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