ИТ Блог. Администрирование серверов на основе Linux (Ubuntu, Debian, CentOS, openSUSE)
Аналитики и исследователи данных с глубоким пониманием нескольких языков аналитического программирования оказываются в преимуществе в современном ландшафте данных. Преобладающий диалог вокруг такого многоязычного подхода, особенно с SQL и Python, обычно изображает языки как взаимодополняющие, но функционально дискретные. Но на самом деле существует много перекрывающихся функций, которые могут быть выполнены как SQL, так и Python.
Изучение перекрывающихся функций SQL и Python может помочь тем из нас, кто знаком с одним языком, лучше освоить другой. И, обладая глубоким пониманием того и другого, мы все можем принимать более взвешенные решения о том, как комбинировать и использовать каждый из них, упрощая выбор правильного инструмента для каждой задачи.
Например; многие функции написаны более кратко на Python. Но если преимущество краткости перевешивается ценностью наличия всего аналитического кода на одном языке, вы все равно захотите использовать SQL. С другой стороны, Python включает много сложных функций, которые не осуществимы в SQL, и ценность подхода, основанного только на SQL, может быть перевешена более широким набором операций, которые становятся доступными при совместном использовании обоих языков. Только узнав о функциональном совпадении аналитических языков, мы сможем эффективно принимать эти решения.
Сегодня мы сосредоточимся на операциях GroupBy , которые являются еще одним отличным примером задачи, которая может быть выполнена как SQL, так и Python, и решение которой зависит от ваших целей.
Чтобы увидеть, все примеры, упомянутые в этом посте, реализованы на практике, посмотрите этот пример отчета. Этот пост структурирован так, что вы можете пропустить его на основе ваших текущих знаний.
Когда мы применяем операцию группировки к набору данных в 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 в 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 позволяет пользователям фильтровать значения, возвращаемые из сгруппированного запроса, на основе результатов функций агрегирования.
Как упоминалось ранее, мы хотим отфильтровать значения, возвращаемые в нашем запросе, для комбинаций начальной и конечной станций с более чем 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.
Как обычно, 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. Это включает в себя такие вещи, как преобразования набора данных , анализ квантилей и сегментов, линейную регрессию по группам и применение пользовательских функций, среди прочих. Доступ к этим типам операций значительно расширяет спектр вопросов, на которые мы можем ответить.
Глубокое понимание функций группировки в SQL и Python может помочь вам определить, какой язык следует использовать для какой функции и в какое время. Если у вас нет сложных требований к группировке, вы, вероятно, захотите работать в SQL, чтобы вы могли хранить всю свою работу на одном языке. Но если ваш анализ включает более тяжелые групповые операции, подобные упомянутым выше, перемещение всех групповых операций в записную книжку Python позволит вам воспользоваться преимуществами более сложной функциональности, доступной в pandas.
Это преимущество возможности использования нескольких языков анализа данных; Вы можете настроить гибридный подход в соответствии с вашими потребностями по мере их развития.
Тэги: , MySQL, pandas, Python, SQL, база данных Категории: MySQL
