Пять лучших оконных функций SQL, которые вы должны знать для интервью по науке о данных

SQL — это универсальный язык в мире данных и самый важный навык, которым должен овладеть специалист по работе с данными.

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

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

С учетом сказанного, давайте погрузимся в это!

1. Дельты с LEAD() и LAG()

 
LEAD() и LAG() в основном используются при сравнении одного периода времени с предыдущим периодом времени для данной метрики. Чтобы привести несколько примеров…

• Вы можете получить дельту между продажами каждого года и продажами предыдущего года.
• Вы можете получать дельту в количестве регистраций/конверсий/посещений сайта от месяца к месяцу.
• Вы можете сравнивать отток пользователей ежемесячно.

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

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

with monthly_costs as ( SELECT date , monthlycosts , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts FROM costs )SELECT date , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange FROM monthly_costs

2. Суммарные суммы с SUM() или COUNT()

 
Вычисление промежуточных сумм можно просто выполнить с помощью функции Windows, которая начинается с SUM() или COUNT(). Это мощный инструмент, когда вы хотите показать рост определенной метрики с течением времени. В частности, это полезно в следующих случаях:

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

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

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

SELECT date , monthlycosts , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts FROM cost_table

3. Скользящие средние с AVG()

 
AVG() действительно эффективен в функциях Windows, поскольку позволяет вычислять Скользящие средние со временем.

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

Более конкретно…

• Их можно использовать для получения общей тенденции еженедельных продаж (увеличивается ли среднее значение с течением времени?). Это будет свидетельствовать о росте компании.
• Их также можно использовать для получения общей тенденции еженедельных конверсий или посещений веб-сайтов.

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

Следующий запрос является примером получения 10-дневной скользящей средней для конверсий.

SELECT Date , dailyConversions , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage FROM conversions

4. НОМЕР_СТРОКИ()

 
ROW_NUMBER() особенно полезен, когда вы хотите получить первую или последнюю запись. Например, если у вас есть таблица, когда члены тренажерного зала посещали тренажерный зал, и вы хотите получить дату первого дня, когда они пришли в тренажерный зал, вы можете РАЗДЕЛИТЬ ПО клиенту (имя/идентификатор) и ЗАКАЗАТЬ ПО дате покупки. Затем, чтобы получить первую строку, вы можете просто отфильтровать строки с номером строки, равным единице.

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

В этом примере показано, как вы можете использовать ROW_NUMBER(), чтобы получить первую дату, когда каждый участник (пользователь) посетил.

with numbered_visits as ( SELECT memberId , visitDate , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber FROM gym_visits )SELECT * FROM numbered_visits WHERE rowNumber = 1

Напомним, что если вам когда-либо понадобится получить первую или последнюю запись, ROW_NUMBER() — отличный способ добиться этого.

5. Запись рейтинга с помощью DENSE_RANK()

 
DENSE_RANK() похож на ROW_NUMBER(), за исключением того, что он возвращает тот же ранг для одинаковых значений. Плотное ранжирование весьма полезно, когда речь идет о поиске лучших записей, например:

• Если вы хотите выбрать 10 самых популярных шоу Netflix на этой неделе
• Если вы хотите получить 100 лучших пользователей по потраченным долларам
• Если вы хотите увидеть поведение 1000 наименее активных пользователей

Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.

Если вы хотите ранжировать своих лучших клиентов по общему объему продаж, DENSE_RANK() будет подходящей функцией.

SELECT customerId , totalSales , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank FROM customers

Спасибо за прочтение!

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

Как всегда, желаю успехов в обучении!

 
 
Теренс Шин является энтузиастом данных с более чем 3-летним опытом работы с SQL и более чем 2-летним опытом работы с Python, а также ведет блог о науке о данных и KDnuggets.

Оригинал, Перемещено с разрешения.

Источник: https://www.kdnuggets.com/2022/01/top-five-sql-window-functions-know-data-science-interviews.html.