Ознакомьтесь с этим эмпирическим анализом того, как структура набора данных влияет на оконные функции, CTE, JOIN и шаблоны слияния pandas.
Изображение предоставлено автором.
# Введение
Решая достаточное количество задач на основе данных, типичных для собеседований, начинаешь замечать забавный эффект: «форма» набора данных незаметно диктует стиль программирования. Таблица временных рядов подталкивает к использованию оконных функций. Звездная схема заставляет использовать цепочки JOIN и GROUP BY. Задача pandas с двумя DataFrame почти напрашивается на использование .merge() и isin().
В этой статье эта интуиция становится измеримой. Используя набор типичных задач SQL и pandas, мы определим основные характеристики структуры кода (использование общих табличных выражений (CTE), частота использования оконных функций, распространенные методы pandas) и проиллюстрируем, какие элементы преобладают и почему.
# Почему структуры данных меняют ваш стиль программирования
Задачи, связанные с данными, — это не просто логические задачи, а скорее ограничения, оформленные в виде таблиц:
// Строки, зависящие от других строк (Время, Ранг, «Предыдущее значение»)
Если ответ для каждой строки зависит от соседних строк (например, температура за вчерашний день, предыдущая транзакция, накопительная сумма), то решения, естественно, опираются на оконные функции, такие как LAG(), LEAD(), ROW_NUMBER() и DENSE_RANK().
Рассмотрим, например, таблицы из этого вопроса на собеседовании:
Результат каждого клиента за конкретный день нельзя определить изолированно. После суммирования стоимости заказов на уровне клиента и дня, каждую строку необходимо оценить относительно других клиентов за тот же день, чтобы определить, у кого общая сумма наибольшая.
Поскольку ответ для каждой строки зависит от ее положения относительно других строк в пределах временного интервала, такая форма набора данных естественным образом приводит к использованию оконных функций, таких как RANK() или DENSE_RANK(), а не к простому агрегированию.
// Несколько таблиц с ролями (измерения и факты)
Когда одна таблица описывает сущности, а другая — события, решения, как правило, используют шаблоны JOIN + GROUP BY (SQL) или .merge() + .groupby() (pandas).
Например, в этом вопросе на собеседовании используются следующие таблицы данных:
В этом примере, поскольку атрибуты сущностей (пользователи и статус учетной записи) и данные событий (загрузки) разделены, логика должна сначала объединить их с помощью операторов JOIN, прежде чем можно будет выполнить осмысленную агрегацию (точно по измерению). Именно эта схема фактов создает решения JOIN + GROUP BY.
// Небольшие выходные данные с логикой исключения (шаблоны против объединения)
Проблемы, возникающие при запросе «кто никогда не делал X», часто сводятся к LEFT JOIN … IS NULL / NOT EXISTS (SQL) или ~df['col'].isin(…) (pandas).
# Что мы измеряем: Характеристики структуры кода
Для сравнения «стиля кодирования» в разных решениях полезно определить ограниченный набор наблюдаемых характеристик, которые можно извлечь из текста SQL-запросов и кода Python.
Хотя эти показатели могут быть не безупречными индикаторами качества решения (например, корректности или эффективности), они могут служить надежными сигналами о том, как аналитики взаимодействуют с набором данных.
// Функции SQL, которые мы измеряем
// Функции Pandas, которые мы измеряем
# Какие конструкции встречаются чаще всего
Чтобы выйти за рамки отдельных наблюдений и количественно оценить эти закономерности, необходим более простой и последовательный метод для получения структурных сигналов непосредственно из кода решения.
В качестве конкретной основы для этого рабочего процесса мы использовали все образовательные вопросы на платформе StrataScratch .
В приведенном ниже результате «общее количество вхождений» — это общее число раз, когда шаблон встречается во всем коде. Решение одного вопроса может использовать оператор JOIN 3 раза, поэтому все эти 3 в сумме дают определенное количество вхождений. «Вопросы, использующие» показывают, сколько различных вопросов имеют хотя бы одно вхождение данной функции (т.е. бинарное «использовано / не использовано» для каждого вопроса).
Этот метод сводит каждое решение к ограниченному набору наблюдаемых характеристик, что позволяет нам последовательно и воспроизводимо сравнивать стили кодирования в разных задачах и напрямую связывать структуру набора данных с доминирующими конструктами.
// Возможности SQL
// Возможности Pandas (решения на Python)
// Код извлечения признаков
Ниже представлены фрагменты кода, которые вы можете использовать в своих собственных решениях (или перефразировать ответы своими словами) и извлечь функции из текста кода.
// Извлечение признаков SQL (пример)
import re from collections import Counter sql = # вставьте код здесь SQL_FEATURES = { «cte»: r»bWITHb», «join»: r»bJOINb», «group_by»: r»bGROUPs+BYb», «window_over»: r»bOVERs*(«, «dense_rank»: r»bDENSE_RANKb», «row_number»: r»bROW_NUMBERb», «lag»: r»bLAGb», «lead»: r»bLEADb», «not_exists»: r»bNOTs+EXISTSb», } def extract_sql_features(sql: str) -> Counter: sql_u = sql.upper() return Counter({k: len(re.findall(p, sql_u)) for k, p in SQL_FEATURES.items()})
// Извлечение признаков с помощью Pandas (пример)
import re from collections import Counter pandas = # вставьте код сюда PD_FEATURES = { «merge»: r».merges*(«, «groupby»: r».groupbys*(«, «rank»: r».ranks*(«, «isin»: r».isins*(«, «sort_values»: r».sort_valuess*(«, «drop_duplicates»: r».drop_duplicatess*(«, «transform»: r».transforms*(«, } def extract_pd_features(code: str) -> Counter: return Counter({k: len(re.findall(p, code)) for k, p in PD_FEATURES.items()})
Теперь давайте подробнее поговорим о закономерностях, которые мы заметили.
# Основные показатели частоты выполнения SQL-запросов
// Количество задач ранжирования, наиболее часто выполняемых оконными функциями в день и благоприятных для совпадения показателей, резко возросло.
Например, в этом вопросе на собеседовании нас просят вычислить ежедневную сумму по каждому клиенту, а затем выбрать наивысший результат для каждой даты, включая совпадения. Это требование естественным образом приводит к использованию оконных функций, таких как RANK() или DENSE_RANK(), сегментированных по дням.
Решение следующее:
WITH customer_daily_totals AS ( SELECT o.cust_id, o.order_date, SUM(o.total_order_cost) AS total_daily_cost FROM orders o WHERE o.order_date BETWEEN '2019-02-01' AND '2019-05-01' GROUP BY o.cust_id, o.order_date ), ranked_daily_totals AS ( SELECT cust_id, order_date, total_daily_cost, RANK() OVER ( PARTITION BY order_date ORDER BY total_daily_cost DESC ) AS rnk FROM customer_daily_totals ) SELECT c.first_name, rdt.order_date, rdt.total_daily_cost AS max_cost FROM ranked_daily_totals rdt JOIN customers c ON rdt.cust_id = c.id WHERE rdt.rnk = 1 ORDER BY rdt.order_date;
Этот двухэтапный подход — сначала агрегирование, а затем ранжирование в рамках каждой даты — показывает, почему оконные функции идеально подходят для сценариев с «наивысшим значением в группе», где необходимо поддерживать совпадения, и почему базовая логика GROUP BY недостаточна.
// Использование CTE увеличивается, когда в вопросе предусмотрены поэтапные вычисления
Использование общего табличного выражения (CTE) (или нескольких CTE) обеспечивает читаемость каждого шага и упрощает проверку промежуточных результатов.
Такая структура также отражает образ мышления аналитиков: разделение подготовки данных и бизнес-логики позволяет упростить понимание запроса, устранение неполадок и его адаптацию по мере изменения потребностей.
// Объединение с агрегацией становится методом по умолчанию в бизнес-метриках для нескольких таблиц
Когда меры находятся в одной таблице, а измерения — в другой, часто избежать использования операторов JOIN не удается. После объединения операторы GROUP BY и условные итоги (SUM(CASE WHEN … THEN … END)) обычно являются кратчайшим путем.
# Основные моменты метода Pandas
// Функция .merge() появляется всякий раз, когда ответ зависит от более чем одной таблицы
Этот вопрос на собеседовании — хороший пример паттерна pandas. Когда данные о поездках, оплате или скидках занимают несколько столбцов и таблиц, обычно сначала объединяют данные, а затем подсчитывают или сравнивают их.
import pandas as pd orders_payments = lyft_orders.merge(lyft_payments, on='order_id') orders_payments = orders_payments[(orders_payments['order_date'].dt.to_period('M') == '2021-08') & (orders_payments['promo_code'] == False)] grouped_df = orders_payments.groupby('city').size().rename('n_orders').reset_index() result = grouped_df[grouped_df['n_orders'] == grouped_df['n_orders'].max()]['city']
После объединения таблиц оставшаяся часть решения сводится к знакомому шагу группировки с помощью метода `.groupby()` и сравнения, что подчеркивает, как первоначальное объединение таблиц может упростить последующую логику в pandas.
# Почему эти закономерности продолжают появляться
// Для таблиц, основанных на времени, часто требуется оконная логика
Когда задача связана с итоговыми значениями «за день», сравнением между днями или выбором наибольшего значения для каждой даты, обычно требуется упорядоченная логика. По этой причине функции ранжирования с оператором OVER широко используются, особенно когда необходимо сохранять значения, совпадающие по нескольким параметрам.
// Многошаговые бизнес-правила выигрывают от использования промежуточного этапа
В некоторых задачах смешиваются правила фильтрации, объединения таблиц и вычисляемые метрики. Можно написать всё в одном запросе, но это усложняет чтение и отладку. CTE помогают в этом, разделяя обогащение и агрегацию таким образом, чтобы упростить проверку, что соответствует модели «Premium против Freemium».
// Вопросы, заданные с использованием нескольких таблиц, естественным образом увеличивают плотность объединений.
Если метрика зависит от атрибутов, хранящихся в другой таблице, требуется объединение таблиц. После объединения таблиц естественным следующим шагом являются сгруппированные сводки. Такая общая структура неоднократно встречается в задачах StrataScratch, в которых данные о событиях сочетаются с профилями сущностей.
# Практические рекомендации для более быстрых и экологичных решений
- Если выходные данные зависят от упорядоченных строк, ожидайте использования оконных функций, таких как ROW_NUMBER() или DENSE_RANK().
- Если вопрос сформулирован как «вычислить A, затем вычислить B из A», то блок WITH обычно повышает ясность.
- Если набор данных разбит на несколько сущностей, спланируйте операцию JOIN заранее и определите ключи группировки до написания окончательного запроса SELECT.
- В pandas метод `.merge()` следует использовать по умолчанию, если логика охватывает несколько DataFrame, а затем формировать метрику с помощью `.groupby()` и фильтрации с помощью `clean`.
# Заключение
Стиль кодирования следует структуре: вопросы, основанные на времени и на принципе «наивысший результат в группе», как правило, приводят к использованию оконных функций. Многошаговые бизнес-правила, как правило, приводят к использованию CTE (Continuous Statement Execution).
Использование метрик для работы с несколькими таблицами увеличивает плотность операций JOIN, и pandas отражает эти же изменения с помощью методов .merge() и .groupby().
Что еще более важно, распознавание этих структурных закономерностей на раннем этапе может существенно изменить ваш подход к решению новой проблемы. Вместо того чтобы начинать с синтаксиса или заученных приемов, вы можете рассуждать, исходя из самого набора данных: это максимум для каждой группы? Поэтапное бизнес-правило? Многотабличная метрика?
Такой подход позволяет предвидеть основную структуру системы еще до написания кода. В конечном итоге это приводит к более быстрой разработке решений, упрощению проверки и большей согласованности между SQL и pandas, поскольку вы реагируете на структуру данных, а не только на текст вопроса.
Как только вы научитесь распознавать структуру набора данных, вы сможете предсказывать доминирующую структуру на ранних этапах. Это ускорит написание решений, упростит отладку и обеспечит большую согласованность при решении новых задач.
Нейт Розиди — специалист по анализу данных и продуктовой стратегии. Он также является адъюнкт-профессором, преподающим аналитику, и основателем StrataScratch, платформы, помогающей специалистам по анализу данных готовиться к собеседованиям с помощью реальных вопросов от ведущих компаний. Нейт пишет о последних тенденциях на рынке труда, дает советы по прохождению собеседований, делится проектами по анализу данных и освещает все аспекты SQL.
Источник: www.kdnuggets.com
