Вы случайно не PHP Middle?
Разработка
NTA
4647

Полезные оконные функции SQL

Можно бесконечно долго «воротить нос» от использования SQL для Data Preparation, отдавая лавры змеиному языку, но нельзя не признавать факт, что чаще мы используем и еще долго будем использовать SQL для работы с данными, в том числе и очень объемными.

В закладки

Более того, считаем, что на текущий момент SQL окажется под рукой сотрудника с большей вероятностью, чем Python, и поможет быстро решить аналитическую задачку с приоритетом «-1».

Предложение OVER помогает «открыть окно», т.е. определить строки, с которым будет работать та или иная функция.

Предложение partion BY не является обязательным, но дополняет OVER и показывает, как именно мы разделяем строки, к которым будет применена функция.

ORDER BY определит порядок обработки строк.

В одном select может быть больше одного OVER, эта прекрасная особенность упростит выполнение аналитической задачи в дальнейшем.

Итак, оконные функции делятся на:

  • Агрегатные функции
  • Ранжирующие функции
  • Функции смещения
  • Аналитические функции

Собственно, те же, что и обычные, только встроенные в конструкцию с OVER

SUM/ AVG / COUNT/ MIN/ MAX

Для наглядности работы данных функций воспользуемся базовым набором данных (T)

Задача:

Найти максимальную задолженность в каждом банке.

Для чего тут оконные функции? Можно же просто написать:

SELECT TB, max(OSZ) OSZ FROM T group by TB

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

Собрать дэшборд, в котором содержится информация о максимальной задолженности в каждом банке, а также средний размер процентной ставки в каждом банке в зависимости от сегмента, плюс еще количество договоров всего всем банкам (в голове рисуются множественные джойны из подзапросов и как-то сразу тяжело на душе). Однако, как я говорил выше, в одном select можно использовать много OVER, а также еще один прекрасный факт: набор строк в окне, связывается с текущей строкой, а не с группой агрегированных. Таким образом:

SELECT TB, ID_CLIENT, ID_DOG, OSZ, PROCENT_RATE, RATING, SEGMENT , MAX(OSZ) OVER (PARTITION BY TB) 'Максимальная задолженность в разбивке по банкам' , AVG(PROCENT_RATE) OVER (PARTITION BY TB, SEGMENT) 'Средняя процентная ставка в разрезе банка и сегмента' , COUNT(ID_DOG) OVER () 'Всего договоров во всех банках' FROM T

На примере AVG(procent_RATE) OVER (partition BY TB, segment) подробнее:

  • Мы применяем AVG – агрегатную функцию по подсчету среднего значения к столбцу procent_RATE.
  • Затем предложением OVER определяем, что будем работать с некоторым набором строк. По умолчанию, если указать OVER() с пустыми строками, то этот набор строк равен всей таблице.
  • Предложением partition BY выделяем разделы в наборе строк по заданному условию, в нашем случае, в разбивке на Территориальные банки и Сегмент.
  • В итоге, к каждой строке базовой таблицы применится функция по подсчету среднего из набора строк, разбитых на разделы (по Территориальным Банкам и Сегменту).

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

Базовый набор данных: банки, отделы и количество ревизий.

Сами ранжирующие функции:

ROW_number – нумерует строки в результирующем наборе.

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

DENSE_RANK -присваивает ранг для каждой строки, если найдутся одинаковые значения, то следующий ранг присваивается без пропуска.

NTILE – помогает разделить результирующий набор на группы.

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

SELECT * , ROW_NUMBER() OVER(ORDER BY count_revisions desc) , Rank() OVER(ORDER BY count_revisions desc) , DENSE_RANK() OVER(ORDER BY count_revisions desc) , NTILE(3) OVER(ORDER BY count_revisions desc) FROM Table_Rev

ROW_number – пронумеровал столбцы в порядке убывания количества ревизий.

RANK – проранжировал отделы во всех банках в порядке убывания количества ревизий, но как только встретились одинаковые значения (количество ревизий 95), функция присвоила им ранг 4, а следующее значение получило ранг 6.

DENSE_RANK – аналогично RANK, но как только встретились одинаковые значения, следующее значение получило ранг 5.

NTILE – функция помогла разбить таблицу на 3 группы (указал в аргументе). Так как в таблице 18 значений, в каждую группу попало по 6.

Задача:

Найти второй отдел во всех банках по количеству ревизий.

Можно, конечно, воспользоваться чем-то вроде:

SELECT MAX(count_revisions) ms FROM Table_Rev WHERE count_revisions!=(SELECT MAX(count_revisions) FROM Table_Rev)

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

With T_R as ( SELECT * , DENSE_RANK() OVER(ORDER BY count_revisions desc) ds FROM Table_Rev ) SELECT * FROM T_R WHERE ds=3

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

With T_R as ( SELECT * , DENSE_RANK() OVER(PARTITION BY tb ORDER BY count_revisions) ds FROM Table_Rev ) SELECT tb,dep,count_revisions FROM T_R WHERE ds=1

Получаем:

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

LAG — смещение назад.

LEAD — смещение вперед.

FIRST_VALUE — найти первое значение набора данных.

LAST_VALUE — найти последнее значение набора данных.

LAG и LEAD имеют следующие аргументы:

  • Столбец, значение которого необходимо вернуть
  • На сколько строк выполнить смешение (дефолт =1)
  • Что вставить, если вернулся NULL

Как обычно, на практике проще:

Базовый набор данных, содержит id задачи, события внутри нее и их дату:

Применяя конструкцию:

SELECT * , LEAD (Event, 1, 'end') OVER (PARTITION BY ID_Task ORDER BY Date_Event) as Next_Event , LEAD (Date_Event, 1, '2099-01-01') OVER(PARTITION BY ID_Task ORDER BY Date_Event) as Next_Date FROM Table_Task

Получаем набор данных, который хоть сейчас в graphviz (нет).

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

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

Лайфхаки IT, проверенные AI-решения для стандартных задач
{ "author_name": "NTA", "author_type": "editor", "tags": [], "comments": 4, "likes": 14, "favorites": 51, "is_advertisement": false, "subsite_label": "dev", "id": 130856, "is_wide": true, "is_ugc": false, "date": "Sat, 30 May 2020 20:41:15 +0300", "is_special": false }
Транспорт
Как мы сделали по-настоящему крутой автопилот для локомотива
Пока железные дороги мира борются с аварийностью, в России советские тепловозы ездят на самом современном автопилоте…
Объявление на vc.ru
0
4 комментария
Популярные
По порядку
5

Вы бы диалект SQL указывали перед листингом кода.

Ответить
1

как можно не узнать Oracle)

Ответить
1

Partition a не partion

Ответить
0

Спасибо! Да, опечатка

Ответить

Комментарии