Оконные преобразования

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

Содержание страницы

Определение оконных полей

// Любая спецификация представления
{
  ...,
  "transform": [
    {
      // Оконное преобразование
      "window": [{
          "op": ...,
          "field": ...,
          "param": ...,
          "as": ...
      }],
      "sort": [
        {"field": ..., "order": ...}
      ],
      "ignorePeers": ...,
      "groupby": [
        "..."
      ],
      "frame": [...,...]
    },
     ...
  ],
  ...
}

Определение преобразования

Свойство Тип Описание
window WindowFieldDef[] Обязательные определения полей и вычислений в окне.
frame [number или null, number или null] Границы скользящего окна: числа задают смещения относительно текущей записи, null — неограниченные предыдущие или последующие записи. По умолчанию: [null, 0], то есть текущая и все предыдущие записи. [-5, 5] включает пять записей с каждой стороны, [null, null] — все записи. Если нужно присоединить одинаковый итог ко всем записям, можно использовать более простое присоединённое агрегирование. Границы влияют только на агрегирование и операции first_value, last_value, nth_value; остальные оконные операции от них не зависят.
ignorePeers boolean Игнорировать ли равные по критериям сортировки записи (peers) при определении границ окна. При false окно расширяется, включая все такие записи; при true определяется только смещениями. Влияет лишь на операции, зависящие от рамки: агрегирование, first_value, last_value, nth_value. По умолчанию: false.
groupby FieldName[] Поля для разделения объектов данных на отдельные окна. Если не заданы, все записи входят в одно окно.
sort SortField[] Определения полей для сортировки объектов внутри окна. Объекты, равные по результату сравнения, считаются peers с одинаковым рангом. Без sort порядок не определён: записи обрабатываются в порядке поступления и ни одна пара не считается равной; ignorePeers игнорируется и действует как true.

frame и ignorePeers влияют на агрегаты и функции first_value, last_value, nth_value; остальные оконные функции от границ рамки не зависят.

Определение оконного поля

Свойство Тип Описание
op AggregateOp или WindowOnlyOp Обязательная оконная операция или агрегирование, например "rank", "lead", "sum", "average" или "count". См. полный список.
param number Параметр оконной функции. Можно не задавать для операций, которые не принимают параметр. См. операции и их параметры.
field FieldName Поле для вычисления агрегирования или оконной функции. Можно не задавать для функций, которые не используют поле, например "count", "rank" и "dense_rank".
as FieldName Обязательное имя выходного поля оконной операции.

Поле сортировки

Свойство Тип Описание
field FieldName Обязательное имя поля для сортировки.
order SortOrder или null Направление сортировки: "ascending" (по умолчанию), "descending" или null — не сортировать.

Для ранговых операций rank, dense_rank, percent_rank, cume_dist и ntile укажите sort явно. Например, записи можно ранжировать по убыванию score:

{
  "transform": [
    {
      "window": [{"op": "rank", "as": "rank"}],
      "sort": [{"field": "score", "order": "descending"}]
    }
  ]
}

Без sort ранг зависит от входного порядка записей, а не от выбранного поля.

Операции, доступные в окне

Помимо всех операций агрегирования, window поддерживает:

Операция Параметр Результат
row_number — Последовательный номер строки начиная с 1.
rank — Ранг начиная с 1. Равные значения получают одинаковый ранг; следующий ранг учитывает число занятых мест. После двух первых мест следующий ранг — 3.
dense_rank — Плотный ранг: равные значения получают одинаковый ранг, а следующий номер не пропускается. После двух первых мест следующий ранг — 2.
percent_rank — Процентный ранг (rank - 1) / (размер_группы - 1).
cume_dist — Накопленная доля распределения от 0 до 1.
ntile Число Номер квантильной группы. Целочисленный параметр задаёт число групп: например, 100 для процентилей или 5 для квинтилей.
lag Число Значение поля из предыдущей записи на заданном расстоянии. По умолчанию расстояние 1; если такой записи нет, возвращается null.
lead Число Значение поля из следующей записи на заданном расстоянии. По умолчанию расстояние 1; если такой записи нет, возвращается null.
first_value — Значение поля из первой записи текущего окна.
last_value — Значение поля из последней записи текущего окна.
nth_value Число Значение поля из записи с заданным неотрицательным целочисленным смещением от начала окна; если её нет, null.

Для lag, lead, first_value, last_value и nth_value нужно задать field.

Примеры

Ниже показаны распространённые способы применения оконного преобразования.

Накопленное распределение частот

Рамка [null, 0] накапливает число записей от начала до текущей позиции:

См. также: гистограмма и накопленная гистограмма в слоях.

Ранжирование

Команды ранжируются по убыванию point, затем по убыванию diff:

Первые K результатов

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

См. также диаграмму первых K результатов с группой «Остальные».

Накопленное среднее

Оконное преобразование показывает, как средний пробег автомобилей на галлон топлива (MPG) менялся по годам:

Доля от общей суммы

window с рамкой [null, null] может добавить общий итог к каждой записи для расчёта доли. Для такой задачи обычно короче преобразование joinaggregate.

Заполнение пропусков оконным вычислением