Время — великолепный учитель, но, к сожалению, оно убивает своих учеников (Г. Берлиоз).

«Group By» в SQL и Python: сравнение

8 мин для чтения
FavoriteLoadingДобавить в избранное
1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (1 оценок, среднее: 5,00 из 5)
Загрузка...
28 апреля 2019
«Group By» в SQL и Python: сравнение
Аналитики и исследователи данных с глубоким пониманием нескольких языков аналитического программирования оказываются в преимуществе в современном ландшафте данных. Преобладающий диалог вокруг такого многоязычного подхода, особенно с SQL и Python, обычно изображает языки как взаимодополняющие, но функционально дискретные. Но на самом деле существует много перекрывающихся функций, которые могут быть выполнены как SQL, так и Python.

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

Например; многие функции написаны более кратко на Python. Но если преимущество краткости перевешивается ценностью наличия всего аналитического кода на одном языке, вы все равно захотите использовать SQL. С другой стороны, Python включает много сложных функций, которые не осуществимы в SQL, и ценность подхода, основанного только на SQL, может быть перевешена более широким набором операций, которые становятся доступными при совместном использовании обоих языков. Только узнав о функциональном совпадении аналитических языков, мы сможем эффективно принимать эти решения.

Сегодня мы сосредоточимся на операциях GroupBy , которые являются еще одним отличным примером задачи, которая может быть выполнена как SQL, так и Python, и решение которой зависит от ваших целей.

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

 

SQL

Функции агрегации

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

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

select count(*) as num_trips
from modeanalytics.sf_bike_share_trip

 

Интересно, что агрегатная функция count() имеет другую, часто упускаемую из вида функциональность. Если вы укажете столбец внутри функции count(), он вернет количество строк с ненулевыми экземплярами этого столбца вместо абсолютного общего количества строк. Например, если мы хотим рассчитать количество поездок, когда поле zip_code не равно нулю, мы могли бы использовать следующий запрос:

select count(zip_code) as num_trips_with_zip_code
from modeanalytics.sf_bike_share_trip

 

Применение функций агрегирования ко всем наборам данных является полезной функциональностью, но, как упоминалось ранее, мы часто хотим применять эту функцию отдельно для отдельных групп данных в наборе данных. Здесь начинается операция group by.

 

Group By

На высоком уровне предложение group by в SQL позволяет независимо применять функции агрегирования к отдельным группам данных в наборе данных.

Возвращаясь к нашему предыдущему запросу, давайте посчитаем количество поездок на велосипеде ( count(*)) по станции, с которой они начали ( start_station_name). Мы делаем это, добавляя start_station_name к списку выбранных столбцов и добавляя предложение group by, явно указывающее SQL для группировки count(*)агрегации по столбцу start_station_name:

select
  bst.start_station_name,
  count(*) as num_trips
from modeanalytics.sf_bike_share_trip bst
group by bst.start_station_name

 

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

select
  bst.start_station_name,
  count(*) as num_trips,
  avg(bst.duration) as avg_duration_seconds,
  min(bst.duration) as min_duration_seconds,
  max(bst.duration) as max_duration_seconds
from modeanalytics.sf_bike_share_trip bst
group by bst.start_station_name

 

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

select
  bst.start_station_name,
  bst.end_station_name,
  count(*) as num_trips,
  avg(bst.duration) as avg_duration_seconds,
  min(bst.duration) as min_duration_seconds,
  max(bst.duration) as max_duration_seconds
from modeanalytics.sf_bike_share_trip bst
group by bst.start_station_name,bst.end_station_name

 

Как видите, мы добавили столбец end_station_name как в наш список select, так и в наше предложение group by. Большинство платформ SQL предлагают некоторый синтаксический сахар для этой операции, что позволяет вам использовать порядковый номер столбца в selectвыражении вместо явной ссылки на имена столбцов. На практике это будет выглядеть так:

select
  bst.start_station_name,
  bst.end_station_name,
  count(*) as num_trips,
  avg(duration) as avg_duration_seconds,
  min(duration) as min_duration_seconds,
  max(duration) as max_duration_seconds
from modeanalytics.sf_bike_share_trip bst
group by 1,2

 

Примечание: не все платформы SQL позволяют ссылаться на столбцы по их порядковому положению в операторе select. Например, в базах данных Oracle и SQL Server вам нужно будет явно ссылаться на столбцы по имени в group byпредложении.

Что если мы хотим строго отфильтровать значения, возвращаемые этим запросом, чтобы начать комбинации станций и конечных станций с более чем 1000 поездок? Поскольку предложение where в SQL поддерживает только фильтрацию записей, а не результатов функций агрегирования, нам нужно найти другой способ. Вот где оператор having вступает в силу.

 

having

Предложение having позволяет пользователям фильтровать значения, возвращаемые из сгруппированного запроса, на основе результатов функций агрегирования.

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

select
  bst.start_station_name,
  bst.end_station_name,
  count(1) as num_trips,
  avg(duration) as avg_duration_seconds,
  min(duration) as min_duration_seconds,
  max(duration) as max_duration_seconds
from modeanalytics.sf_bike_share_trip bst
group by 1,2
having count(1) > 1000

 

На данный момент мы изучили многое из того, что SQL может делать с функциональностью group by. Давайте посмотрим, как эта же реализация будет выглядеть в Python, используя библиотеку pandas.

 

Python

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

В pandas, «groups» данные создаются с помощью python метода называется groupby(). Метод groupby() может быть вызван непосредственно на объект Dataframe в pandas.

В качестве примера мы собираемся использовать выходные данные SQL-запроса, названные Python в качестве входных данных для нашего Dataframe( df) в нашей записной книжке Python. Обратите внимание, что этот Dataframe не имеет функций агрегирования, вычисляемых с помощью SQL. Он просто использует SQL, чтобы выбрать необходимые поля для нашего анализа, а мы будем использовать панды, чтобы сделать все остальное. Дополнительным преимуществом выполнения этой операции в Python является то, что рабочая нагрузка перемещается из хранилища данных.

В нашей записной книжке Python мы собираемся использовать метод groupby(), чтобы сгруппировать наш Dataframe по столбцу start_station_name и присвоить этот результат новой переменной с именем grouped_single:

grouped_single = df.groupby('start_station_name')

 

Применение метода groupby() к нашему Dataframe объекта возвращает объект GroupBy, который затем присваивается переменной grouped_single. Важная вещь, которую следует отметить в отношении объекта GroupBy для Pandas, заключается в том, что в момент создания объекта не происходило разбиение Dataframe. Объект GroupBy просто содержит всю необходимую ему информацию о характере группировки. Агрегирование не будет происходить, пока мы явно не вызовем функцию агрегирования для объекта GroupBy.

Если мы хотим , чтобы наши группы Dataframe на как в колонках start_station_name и end_station_name, как мы это делали в нашем SQL запрос, мы можем просто добавить столбец end_station_name в наш список группирования ключей внутри метода groupby():

grouped_multiple = df.groupby(['start_station_name','end_station_name'])

 

В отличие от SQL, метод groupby() в pandas не имеет понятия порядковых ссылок на позиции. Таким образом, вам нужно будет явно ссылаться на ключи группировки по имени.

Теперь, когда у нас есть объект GroupBy, созданный с соответствующими группировками, мы можем применить к нему методы агрегирования. По умолчанию pandas будет применять любой метод агрегации, который вы вызываете для объекта GroupBy, ко всем столбцам, не требующим вмешательства. Поскольку нас интересует только применение методов агрегирования к одному столбцу ( trip_duration_seconds), мы выберем только этот столбец из нашего нового объекта GroupBy. То, как вы выбираете отдельные столбцы в объекте GroupBy, аналогично тому, как вы делаете это с объектом Dataframe:

grouped_multiple_column = df.groupby(['start_station_name','end_station_name'])['trip_duration_seconds']

 

Pandas позволяет выбрать любое количество столбцов, используя эту операцию. Pandas будет возвращать сгруппированные серии, когда вы выбираете один столбец, и сгруппированные кадры данных, когда вы выбираете несколько столбцов. Теперь, когда у нас есть один столбец, выбранный из нашего объекта GroupBy, мы можем применить к нему соответствующие методы агрегирования. Есть несколько способов сделать это. Мы можем присвоить переменную объекту GroupBy и затем вызвать метод агрегирования для этой переменной:

grouped_multiple = df.groupby(['start_station_name','end_station_name'])
avg_trip_duration = grouped_multiple['trip_duration_seconds'].mean()

 

Или мы можем использовать метод цепочки:

avg_duration_seconds = df.groupby(['start_station_name','end_station_name'])['trip_duration_seconds'].mean()

 

Мы можем повторить этот процесс для каждого из агрегатов, которые мы рассчитываем:

# Вычисления агрегатов по отдельности
num_trips = df.groupby(['start_station_name','end_station_name']).size()
avg_duration_seconds = df.groupby(['start_station_name','end_station_name'])['trip_duration_seconds'].mean()
min_duration_seconds = df.groupby(['start_station_name','end_station_name'])['trip_duration_seconds'].min()
max_duration_seconds = df.groupby(['start_station_name','end_station_name'])['trip_duration_seconds'].max(

 

Вы можете увидеть, как это может запутаться, если вы вычисляете большое количество агрегатов. К счастью, pandas предлагает способ вычисления нескольких агрегатов для одного объекта GroupBy. Метод agg() может принимать принимать список методов агрегирования для отдельных столбцов:

# Calculate aggregations at once
all_together = (df.groupby(['start_station_name','end_station_name']
                  .agg({'trip_duration_seconds': [np.size, np.mean, np.min, np.max]}))

 

Вы также можете связать метод rename() в pandas, чтобы переименовать новые столбцы агрегации в нужные вам имена:

# Calculate and rename aggregations
all_together = (df.groupby(['start_station_name','end_station_name'])
                  .agg({'trip_duration_seconds': [np.size, np.mean, np.min, np.max]})
                  .rename(columns={'size': 'num_trips', 'mean': 'avg_duration_seconds', 'amin': 'min_duration_seconds', 'amax': 'max_duration_seconds'}))

 

Теперь мы реплицировали наш SQL-запрос до момента фильтрации наших групп на основе результата функции агрегирования. В pandas вы можете использовать стандартное индексирование для возврата подмножества исходного объекта на основе результата функции агрегирования. Например, если мы хотим отфильтровать наш результирующий набор только для комбинаций начальной станции и конечной станции с более чем 1000 поездок, мы могли бы использовать следующий оператор:

all_together[all_together['trip_duration_seconds']['num_trips'] > 1000]

 

К этому моменту мы полностью реплицировали вывод нашего исходного запроса SQL, перенося работу по группированию и агрегированию на панд. Опять же, этот пример только показывает, что возможно, используя функциональность группировки панд. Многие групповые операции, которые являются сложными (или даже невозможными) с использованием SQL, оптимизированы в среде pandas. Это включает в себя такие вещи, как преобразования набора данных , анализ квантилей и сегментов, линейную регрессию по группам и применение пользовательских функций, среди прочих. Доступ к этим типам операций значительно расширяет спектр вопросов, на которые мы можем ответить.

 

Blur The Line

Глубокое понимание функций группировки в SQL и Python может помочь вам определить, какой язык следует использовать для какой функции и в какое время. Если у вас нет сложных требований к группировке, вы, вероятно, захотите работать в SQL, чтобы вы могли хранить всю свою работу на одном языке. Но если ваш анализ включает более тяжелые групповые операции, подобные упомянутым выше, перемещение всех групповых операций в записную книжку Python позволит вам воспользоваться преимуществами более сложной функциональности, доступной в pandas.

Это преимущество возможности использования нескольких языков анализа данных; Вы можете настроить гибридный подход в соответствии с вашими потребностями по мере их развития.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Просмотров: 187

Если статья понравилась, то поделитесь ей в социальных сетях:

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

Войти с помощью: 

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

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам:

Заполните форму и наш менеджер перезвонит Вам в самое ближайшее время!

badge
Обратный звонок 1
Отправить
galka

Спасибо! Ваша заявка принята

close
galka

Спасибо! Ваша заявка принята

close