WINDOW¶

Операция WINDOW может использоваться для агрегирования по соседним документам, или предшествующим и/или последующим строкам, другими словами. Она также может агрегировать на основе значения или диапазона длительности относительно атрибута документа.

Операция выполняет COLLECT AGGREGATE-подобную операцию над набором строк запроса. Однако если операция COLLECT группирует несколько строк запроса в одну группу результатов, то операция WINDOW выдает результат для каждой строки запроса:

• Строка, для которой происходит оценка функции, называется текущей строкой.
• Строки запроса, связанные с текущей строкой, над которой происходит оценка функции, составляют рамку окна для текущей строки.

Рамки окон определяются относительно текущего ряда:

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

Синтаксис¶

Существует два варианта синтаксиса для операций WINDOW.

На основе строк (смежные документы):

WINDOW { preceding: numPrecedingRows, following: numFollowingRows } AGGREGATE variableName = aggregateExpression

Range-based (диапазон значений или продолжительности):

WINDOW rangeValue WITH { preceding: offsetPreceding, following: offsetFollowing } AGGREGATE variableName = aggregateExpression

В агрегатных выражениях поддерживаются вызовы следующих функций:

• LENGTH() / COUNT()
• MIN()
• MAX()
• SUM()
• AVERAGE() / AVG()
• STDDEV_POPULATION() / STDDEV()
• STDDEV_SAMPLE()
• VARIANCE_POPULATION() / VARIANCE()
• VARIANCE_SAMPLE()
• UNIQUE()
• SORTED_UNIQUE()
• COUNT_DISTINCT() / COUNT_UNIQUE()
• BIT_AND()
• BIT_OR()
• BIT_XOR()

Агрегирование по строкам¶

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

@startDocuBlockInline windowAggregationRow
@EXAMPLE_AQL{windowAggregationRow}
@DATASET{observationsSampleDataset}
FOR t IN observations
    SORT t.time
    WINDOW { preceding: 1, following: 1 }
    AGGREGATE rollingAverage = AVG(t.val), rollingSum = SUM(t.val)
    WINDOW { preceding: "unbounded", following: 0}
    AGGREGATE cumulativeSum = SUM(t.val)
    RETURN {
        time: t.time,
        subject: t.subject,
        val: t.val,
        rollingAverage, // average of the window's values
        rollingSum,     // sum of the window's values
        cumulativeSum   // running total
    }
@END_EXAMPLE_AQL
@endDocuBlock windowAggregationRow

Порядок строк контролируется операцией SORT над атрибутом time.

Первая операция WINDOW объединяет предыдущий, текущий и следующий ряд (предшествующий и последующий имеют значение 1) и вычисляет среднее и сумму этих трех значений. В случае первого ряда нет предыдущего ряда, но есть следующий ряд, поэтому значения 10 и 0 складываются для вычисления суммы, которая делится на 2 для вычисления среднего значения. Для второго ряда значения 10, 0 и 9 суммируются и делятся на 3, и так далее.

Вторая операция WINDOW объединяет все предыдущие значения (не ограниченные) для вычисления текущей суммы. Для первого ряда это просто 10, для второго - 10 + 0, для третьего 10 + 0 + 9, и так далее.

Приведенный ниже запрос демонстрирует использование оконных рамок для вычисления текущих итогов в каждой subject группе упорядоченных по времени строк запроса, а также скользящих сумм и средних, вычисленных по текущей строке и строкам, непосредственно предшествующим и следующим за ней, также по subject группе и отсортированных по времени:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

@startDocuBlockInline windowAggregationRowGrouped
@EXAMPLE_AQL{windowAggregationRowGrouped}
@DATASET{observationsSampleDataset}
FOR t IN observations
    COLLECT subject = t.subject INTO group = t
    LET subquery = (FOR t2 IN group
    SORT t2.time
    WINDOW { preceding: 1, following: 1 }
    AGGREGATE rollingAverage = AVG(t2.val), rollingSum = SUM(t2.val)
    WINDOW { preceding: "unbounded", following: 0 }
    AGGREGATE cumulativeSum = SUM(t2.val)
    RETURN {
        time: t2.time,
        subject: t2.subject,
        val: t2.val,
        rollingAverage,
        rollingSum,
        cumulativeSum
    }
    )
    // flatten subquery result
    FOR t2 IN subquery
        RETURN t2
@END_EXAMPLE_AQL
@endDocuBlock windowAggregationRowGrouped

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

Агрегация на основе диапазона¶

Вторая синтаксическая форма WINDOW позволяет агрегировать по всем документам в пределах диапазона значений. Смещение - это разница в значениях атрибутов по сравнению с текущим документом.

Значения атрибутов должны быть числовыми. Вычисления смещения выполняются путем сложения или вычитания числовых смещений, указанных в атрибутах following и preceding. Числа смещения должны быть положительными и должны быть определены во время компиляции запроса. По умолчанию смещение равно 0.

Синтаксис окна на основе диапазона требует, чтобы входные строки были отсортированы по значению строки. Для обеспечения корректности результата оптимизатор AQL автоматически вставит в запрос оператор SORT перед оператором WINDOW. В дальнейшем оптимизатор может отказаться от этого оператора SORT, если для групповых критериев имеется индекс сортировки.

Следующий запрос демонстрирует использование оконных фреймов для вычисления итогов, а также средних значений, вычисленных на основе текущего документа и документов, имеющих значения атрибутов в t.val в диапазоне [-10, +5] (включительно), предшествующих и последующих:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

@startDocuBlockInline windowAggregationRangeValue
@EXAMPLE_AQL{windowAggregationRangeValue}
@DATASET{observationsSampleDataset}
FOR t IN observations
    WINDOW t.val WITH { preceding: 10, following: 5 }
    AGGREGATE rollingAverage = AVG(t.val), rollingSum = SUM(t.val)
    RETURN {
        time: t.time,
        subject: t.subject,
        val: t.val,
        rollingAverage,
        rollingSum
    }
@END_EXAMPLE_AQL
@endDocuBlock windowAggregationRangeValue

Диапазон значений первой строки - [-10, 5], так как val - 0, поэтому значения из первой и второй строки складываются до 5, а среднее значение равно 2.5. Диапазон значений последней строки - [20, 35], так как val - 30, что означает, что последние четыре строки суммируются до суммы 100 и среднего значения 25 (диапазон включительно, т.е. val попадает в диапазон со значением 20).

Агрегирование по временным интервалам¶

Агрегирование по временным интервалам - это подтип агрегирования на основе диапазона, который использует вторую синтаксическую форму WINDOW, но с длительностями ISO.

Для поддержки фреймов WINDOW над данными временных рядов операция WINDOW может вычислять смещения временных меток, используя положительные строки длительности ISO 8601, например P1Y6M (1 год и 6 месяцев) или PT12H30M (12 часов и 30 минут). Также смотрите Функции даты. В отличие от стандарта ISO 8601, компоненты недели могут свободно комбинироваться с другими компонентами. Например, P1WT1H и P1M1W являются допустимыми. Дробные значения поддерживаются только для секунд, и только с точностью до трех десятичных знаков после разделителя, т.е. с точностью до миллисекунды. Например, PT0.123S является допустимой продолжительностью, а PT0.5H и PT0.1234S - нет.

Длительности могут быть указаны отдельно в following и preceding. Если используется такая длительность, то значение атрибута текущего документа должно быть числом и рассматривается как числовой timestamp в миллисекундах. Диапазон включительно. Если ни одна из границ не указана, она рассматривается как пустая длительность (т.е. P0D).

Следующий запрос демонстрирует использование оконных фреймов для вычисления скользящих сумм и средних по наблюдениям за последние 30 минут (включительно) на основе атрибута документа time, который преобразуется из строки времени даты в числовую метку времени:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

@startDocuBlockInline windowAggregationRangeDuration
@EXAMPLE_AQL{windowAggregationRangeDuration}
@DATASET{observationsSampleDataset}
FOR t IN observations
    WINDOW DATE_TIMESTAMP(t.time) WITH { preceding: "PT30M" }
    AGGREGATE rollingAverage = AVG(t.val), rollingSum = SUM(t.val)
    RETURN {
        time: t.time,
        subject: t.subject,
        val: t.val,
        rollingAverage,
        rollingSum
    }
@END_EXAMPLE_AQL
@endDocuBlock windowAggregationRangeDuration

При времени 07:30:00 все, что происходит с 07:00:00 до 07:30:00 в тот же день, попадает в диапазон продолжительности с предшествующим: "PT30M", таким образом, агрегируя шесть верхних строк с суммой 59 и средним значением 9.8333....

Комментарии