Введение

MariaDB ColumnStore поддерживает функции окон, в основном следуя спецификации SQL 2003. Функция окна позволяет выполнять вычисления, относящиеся к окну данных, окружающему текущую строку в наборе результатов. Эта возможность упрощает запросы, отвечая на общие бизнес-вопросы, такие как кумулятивные суммы, скользящие средние и списки из 10 лучших.

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

Два ключевых понятия для функций окон — это Разделение и Фрейм:

• Разделение — это группа строк, или окно, которые имеют одинаковое значение для определенного столбца, например, Разделение может быть создано по периодам времени, таким как квартал или значениям поиска.
• Фрейм для каждой строки — это подмножество разделения строки. Фрейм обычно динамичный, позволяющий скользящему фрейму строк внутри разделения. Фрейм определяет диапазон строк для функции окна. Фрейм можно определить как последние X строк и следующие Y строк до всего Разделения.

Функции окон применяются после вычисления условий объединения, группировки и имеющих.

Синтаксис

Функция окна применяется в предложении select, используя следующий синтаксис:

function_name ([expression [, expression ... ]]) OVER ( window_definition )

где определение_окна определяется как:

[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

PARTITION BY:

• Разделяет набор результатов окна на группы на основе одного или нескольких выражений.
• Выражение может быть константой, столбцом и выражениями, не являющимися функциями окон.
• Запрос не ограничивается одним предложением partition by. Различные предложения partition by могут использоваться в разных применениях функций окон.
• Столбцы partition by не должны быть в списке select, но должны быть доступны в наборе результатов запроса.
• Если предложение PARTITION BY отсутствует, все строки набора результатов определяют группу.

ORDER BY

• Определяет порядок значений внутри разделения.
• Может быть отсортирован по нескольким ключам, которые могут быть константой, столбцом или выражением, не являющимся функцией окна.
• Столбцы order by не должны быть в списке select, но должны быть доступны в наборе результатов запроса.
• Использование псевдонима столбца select из запроса не поддерживается.
• Опции ASC (по умолчанию) и DESC позволяют сортировать по возрастанию или убыванию.
• Опции NULLS FIRST и NULL_LAST указывают, появляются ли нулевые значения вначале или в конце последовательности сортировки. NULLS_FIRST — по умолчанию для ASC, а NULLS_LAST — по умолчанию для DESC.

и необязательное frame_clause определяется как:

{ RANGE | ROWS } frame_start
{ RANGE | ROWS } BETWEEN frame_start AND frame_end

и необязательные frame_start и frame_end определяются как (значение — числовое выражение):

UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING

RANGE/ROWS:

• Определяет предложение окна для расчета набора строк, к которым функция применяется для вычисления результата функции окна для данной строки.
• Требуется предложение ORDER BY для определения порядка строк для окна.
• ROWS задают окно в физических единицах, т. е. строки набора результатов и должны быть константой или выражением, вычисляющим положительное числовое значение.
• RANGE задает окно как логическое смещение. Если выражение вычисляет числовое значение, то выражение ORDER BY должно быть числового или типа DATE. Если выражение вычисляет значение интервала, то выражение ORDER BY должно быть типа DATE.
• UNBOUNDED PRECEDING указывает, что окно начинается с первой строки раздела.
• UNBOUNDED FOLLOWING указывает, что окно заканчивается последней строкой раздела.
• CURRENT ROW указывает, что окно начинается или заканчивается на текущей строке или значении.
• Если опущено, по умолчанию — ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Поддерживаемые функции

Примеры

Пример схемы

Все примеры основаны на следующей упрощенной таблице возможностей продаж:

create table opportunities (
id int,
accountName varchar(20),
name varchar(128),
owner varchar(7),
amount decimal(10,2),
closeDate date,
stageName varchar(11)
) engine=columnstore;

Некоторые примеры значений (спасибо https://www.mockaroo.com за генерацию тестовых данных):

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

Пример кумулятивной суммы и максимального значения

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

select owner, 
accountName, 
CloseDate, 
amount, 
sum(amount) over (order by CloseDate rows between unbounded preceding and current row) cumeWon, 
max(amount) over (order by CloseDate rows between unbounded preceding and current row) runningMax
from opportunities 
where stageName='ClosedWon' 
and closeDate >= '2016-10-02' and closeDate <= '2016-10-09' 
order by CloseDate;

с примерами результатов:

Пример разбиения кумулятивной суммы и текущего максимума

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

select owner,  
accountName,  
CloseDate,  
amount,  
sum(amount) over (partition by owner order by CloseDate rows between unbounded preceding and current row) cumeWon,  
max(amount) over (partition by owner order by CloseDate rows between unbounded preceding and current row) runningMax 
from opportunities  
where stageName='ClosedWon' 
and closeDate >= '2016-10-02' and closeDate <= '2016-10-09'  
order by owner, CloseDate;

с результатами примера:

Ранжирование/Топ-результаты

Функция окна ранжирования позволяет ранжировать или присваивать порядковый номер на основе определения функции окна. При использовании функции Rank() одинаковые значения будут иметь одинаковый рейтинг, а следующий рейтинг будет пропущен. Функция Dense_Rank() ведет себя аналогично, за исключением того, что после совпадения используется следующее последовательное число, а не пропускается. Функция Row_Number() обеспечит уникальный порядковый номер. В приведенном примере функции Rank() используется для ранжирования торговых представителей по количеству возможностей для 4-го квартала 2016 года.

select owner, 
wonCount, 
rank() over (order by wonCount desc) rank 
from (
  select owner, 
  count(*) wonCount 
  from opportunities 
  where stageName='ClosedWon' 
  and closeDate >= '2016-10-01' and closeDate < '2016-12-31'  
  group by owner
) t
order by rank;

с результатами примера (обратите внимание, что запрос технически неверен, используя closeDate < '2016-12-31', но это создает сценарий ничьей для целей иллюстрации):

Если используется функция dense_rank, значения ранга будут 1, 2, 3, 3, 4, а для функции row_number - 1, 2, 3, 4, 5.

Первые и последние значения

Функции first_value и last_value позволяют определить первое и последнее значения заданного диапазона. В сочетании с группировкой по группам это позволяет подытожить начальные и конечные значения. Пример показывает более сложный случай, где представлена подробная информация о первой и последней возможности по кварталу.

select a.year, 
a.quarter, 
f.accountName firstAccountName, 
f.owner firstOwner, 
f.amount firstAmount, 
l.accountName lastAccountName, 
l.owner lastOwner, 
l.amount lastAmount 
from (
  select year, 
  quarter, 
  min(firstId) firstId, 
  min(lastId) lastId 
  from (
    select year(closeDate) year, 
    quarter(closeDate) quarter, 
    first_value(id) over (partition by year(closeDate), quarter(closeDate) order by closeDate rows between unbounded preceding and current row) firstId, 
    last_value(id) over (partition by year(closeDate), quarter(closeDate) order by closeDate rows between current row and unbounded following) lastId 
    from opportunities  where stageName='ClosedWon'
  ) t 
  group by year, quarter order by year,quarter
) a 
join opportunities f on a.firstId = f.id 
join opportunities l on a.lastId = l.id 
order by year, quarter;

с результатами примера:

Предыдущий и следующий пример

Иногда полезно понимать предыдущие и последующие значения в контексте данной строки. Функции окна lag и lead предоставляют эту возможность. По умолчанию смещение равно одному, предоставляя предыдущее или следующее значение, но его также можно указать, чтобы получить большее смещение. Пример запроса представляет собой отчет о возможностях по названию счета, показывающий сумму возможности и сумму предыдущей и следующей возможности для этого счета по дате закрытия.

select accountName, 
closeDate,  
amount currentOppAmount, 
lag(amount) over (partition by accountName order by closeDate) priorAmount, lead(amount) over (partition by accountName order by closeDate) nextAmount 
from opportunities 
order by accountName, closeDate 
limit 9;

с результатами примера:

Пример квартилей

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

select t.quartile, 
min(t.amount) min, 
max(t.amount) max 
from (
  select amount, 
  ntile(4) over (order by amount asc) quartile 
  from opportunities 
  where closeDate >= '2016-10-01' and closeDate <= '2016-12-31'
  ) t 
group by quartile 
order by quartile;

С результатами примера:

Пример процентилей

Функции процентилей имеют немного другой синтаксис, чем другие функции окон, как видно в примере ниже. Эти функции могут применяться только к числовым значениям. Аргументом функции является процентиль для оценки. После 'внутри группы' следует выражение сортировки, которое указывает столбец сортировки и, необязательно, порядок. Наконец, после 'над' находится необязательная часть "разбиение по", если часть "разбиение по" отсутствует, используется 'над ()'. В примере ниже используется значение 0,5 для вычисления медианы суммы возможностей в строках. Иногда значения отличаются, потому что percentile_cont вернет среднее значение 2 средних строк для четного набора данных, а percentile_desc вернет первое встреченное значение в сортировке.

select owner,  
accountName,  
CloseDate,  
amount,
percentile_cont(0.5) within group (order by amount) over (partition by owner) pct_cont,
percentile_disc(0.5) within group (order by amount) over (partition by owner) pct_disc
from opportunities  
where stageName='ClosedWon' 
and closeDate >= '2016-10-02' and closeDate <= '2016-10-09'  
order by owner, CloseDate;

С результатами примера: