DataWell

Оформление

Оконные функции ​

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

Примеры считаются на демо-датасете «Продажи»: заказы, товары и покупатели. Разрез — категория товара, если не сказано иное.

В каком порядке считается окно ​

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

Проверено на демо-датасете: RSUM(SUM([orders.amount])) в разрезе категорий даёт 4 003 500 → 86 743 500 → 116 698 500 → 134 683 500, а последнее значение равно общей сумме. Категории здесь идут по алфавиту, и виджет показывает их в том же порядке — поэтому накопление выглядит «сверху вниз по таблице».

Сортировка виджета порядок накопления не меняет

Это главная ловушка оконных функций в текущей версии. Полка Сортировка упорядочивает строки уже после расчёта окна, поэтому нарастающий итог считается не в том порядке, в каком вы видите строки.

Как это выглядит: таблица товаров, отсортированная по убыванию выручки, показывает нарастающий итог вразнобой — 115 768 500 у первой строки, 68 398 500 у второй, 48 913 500 у третьей. Накопление идёт по алфавиту названий товаров, а не по выручке.

Практический вывод: нарастающий итог достоверен только тогда, когда порядок значений измерения совпадает с нужным вам порядком — например, накопление по месяцам, где алфавит дат совпадает с хронологией. Для накопления по убыванию суммы (ABC-анализ, «топ, дающий 80 % выручки») результат в текущей версии неверен.

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

Не все функции доезжают до источника

В режиме прямого запроса формула переводится в SQL вашей базы, и часть функций в её диалекте отсутствует. У таких ниже стоит строка «В прямом запросе…» с перечнем баз, на которых расчёт не соберётся. Oracle, Firebird и Trino в этот перечень не входят: на них в прямом запросе не строится большинство виджетов — поддержка функций источниками.

FIRST ​

FIRST( value )

Первое значение в окне.

Пример.

FIRST(SUM([orders.amount]))

Сумма первой строки окна повторяется во всех строках — удобно как база для сравнения «текущий период к первому».

LAG ​

LAG( value [, offset ] )

Значение со смещением назад в окне.

Пример.

LAG(SUM([orders.amount]), 1)

Сумма предыдущей строки: у «Компьютеров» — 4 003 500, то есть значение «Аксессуаров». У первой строки предшественника нет, и приходит 0, а не пусто.

В прямом запросе не работает на: ClickHouse.

LAST ​

LAST( value )

Последнее значение в окне.

Пример.

LAST(SUM([orders.amount]))

Сумма последней строки окна, одинаковая во всех строках. Пара к FIRST.

LEAD ​

LEAD( value [, offset ] )

Значение со смещением вперёд в окне.

Пример.

LEAD(SUM([orders.amount]), 1)

Сумма следующей строки: у «Аксессуаров» — 82 740 000, то есть значение «Компьютеров».

В прямом запросе не работает на: ClickHouse.

MAVG ​

MAVG( number, rows )

Скользящее среднее по окну строк.

Пример.

MAVG(SUM([orders.amount]), 2)

Среднее по текущей и предыдущей строке: 43 371 750 у «Компьютеров» — половина суммы 4 003 500 и 82 740 000. Так сглаживают колебания в динамике.

В прямом запросе не работает на: Vertica, MS SQL Server.

MCOUNT ​

MCOUNT( value, rows )

Скользящее количество по окну строк.

Пример.

MCOUNT(SUM([orders.amount]), 2)

Сколько строк попало в окно: 1 у первой строки, 2 у остальных — у первой предшественника нет.

MMAX ​

MMAX( value, rows )

Скользящий максимум по окну строк.

Пример.

MMAX(SUM([orders.amount]), 2)

Наибольшее из текущей и предыдущей строки: 82 740 000 у «Компьютеров».

MMIN ​

MMIN( value, rows )

Скользящий минимум по окну строк.

Пример.

MMIN(SUM([orders.amount]), 2)

Наименьшее из двух последних строк: 4 003 500 у «Компьютеров».

MSUM ​

MSUM( number, rows )

Скользящая сумма по окну строк.

Пример.

MSUM(SUM([orders.amount]), 2)

Сумма двух последних строк: 86 743 500 у «Компьютеров».

RANK ​

RANK( value [, direction ] )

Ранг значения; одинаковым значениям — общий ранг, следующий идёт с пропуском.

Пример.

RANK(SUM([orders.amount]))

Место категории по выручке: «Компьютеры» — 1, «Аксессуары» — 4. По умолчанию ранг считается по убыванию: наибольшему значению достаётся первое место.

RANK_DENSE ​

RANK_DENSE( value [, direction ] )

Плотный ранг значения — без пропусков после совпадений.

Пример.

RANK_DENSE(SUM([orders.amount]))

На демо-витрине совпадений нет, и результат совпадает с RANK. Разница проявится там, где два значения равны: RANK даст 1, 1, 3, а RANK_DENSE — 1, 1, 2.

RANK_PERCENTILE ​

RANK_PERCENTILE( value [, direction ] )

Ранг значения в долях от единицы.

Пример.

RANK_PERCENTILE(SUM([orders.amount]))

0 у лидера, 1 у последней строки: значение показывает, какая доля строк оказалась выше. Так отбирают верхний квартиль — условием < 0.25.

В прямом запросе не работает на: Vertica, MS SQL Server.

RANK_UNIQUE ​

RANK_UNIQUE( value [, direction ] )

Ранг с обязательным различением совпавших значений.

Пример.

RANK_UNIQUE(SUM([orders.amount]))

1, 2, 3, 4 — совпавшим значениям всё равно достаются разные места. Нужен там, где ранг используется как ключ строки.

RAVG ​

RAVG( number )

Нарастающее среднее.

Пример.

RAVG(SUM([orders.amount]))

Среднее по всем строкам от начала до текущей: 43 371 750 у второй строки.

В прямом запросе не работает на: Vertica, MS SQL Server.

RCOUNT ​

RCOUNT( value )

Нарастающее количество.

Пример.

RCOUNT(SUM([orders.amount]))

Номер строки в накоплении: 1, 2, 3, 4.

RMAX ​

RMAX( value )

Нарастающий максимум.

Пример.

RMAX(SUM([orders.amount]))

Наибольшее значение от начала до текущей строки: 82 740 000 начиная со второй.

RMIN ​

RMIN( value )

Нарастающий минимум.

Пример.

RMIN(SUM([orders.amount]))

Наименьшее значение от начала до текущей строки: 4 003 500 во всех строках после первой.

ROW_NUMBER ​

ROW_NUMBER()

Номер строки в окне.

Пример.

ROW_NUMBER()

Порядковый номер строки: 1, 2, 3, 4. В отличие от RANK, не смотрит на значения — нумерует строки в том порядке, в каком они пришли.

RSUM ​

RSUM( number )

Нарастающая сумма.

Пример.

RSUM(SUM([orders.amount]))

Нарастающий итог сверху вниз: 4 003 500 → 86 743 500 → 116 698 500. Порядок накопления задаёт измерение, а не полка сортировки — об этом сказано в начале страницы.

Что дальше ​