[Документация Yandex Cloud](../../index.md) > [Yandex DataLens](../index.md) > Вычисляемые поля > Примеры использования функций > Оконные функции

# Оконные функции в Yandex DataLens

[Оконные функции](../function-ref/window-functions.md) являются аналогом агрегатных функций. Они позволяют получить дополнительную информацию об исходной выборке, например, вычислить нарастающий итог, скользящее среднее или ранжировать значения.

Отличие оконных функций от агрегатных состоит в том, что при вычислении оконных функций строки не объединяются в одну, а продолжают существовать отдельно. Результат вычисления функции при этом отображается в каждой строке. Таким образом, исходное количество строк в результате вычисления оконной функции не изменяется. Подробнее о том, как работают агрегация и группировка данных в DataLens, читайте в разделе [Агрегирование данных в DataLens](aggregation-tutorial.md#datalens-aggregation).


В качестве исходных данных для примеров будет использоваться файл [Selling.csv](https://storage.yandexcloud.net/doc-files/Selling.csv) с информацией о продажах в городах.

## Применение оконных функций {#usage-window-function}

В DataLens аргументами оконных функций могут быть только [показатели](../dataset/data-model.md#field). Группы значений, для которых вычисляется функция, задаются в виде списка [измерений](../dataset/data-model.md#field) и называются окнами. Для [группировки](#grouping) могут применяться только измерения, участвующие в построении чарта. К ним относятся все измерения, которые находятся в одной из секций чарта.

В качестве исходных данных рассмотрим таблицу `Selling`, содержащую данные о продажах в городах:

| # | City | Category | Date | Sales | Profit | Day's discount |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | Detroit | Office Supplies | 2014-01-02 |  10 | 7 | 0,05 |
| 2 | Portland | Office Supplies | 2014-04-05 | 14 | 10 | 0,00 |
| 3 | Portland | Office Supplies | 2014-01-21 | 20 | 12 | 0,20 |
| 4 | San Francisco | Office Supplies | 2014-03-11 | 8 | 3 | 0,10 |
| 5 | Detroit | Furniture | 2014-01-01 | 12 | 3 | 0,00 |
| 6 | Portland | Furniture | 2014-01-21 | 7 | 2 | 0,05 |
| 7 | San Francisco | Technology | 2014-01-02 | 7 | 3 | 0,10 |
| 8 | San Francisco | Technology | 2014-01-17 | 13 | 5 | 0,20 |

**Пример 1**

В чарте на основе таблицы `Selling` с группировкой по измерениям `City` и `Category` необходимо посчитать общую сумму продаж (`TotalSales`), а также долю каждой категории в городе от общей суммы (`% Total`). Для этого нужно создать два показателя с помощью оконной функции [SUM](../function-ref/SUM_WINDOW.md):

* TotalSales — `SUM(SUM([Sales]) TOTAL)`
* % Total — `SUM([Sales]) / [TotalSales]`

На примере чарта **Таблица** результат будет выглядеть так:

![image](../../_assets/datalens/concepts/tutorial/window-func-1.png)

**Пример 2**

Необходимо упорядочить строки в таблице `Selling` в зависимости от величины суммы продаж. Для этого можно использовать оконную функцию [RANK](../function-ref/RANK.md) — `RANK(SUM([Sales]))`. В результате каждой строке будет присвоен свой порядковый номер: строке с наибольшей суммой продаж — номер 1, строке с наименьшей суммой — номер 6.

![image](../../_assets/datalens/concepts/tutorial/window-func-2.png)

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

**Пример 3**

Необходимо упорядочить строки в таблице `Selling` в зависимости от величины средней суммы продаж за все даты в городе. Среднюю сумму продаж по городу можно вычислить с помощью функции [AVG](../function-ref/AVG_WINDOW.md) — `AVG(SUM([Sales]) WITHIN [City])`. Названия городов в таблице повторяются, поэтому для ранжирования лучше использовать функцию [RANK_DENSE](../function-ref/RANK_DENSE.md) — она не пропускает порядковые номера для строк с одинаковым значением. В результате получается следующая формула — `RANK_DENSE(AVG(SUM([Sales]) WITHIN [City]) TOTAL)`.

![image](../../_assets/datalens/concepts/tutorial/window-func-11.png)

**Пример 4**

Рассмотрим более сложный пример использования оконных функций. В качестве источника построим датасет на основе [подключения](../tutorials/data-from-ch-to-sql-chart.md#create-connection) к демонстрационной БД (таблица `SampleLite`). Построим график статистики продаж по подкатегориям товаров. На графике отобразим только те подкатегории, которые хотя бы один раз входили в топ-3 по продажам за день.

{% cut "Подробнее" %}

1. Упорядочим подкатегории товаров в пределах каждой даты по убыванию суммы продаж. Для этого создадим показатель с помощью оконной функции [RANK](../function-ref/RANK.md):

   * Sales Rank — `RANK(SUM([Sales]) WITHIN [Date])`

   В результате в пределах каждой даты подкатегории с максимальной суммой продаж будет присвоено значение `1`, следующей по сумме категории — значение `2` и т.д. Для удобства разместим данные в чарте типа **Таблица**:

   ![image](../../_assets/datalens/concepts/tutorial/window-func-12.png)

1. Отметим подкатегории, которые попадают в топ-3 по продажам в рамках одной даты. Для этого создадим показатель:

   * Top-3 — `IF([Sales Rank] <= 3, 1, 0)`

   Для подкатегорий товаров, которые попадают в топ-3 по продажам в рамках даты, показатель `[Top-3]` будет равен `1`, а для всех остальных категорий в рамках той же даты — `0`.

   ![image](../../_assets/datalens/concepts/tutorial/window-func-13.png)

1. С помощью показателя `[Top-3]` мы отметили категории в рамках одной даты. Теперь нужно отметить эти подкатегории в остальных датах. Для этого создадим показатель с помощью оконной функции [MAX](../function-ref/MAX_WINDOW.md):

   * [Show Category] — `MAX([Top-3] WITHIN [Sub-Category])`

   Для каждой подкатегории товаров показатель `[Show Category]` будет равен `1` не только в рамках той даты, когда она была в топ-3 по продажам, но и для всех остальных дат. Если подкатегория ни в один из дней не входила в топ-3 по продажам, показатель `[Show Category]` для нее будет равен `0`.

   ![image](../../_assets/datalens/concepts/tutorial/window-func-14.png)

1. Добавим фильтр в чарт: `[Show Category] = 1`. Таким образом мы получили список подкатегорий товаров, которые нужно отобразить на графике.

1. Теперь поменяем тип чарта на **Линейная диаграмма**. Настроим визуализацию:

   * в секцию **X** перетащим измерение `Date`;
   * в секцию **Y** перетащим показатель `Sales`;
   * в секцию **Цвета** перетащим измерение `Sub-Category`;
   * в секции **Фильтры** оставим фильтр по значению `1` показателя `Show Category`;
   * в настройках оси **Y** для опции **Пустые значения (null)** установим значение **Отображать как 0**.

     ![image](../../_assets/datalens/concepts/tutorial/window-func-15.png)

{% endcut %}


## Группировка в оконных функциях {#grouping}

Так же, как и агрегатные функции, оконные функции могут быть вычислены:

* для [одного окна](#one-window-grouping);
* для [нескольких окон](#some-window-grouping).

Подробнее о группировке в оконных функциях читайте в разделе [Группировка](../function-ref/window-functions.md#syntax-grouping).

### Группировка для одного окна {#one-window-grouping}

При таком варианте группировки функция вычисляется для единственного окна, в которое попадают все строки. Для этого используется тип группировки `TOTAL`. Этот тип группировки подходит для подсчета итоговых сумм, ранжирования строк и прочих операций, для которых нужна информация о всех исходных данных.

**Пример**

Необходимо вычислить среднюю сумму продаж (`AvgSales`) и отклонения от нее для каждой категории в городе (`DeltaFromAvg`). Для этого подойдет функция [AVG](../function-ref/AVG_WINDOW.md):

* AvgSales — `AVG(SUM([Sales]) TOTAL)`
* DeltaFromAvg — `SUM([Sales]) - [AvgSales]`

![image](../../_assets/datalens/concepts/tutorial/window-func-3.png)

### Группировка для нескольких окон {#some-window-grouping}

Иногда оконную функцию нужно вычислить не среди всех записей, а раздельно по группам. В этих случаях используются типы группировки `WITHIN` и `AMONG`.

#### WITHIN {#within}

`WITHIN` — это аналог `GROUP BY` в `SQL`. В нем перечисляются все измерения, по которым будет производиться разбиение на окна. Также в `WITHIN` можно использовать показатели. В этом случае их значения таким же образом будут участвовать в группировке окна.

{% note warning %}

В `WITHIN` игнорируются [измерения](aggregation-tutorial.md#dimensions-and-measures), которые не участвуют в группировке чарта. Например, в чарте с группировкой по измерениям `City` и `Category` для показателя `SUM(SUM([Sales]) WITHIN [Date])` измерение `Date` будет проигнорировано, и он станет равносилен показателю `SUM(SUM([Sales]) TOTAL)`.

{% endnote %}

**Пример**

Вычисление доли каждой категории (`% Total`) от общей суммы продаж по городу (`TotalSales`):

* TotalSales — `SUM(SUM([Sales]) WITHIN [City])`
* % Total — `SUM([Sales]) / [TotalSales]`

Результат на примере чарта **Столбчатая диаграмма**:

![image](../../_assets/datalens/concepts/tutorial/window-func-4.png)

#### AMONG {#among}

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

Так, например, для чарта с группировкой по измерениям `City` и `Category` равносильны показатели:

* `SUM(SUM([Sales]) AMONG [Category])` и `SUM(SUM([Sales]) WITHIN [City])`
* `SUM(SUM([Sales]) AMONG [City], [Category])`и `SUM(SUM([Sales]) TOTAL)`

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

{% note warning %}

Измерения, перечисленные в `AMONG`, должны быть добавлены в секции чарта. В другом случае чарт вернет ошибку.

{% endnote %}

## Сортировка {#order-by}

Некоторые оконные функции поддерживают [сортировку](../function-ref/window-functions.md#syntax-order-by), направление которой влияет на расчет значения. Указать сортировку для оконной функции можно следующими способами:

* указать измерения или показатели в секции `ORDER BY`;
* в чарте перенести измерения или показатели в секцию **Сортировка**.

Измерения и показатели для сортировки берутся сначала из секции `ORDER BY` в формуле, а затем из секции чарта **Сортировка**.

**Пример**

Необходимо рассчитать изменение общей суммы продаж (`IncTotal`) за весь период, начиная от самой ранней даты и заканчивая самой поздней. Для этого можно воспользоваться функцией [RSUM](../function-ref/RSUM.md) с сортировкой по измерению `Date` — `RSUM(SUM([Sales]) TOTAL ORDER BY [Date])`.

Результат на примере чарта **Линейная диаграмма**:

![image](../../_assets/datalens/concepts/tutorial/window-func-5.png)

Аналогичный результат можно получить, если задать показатель `IncTotal` формулой `RSUM(SUM([Sales]) TOTAL)` и добавить измерение `Date` в секцию **Сортировка**.

## Фильтрация {#before-filter-by}

Вычисление значений функций в чартах выполняется после применения [фильтров](chart/settings.md#filter) по измерениям и показателям, добавленным в секцию **Фильтры**. У оконных функций можно переопределить этот порядок. Для это нужно указать необходимые измерения или показатели в секции `BEFORE FILTER BY` формулы. В таком случае значение функции будет вычислено до применения фильтрации.

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

**Пример**

Необходимо рассчитать изменение общей суммы продаж (`IncTotal`) за период с `17.01.2014` по `11.03.2014`. Если добавить в чарт фильтр по измерению `Date` и создать показатель `RSUM(SUM([Sales]) TOTAL ORDER BY [Date])`, то функция будет рассчитана только для тех данных, которые ограничены фильтром:

![image](../../_assets/datalens/concepts/tutorial/window-func-6.png)

Чтобы вычислить функцию для всего объема данных, но отобразить результат только в определенном периоде, необходимо добавить измерение `Date` в секцию `BEFORE FILTER BY` — `RSUM(SUM([Sales]) TOTAL ORDER BY [Date] BEFORE FILTER BY [Date])`.

![image](../../_assets/datalens/concepts/tutorial/window-func-7.png)

## Создание показателя для оконной функции {#create-measure}

В качестве первого аргумента (`value` в описании синтаксиса) оконной функции нельзя напрямую использовать [измерение](../dataset/data-model.md#field). Сначала нужно применить к нему [функцию агрегации](../function-ref/aggregation-functions.md), в результате чего измерение станет [показателем](../dataset/data-model.md#field), который можно использовать в оконной функции.

Например, в чарте с группировкой по измерениям `Year` и `Category` вы хотите ранжировать записи продаж по прибыли за весь период. Нельзя для этого использовать формулу `RANK([Profit])`, где `Profit` — измерение. Сначала надо применить функцию агрегации, чтобы перевести измерение `Profit` в показатель. Наиболее подходящей в данном случае будет агрегатная функция [SUM](../function-ref/SUM.md), которая вернет сумму прибыли: `SUM([Profit])`. Теперь к полученному показателю можно применить оконную функцию ранжирования [RANK](../function-ref/RANK.md). Итоговая корректная формула: `RANK(SUM([Profit]))`.

Показатели можно добавлять как на уровне датасета, так и на уровне чарта. Подробнее в [Способы создания показателей](aggregation-tutorial.md#create-measure).

Чтобы понять, какую именно агрегатную функцию выбрать для перевода измерения в показатель, следует уточнить, какой итоговый показатель вы хотите получить с помощью оконной функции. Например, в чарте с группировкой по категориям товаров (измерение `Category`) требуется упорядочить записи по продажам (измерение `Sales`). Если вы хотите упорядочить записи по сумме продаж, то следует выбрать агрегатную функцию [SUM](../function-ref/SUM.md): `SUM([Sales])`, если по количеству продаж — [COUNT](../function-ref/COUNT.md): `COUNT([Sales])`.

Если требуется получить некоторый строковый показатель, значение которого будет определено путем группировки и сортировки в оконной функции, можно применить агрегатную функцию [ANY](../function-ref/ANY.md).


Рассмотрим создание показателя для оконной функции на примерах. В качестве источника построим датасет на основе [подключения](../tutorials/data-from-ch-to-sql-chart.md#create-connection) к демонстрационной БД (таблица `MS_SalesFullTable`).

**Пример 1**

Необходимо показать количество продаж за день для каждой категории товаров и общее количество продаж за день.

1. Выберите тип чарта **Таблица**.
1. Добавьте в чарт поле `OrderDate` с формулой `DATE([OrderDatetime])`.
1. В секцию **Столбцы** поместите измерения `OrderDate` и `ProductCategory`.
1. Чтобы упорядочить записи по дате продажи, поместите измерение `OrderDate` в секцию **Сортировка**.
1. Для подсчета количества продаж за день для каждой категории товаров добавьте в чарт показатель `cnt_order_date_category`. Используйте агрегатную функцию [COUNT](../function-ref/COUNT.md). Она будет использовать группировку по измерениям, размещенным в секции **Столбцы**. Итоговая формула: `COUNT([OrderID])`.
1. Поместите показатель `cnt_order_date_category` в секцию **Столбцы**.
1. Для подсчета общего количества продаж за день добавьте в чарт показатель `cnt_order_date`. Используйте оконную функцию [SUM](../function-ref/SUM_WINDOW.md), применив группировку по измерению `OrderDate`. Чтобы перевести измерение `OrderID` в показатель, используйте агрегатную функцию [COUNT](../function-ref/COUNT.md): `COUNT([OrderID])`. Итоговая формула: `SUM(COUNT([OrderID]) WITHIN [OrderDate])`.
1. Поместите показатель `cnt_order_date_category` в секцию **Столбцы**.

   ![image](../../_assets/datalens/concepts/tutorial/window-func-measure-cnt.png)

**Пример 2**

Необходимо показать среднюю стоимость продаж по магазину и по категориям товаров в магазине:

1. Выберите тип чарта **Таблица**. В секцию **Столбцы** поместите измерения `ShopName` и `ProductCategory`.
1. Для подсчета средней стоимости продаж по категориям товаров в магазине добавьте в чарт показатель `avg_category_sale`. Используйте агрегатную функцию [AVG](../function-ref/AVG.md). Она будет использовать группировку по измерениям, размещенным в секции **Столбцы**. Итоговая формула: `AVG([Sales])`.
1. Поместите показатель `avg_category_sale` в секцию **Столбцы**.
1. Для подсчета средней стоимости продаж по магазину добавьте в чарт показатель `avg_shop_sale`. Используйте оконную функцию [AVG](../function-ref/AVG_WINDOW.md), применив группировку по измерению `ShopName`. Чтобы перевести измерение `Sales` в показатель, используйте агрегатную функцию [AVG](../function-ref/AVG.md): `AVG([Sales])`. Итоговая формула: `AVG(AVG([Sales]) WITHIN [ShopName])`.
1. Поместите показатель `avg_shop_sale` в секцию **Столбцы**.

   ![image](../../_assets/datalens/concepts/tutorial/window-func-measure-avg.png)

**Пример 3**

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

1. Чтобы иметь возможность группировки данных по дате продажи (без учета времени), добавьте в чарт поле `Date` с формулой `DATE_PARSE(STR([OrderDatetime]))`.
1. Чтобы иметь возможность сортировки данных по времени продажи, добавьте в чарт поле `Time` с формулой `RIGHT(STR([OrderDatetime]),8)`.
1. Выберите тип чарта **Сводная таблица**. В секцию **Строки** поместите измерения `ShopName`, `OrderDatetime` и `OrderID`.
1. Добавьте в чарт показатель `last_shop_order`. Используйте оконную функцию [LAST](../function-ref/LAST.md), применив группировку по измерению `ShopName` и сортировку по `Time`. Для перевода строкового измерения в показатель используйте агрегатную функцию [ANY](../function-ref/ANY.md) с группировкой `INCLUDE` (для выдачи уникальных значений): `ANY([OrderID] INCLUDE [OrderID])`. Итоговая формула: `LAST(ANY([OrderID] INCLUDE [OrderID]) WITHIN [ShopName], [Date] ORDER BY [Time])`.
1. Поместите показатель `last_shop_order` в секцию **Показатели**.

   ![image](../../_assets/datalens/concepts/tutorial/window-func-measure-last.png)


## Вопросы и ответы {#qa}

{% cut "Как упорядочить значения при расчете нарастающего итога или скользящего среднего?" %}

   Для правильной работы функций, зависящих от порядка записей в окне (например, [RSUM](../function-ref/RSUM.md), [MAVG](../function-ref/MAVG.md), [LAG](../function-ref/LAG.md), [LAST](../function-ref/LAST.md), [FIRST](../function-ref/FIRST.md)), обязательно указывать сортировку. Это можно сделать следующими способами:

   * перетащить в секцию **Сортировка** измерение или показатель, по которым будет отсортирован весь чарт;
   * задать сортировку для конкретной функции с помощью `ORDER BY`.

{% endcut %}

{% cut "Как правильно посчитать нарастающий итог после добавления поля в секцию Цвета?" %}

В качестве примера рассмотрим линейную диаграмму, на которой построен график изменения общей суммы продаж по датам (подробнее в таблице [Selling](#usage-window-function)). Нарастающий итог (`IncTotal`) вычислен с использованием оконной функции [RSUM](../function-ref/RSUM.md) — `RSUM(SUM([Sales]))`.

![image](../../_assets/datalens/concepts/tutorial/window-func-8.png)

Чтобы отобразить изменение суммы продаж по каждой категории товаров, нужно добавить измерение `Category` в секцию **Цвета**.

![image](../../_assets/datalens/concepts/tutorial/window-func-9.png)

После этого на диаграмме отобразится отдельный график для каждой категории, но итоговые суммы на них посчитаны неверно: `Furniture` — 49 вместо 19, `Office Supplies` — 91 вместо 52, `Technology` — 42 вместо 20. Это связано с тем, что измерение в секции **Цвета** (`Category`) участвует в группировке так же, как и измерение в секции **X** (`Date`). Чтобы сумма посчиталась корректно, нужно добавить измерение `Category` в секцию `WITHIN` или измерение `Date` в секцию `AMONG`: `RSUM(SUM([Sales]) WITHIN [Category])` или `RSUM(SUM([Sales]) AMONG [Date])`.

![image](../../_assets/datalens/concepts/tutorial/window-func-10.png)

{% endcut %}

{% cut "Как правильно вычислить оконную функцию, если в чарте указать группировку для даты?" %}

При добавлении группировки (округления) для даты в чарте исходное поле подменяется на другое, автоматически сгенерированное. Например, при округлении до месяца вместо измерения `[Date]` подставляется новое поле с формулой `DATETRUNC([Date], "month")`. Так как исходное поле `[Date]`  исчезает из перечня измерений чарта, то и оконная функция, в которой оно используется, перестает работать. Для корректной работы функции необходимо в формуле округлить исходное измерение `[Date]` с помощью функции [DATETRUNC](../function-ref/DATETRUNC.md).

{% endcut %}