4 вещи, которые вы должны прекратить делать в SQL и начать делать в Python
Как аналитики, вы должны знать, что на каждый вопрос наших данных мы можем ответить с помощью нескольких потенциальных языков и наборов инструментов. У каждого языка есть свои сильные стороны, и мы часто размышляли о различиях.
Хотя позиция обычно не зависит от языка, в SQL есть некоторые действия, которые в Python просто более эффективны. В этой статье мы расскажем о 4 примерах, позволяющих сэкономить время, когда Python намного лучше, чем SQL, для изучения и анализа вашего набора данных.
Описательная статистика
Допустим, вы изучаете новый набор данных. Есть много разных способов статистически описать данные и почувствовать их. Например:
- Подсчитывать
- Имею в виду
- Стандартное отклонение
- Минимальное значение
- Распределение 25-го квартиля
- Распределение 50-го квартиля (Медиана)
- Распределение 75-го квартиля
- Максимальное значение
Чтобы собрать эту информацию в SQL, вы должны написать что-то вроде этого:
SELECT ROUND(MIN(first_class_rev)::numeric, 2) AS min,
ROUND(PERCENTILE_CONT(0.25) WITHIN
GROUP (ORDER BY first_class_rev)::numeric, 2) AS pc25,
ROUND(PERCENTILE_CONT(0.50) WITHIN
GROUP (ORDER BY first_class_rev)::numeric, 2) AS median,
ROUND(AVG(first_class_rev)::numeric, 2) AS mean,
ROUND(PERCENTILE_CONT(0.75) WITHIN
GROUP (ORDER BY first_class_rev)::numeric, 2) AS pc75,
ROUND(MAX(first_class_rev)::numeric, 2) AS max
FROM andreyex.flight_revenue
Но, возможно, вы не хотите печатать весь этот код. Вы можете использовать функцию pandas DataFrame.describe(), чтобы получить базовую описательную статистику для базового набора данных.
datasets['Python Input'].describe().round(1)
Скользящие средние
Теперь предположим, что вы хотите вычислить скользящие средние, чтобы получить четкое среднее для постоянно меняющихся входных данных. Скользящие средние служат для сглаживания резких спадов и скачков в ваших данных, так что долгосрочные тренды могут стать более заметными.
В SQL вы можете написать запрос, подобный этому:
WITH input AS (
SELECT
COUNT(1) AS trips,
DATE_TRUNC('day',start_date) AS date
FROM andreyex.sf_bike_share_trip
GROUP BY 2
)
SELECT
trips,
AVG(trips) over (order BY date rows between 12 preceding AND current row) AS mvg_avg, -- Функция окна для расчета 13-дневной скользящей средней поездок rideshare
SUM(trips) over (order BY date rows unbounded preceding) AS running_total_trips, -- Функция окна для вычисления общего количества поездок rideshare
lag(trips,7) over (order BY date) AS num_trips_previous_day, -- Функция окна, чтобы захватить количество поездок в предыдущий день
trips - lag(trips,7) over (order BY date) AS wow_difference,
(trips - lag(trips,7) over (order BY date))/lag(trips,7) over (order BY date)::DECIMAL(18,2) AS wow_percent_change, -- Оконная функция для вычисления неделя-через-неделю-процентное увеличение в поездках
date
FROM input
ORDER BY date;
В Python вы можете быстро получить ту же двухнедельную скользящую среднюю с помощью следующего кода:
import numpy as np import pandas as pd import matplotlib.pyplot as plt df = datasets["Trips - Python Window"] df["mvg_avg"] = df.trips.rolling(14).mean()
Кроме того, Python позволяет продвигать визуализацию еще дальше.
Pivot
Чтобы переупорядочить данные и сводную диаграмму или формат, готовый для презентации, вам нужно будет сделать несколько шагов в SQL. В этом примере мы собираемся повернуть строки в столбцы из набора данных игроков хоккея в Public Mode Warehouse.
Сначала мы соберем игроков каждого года и конференции, чтобы подготовить данные.
SELECT teams.conference AS conference,
players.year,
COUNT(1) AS players
FROM andreyex.institute_hockey_players players
JOIN andreyex.institute_hockey_teams teams
ON teams.institute_name = players.institute_name
GROUP BY 1,2
ORDER BY 1,2
Далее, чтобы преобразовать данные, мы вложим запрос в подзапрос.
SELECT *
FROM (
SELECT teams.conference AS conference,
players.year,
COUNT(1) AS players
FROM andreyex.institute_hockey_players players
JOIN andreyex.institute_hockey_teams teams
ON teams.institute_name = players.institute_name
GROUP BY 1,2
) sub
Затем вы хотите разбить результаты на столбцы для каждого года. Вы можете создать отдельные столбцы с каждым элементом в операторе SELECT.
SELECT conference,
SUM(CASE WHEN year = 'RU' THEN players ELSE NULL END) AS ru,
SUM(CASE WHEN year = 'SO' THEN players ELSE NULL END) AS so,
SUM(CASE WHEN year = 'JR' THEN players ELSE NULL END) AS jr,
SUM(CASE WHEN year = 'SR' THEN players ELSE NULL END) AS sr
FROM (
SELECT teams.conference AS conference,
players.year,
COUNT(1) AS players
FROM andreyex.institute_hockey_players players
JOIN andreyex.institute_hockey_teams teams
ON teams.institute_name = players.institute_name
GROUP BY 1,2
) sub
GROUP BY 1
ORDER BY 1
Хотя это дает вам большой контроль над запросами и процессами, в Python есть более простой способ добиться этого. После того, как вы ВЫБИРАЕТЕ из хоккейных игроков института, из года и конференции, вы можете перейти в Блокнот и запустить DataFrame.pivot. Это позволяет изменить форму данных на основе значений столбцов, чтобы можно было переупорядочить наборы результатов.
DataFrame.pivot
Self Join
Есть много случаев, когда вы можете захотеть присоединить таблицу к себе. Чтобы создать объединение, сначала нужно написать этот запрос, который дает разные имена ссылок на одну и ту же таблицу.
SELECT DISTINCT belarus_investments.company_name,
belarus_investments.company_permalink
FROM andreyex.crunchbase_investments_part1 belarus_investments
JOIN andreyex.crunchbase_investments_part1 ru_investments
ON belarus_investments.company_name = ru_investments.company_name
AND ru_investments.investor_country_code = 'RUS'
AND ru_investments.funded_at > belarus_investments.funded_at
WHERE belarus_investments.investor_country_code = 'BEL'
ORDER BY 1
В pandas мы можем достичь этого через этот запрос:
d = pd.DataFrame(['A','B','C'], columns = ['Column_1'])
d['Column_2'] = [1,2,1]
d.join(d.drop('Column_2', 1), on='Column_2', rsuffix='.1')
Есть всегда много способов достичь того же результата, но эти советы помогут вам работать умнее, а не усерднее.
Редактор: AndreyEx
