Ms sql оконные функции: Оконные функции SQL простым языком с примерами / Хабр

Полезные оконные функции SQL — Разработка на vc.ru

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

30 492
просмотров

Более того, считаем, что на текущий момент 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 (нет).

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

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

Оконные функции SQL: скользящие агрегаты

Это пятая, заключительная статья из серии Оконные функции в картинках. Рекомендую не просто читать, а проходить курс — с ним знания превратятся в навыки.

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

Разберемся на примерах. Здесь возьмем другую табличку — expenses. Она показывает доходы и расходы одного из сотрудников (пусть это будет Марина) за 9 месяцев 2020 года:

┌──────┬───────┬────────┬─────────┐
│ year │ month │ income │ expense │
├──────┼───────┼────────┼─────────┤
│ 2020 │ 1     │ 94     │ 82      │
│ 2020 │ 2     │ 94     │ 75      │
│ 2020 │ 3     │ 94     │ 104     │
│ 2020 │ 4     │ 100    │ 94      │
│ 2020 │ 5     │ 100    │ 99      │
│ 2020 │ 6     │ 100    │ 105     │
│ 2020 │ 7     │ 100    │ 95      │
│ 2020 │ 8     │ 100    │ 110     │
│ 2020 │ 9     │ 104    │ 104     │
└──────┴───────┴────────┴─────────┘

• скользящее среднее,
• фрейм,
• нарастающий итог,
• функции.

Все запросы можно повторять в песочнице.

Скользящее среднее по расходам

Судя по данным, доходы у Марины растут: 94К ₽ в январе → 104К ₽ в сентябре. А вот растут ли расходы? Сходу сложно сказать, месяц на месяц не приходится. Чтобы сгладить эти скачки, используют «скользящее среднее» — для каждого месяца рассчитывают средний расход с учетом предыдущего и следующего месяца. Например:

• скользящее среднее за февраль = (январь + февраль + март) / 3;
• за март = (февраль + март + апрель) / 3;
• за апрель = (март + апрель + май) / 3;
• и так далее.

Рассчитаем скользящее среднее по всем месяцам:

Было

Стало

Столбец roll_avg показывает скользящее среднее по расходам за три месяца (текущий, предыдущий и следующий). Теперь хорошо видно, что расходы стабильно растут.

Как перейти от «было» к «стало»?

Отсортируем таблицу по месяцам:

select
  year, month, expense,
  null as roll_avg
from expenses
order by year, month;

┌──────┬───────┬─────────┬──────────┐
│ year │ month │ expense │ roll_avg │
├──────┼───────┼─────────┼──────────┤
│ 2020 │ 1     │ 82      │          │
│ 2020 │ 2     │ 75      │          │
│ 2020 │ 3     │ 104     │          │
│ 2020 │ 4     │ 94      │          │
│ 2020 │ 5     │ 99      │          │
│ 2020 │ 6     │ 105     │          │
│ 2020 │ 7     │ 95      │          │
│ 2020 │ 8     │ 110     │          │
│ 2020 │ 9     │ 104     │          │
└──────┴───────┴─────────┴──────────┘

Теперь пройдем от первой строчки до последней. На каждом шаге будем считать среднее по предыдущему, текущему и следующему значению из столбца expense:

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

и так далее…

Одной гифкой:

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

1. Окно состоит из одной или нескольких секций (в нашем случае секция одна — все записи expenses).
2. Внутри секции записи упорядочены по конкретным столбцам (order by year, month).
3. У каждой записи свой фрейм.

Фрейм на каждом шаге охватывает три записи — текущую, предыдущую и следующую:

Вот как записать это на SQL:

window w as (
  order by year, month
  rows between 1 preceding and 1 following
)

С order by все понятно, а вторая строчка — это как раз определение фрейма: «выбрать строки от 1 предыдущей до 1 следующей». На следующем шаге разберемся с фреймами подробно, а пока закончим с нашим запросом.

Считаем среднее по расходам — это функция avg():

avg(expense) over w

Добавим округление и сведем все вместе:

select
  year, month, expense,
  round(avg(expense) over w) as roll_avg
from expenses
window w as (
  order by year, month
  rows between 1 preceding and 1 following
)
order by year, month;

┌──────┬───────┬─────────┬──────────┐
│ year │ month │ expense │ roll_avg │
├──────┼───────┼─────────┼──────────┤
│ 2020 │ 1     │ 82      │ 79.0     │
│ 2020 │ 2     │ 75      │ 87.0     │
│ 2020 │ 3     │ 104     │ 91.0     │
│ 2020 │ 4     │ 94      │ 99.0     │
│ 2020 │ 5     │ 99      │ 99.0     │
│ 2020 │ 6     │ 105     │ 100.0    │
│ 2020 │ 7     │ 95      │ 103.0    │
│ 2020 │ 8     │ 110     │ 103.0    │
│ 2020 │ 9     │ 104     │ 107.0    │
└──────┴───────┴─────────┴──────────┘

Скользящее среднее по расходам готово!

Фрейм

В общем случае определение фрейма выглядит так:

rows between X preceding and Y following

Где X — количество строк перед текущей, а Y — количество строк после текущей:

Если указать вместо X или Y значение unbounded — это значит «граница секции»:

Если указать вместо X preceding или Y following значение current row — это значит «текущая запись»:

Фрейм никогда не выходит за границы секции, если столкнулся с ней — обрезается:

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

Прибыль нарастающим итогом

Благодаря скользящему среднему, мы выяснили, что в expenses растут и доходы, и расходы. А как они соотносятся друг с другом? Хочется понять, находится ли человек «в плюсе» или «в минусе» с учетом всех заработанных и потраченных денег.

Причем важно понимать не на конец года, а на каждый месяц. Потому что если по итогам года у Марины все ОК, а в июне ушла в минус — это потенциальная проблема (у компаний такую ситуацию называют «кассовым разрывом»).

Поэтому посчитаем доходы и расходы по месяцам нарастающим итогом (кумулятивно):

• кумулятивный доход за январь = январь;
• за февраль = январь + февраль;
• за март = январь + февраль + март;
• за апрель = январь + февраль + март + апрель;
• и так далее.

t_income показывает доходы нарастающим итогом, t_expense — расходы, а t_profit — прибыль.

t_profit = t_income - t_expense

Как рассчитать кумулятивные показатели?

Отсортируем таблицу по месяцам:

select
  year, month, income, expense,
  null as t_income,
  null as t_expense,
  null as t_profit
from expenses
order by year, month;

┌──────┬───────┬────────┬─────────┬──────────┬───────────┬──────────┐
│ year │ month │ income │ expense │ t_income │ t_expense │ t_profit │
├──────┼───────┼────────┼─────────┼──────────┼───────────┼──────────┤
│ 2020 │ 1     │ 94     │ 82      │          │           │          │
│ 2020 │ 2     │ 94     │ 75      │          │           │          │
│ 2020 │ 3     │ 94     │ 104     │          │           │          │
│ 2020 │ 4     │ 100    │ 94      │          │           │          │
│ 2020 │ 5     │ 100    │ 99      │          │           │          │
│ 2020 │ 6     │ 100    │ 105     │          │           │          │
│ 2020 │ 7     │ 100    │ 95      │          │           │          │
│ 2020 │ 8     │ 100    │ 110     │          │           │          │
│ 2020 │ 9     │ 104    │ 104     │          │           │          │
└──────┴───────┴────────┴─────────┴──────────┴───────────┴──────────┘

Теперь пройдем от первой строчки до последней. На каждом шаге будем считать суммарные показатели от начала таблицы до текущей строки:

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

и так далее…

Одной гифкой:

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

window w as (
  order by year, month
  rows between unbounded preceding and current row
)

Считаем сумму по доходам и расходам — это функция sum():

sum(income) over w as t_income,
sum(expense) over w as t_expense,

Прибыль считаем как разницу между доходами и расходами:

(sum(income) over w) - (sum(expense) over w) as t_profit

Все вместе:

select
  year, month, income, expense,
  sum(income) over w as t_income,
  sum(expense) over w as t_expense,
  (sum(income) over w) - (sum(expense) over w) as t_profit
from expenses
window w as (
  order by year, month
  rows between unbounded preceding and current row
)
order by year, month;

┌──────┬───────┬────────┬─────────┬──────────┬───────────┬──────────┐
│ year │ month │ income │ expense │ t_income │ t_expense │ t_profit │
├──────┼───────┼────────┼─────────┼──────────┼───────────┼──────────┤
│ 2020 │ 1     │ 94     │ 82      │ 94       │ 82        │ 12       │
│ 2020 │ 2     │ 94     │ 75      │ 188      │ 157       │ 31       │
│ 2020 │ 3     │ 94     │ 104     │ 282      │ 261       │ 21       │
│ 2020 │ 4     │ 100    │ 94      │ 382      │ 355       │ 27       │
│ 2020 │ 5     │ 100    │ 99      │ 482      │ 454       │ 28       │
│ 2020 │ 6     │ 100    │ 105     │ 582      │ 559       │ 23       │
│ 2020 │ 7     │ 100    │ 95      │ 682      │ 654       │ 28       │
│ 2020 │ 8     │ 100    │ 110     │ 782      │ 764       │ 18       │
│ 2020 │ 9     │ 104    │ 104     │ 886      │ 868       │ 18       │
└──────┴───────┴────────┴─────────┴──────────┴───────────┴──────────┘

По t_profit видно, что дела у Марины идут неплохо. В некоторых месяцах расходы превышают доходы, но благодаря накопленной «денежной подушке» кассового разрыва не происходит.

Функции агрегации

Скользящие агрегаты используют те же самые функции, что и агрегаты обычные:

• min() и max()
• count(), avg() и sum()

Разница только в наличии фрейма у скользящих агрегатов.

⌘ ⌘ ⌘

Мы рассмотрели четыре класса задач, которые решаются с помощью оконных функций в SQL:

• Ранжирование (всевозможные рейтинги).
• Сравнение со смещением (соседние элементы и границы).
• Агрегация (количество, сумма и среднее).
• Скользящие агрегаты (сумма и среднее в динамике).

Теперь попробуйте применить «окошки» на практике!

Чтобы узнать больше об оконных функциях или потренироваться — записывайтесь на курс 🚀

Подписывайтесь на канал, чтобы не пропустить новые заметки 🚀

Как использовать функции окна в SQL Server

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

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

Давайте посмотрим, как работают функции Window, а затем рассмотрим несколько примеров их использования на практике, чтобы убедиться, что все понятно, а также сравним SQL и выходные данные с функциями SUM().

Как всегда убедитесь, что у вас есть полная резервная копия, особенно если вы пробуете новые вещи с вашей базой данных.

Введение в функции окна

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

Мы определяем Окно (набор строк, с которыми работают функции), используя предложение OVER(). Подробнее о предложении OVER() мы поговорим в статье ниже.

Типы оконных функций

• Агрегированные оконные функции
  
  СУММ(), МАКС(), МИН(), СРЕДН(). СЧИТАТЬ()
• Ранжирование оконных функций
  
  RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
• Функции окна значений
  

  LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Синтаксис

Аргументы

функция_окна

Укажите имя оконной функции

ВСЕ


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

выражение


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

НАД


Задает предложения окна для агрегатных функций.

РАЗДЕЛ ПО partition_list


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

ЗАКАЗАТЬ ПО order_list


Сортирует строки в каждом разделе. Если ORDER BY не указан, ORDER BY использует всю таблицу.

Примеры

Давайте создадим таблицу и вставим фиктивные записи для написания дальнейших запросов. Запустите код ниже.

Агрегированные оконные функции

SUM()

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

Ниже приведен пример обычной агрегатной функции SUM(). Суммирует сумму заказа для каждого города.

Из набора результатов видно, что обычная агрегатная функция группирует несколько строк в одну выходную строку, из-за чего отдельные строки теряют свою идентичность.

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

АВГ()

AVG или Average работают точно так же с функцией Window.

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

Мы указываем более одного среднего значения, указав несколько полей в списке разделов.

Также стоит отметить, что вы можете использовать выражения в таких списках, как MONTH(order_date), как показано в запросе ниже. Как всегда, вы можете сделать эти выражения настолько сложными, насколько захотите, при условии правильного синтаксиса!

На изображении выше мы ясно видим, что в среднем мы получили 12 333 заказа для города Арлингтон на апрель 2017 года.

Средняя сумма заказа = общая сумма заказа / общая сумма заказов

= (20 000 + 15 000 + 2 000) / 3

= 12 333

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

МИН()

Агрегатная функция MIN() найдет минимальное значение для указанной группы или для всей таблицы, если группа не указана.

Например, мы ищем наименьший заказ (минимальный заказ) для каждого города, мы бы использовали следующий запрос.

МАКС()

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

найдем самый большой заказ (максимальную сумму заказа) для каждого города.

СЧЕТ()

Функция COUNT() будет подсчитывать записи/строки.

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

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

COUNT(имя_клиента) даст вам неверный результат, так как будет подсчитываться дубликаты. Принимая во внимание, что COUNT(DISTINCT customer_name) даст вам правильный результат, поскольку он подсчитывает каждого уникального клиента только один раз.

Действительно для обычной функции COUNT():

Недействительно для оконной функции COUNT():

Приведенный выше запрос с функцией Window даст вам следующую ошибку.

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

Функции окна ранжирования

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

Наиболее распространенное использование функций RANKING — поиск лучших (N) записей на основе определенного значения. Например, Топ-10 самых высокооплачиваемых сотрудников, Топ-10 студентов, Топ-50 крупнейших заказов и т. д.

Поддерживаются следующие функции РЕЙТИНГА:

RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()

Давайте обсудим их один за другим.

РАНГ()

Функция RANK() используется для присвоения каждой записи уникального ранга на основе указанного значения, например, оклада, суммы заказа и т. д.

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

Давайте ранжируем каждый заказ по сумме заказа.

На изображении выше видно, что один и тот же ранг (3) назначается двум идентичным записям (каждая из которых имеет сумму заказа 15 000), а затем он пропускает следующий ранг (4) и присваивает ранг 5 следующей записи.

DENSE_RANK()

Функция DENSE_RANK() идентична функции RANK(), за исключением того, что она не пропускает ни одного ранга. Это означает, что если будут найдены две идентичные записи, то DENSE_RANK() присвоит обеим записям одинаковый ранг, но не пропустит, а затем пропустит следующий ранг.

Давайте посмотрим, как это работает на практике.

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

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

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

Номер строки будет сброшен для каждого раздела, если указано PARTITION BY. Давайте посмотрим, как ROW_NUMBER() работает без PARTITION BY, а затем с PARTITION BY.

ROW_ NUMBER() без РАЗДЕЛА ПО

ROW_NUMBER() с PARTITION BY

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

NTILE()

NTILE() — очень полезная оконная функция. Это поможет вам определить, к какому процентилю (или квартилю, или любому другому подразделению) относится данная строка.

Это означает, что если у вас есть 100 строк и вы хотите создать 4 квартили на основе указанного поля значений, вы можете легко сделать это и посмотреть, сколько строк попадает в каждый квартиль.

Давайте посмотрим пример. В приведенном ниже запросе мы указали, что хотим создать четыре квартили на основе суммы заказа. Затем мы хотим увидеть, сколько заказов попадает в каждый квартиль.

NTILE создает тайлы на основе следующей формулы:

Количество строк в каждой плитке = количество строк в результирующем наборе / количество указанных плиток

Вот наш пример, у нас всего 10 строк и в запросе указано 4 плитки, поэтому количество строк в каждой плитке будет 2,5 (10/4). Поскольку количество строк должно быть целым числом, а не десятичным. Механизм SQL назначит 3 строки для первых двух групп и 2 строки для оставшихся двух групп.

Функции окна значений

Функции окна значений используются для поиска первого, последнего, предыдущего и следующего значений. Можно использовать следующие функции: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE().

LAG() и LEAD()

Функции LEAD() и LAG() очень мощные, но их сложно объяснить.

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

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

Скрипт для поиска даты предыдущего заказа с помощью функции LAG():

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

Скрипт для поиска даты следующего заказа с помощью функции LEAD():

ПЕРВОЕ_ЗНАЧ() и ПОСЛЕДНЕЕ_ЗНАЧ()

Эти функции помогают определить первую и последнюю запись в разделе или во всей таблице, если PARTITION BY не указан.

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

На изображении выше мы ясно видим, что первый заказ был получен 2 апреля 2017 г., а последний заказ получен 11 апреля 2017 г. для города Арлингтон, и это работает одинаково для других городов.

Полезные ссылки

• Типы и стратегии резервного копирования для баз данных SQL
• Статья TechNet о пункте OVER
• Статья MSDN о DENSE_RANK

Другие замечательные статьи Бена

• Автор
• Последние сообщения

Бен Ричардсон

Бен Ричардсон руководит Acuity Training, ведущим поставщиком услуг по обучению SQL в Великобритании. Он предлагает полный спектр обучения SQL от вводных курсов до продвинутого обучения администрированию и работе с хранилищами данных — см. здесь для получения более подробной информации. Acuity имеет офисы в Лондоне и Гилфорде, графство Суррей. Он также иногда пишет в блоге Acuity

Просмотреть все сообщения Бена Ричардсона

Последние сообщения Бена Ричардсона (посмотреть все)

Функции окна SQL | Расширенный SQL

Начиная отсюда? Этот урок является частью полного учебника по использованию SQL для анализа данных. Проверьте начало.

В этом уроке мы рассмотрим:

• Введение в оконные функции
• Основной синтаксис управления окнами
• Обычные подозреваемые: SUM, COUNT и AVG
• ROW_NUMBER()
• РАНГ() и DENSE_RANK()
• НТИЛЕ
• LAG и LEAD
• Определение псевдонима окна
• Расширенные методы работы с окнами

В этом уроке используются данные программы Capital Bikeshare Program в Вашингтоне, округ Колумбия, которая публикует подробные исторические данные о поездках на своем веб-сайте. Данные были загружены в феврале 2014 г., но ограничены данными, собранными в течение первого квартала 2012 г. Каждая строка представляет одну поездку. Большинство полей говорят сами за себя, за исключением rider_type : «Зарегистрированный» указывает на ежемесячное членство в программе совместного использования, «Повседневный» указывает, что гонщик купил проездной на 3 дня. 9Поля 0816 start_time и end_time были очищены от их исходных форм, чтобы соответствовать форматированию даты SQL — они хранятся в этой таблице как метки времени.

Введение в оконные функции

Документация PostgreSQL прекрасно знакомит с концепцией оконных функций:

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

Наиболее практичным примером этого является промежуточный итог:

 SELECT duration_seconds,
       SUM(duration_seconds) OVER (ORDER BY start_time) AS running_total
  ИЗ tutorial.dc_bikeshare_q1_2012
 

Вы можете видеть, что приведенный выше запрос создает агрегацию ( running_total ) без использования GROUP BY . Давайте разберем синтаксис и посмотрим, как он работает.

Основной синтаксис работы с окнами

Первая часть вышеприведенной агрегации, SUM(duration_seconds) очень похоже на любую другую агрегацию. Добавление НАД определяет его как оконную функцию. Вы можете прочитать приведенную выше агрегацию как «возьмите сумму duration_seconds по всему набору результатов, в порядке start_time ».

Если вы хотите сузить окно от всего набора данных до отдельных групп в наборе данных, вы можете использовать PARTITION BY для этого:

 SELECT start_terminal,
       длительность_секунд,
       СУММ(длительность_секунд) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
         AS running_total
  ИЗ tutorial. dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 

Приведенный выше запрос группирует и упорядочивает запрос по start_terminal . В каждом значении start_terminal оно упорядочено по start_time и промежуточной общей сумме по текущей строке и всем предыдущим строкам duration_seconds . Прокрутите вниз, пока значение start_terminal не изменится, и вы заметите, что running_total начинается заново. Вот что происходит, когда вы группируете с помощью PARTITION BY . Если вы все еще в тупике ORDER BY , он просто упорядочивает по назначенному столбцу (столбцам) так же, как предложение ORDER BY , за исключением того, что он рассматривает каждый раздел как отдельный. Он также создает промежуточную сумму — без ORDER BY каждое значение будет просто суммой всех значений duration_seconds в соответствующем start_terminal . Попробуйте выполнить приведенный выше запрос без ORDER BY , чтобы понять:

 SELECT start_terminal,
       длительность_секунд,
       СУММ(длительность_секунд) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal) КАК start_terminal_total
  ИЗ tutorial.dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 

ПРИКАЗ и ЧАСТЬ определяют так называемое «окно» — упорядоченное подмножество данных, по которым выполняются вычисления.

Примечание. В одном запросе нельзя использовать оконные функции и стандартные агрегаты. В частности, вы не можете включать оконные функции в предложение GROUP BY .

Практическая задача

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

Попробуйте См. ответ

Обычные подозреваемые: SUM, COUNT и AVG

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

 ВЫБЕРИТЕ start_terminal,
       длительность_секунд,
       СУММ(длительность_секунд) БОЛЕЕ
         (РАЗДЕЛ BY start_terminal) AS running_total,
       COUNT(длительность_секунд) БОЛЕЕ
         (РАЗДЕЛ ПО start_terminal) AS running_count,
       СРЕДНЕЕ (длительность_секунд) БОЛЕЕ
         (РАЗДЕЛ ПО start_terminal) AS running_avg
  ИЗ tutorial.dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 

В качестве альтернативы те же функции с ORDER BY :

 SELECT start_terminal,
       длительность_секунд,
       СУММ(длительность_секунд) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
         AS running_total,
       COUNT(длительность_секунд) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
         AS running_count,
       СРЕДНЕЕ (длительность_секунд) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
         AS running_avg
  ИЗ tutorial. dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 

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

Практическая задача

Напишите запрос, показывающий промежуточную сумму продолжительности поездок на велосипеде (аналогично последнему примеру), но сгруппированный по end_terminal , а продолжительность поездки отсортирована в порядке убывания.

ПопробуйтеПосмотреть ответ

ROW_NUMBER()

ROW_NUMBER() делает именно то, на что похоже — отображает номер заданной строки. Он начинается с 1 и нумерует строки в соответствии с ORDER BY частью оператора окна. ROW_NUMBER() не требует указания переменной в круглых скобках:

 SELECT start_terminal,
       время начала,
       длительность_секунд,
       ROW_NUMBER() OVER (ЗАКАЗАТЬ ПО start_time)
                    AS row_number
  ИЗ tutorial. dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 

Использование предложения PARTITION BY позволит вам снова начать считать 1 в каждом разделе. Следующий запрос снова запускает счет для каждого терминала:

 SELECT start_terminal,
       время начала,
       длительность_секунд,
       ROW_NUMBER() OVER (РАЗДЕЛ ПО start_terminal
                          ЗАКАЗАТЬ ПО start_time)
                    AS row_number
  ИЗ tutorial.dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 

РАНГ() и DENSE_RANK()

RANK() немного отличается от ROW_NUMBER() . Например, если вы заказываете по start_time , может случиться так, что на некоторых терминалах есть поездки с двумя одинаковыми временами начала. В этом случае им присваивается одинаковый ранг, тогда как ROW_NUMBER() присваивает им разные номера. В следующем запросе вы видите 4-е и 5-е наблюдения для start_terminal 31000 — им обоим присваивается ранг 4, а следующий результат получает ранг 6:

 ВЫБЕРИТЕ start_terminal,
       длительность_секунд,
       RANK() OVER (РАЗДЕЛ ПО start_terminal
                    ЗАКАЗАТЬ ПО start_time)
              AS ранг
  ИЗ tutorial. dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 

Вы также можете использовать DENSE_RANK() вместо RANK() в зависимости от вашего приложения. Представьте ситуацию, в которой три записи имеют одинаковое значение. Используя любую команду, все они получат одинаковый ранг. Для примера допустим, что это «2». Вот как две команды будут по-разному оценивать следующие результаты:

• RANK() даст идентичным строкам ранг 2, затем пропустит ранги 3 и 4, так что следующим результатом будет 5
• DENSE_RANK() по-прежнему будет присваивать всем идентичным строкам ранг 2, но следующая строка будет иметь ранг 3 — ни один ранг не будет пропущен.

Практическая задача

Напишите запрос, который показывает 5 самых длинных поездок от каждого начального терминала, упорядоченных по терминалам, и от самых длинных до самых коротких поездок в каждом терминале. Ограничение на поездки, совершенные до 8 января 2012 года.

Попробуйте См. ответ

NTILE

Вы можете использовать оконные функции, чтобы определить, в какой процентиль (или квартиль, или любое другое подразделение) попадает данная строка. Синтаксис: NTILE(*# сегментов*) . В этом случае ORDER BY определяет, какой столбец использовать для определения квартилей (или любого другого количества плиток, которое вы укажете). Например:

 ВЫБЕРИТЕ start_terminal,
       длительность_секунд,
       НЕТ (4) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
          Квартиль AS,
       НЕТ (5) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
         Квинтиль AS,
       NTILE(100) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
         AS процентиль
  ИЗ tutorial.dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 ORDER BY start_terminal, duration_seconds
 

Глядя на результаты запроса выше, вы можете видеть, что столбец процентиль вычисляется не так, как вы могли бы ожидать. Если бы у вас было только две записи и вы измеряли процентили, вы бы ожидали, что одна запись будет определять 1-й процентиль, а другая — 100-й процентиль. Используя функцию NTILE , вы фактически увидите одну запись в 1-м процентиле и одну во 2-м процентиле. Вы можете увидеть это в результатах для start_terminal 9.0817 31000 — столбец процентилей просто выглядит как числовое ранжирование. Если вы прокрутите вниз до start_terminal 31007, вы увидите, что он правильно вычисляет процентили, поскольку для этого start_terminal существует более 100 записей. Если вы работаете с очень маленькими окнами, имейте это в виду и рассмотрите возможность использования квартилей или аналогичных небольших диапазонов.

Практическая задача

Напишите запрос, который показывает только продолжительность поездки и процентиль, в который попадает эта продолжительность (по всему набору данных, а не по терминалам).

Попробуйте. См. ответ

LAG и LEAD

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

 ВЫБЕРИТЕ start_terminal,
       длительность_секунд,
       ЗАДЕРЖКА(длительность_секунд, 1) ПРЕВЫШЕНО
         (PARTITION BY start_terminal ORDER BY duration_seconds) AS отставание,
       ОПЕРЕЖЕНИЕ(длительность_секунд, 1) ПРЕВЫШЕНИЕ
         (PARTITION BY start_terminal ORDER BY duration_seconds) AS ведущий
  ИЗ tutorial.dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 ORDER BY start_terminal, duration_seconds
 

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

 SELECT start_terminal,
       длительность_секунд,
       duration_seconds -LAG(duration_seconds, 1) ПРЕВЫШЕНО
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
         разница в качестве
  ИЗ tutorial. dc_bikeshare_q1_2012
 ГДЕ start_time < '2012-01-08'
 ORDER BY start_terminal, duration_seconds
 

Первая строка столбца разность пуста, так как нет предыдущей строки, из которой можно извлечь. Точно так же использование LEAD создаст нули в конце набора данных. Если вы хотите сделать результаты немного чище, вы можете обернуть их во внешний запрос, чтобы удалить пустые значения:

 SELECT *
  ИЗ (
    ВЫБЕРИТЕ start_terminal,
           длительность_секунд,
           duration_seconds -LAG(duration_seconds, 1) ПРЕВЫШЕНО
             (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
             разница в качестве
      ИЗ tutorial.dc_bikeshare_q1_2012
     ГДЕ start_time < '2012-01-08'
     ORDER BY start_terminal, duration_seconds
       ) суб
 ГДЕ sub.difference НЕ NULL
 

Определение псевдонима окна

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