Сосредоточение внимания на важных концепциях для специалистов по данным.

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 () особенно полезен, когда вы хотите получить первую или последнюю запись. Например, если у вас есть таблица, когда участники тренажерного зала пришли в тренажерный зал, и вы хотите узнать дату первого дня, когда они пришли в тренажерный зал, вы можете РАЗБИРАТЬ ПО клиенту (имя / идентификатор) и ЗАКАЗАТЬ ПО дате покупки. Затем, чтобы получить первую строку, вы можете просто отфильтровать строки с rowNumber, равным единице.

Пример:
В этом примере показано, как с помощью 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.

Как всегда, я желаю вам удачи в учебе!

Не знаете, что читать дальше? Я подобрала для вас другую статью:



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



и еще один!



Завершите 52-недельную учебную программу, чтобы стать специалистом по анализу данных в 2021 году
Учите что-нибудь каждую неделю в течение 52 недель! todatascience.com



Теренс Шин