Аналитические функции SQL, чтобы вывести ваши аналитические навыки на новый уровень

Аналитические функции - один из самых популярных инструментов среди аналитиков бизнес-аналитики / данных для выполнения комплексного анализа данных. Эти функции выполняют вычисления над несколькими строками и также возвращают несколько строк. Сегодня мы рассмотрим 5 функций, которые я считаю наиболее полезными, с множеством практических примеров.

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

analytic_function_name([argument_list])
OVER (
[PARTITION BY partition_expression,…]
[ORDER BY sort_expression, … [ASC|DESC]])

Этот синтаксис состоит из трех частей: функция, разбиение по и порядок. Давайте кратко рассмотрим, чем занимается каждый из них:

  • analytic_function_name: имя функции - например, RANK(), SUM(), FIRST() и т. Д.
  • partition_expression: столбец / выражение, на основе которого должна быть создана перегородка или оконные рамы
  • sort_expression: столбец / выражение, на основе которого будут отсортированы строки в разделе

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

А теперь давайте начнем с практической части?

СРЕДНЕЕ () и СУММ ()

Все мы использовали агрегатные функции, такие как SUM, AVG, MIN, MAX и COUNT в наших GROUP BY предложениях. Но когда эти функции используются в предложении ORDER BY, они могут дать нам текущую сумму, среднее, общее и т. Д.

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

SELECT ord_date, agent_code, AVG(ord_amount) OVER (
    PARTITION BY agent_code
    ORDER BY ord_date
) running_agent_avg_revenue, 
    SUM (ord_amount) OVER (
        PARTITION BY agent_code
        ORDER BY ord_date
    ) running_agent_total_revenue
FROM orders
WHERE ord_date BETWEEN ‘2008–07–01’ AND ‘2008–09–30’;

И вот результаты:

Потрясающие! Эти функции просты и не требуют дополнительных пояснений. Приступим.

FIRST_VALUE (), LAST_VALUE () и NTH_VALUE ()

FIRST_VALUE() - аналитическая функция, которая возвращает значение указанного столбца из первой строки рамки окна. Если вы поняли предыдущее предложение, LAST_VALUE() говорит само за себя. Он извлекает значение из последней строки.

PostgreSQL предоставляет нам еще одну дополнительную функцию с именем NTH_VALUE(column_name, n), которая извлекает значение из n-й строки. Разве это не здорово? Больше никаких сложных соединений.

Давайте ответим на следующий вопрос: Через сколько дней после первой покупки покупателя была сделана следующая покупка?

SELECT cust_code, ord_date, ord_date — FIRST_VALUE(ord_date) OVER (
    PARTITION BY cust_code 
    ORDER BY ord_date) next_order_gap
FROM orders
ORDER BY cust_code, next_order_gap;

И вот результаты:

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

Перейдем к следующему.

LEAD () и LAG ()

LEAD() функция, как следует из названия, выбирает значение определенного столбца из следующей строки и возвращает полученное значение в текущей строке. В PostgreSQL LEAD() принимает два аргумента:

  • column_name, из которого нужно выбрать следующее значение
  • index следующей строки относительно текущей строки.

LAG() - полная противоположность. Он извлекает значения из предыдущих строк.

Давайте ответим на следующий вопрос, чтобы прояснить эту концепцию: какова последняя наибольшая сумма, на которую агент продал заказ?

SELECT agent_code, ord_amount, LAG(ord_amount, 1) OVER (
    PARTITION BY agent_code
    ORDER BY ord_amount DESC
) last_highest_amount
FROM orders
ORDER BY agent_code, ord_amount DESC;

И вот результаты:

Выше вы можете увидеть, как last_highest_amount четко показывает данные по каждому агенту - вот почему нет результата для агента A001, а первые значения для других агентов - NULL.

RANK () и DENSE_RANK ()

RANK() и DENSE_RANK() - функции нумерации. Они присваивают строке целочисленное значение в зависимости от раздела и порядка. Я не могу не подчеркнуть важность этих функций, когда дело доходит до поиска n-й самой высокой / самой низкой записи из таблицы.

DENSE_RANK() и RANK() отличаются тем, что в первом мы получаем последовательные ранги, а во втором ранг после ничьей пропускается. Например, ранжирование с использованием DENSE_RANK() будет примерно таким, как (1,2,2,3), тогда как ранжирование с использованием RANK() будет (1,2,2,4). Надеюсь, вы почувствуете разницу.

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

SELECT * FROM (
    SELECT ord_num, ord_date, ord_amount, DENSE_RANK() OVER(
        PARTITION BY DATE_PART(‘month’, ord_date) 
        ORDER BY ord_amount DESC) order_rank 
    FROM orders
) t
WHERE order_rank = 2
ORDER BY ord_date;

И вот результаты:

Прохладный! Перейдем к следующему.

CUME_DIST ()

Функция CUME_DIST() используется для вычисления совокупного распределения значений в заданном разделе. Он вычисляет долю строк в разделе, которая меньше или равна текущей строке. Это очень полезно, когда нам нужно получить только первые n% результатов.

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

SELECT DATE_PART(‘Month’,ord_date), agent_code, ord_amount, CUME_DIST() OVER(
   PARTITION BY DATE_PART(‘Month’,ord_date)
    ORDER BY ord_amount
)
FROM orders
WHERE ord_date BETWEEN ‘2008–08–01’ AND ‘2008–09–30’;

И вот результаты:

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

Прежде чем ты уйдешь

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

Я надеюсь, что эта пятерка вам подойдет, и не стесняйтесь исследовать и изучать больше самостоятельно. Спасибо за прочтение.

Присоединяйтесь к моему личному списку адресов электронной почты, чтобы получать больше полезной информации.