Аналитические функции 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.
Я надеюсь, что эта пятерка вам подойдет, и не стесняйтесь исследовать и изучать больше самостоятельно. Спасибо за прочтение.