Версия: 2.0.x

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

Введение

Оконная функция — это особый класс встроенных функций. Подобно агрегатным функциям, она также выполняет вычисления над несколькими входными строками для получения одного значения данных. Разница в том, что оконная функция обрабатывает входные данные в пределах определённого окна, а не использует метод "group by". Данные в каждом окне могут быть отсортированы и сгруппированы с использованием предложения over(). Оконная функция вычисляет отдельное значение для каждой строки, а не одно значение для каждой группы. Эта гибкость позволяет пользователям добавлять дополнительные столбцы в предложение select и дополнительно фильтровать результирующий набор. Оконная функция может появляться только в списке select и во внешней позиции предложения. Она применяется в конце запроса, то есть после выполнения операций join, where и group by. Оконная функция часто используется для анализа трендов, вычисления выбросов и выполнения анализа распределения по корзинам на больших данных.

Использование

Синтаксис

function(args) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])
partition_by_clause ::= PARTITION BY expr [, expr ...]
order_by_clause ::= ORDER BY expr [ASC | DESC] [, expr [ASC | DESC] ...]

Предложение PARTITION BY

Предложение Partition By аналогично Group By. Оно группирует входные строки по одному или нескольким указанным столбцам. Строки с одинаковым значением группируются вместе.

Предложение ORDER BY

Предложение Order By в основном такое же, как внешнее Order By. Оно определяет порядок входных строк. Если указано Partition By, Order By определяет порядок внутри каждой группы Partition. Единственное отличие в том, что Order By n (n — положительное целое число) в предложении OVER эквивалентно отсутствию операции, тогда как n во внешнем Order By указывает на сортировку по n-му столбцу.

Пример:

Этот пример показывает добавление столбца id в список select со значениями 1, 2, 3 и т.д., отсортированными по столбцу date_and_time в таблице events.

SELECT row_number() OVER (ORDER BY date_and_time) AS id,
    c1, c2, c3, c4
FROM events;

Предложение Window

Предложение window используется для указания диапазона строк для операций (предшествующие и следующие строки относительно текущей строки). Оно поддерживает следующие синтаксисы: AVG(), COUNT(), FIRST_VALUE(), LAST_VALUE() и SUM(). Для MAX() и MIN() предложение window может указывать начало до UNBOUNDED PRECEDING.

Синтаксис:

ROWS BETWEEN [ { m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]
RANGE BETWEEN [ { m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]

примечание

Ограничение оконного фрейма ARRAY_AGG(): При использовании ARRAY_AGG() как оконной функции поддерживаются только фреймы RANGE. Фреймы ROWS НЕ поддерживаются. Например:

-- Поддерживается: фрейм RANGE
array_agg(col) OVER (PARTITION BY x ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- НЕ поддерживается: фрейм ROWS (вызовет ошибку)
array_agg(col) OVER (PARTITION BY x ORDER BY y ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

Пример таблицы для оконных функций

В этом разделе создаётся примерная таблица scores. Вы можете использовать эту таблицу для тестирования многих оконных функций ниже.

CREATE TABLE `scores` (
    `id` int(11) NULL,
    `name` varchar(11) NULL,
    `subject` varchar(11) NULL,
    `score` int(11) NULL
  )
DISTRIBUTED BY HASH(`score`) BUCKETS 10;

INSERT INTO `scores` VALUES
  (1, "lily", "math", NULL),
  (1, "lily", "english", 100),
  (1, "lily", "physics", 60),
  (2, "tom", "math", 80),
  (2, "tom", "english", 98),
  (2, "tom", "physics", NULL),
  (3, "jack", "math", 95),
  (3, "jack", "english", NULL),
  (3, "jack", "physics", 99),
  (4, "amy", "math", 80),
  (4, "amy", "english", 92),
  (4, "amy", "physics", 99),
  (5, "mike", "math", 70),
  (5, "mike", "english", 85),
  (5, "mike", "physics", 85),
  (6, "amber", "math", 92),
  (6, "amber", NULL, 90),
  (6, "amber", "physics", 100);

Примеры функций

В этом разделе описаны оконные функции, поддерживаемые в Selena.

AVG()

Вычисляет среднее значение поля в заданном окне. Эта функция игнорирует значения NULL.

Синтаксис:

AVG([DISTINCT] expr) [OVER (*analytic_clause*)]

DISTINCT поддерживается начиная с Selena v2.0.0. При указании AVG() вычисляет среднее только уникальных значений в окне.

примечание

Ограничение оконного фрейма: При использовании AVG(DISTINCT) как оконной функции поддерживаются только фреймы RANGE. Фреймы ROWS НЕ поддерживаются.

Примеры:

В следующем примере используются данные акций.

CREATE TABLE stock_ticker (
    stock_symbol  STRING,
    closing_price DECIMAL(8,2),
    closing_date  DATETIME
)
DUPLICATE KEY(stock_symbol)
COMMENT "OLAP"
DISTRIBUTED BY HASH(closing_date);

INSERT INTO stock_ticker VALUES 
    ("JDR", 12.86, "2014-10-02 00:00:00"), 
    ("JDR", 12.89, "2014-10-03 00:00:00"), 
    ("JDR", 12.94, "2014-10-04 00:00:00"), 
    ("JDR", 12.55, "2014-10-05 00:00:00"), 
    ("JDR", 14.03, "2014-10-06 00:00:00"), 
    ("JDR", 14.75, "2014-10-07 00:00:00"), 
    ("JDR", 13.98, "2014-10-08 00:00:00")
;

Вычисление средней цены закрытия для текущей строки и каждой строки до и после неё.

select stock_symbol, closing_date, closing_price,
    avg(closing_price)
        over (partition by stock_symbol
              order by closing_date
              rows between 1 preceding and 1 following
        ) as moving_average
from stock_ticker;

Вывод:

+--------------+---------------------+---------------+----------------+
| stock_symbol | closing_date        | closing_price | moving_average |
+--------------+---------------------+---------------+----------------+
| JDR          | 2014-10-02 00:00:00 |         12.86 |    12.87500000 |
| JDR          | 2014-10-03 00:00:00 |         12.89 |    12.89666667 |
| JDR          | 2014-10-04 00:00:00 |         12.94 |    12.79333333 |
| JDR          | 2014-10-05 00:00:00 |         12.55 |    13.17333333 |
| JDR          | 2014-10-06 00:00:00 |         14.03 |    13.77666667 |
| JDR          | 2014-10-07 00:00:00 |         14.75 |    14.25333333 |
| JDR          | 2014-10-08 00:00:00 |         13.98 |    14.36500000 |
+--------------+---------------------+---------------+----------------+

Например, 12.87500000 в первой строке — это среднее значение цен закрытия на "2014-10-02" (12.86), предыдущий день "2014-10-01" (null) и следующий день "2014-10-03" (12.89).

Пример 2: Использование AVG(DISTINCT) по всему окну

Вычисление среднего уникальных оценок по всем строкам:

SELECT id, subject, score,
    AVG(DISTINCT score) OVER () AS distinct_avg
FROM test_scores;

Вывод:

+----+---------+-------+-------------+
| id | subject | score | distinct_avg|
+----+---------+-------+-------------+
|  1 | math    |    80 |       85.00 |
|  2 | math    |    85 |       85.00 |
|  3 | math    |    80 |       85.00 |
|  4 | english |    90 |       85.00 |
|  5 | english |    85 |       85.00 |
|  6 | english |    90 |       85.00 |
+----+---------+-------+-------------+

Среднее уникальных значений равно 85.00 ((80 + 85 + 90) / 3).

Пример 3: Использование AVG(DISTINCT) по фреймированному окну с фреймом RANGE

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

SELECT id, subject, score,
    AVG(DISTINCT score) OVER (
        PARTITION BY subject 
        ORDER BY score 
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS distinct_avg
FROM test_scores;

Вывод:

+----+---------+-------+-------------+
| id | subject | score | distinct_avg|
+----+---------+-------+-------------+
|  1 | math    |    80 |       80.00 |
|  3 | math    |    80 |       80.00 |
|  2 | math    |    85 |       82.50 |
|  5 | english |    85 |       85.00 |
|  4 | english |    90 |       87.50 |
|  6 | english |    90 |       87.50 |
+----+---------+-------+-------------+

Для каждой строки функция вычисляет среднее уникальных оценок от начала раздела до текущего значения оценки строки включительно.

ARRAY_AGG()

Агрегирует значения (включая значения NULL) в окне в массив. Вы можете использовать необязательное предложение ORDER BY для сортировки элементов внутри массива.

Эта функция поддерживается начиная с v1.5.2.

подсказка

Важное ограничение: ARRAY_AGG() как оконная функция поддерживает только оконные фреймы RANGE. Оконные фреймы ROWS НЕ поддерживаются. Если оконный фрейм не указан, используется значение по умолчанию RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Синтаксис:

ARRAY_AGG([DISTINCT] expr [ORDER BY expr [ASC | DESC]]) OVER([partition_by_clause] [order_by_clause] [window_clause])

Параметры:

expr: Выражение для агрегации. Может быть столбцом любого поддерживаемого типа данных.
DISTINCT: Необязательно. Удаляет дублирующиеся значения из результирующего массива.
ORDER BY: Необязательно. Указывает порядок элементов внутри массива.

Возвращаемое значение:

Возвращает ARRAY, содержащий все значения в окне.

Примечания по использованию:

Фреймы ROWS НЕ поддерживаются. Только фреймы RANGE могут использоваться с ARRAY_AGG() как оконной функцией. Использование фреймов ROWS приведёт к ошибке.
Значения NULL включаются в результирующий массив.
При указании DISTINCT дублирующиеся значения удаляются из массива.
При указании ORDER BY внутри ARRAY_AGG() элементы в результирующем массиве сортируются соответственно.

Примеры:

Этот пример использует данные из Примера таблицы scores.

Пример 1: Базовый ARRAY_AGG() по окну

Сбор всех оценок в каждом разделе предмета:

SELECT *,
    array_agg(score)
        OVER (
            PARTITION BY subject
            ORDER BY score
        ) AS score_array
FROM scores
WHERE subject = 'math';

Вывод:

+------+-------+---------+-------+----------------------+
| id   | name  | subject | score | score_array          |
+------+-------+---------+-------+----------------------+
|    1 | lily  | math    |  NULL | [null]               |
|    5 | mike  | math    |    70 | [null,70]            |
|    2 | tom   | math    |    80 | [null,70,80,80]      |
|    4 | amy   | math    |    80 | [null,70,80,80]      |
|    6 | amber | math    |    92 | [null,70,80,80,92]   |
|    3 | jack  | math    |    95 | [null,70,80,80,92,95]|
+------+-------+---------+-------+----------------------+

Примечание: Строки с одинаковым значением score (tom и amy оба имеют 80) получают один и тот же массив из-за семантики фрейма RANGE.

Пример 2: ARRAY_AGG(DISTINCT) по окну

Сбор уникальных оценок в каждом разделе предмета:

SELECT *,
    array_agg(DISTINCT score)
        OVER (
            PARTITION BY subject
            ORDER BY score
        ) AS distinct_scores
FROM scores
WHERE subject = 'math';

Вывод:

+------+-------+---------+-------+-------------------+
| id   | name  | subject | score | distinct_scores   |
+------+-------+---------+-------+-------------------+
|    1 | lily  | math    |  NULL | [null]            |
|    5 | mike  | math    |    70 | [null,70]         |
|    2 | tom   | math    |    80 | [null,70,80]      |
|    4 | amy   | math    |    80 | [null,70,80]      |
|    6 | amber | math    |    92 | [null,70,80,92]   |
|    3 | jack  | math    |    95 | [null,70,80,92,95]|
+------+-------+---------+-------+-------------------+

Пример 3: ARRAY_AGG() с ORDER BY

Сбор оценок, отсортированных по убыванию внутри массива:

SELECT *,
    array_agg(score ORDER BY score DESC)
        OVER (
            PARTITION BY subject
        ) AS scores_desc
FROM scores
WHERE subject = 'math';

Вывод:

+------+-------+---------+-------+----------------------+
| id   | name  | subject | score | scores_desc          |
+------+-------+---------+-------+----------------------+
|    1 | lily  | math    |  NULL | [95,92,80,80,70,null]|
|    5 | mike  | math    |    70 | [95,92,80,80,70,null]|
|    2 | tom   | math    |    80 | [95,92,80,80,70,null]|
|    4 | amy   | math    |    80 | [95,92,80,80,70,null]|
|    6 | amber | math    |    92 | [95,92,80,80,70,null]|
|    3 | jack  | math    |    95 | [95,92,80,80,70,null]|
+------+-------+---------+-------+----------------------+

Пример 4: ARRAY_AGG() с фреймом RANGE

Сбор всех оценок во всём разделе с использованием RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING:

SELECT *,
    array_agg(score)
        OVER (
            PARTITION BY subject
            ORDER BY score
            RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS all_scores
FROM scores
WHERE subject = 'math';

Вывод:

+------+-------+---------+-------+----------------------+
| id   | name  | subject | score | all_scores           |
+------+-------+---------+-------+----------------------+
|    1 | lily  | math    |  NULL | [null,70,80,80,92,95]|
|    5 | mike  | math    |    70 | [null,70,80,80,92,95]|
|    2 | tom   | math    |    80 | [null,70,80,80,92,95]|
|    4 | amy   | math    |    80 | [null,70,80,80,92,95]|
|    6 | amber | math    |    92 | [null,70,80,80,92,95]|
|    3 | jack  | math    |    95 | [null,70,80,80,92,95]|
+------+-------+---------+-------+----------------------+

Пример 5: Сбор имён, разделённых по диапазонам оценок

Используя таблицу stock_ticker, сбор символов акций в скользящем окне:

SELECT
    stock_symbol,
    closing_date,
    closing_price,
    array_agg(closing_price)
        OVER (
            PARTITION BY stock_symbol
            ORDER BY closing_date
        ) AS price_history
FROM stock_ticker;

Вывод:

+--------------+---------------------+---------------+---------------------------------------+
| stock_symbol | closing_date        | closing_price | price_history                         |
+--------------+---------------------+---------------+---------------------------------------+
| JDR          | 2014-10-02 00:00:00 |         12.86 | [12.86]                               |
| JDR          | 2014-10-03 00:00:00 |         12.89 | [12.86,12.89]                         |
| JDR          | 2014-10-04 00:00:00 |         12.94 | [12.86,12.89,12.94]                   |
| JDR          | 2014-10-05 00:00:00 |         12.55 | [12.86,12.89,12.94,12.55]             |
| JDR          | 2014-10-06 00:00:00 |         14.03 | [12.86,12.89,12.94,12.55,14.03]       |
| JDR          | 2014-10-07 00:00:00 |         14.75 | [12.86,12.89,12.94,12.55,14.03,14.75] |
| JDR          | 2014-10-08 00:00:00 |         13.98 | [12.86,12.89,12.94,12.55,14.03,14.75,13.98] |
+--------------+---------------------+---------------+---------------------------------------+

Пример 6: Неверное использование - фрейм ROWS (вызовет ошибку)

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

-- Это вызовет ошибку!
SELECT *,
    array_agg(score)
        OVER (
            PARTITION BY subject
            ORDER BY score
            ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING  -- НЕ ПОДДЕРЖИВАЕТСЯ!
        ) AS score_array
FROM scores;

Сообщение об ошибке:

ERROR: array_agg as window function does not support ROWS frame type. Please use RANGE frame instead.

COUNT()

Вычисляет общее количество строк, соответствующих указанным условиям в заданном окне.

Синтаксис:

COUNT([DISTINCT] expr) [OVER (analytic_clause)]

DISTINCT поддерживается начиная с Selena v2.0.0. При указании COUNT() подсчитывает только уникальные значения в окне.

примечание

Ограничение оконного фрейма: При использовании COUNT(DISTINCT) как оконной функции поддерживаются только фреймы RANGE. Фреймы ROWS НЕ поддерживаются. Например:

-- Поддерживается: фрейм RANGE
count(distinct col) OVER (PARTITION BY x ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- НЕ поддерживается: фрейм ROWS (вызовет ошибку)
count(distinct col) OVER (PARTITION BY x ORDER BY y ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

Примеры:

Подсчёт появлений оценок по математике, которые больше 90, от текущей строки до первой строки в разделе математики. Этот пример использует данные из Примера таблицы scores.

select *,
    count(score)
        over (
            partition by subject
            order by score
            rows between unbounded preceding and current row
        ) as 'score_count'
from scores where subject in ('math') and score > 90;

+------+-------+---------+-------+-------------+
| id   | name  | subject | score | score_count |
+------+-------+---------+-------+-------------+
|    6 | amber | math    |    92 |           1 |
|    3 | jack  | math    |    95 |           2 |
+------+-------+---------+-------+-------------+

Пример 2: Использование COUNT(DISTINCT) по всему окну

Подсчёт уникальных оценок по всем строкам:

CREATE TABLE test_scores (
    id INT,
    subject VARCHAR(20),
    score INT
) DISTRIBUTED BY HASH(id);

INSERT INTO test_scores VALUES
    (1, 'math', 80),
    (2, 'math', 85),
    (3, 'math', 80),
    (4, 'english', 90),
    (5, 'english', 85),
    (6, 'english', 90);

SELECT id, subject, score,
    COUNT(DISTINCT score) OVER () AS distinct_count
FROM test_scores;

Вывод:

+----+---------+-------+---------------+
| id | subject | score | distinct_count|
+----+---------+-------+---------------+
|  1 | math    |    80 |             4 |
|  2 | math    |    85 |             4 |
|  3 | math    |    80 |             4 |
|  4 | english |    90 |             4 |
|  5 | english |    85 |             4 |
|  6 | english |    90 |             4 |
+----+---------+-------+---------------+

Количество уникальных значений равно 4 (значения: 80, 85, 90 и NULL, если есть).

Пример 3: Использование COUNT(DISTINCT) по фреймированному окну с фреймом RANGE

Подсчёт уникальных оценок в каждом разделе предмета с использованием фрейма RANGE:

SELECT id, subject, score,
    COUNT(DISTINCT score) OVER (
        PARTITION BY subject 
        ORDER BY score 
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS distinct_count
FROM test_scores;

Вывод:

+----+---------+-------+---------------+
| id | subject | score | distinct_count|
+----+---------+-------+---------------+
|  1 | math    |    80 |             1 |
|  3 | math    |    80 |             1 |
|  2 | math    |    85 |             2 |
|  5 | english |    85 |             1 |
|  4 | english |    90 |             2 |
|  6 | english |    90 |             2 |
+----+---------+-------+---------------+

Для каждой строки функция подсчитывает уникальные оценки от начала раздела до текущего значения оценки строки включительно.

CUME_DIST()

Функция CUME_DIST() вычисляет кумулятивное распределение значения в разделе или окне, указывая его относительную позицию как процент в разделе. Часто используется для вычисления распределения наивысших или наименьших значений в группе.

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

Кумулятивное распределение находится в диапазоне от 0 до 1. Это полезно для вычисления процентилей и анализа распределения данных.

Эта функция поддерживается начиная с v1.5.2.

Синтаксис:

CUME_DIST() OVER (partition_by_clause order_by_clause)

partition_by_clause: необязательно. Если это предложение не указано, весь результирующий набор обрабатывается как один раздел.
order_by_clause: Эта функция должна использоваться с ORDER BY для сортировки строк раздела в желаемом порядке.

CUME_DIST() содержит значения NULL и рассматривает их как наименьшие значения.

Примеры:

Следующий пример показывает кумулятивное распределение каждой оценки в каждой группе subject. Этот пример использует данные из Примера таблицы scores.

SELECT *, 
    cume_dist() 
      OVER (
        PARTITION BY subject
        ORDER BY score
      ) AS cume_dist 
FROM scores;
+------+-------+---------+-------+---------------------+
| id   | name  | subject | score | cume_dist           |
+------+-------+---------+-------+---------------------+
|    6 | amber | NULL    |    90 |                   1 |
|    3 | jack  | english |  NULL |                 0.2 |
|    5 | mike  | english |    85 |                 0.4 |
|    4 | amy   | english |    92 |                 0.6 |
|    2 | tom   | english |    98 |                 0.8 |
|    1 | lily  | english |   100 |                   1 |
|    1 | lily  | math    |  NULL | 0.16666666666666666 |
|    5 | mike  | math    |    70 |  0.3333333333333333 |
|    2 | tom   | math    |    80 |  0.6666666666666666 |
|    4 | amy   | math    |    80 |  0.6666666666666666 |
|    6 | amber | math    |    92 |  0.8333333333333334 |
|    3 | jack  | math    |    95 |                   1 |
|    2 | tom   | physics |  NULL | 0.16666666666666666 |
|    1 | lily  | physics |    60 |  0.3333333333333333 |
|    5 | mike  | physics |    85 |                 0.5 |
|    4 | amy   | physics |    99 |  0.8333333333333334 |
|    3 | jack  | physics |    99 |  0.8333333333333334 |
|    6 | amber | physics |   100 |                   1 |
+------+-------+---------+-------+---------------------+

Для cume_dist в первой строке группа NULL имеет только одну строку, и только эта строка сама соответствует условию "меньше или равно текущей строке". Кумулятивное распределение равно 1.
Для cume_dist во второй строке группа english имеет пять строк, и только эта строка сама (NULL) соответствует условию "меньше или равно текущей строке". Кумулятивное распределение равно 0.2.
Для cume_dist в третьей строке группа english имеет пять строк, и две строки (85 и NULL) соответствуют условию "меньше или равно текущей строке". Кумулятивное распределение равно 0.4.

DENSE_RANK()

Функция DENSE_RANK() используется для представления рангов. В отличие от RANK(), DENSE_RANK() не имеет пропусков в номерах. Например, если есть две 1, третий номер DENSE_RANK() всё равно будет 2, тогда как третий номер RANK() будет 3.

Синтаксис:

DENSE_RANK() OVER(partition_by_clause order_by_clause)

Примеры:

Следующий пример показывает ранжирование оценок по математике (отсортированных по убыванию). Этот пример использует данные из Примера таблицы scores.

select *,
    dense_rank()
        over (
            partition by subject
            order by score desc
        ) as `rank`
from scores where subject in ('math');

+------+-------+---------+-------+------+
| id   | name  | subject | score | rank |
+------+-------+---------+-------+------+
|    3 | jack  | math    |    95 |    1 |
|    6 | amber | math    |    92 |    2 |
|    2 | tom   | math    |    80 |    3 |
|    4 | amy   | math    |    80 |    3 |
|    5 | mike  | math    |    70 |    4 |
|    1 | lily  | math    |  NULL |    5 |
+------+-------+---------+-------+------+

Результирующие данные имеют две строки с оценкой 80. Они все имеют ранг 3. Ранг для следующей оценки 70 равен 4. Это показывает, что DENSE_RANK() не имеет пропусков в номерах.

FIRST_VALUE()

FIRST_VALUE() возвращает первое значение диапазона окна.

Синтаксис:

FIRST_VALUE(expr [IGNORE NULLS]) OVER(partition_by_clause order_by_clause [window_clause])

IGNORE NULLS поддерживается начиная с v1.5.2. Используется для определения, исключаются ли значения NULL expr из расчёта. По умолчанию значения NULL включаются, что означает, что NULL возвращается, если первое значение в отфильтрованном результате равно NULL. Если вы указываете IGNORE NULLS, возвращается первое ненулевое значение в отфильтрованном результате. Если все значения равны NULL, NULL возвращается даже при указании IGNORE NULLS.

Типы ARRAY поддерживаются начиная с Selena v1.5.2. Вы можете использовать FIRST_VALUE() со столбцами ARRAY для получения первого значения массива в окне.

Примеры:

Возврат первого значения score для каждого участника в каждой группе (по убыванию), группировка по subject. Этот пример использует данные из Примера таблицы scores.

select *,
    first_value(score IGNORE NULLS)
        over (
            partition by subject
            order by score desc
        ) as first
from scores;

+------+-------+---------+-------+-------+
| id   | name  | subject | score | first |
+------+-------+---------+-------+-------+
|    1 | lily  | english |   100 |   100 |
|    2 | tom   | english |    98 |   100 |
|    4 | amy   | english |    92 |   100 |
|    5 | mike  | english |    85 |   100 |
|    3 | jack  | english |  NULL |   100 |
|    6 | amber | physics |   100 |   100 |
|    3 | jack  | physics |    99 |   100 |
|    4 | amy   | physics |    99 |   100 |
|    5 | mike  | physics |    85 |   100 |
|    1 | lily  | physics |    60 |   100 |
|    2 | tom   | physics |  NULL |   100 |
|    6 | amber | NULL    |    90 |    90 |
|    3 | jack  | math    |    95 |    95 |
|    6 | amber | math    |    92 |    95 |
|    2 | tom   | math    |    80 |    95 |
|    4 | amy   | math    |    80 |    95 |
|    5 | mike  | math    |    70 |    95 |
|    1 | lily  | math    |  NULL |    95 |
+------+-------+---------+-------+-------+

Пример 2: Использование FIRST_VALUE() с типами ARRAY

Создание таблицы со столбцами ARRAY:

CREATE TABLE test_array_value (
    col_1 INT,
    arr1 ARRAY<INT>
) DISTRIBUTED BY HASH(col_1);

INSERT INTO test_array_value (col_1, arr1) VALUES
    (1, [1, 11]),
    (2, [2, 22]),
    (3, [3, 33]),
    (4, NULL),
    (5, [5, 55]);

Запрос данных с использованием FIRST_VALUE() с типами ARRAY:

SELECT col_1, arr1, 
    FIRST_VALUE(arr1) OVER (ORDER BY col_1) AS first_array
FROM test_array_value;

Вывод:

+-------+--------+------------+
| col_1 | arr1   | first_array|
+-------+--------+------------+
|     1 | [1,11] | [1,11]     |
|     2 | [2,22] | [1,11]     |
|     3 | [3,33] | [1,11]     |
|     4 | NULL   | [1,11]     |
|     5 | [5,55] | [1,11]     |
+-------+--------+------------+

Первое значение массива [1,11] возвращается для всех строк в окне.

LAST_VALUE()

LAST_VALUE() возвращает последнее значение диапазона окна. Это противоположность FIRST_VALUE().

Синтаксис:

LAST_VALUE(expr [IGNORE NULLS]) OVER(partition_by_clause order_by_clause [window_clause])

IGNORE NULLS поддерживается начиная с v1.5.2. Используется для определения, исключаются ли значения NULL expr из расчёта. По умолчанию значения NULL включаются, что означает, что NULL возвращается, если последнее значение в отфильтрованном результате равно NULL. Если вы указываете IGNORE NULLS, возвращается последнее ненулевое значение в отфильтрованном результате. Если все значения равны NULL, NULL возвращается даже при указании IGNORE NULLS.

По умолчанию LAST_VALUE() вычисляет rows between unbounded preceding and current row, что сравнивает текущую строку со всеми предшествующими строками. Если вы хотите показать только одно значение для каждого раздела, используйте rows between unbounded preceding and unbounded following после ORDER BY.

Типы ARRAY поддерживаются начиная с Selena v1.5.2. Вы можете использовать LAST_VALUE() со столбцами ARRAY для получения последнего значения массива в окне.

Примеры:

Возврат последней score для каждого участника в группе (по убыванию), группировка по subject. Этот пример использует данные из Примера таблицы scores.

select *,
    last_value(score IGNORE NULLS)
        over (
            partition by subject
            order by score desc
            rows between unbounded preceding and unbounded following
        ) as last
from scores;

+------+-------+---------+-------+------+
| id   | name  | subject | score | last |
+------+-------+---------+-------+------+
|    1 | lily  | english |   100 |   85 |
|    2 | tom   | english |    98 |   85 |
|    4 | amy   | english |    92 |   85 |
|    5 | mike  | english |    85 |   85 |
|    3 | jack  | english |  NULL |   85 |
|    6 | amber | physics |   100 |   60 |
|    3 | jack  | physics |    99 |   60 |
|    4 | amy   | physics |    99 |   60 |
|    5 | mike  | physics |    85 |   60 |
|    1 | lily  | physics |    60 |   60 |
|    2 | tom   | physics |  NULL |   60 |
|    6 | amber | NULL    |    90 |   90 |
|    3 | jack  | math    |    95 |   70 |
|    6 | amber | math    |    92 |   70 |
|    2 | tom   | math    |    80 |   70 |
|    4 | amy   | math    |    80 |   70 |
|    5 | mike  | math    |    70 |   70 |
|    1 | lily  | math    |  NULL |   70 |
+------+-------+---------+-------+------+

Пример 2: Использование LAST_VALUE() с типами ARRAY

Использование той же таблицы из Примера 2 FIRST_VALUE():

SELECT col_1, arr1, 
    LAST_VALUE(arr1) OVER (
        ORDER BY col_1 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_array
FROM test_array_value;

Вывод:

+-------+--------+-----------+
| col_1 | arr1   | last_array|
+-------+--------+-----------+
|     1 | [1,11] | [5,55]    |
|     2 | [2,22] | [5,55]    |
|     3 | [3,33] | [5,55]    |
|     4 | NULL   | [5,55]    |
|     5 | [5,55] | [5,55]    |
+-------+--------+-----------+

Последнее значение массива [5,55] возвращается для всех строк в окне.

LAG()

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

LAG() можно использовать для запроса данных следующих типов:

Числовые: TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL
Строковые: CHAR, VARCHAR
Даты: DATE, DATETIME
BITMAP и HLL поддерживаются начиная с Selena v1.5.2.
Типы ARRAY поддерживаются начиная с Selena v1.5.2.

Синтаксис:

LAG(expr [IGNORE NULLS] [, offset[, default]])
OVER([<partition_by_clause>] [<order_by_clause>])

Параметры:

expr: поле, которое вы хотите вычислить.
offset: смещение. Должно быть положительным целым числом. Если этот параметр не указан, по умолчанию используется 1.
default: значение по умолчанию, возвращаемое, если совпадающая строка не найдена. Если этот параметр не указан, по умолчанию используется NULL. default поддерживает любое выражение, тип которого совместим с expr.
IGNORE NULLS поддерживается начиная с v1.5.2. Используется для определения, включаются ли значения NULL expr в результат. По умолчанию значения NULL включаются при подсчёте offset строк, что означает, что NULL возвращается, если значение целевой строки равно NULL. См. Пример 1. Если вы указываете IGNORE NULLS, значения NULL игнорируются при подсчёте offset строк, и система продолжает искать offset ненулевых значений. Если offset ненулевых значений не может быть найдено, возвращается NULL или default (если указано). См. Пример 2.

Пример 1: IGNORE NULLS не указан

Создание таблицы и вставка значений:

CREATE TABLE test_tbl (col_1 INT, col_2 INT)
DISTRIBUTED BY HASH(col_1);

INSERT INTO test_tbl VALUES 
    (1, NULL),
    (2, 4),
    (3, NULL),
    (4, 2),
    (5, NULL),
    (6, 7),
    (7, 6),
    (8, 5),
    (9, NULL),
    (10, NULL);

Запрос данных из этой таблицы, где offset равен 2, что означает обход двух предыдущих строк; default равен 0, что означает, что 0 возвращается, если совпадающие строки не найдены.

Вывод:

SELECT col_1, col_2, LAG(col_2,2,0) OVER (ORDER BY col_1) 
FROM test_tbl ORDER BY col_1;
+-------+-------+---------------------------------------------+
| col_1 | col_2 | lag(col_2, 2, 0) OVER (ORDER BY col_1 ASC ) |
+-------+-------+---------------------------------------------+
|     1 |  NULL |                                           0 |
|     2 |     4 |                                           0 |
|     3 |  NULL |                                        NULL |
|     4 |     2 |                                           4 |
|     5 |  NULL |                                        NULL |
|     6 |     7 |                                           2 |
|     7 |     6 |                                        NULL |
|     8 |     5 |                                           7 |
|     9 |  NULL |                                           6 |
|    10 |  NULL |                                           5 |
+-------+-------+---------------------------------------------+

Для первых двух строк предыдущие две строки не существуют, и возвращается значение по умолчанию 0.

Для NULL в строке 3 значение на две строки назад равно NULL, и возвращается NULL, потому что значения NULL разрешены.

Пример 2: IGNORE NULLS указан

Использование предыдущей таблицы и настроек параметров.

SELECT col_1, col_2, LAG(col_2 IGNORE NULLS,2,0) OVER (ORDER BY col_1) 
FROM test_tbl ORDER BY col_1;
+-------+-------+---------------------------------------------+
| col_1 | col_2 | lag(col_2, 2, 0) OVER (ORDER BY col_1 ASC ) |
+-------+-------+---------------------------------------------+
|     1 |  NULL |                                           0 |
|     2 |     4 |                                           0 |
|     3 |  NULL |                                           0 |
|     4 |     2 |                                           0 |
|     5 |  NULL |                                           4 |
|     6 |     7 |                                           4 |
|     7 |     6 |                                           2 |
|     8 |     5 |                                           7 |
|     9 |  NULL |                                           6 |
|    10 |  NULL |                                           6 |
+-------+-------+---------------------------------------------+

Для строк с 1 по 4 система не может найти два ненулевых значения для каждой из них в предыдущих строках, и возвращается значение по умолчанию 0.

Для значения 6 в строке 7 значение на две строки назад равно NULL, и NULL игнорируется, потому что указан IGNORE NULLS. Система продолжает искать ненулевые значения, и возвращается 2 из строки 4.

Пример 3: Установка значения по умолчанию в LAG() равным имени столбца

Использование предыдущей таблицы и настроек параметров.

SELECT col_1, col_2, LAG(col_2 ,2,col_1) OVER (ORDER BY col_1)
FROM test_tbl ORDER BY col_1;
+-------+-------+-------------------------------------------------+
| col_1 | col_2 | lag(col_2, 2, col_1) OVER (ORDER BY col_1 ASC ) |
+-------+-------+-------------------------------------------------+
|     1 |  NULL |                                               1 |
|     2 |     4 |                                               2 |
|     3 |  NULL |                                            NULL |
|     4 |     2 |                                               4 |
|     5 |  NULL |                                            NULL |
|     6 |     7 |                                               2 |
|     7 |     6 |                                            NULL |
|     8 |     5 |                                               7 |
|     9 |  NULL |                                               6 |
|    10 |  NULL |                                               5 |
+-------+-------+-------------------------------------------------+

Как видите, для строк 1 и 2 нет двух ненулевых значений при сканировании назад, поэтому возвращается значение col_1 текущей строки по умолчанию.

Все остальные строки ведут себя так же, как в Примере 1.

Пример 4: Использование LAG() с типами ARRAY

Создание таблицы со столбцами ARRAY:

CREATE TABLE test_array_value (
    col_1 INT,
    arr1 ARRAY<INT>,
    arr2 ARRAY<INT> NOT NULL
) DISTRIBUTED BY HASH(col_1);

INSERT INTO test_array_value (col_1, arr1, arr2) VALUES
    (1, [1, 11], [101, 111]),
    (2, [2, 22], [102, 112]),
    (3, [3, 33], [103, 113]),
    (4, NULL,    [104, 114]),
    (5, [5, 55], [105, 115]),
    (6, [6, 66], [106, 116]);

Запрос данных с использованием LAG() с типами ARRAY:

SELECT col_1, arr1, LAG(arr1, 2, arr2) OVER (ORDER BY col_1) AS lag_result 
FROM test_array_value;

Вывод:

+-------+--------+-------------+
| col_1 | arr1   | lag_result  |
+-------+--------+-------------+
|     1 | [1,11] | [101,111]   |
|     2 | [2,22] | [102,112]   |
|     3 | [3,33] | [1,11]      |
|     4 | NULL   | [2,22]      |
|     5 | [5,55] | [3,33]      |
|     6 | [6,66] | NULL        |
+-------+--------+-------------+

Для первых двух строк предыдущие две строки не существуют, поэтому возвращается значение по умолчанию из arr2.

LEAD()

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

Типы данных, которые можно запрашивать с помощью LEAD(), такие же, как поддерживаемые LAG().

Синтаксис:

LEAD(expr [IGNORE NULLS] [, offset[, default]])
OVER([<partition_by_clause>] [<order_by_clause>])

Параметры:

expr: поле, которое вы хотите вычислить.
offset: смещение. Должно быть положительным целым числом. Если этот параметр не указан, по умолчанию используется 1.
default: значение по умолчанию, возвращаемое, если совпадающая строка не найдена. Если этот параметр не указан, по умолчанию используется NULL. default поддерживает любое выражение, тип которого совместим с expr.
IGNORE NULLS поддерживается начиная с v1.5.2. Используется для определения, включаются ли значения NULL expr в результат. По умолчанию значения NULL включаются при подсчёте offset строк, что означает, что NULL возвращается, если значение целевой строки равно NULL. См. Пример 1. Если вы указываете IGNORE NULLS, значения NULL игнорируются при подсчёте offset строк, и система продолжает искать offset ненулевых значений. Если offset ненулевых значений не может быть найдено, возвращается NULL или default (если указано). См. Пример 2.

Пример 1: IGNORE NULLS не указан

Создание таблицы и вставка значений:

CREATE TABLE test_tbl (col_1 INT, col_2 INT)
DISTRIBUTED BY HASH(col_1);

INSERT INTO test_tbl VALUES 
    (1, NULL),
    (2, 4),
    (3, NULL),
    (4, 2),
    (5, NULL),
    (6, 7),
    (7, 6),
    (8, 5),
    (9, NULL),
    (10, NULL);

Запрос данных из этой таблицы, где offset равен 2, что означает обход двух последующих строк; default равен 0, что означает, что 0 возвращается, если совпадающие строки не найдены.

Вывод:

SELECT col_1, col_2, LEAD(col_2,2,0) OVER (ORDER BY col_1) 
FROM test_tbl ORDER BY col_1;
+-------+-------+----------------------------------------------+
| col_1 | col_2 | lead(col_2, 2, 0) OVER (ORDER BY col_1 ASC ) |
+-------+-------+----------------------------------------------+
|     1 |  NULL |                                         NULL |
|     2 |     4 |                                            2 |
|     3 |  NULL |                                         NULL |
|     4 |     2 |                                            7 |
|     5 |  NULL |                                            6 |
|     6 |     7 |                                            5 |
|     7 |     6 |                                         NULL |
|     8 |     5 |                                         NULL |
|     9 |  NULL |                                            0 |
|    10 |  NULL |                                            0 |
+-------+-------+----------------------------------------------+

Для первой строки значение на две строки вперёд равно NULL, и возвращается NULL, потому что значения NULL разрешены.

Для последних двух строк последующие две строки не существуют, и возвращается значение по умолчанию 0.

Пример 2: IGNORE NULLS указан

Использование предыдущей таблицы и настроек параметров.

SELECT col_1, col_2, LEAD(col_2 IGNORE NULLS,2,0) OVER (ORDER BY col_1) 
FROM test_tbl ORDER BY col_1;
+-------+-------+----------------------------------------------+
| col_1 | col_2 | lead(col_2, 2, 0) OVER (ORDER BY col_1 ASC ) |
+-------+-------+----------------------------------------------+
|     1 |  NULL |                                            2 |
|     2 |     4 |                                            7 |
|     3 |  NULL |                                            7 |
|     4 |     2 |                                            6 |
|     5 |  NULL |                                            6 |
|     6 |     7 |                                            5 |
|     7 |     6 |                                            0 |
|     8 |     5 |                                            0 |
|     9 |  NULL |                                            0 |
|    10 |  NULL |                                            0 |
+-------+-------+----------------------------------------------+

Для строк с 7 по 10 система не может найти два ненулевых значения в последующих строках, и возвращается значение по умолчанию 0.

Для первой строки значение на две строки вперёд равно NULL, и NULL игнорируется, потому что указан IGNORE NULLS. Система продолжает искать второе ненулевое значение, и возвращается 2 из строки 4.

Пример 3: Установка значения по умолчанию в LEAD() равным имени столбца

Использование предыдущей таблицы и настроек параметров.

SELECT col_1, col_2, LEAD(col_2 ,2,col_1) OVER (ORDER BY col_1)
FROM test_tbl ORDER BY col_1;
+-------+-------+--------------------------------------------------+
| col_1 | col_2 | lead(col_2, 2, col_1) OVER (ORDER BY col_1 ASC ) |
+-------+-------+--------------------------------------------------+
|     1 |  NULL |                                             NULL |
|     2 |     4 |                                                2 |
|     3 |  NULL |                                             NULL |
|     4 |     2 |                                                7 |
|     5 |  NULL |                                                6 |
|     6 |     7 |                                                5 |
|     7 |     6 |                                             NULL |
|     8 |     5 |                                             NULL |
|     9 |  NULL |                                                9 |
|    10 |  NULL |                                               10 |
+-------+-------+--------------------------------------------------+

Как видите, для строк 9 и 10 нет двух ненулевых значений при сканировании вперёд, поэтому возвращается значение col_1 текущей строки по умолчанию.

Все остальные строки ведут себя так же, как в Примере 1.

Пример 4: Использование LEAD() с типами ARRAY

Использование той же таблицы из Примера 4 LAG():

SELECT col_1, arr1, LEAD(arr1, 2, arr2) OVER (ORDER BY col_1) AS lead_result 
FROM test_array_value;

Вывод:

+-------+--------+-------------+
| col_1 | arr1   | lead_result |
+-------+--------+-------------+
|     1 | [1,11] | [3,33]      |
|     2 | [2,22] | NULL        |
|     3 | [3,33] | [5,55]      |
|     4 | NULL   | [6,66]      |
|     5 | [5,55] | [105,115]   |
|     6 | [6,66] | [106,116]   |
+-------+--------+-------------+

Для последних двух строк последующие две строки не существуют, поэтому возвращается значение по умолчанию из arr2.

MAX()

Возвращает максимальное значение указанных строк в текущем окне.

Синтаксис:

MAX(expr) [OVER (analytic_clause)]

Примеры:

Вычисление максимального значения строк от первой строки до строки после текущей строки. Этот пример использует данные из Примера таблицы scores.

select *,
    max(scores)
        over (
            partition by subject
            order by score
            rows between unbounded preceding and 1 following
        ) as max
from scores
where subject in ('math');

+------+-------+---------+-------+------+
| id   | name  | subject | score | max  |
+------+-------+---------+-------+------+
|    1 | lily  | math    |  NULL |   70 |
|    5 | mike  | math    |    70 |   80 |
|    2 | tom   | math    |    80 |   80 |
|    4 | amy   | math    |    80 |   92 |
|    6 | amber | math    |    92 |   95 |
|    3 | jack  | math    |    95 |   95 |
+------+-------+---------+-------+------+

Следующий пример вычисляет максимальную оценку среди всех строк для предмета math.

select *,
    max(score)
        over (
            partition by subject
            order by score
            rows between unbounded preceding and unbounded following
        ) as max
from scores
where subject in ('math');

Начиная с Selena 2.4, вы можете указать диапазон строк как rows between n preceding and n following, что означает, что вы можете захватить n строк до текущей строки и n строк после текущей строки.

Пример оператора:

select *,
    max(score)
        over (
            partition by subject
            order by score
            rows between 3 preceding and 2 following) as max
from scores
where subject in ('math');

MIN()

Возвращает минимальное значение указанных строк в текущем окне.

Синтаксис:

MIN(expr) [OVER (analytic_clause)]

Примеры:

Вычисление наименьшей оценки среди всех строк для предмета математика. Этот пример использует данные из Примера таблицы scores.

select *, 
    min(score)
        over (
            partition by subject
            order by score
            rows between unbounded preceding and unbounded following)
            as min
from scores
where subject in ('math');

+------+-------+---------+-------+------+
| id   | name  | subject | score | min  |
+------+-------+---------+-------+------+
|    1 | lily  | math    |  NULL |   70 |
|    5 | mike  | math    |    70 |   70 |
|    2 | tom   | math    |    80 |   70 |
|    4 | amy   | math    |    80 |   70 |
|    6 | amber | math    |    92 |   70 |
|    3 | jack  | math    |    95 |   70 |
+------+-------+---------+-------+------+

Пример оператора:

select *,
    min(score)
        over (
            partition by subject
            order by score
            rows between 3 preceding and 2 following) as max
from scores
where subject in ('math');

NTILE()

Функция NTILE() делит отсортированные строки в разделе на указанное количество num_buckets как можно более равномерно, сохраняет разделённые строки в соответствующих корзинах, начиная с 1 [1, 2, ..., num_buckets], и возвращает номер корзины, в которой находится каждая строка.

О размере корзины:

Если количество строк делится на указанное число num_buckets ровно, все корзины будут одинакового размера.
Если количество строк не делится на указанное число num_buckets ровно, будут корзины двух разных размеров. Разница между размерами равна 1. Корзины с большим количеством строк будут перечислены перед корзинами с меньшим количеством строк.

Синтаксис:

NTILE (num_buckets) OVER (partition_by_clause order_by_clause)

num_buckets: Количество корзин для создания. Значение должно быть константным положительным целым числом, максимум 2^63 - 1.

Предложение window не допускается в функции NTILE().

Функция NTILE() возвращает данные типа BIGINT.

Примеры:

Следующий пример делит все строки в разделе на две корзины. Этот пример использует данные из Примера таблицы scores.

select *,
    ntile(2)
        over (
            partition by subject
            order by score
        ) as bucket_id
from scores;

Вывод:

+------+-------+---------+-------+-----------+
| id   | name  | subject | score | bucket_id |
+------+-------+---------+-------+-----------+
|    6 | amber | NULL    |    90 |         1 |
|    1 | lily  | math    |  NULL |         1 |
|    5 | mike  | math    |    70 |         1 |
|    2 | tom   | math    |    80 |         1 |
|    4 | amy   | math    |    80 |         2 |
|    6 | amber | math    |    92 |         2 |
|    3 | jack  | math    |    95 |         2 |
|    3 | jack  | english |  NULL |         1 |
|    5 | mike  | english |    85 |         1 |
|    4 | amy   | english |    92 |         1 |
|    2 | tom   | english |    98 |         2 |
|    1 | lily  | english |   100 |         2 |
|    2 | tom   | physics |  NULL |         1 |
|    1 | lily  | physics |    60 |         1 |
|    5 | mike  | physics |    85 |         1 |
|    3 | jack  | physics |    99 |         2 |
|    4 | amy   | physics |    99 |         2 |
|    6 | amber | physics |   100 |         2 |
+------+-------+---------+-------+-----------+

Как показано в приведённом выше примере, когда num_buckets равно 2:

Для первой строки этот раздел имеет только эту запись, и она назначается только в одну корзину.
Для строк со 2 по 7 раздел имеет 6 записей, и первые 3 записи назначаются в корзину 1, а другие 3 записи назначаются в корзину 2.

PERCENT_RANK()

Вычисляет относительный ранг строки в результирующем наборе как процент.

PERCENT_RANK() вычисляется по следующей формуле, где Rank представляет ранг текущей строки в разделе.

(Rank - 1)/(Rows in partition - 1)

Возвращаемые значения находятся в диапазоне от 0 до 1. Эта функция полезна для вычисления процентилей и анализа распределения данных. Поддерживается начиная с v1.5.2.

Синтаксис:

PERCENT_RANK() OVER (partition_by_clause order_by_clause)

Эта функция должна использоваться с ORDER BY для сортировки строк раздела в желаемом порядке.

Примеры:

Следующий пример показывает относительный ранг каждой score в группе math. Этот пример использует данные из Примера таблицы scores.

SELECT *,
    PERCENT_RANK()
        OVER (
            PARTITION BY subject
            ORDER BY score
        ) AS `percent_rank`
FROM scores where subject in ('math');

+------+-------+---------+-------+--------------+
| id   | name  | subject | score | percent_rank |
+------+-------+---------+-------+--------------+
|    1 | lily  | math    |  NULL |            0 |
|    5 | mike  | math    |    70 |          0.2 |
|    2 | tom   | math    |    80 |          0.4 |
|    4 | amy   | math    |    80 |          0.4 |
|    6 | amber | math    |    92 |          0.8 |
|    3 | jack  | math    |    95 |            1 |
+------+-------+---------+-------+--------------+

RANK()

Функция RANK() используется для представления рангов. В отличие от DENSE_RANK(), RANK() имеет пропуски в номерах. Например, если появляются две 1, третий номер RANK() будет 3 вместо 2.

Синтаксис:

RANK() OVER(partition_by_clause order_by_clause)

Примеры:

Ранжирование оценок по математике в группе. Этот пример использует данные из Примера таблицы scores.

select *, 
    rank() over(
        partition by subject
        order by score desc
        ) as `rank`
from scores where subject in ('math');

+------+-------+---------+-------+------+
| id   | name  | subject | score | rank |
+------+-------+---------+-------+------+
|    3 | jack  | math    |    95 |    1 |
|    6 | amber | math    |    92 |    2 |
|    4 | amy   | math    |    80 |    3 |
|    2 | tom   | math    |    80 |    3 |
|    5 | mike  | math    |    70 |    5 |
|    1 | lily  | math    |  NULL |    6 |
+------+-------+---------+-------+------+

Результирующие данные имеют две строки с оценкой 80. Они все имеют ранг 3. Ранг для следующей оценки 70 равен 5.

ROW_NUMBER()

Возвращает непрерывно увеличивающееся целое число, начиная с 1, для каждой строки раздела. В отличие от RANK() и DENSE_RANK(), значение, возвращаемое ROW_NUMBER(), не повторяется и не имеет пропусков и непрерывно увеличивается.

Синтаксис:

ROW_NUMBER() OVER(partition_by_clause order_by_clause)

Примеры:

Ранжирование оценок по математике в группе. Этот пример использует данные из Примера таблицы scores.

select *, row_number() over(
    partition by subject
    order by score desc) as `rank`
from scores where subject in ('math');

+------+-------+---------+-------+------+
| id   | name  | subject | score | rank |
+------+-------+---------+-------+------+
|    3 | jack  | math    |    95 |    1 |
|    6 | amber | math    |    92 |    2 |
|    2 | tom   | math    |    80 |    3 |
|    4 | amy   | math    |    80 |    4 |
|    5 | mike  | math    |    70 |    5 |
|    1 | lily  | math    |  NULL |    6 |
+------+-------+---------+-------+------+

QUALIFY()

Предложение QUALIFY фильтрует результаты оконных функций. В операторе SELECT вы можете использовать предложение QUALIFY для применения условий к столбцу для фильтрации результатов. QUALIFY аналогично предложению HAVING в агрегатных функциях. Эта функция поддерживается начиная с v1.5.2.

QUALIFY упрощает написание операторов SELECT.

До использования QUALIFY оператор SELECT мог выглядеть так:

SELECT *
FROM (SELECT DATE,
             PROVINCE_CODE,
             TOTAL_SCORE,
             ROW_NUMBER() OVER(PARTITION BY PROVINCE_CODE ORDER BY TOTAL_SCORE) AS SCORE_ROWNUMBER
      FROM example_table) T1
WHERE T1.SCORE_ROWNUMBER = 1;

После использования QUALIFY оператор сокращается до:

SELECT DATE, PROVINCE_CODE, TOTAL_SCORE
FROM example_table
QUALIFY ROW_NUMBER() OVER(PARTITION BY PROVINCE_CODE ORDER BY TOTAL_SCORE) = 1;

QUALIFY поддерживает только следующие три оконные функции: ROW_NUMBER(), RANK() и DENSE_RANK().

Синтаксис:

SELECT <column_list>
FROM <data_source>
[GROUP BY ...]
[HAVING ...]
QUALIFY <window_function>
[ ... ]

Параметры:

<column_list>: столбцы, из которых вы хотите получить данные.

<data_source>: Источник данных обычно является таблицей.

<window_function>: За предложением QUALIFY может следовать только оконная функция, включая ROW_NUMBER(), RANK() и DENSE_RANK().

Примеры:

-- Создание таблицы.
CREATE TABLE sales_record (
   city_id INT,
   item STRING,
   sales INT
) DISTRIBUTED BY HASH(`city_id`);

-- Вставка данных в таблицу.
insert into sales_record values
(1,'fruit',95),
(2,'drinks',70),
(3,'fruit',87),
(4,'drinks',98);

-- Запрос данных из таблицы.
select * from sales_record order by city_id;
+---------+--------+-------+
| city_id | item   | sales |
+---------+--------+-------+
|       1 | fruit  |    95 |
|       2 | drinks |    70 |
|       3 | fruit  |    87 |
|       4 | drinks |    98 |
+---------+--------+-------+

Пример 1: Получение записей, номер строки которых больше 1, из таблицы.

SELECT city_id, item, sales
FROM sales_record
QUALIFY row_number() OVER (ORDER BY city_id) > 1;
+---------+--------+-------+
| city_id | item   | sales |
+---------+--------+-------+
|       2 | drinks |    70 |
|       3 | fruit  |    87 |
|       4 | drinks |    98 |
+---------+--------+-------+

Пример 2: Получение записей, номер строки которых равен 1, из каждого раздела таблицы. Таблица разделена на два раздела по item, и возвращается первая строка в каждом разделе.

SELECT city_id, item, sales
FROM sales_record 
QUALIFY ROW_NUMBER() OVER (PARTITION BY item ORDER BY city_id) = 1
ORDER BY city_id;
+---------+--------+-------+
| city_id | item   | sales |
+---------+--------+-------+
|       1 | fruit  |    95 |
|       2 | drinks |    70 |
+---------+--------+-------+
2 rows in set (0.01 sec)

Пример 3: Получение записей с рангом продаж №1 из каждого раздела таблицы. Таблица разделена на два раздела по item, и возвращается строка с наивысшими продажами в каждом разделе.

SELECT city_id, item, sales
FROM sales_record
QUALIFY RANK() OVER (PARTITION BY item ORDER BY sales DESC) = 1
ORDER BY city_id;
+---------+--------+-------+
| city_id | item   | sales |
+---------+--------+-------+
|       1 | fruit  |    95 |
|       4 | drinks |    98 |
+---------+--------+-------+

Примечания по использованию:

QUALIFY supports only the following three window functions: ROW_NUMBER(), RANK(), and DENSE_RANK().
The execution order of clauses in a query with QUALIFY is evaluated in the following order:

FROM
WHERE
GROUP BY
HAVING
Window
QUALIFY
DISTINCT
ORDER BY
LIMIT

SUM()

Calculates the sum of specified rows.

Syntax:

SUM([DISTINCT] expr) [OVER (analytic_clause)]

DISTINCT is supported from Selena v2.0.0. When specified, SUM() sums only distinct values in the window.

примечание

Window frame limitation: When using SUM(DISTINCT) as a window function, only RANGE frames are supported. ROWS frames are NOT supported.

Examples:

Group data by subject and calculate the sum of scores of all rows within the group. This example uses the data in the Sample table scores.

select *,
    sum(score)
        over (
            partition by subject
            order by score
            rows between unbounded preceding and unbounded following
        ) as 'sum'
from scores;

+------+-------+---------+-------+------+
| id   | name  | subject | score | sum  |
+------+-------+---------+-------+------+
|    6 | amber | NULL    |    90 |   90 |
|    1 | lily  | math    |  NULL |  417 |
|    5 | mike  | math    |    70 |  417 |
|    2 | tom   | math    |    80 |  417 |
|    4 | amy   | math    |    80 |  417 |
|    6 | amber | math    |    92 |  417 |
|    3 | jack  | math    |    95 |  417 |
|    3 | jack  | english |  NULL |  375 |
|    5 | mike  | english |    85 |  375 |
|    4 | amy   | english |    92 |  375 |
|    2 | tom   | english |    98 |  375 |
|    1 | lily  | english |   100 |  375 |
|    2 | tom   | physics |  NULL |  443 |
|    1 | lily  | physics |    60 |  443 |
|    5 | mike  | physics |    85 |  443 |
|    3 | jack  | physics |    99 |  443 |
|    4 | amy   | physics |    99 |  443 |
|    6 | amber | physics |   100 |  443 |
+------+-------+---------+-------+------+

Example 2: Using SUM(DISTINCT) over overall window

Sum distinct scores across all rows:

SELECT id, subject, score,
    SUM(DISTINCT score) OVER () AS distinct_sum
FROM test_scores;

Вывод:

+----+---------+-------+-------------+
| id | subject | score | distinct_sum|
+----+---------+-------+-------------+
|  1 | math    |    80 |          255|
|  2 | math    |    85 |          255|
|  3 | math    |    80 |          255|
|  4 | english |    90 |          255|
|  5 | english |    85 |          255|
|  6 | english |    90 |          255|
+----+---------+-------+-------------+

The distinct sum is 255 (80 + 85 + 90).

Example 3: Using SUM(DISTINCT) over framed window with RANGE frame

Sum distinct scores within each subject partition using a RANGE frame:

SELECT id, subject, score,
    SUM(DISTINCT score) OVER (
        PARTITION BY subject 
        ORDER BY score 
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS distinct_sum
FROM test_scores;

Вывод:

+----+---------+-------+-------------+
| id | subject | score | distinct_sum|
+----+---------+-------+-------------+
|  1 | math    |    80 |           80|
|  3 | math    |    80 |           80|
|  2 | math    |    85 |          165|
|  5 | english |    85 |           85|
|  4 | english |    90 |          175|
|  6 | english |    90 |          175|
+----+---------+-------+-------------+

For each row, the function sums distinct scores from the beginning of the partition up to and including the current row's score value.

VARIANCE, VAR_POP, VARIANCE_POP

Returns the population variance of an expression. VAR_POP and VARIANCE_POP are aliases of VARIANCE. These functions can be used as window functions since v1.5.2.

Syntax:

VARIANCE(expr) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])

подсказка

From 2.5.13, 3.0.7, 3.1.4 onwards, this window function supports the ORDER BY and Window clauses.

Parameters:

If expr is a table column, it must evaluate to TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, or DECIMAL.

Examples:

This example uses the data in the Sample table scores.

select *,
    variance(score)
        over (
            partition by subject
            order by score
        ) as 'variance'
from scores where subject in ('math');
+------+-------+---------+-------+--------------------+
| id   | name  | subject | score | variance           |
+------+-------+---------+-------+--------------------+
|    1 | lily  | math    |  NULL |               NULL |
|    5 | mike  | math    |    70 |                  0 |
|    2 | tom   | math    |    80 | 22.222222222222225 |
|    4 | amy   | math    |    80 | 22.222222222222225 |
|    6 | amber | math    |    92 |  60.74999999999997 |
|    3 | jack  | math    |    95 |  82.23999999999998 |
+------+-------+---------+-------+--------------------+

VAR_SAMP, VARIANCE_SAMP

Returns the sample variance of an expression. These functions can be used as window functions since v1.5.2.

Syntax:

VAR_SAMP(expr) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])

подсказка

From 2.5.13, 3.0.7, 3.1.4 onwards, this window function supports the ORDER BY and Window clauses.

Parameters:

If expr is a table column, it must evaluate to TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, or DECIMAL.

Examples:

This example uses the data in the Sample table scores.

select *,
    VAR_SAMP(score)
       over (partition by subject
            order by score) as VAR_SAMP
from scores where subject in ('math');
+------+-------+---------+-------+--------------------+
| id   | name  | subject | score | VAR_SAMP           |
+------+-------+---------+-------+--------------------+
|    1 | lily  | math    |  NULL |               NULL |
|    5 | mike  | math    |    70 |                  0 |
|    2 | tom   | math    |    80 | 33.333333333333336 |
|    4 | amy   | math    |    80 | 33.333333333333336 |
|    6 | amber | math    |    92 |  80.99999999999996 |
|    3 | jack  | math    |    95 | 102.79999999999997 |
+------+-------+---------+-------+--------------------+

STD, STDDEV, STDDEV_POP

Returns the standard deviation of an expression. These functions can be used as window functions since v1.5.2.

Syntax:

STD(expr) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])

подсказка

From 2.5.13, 3.0.7, 3.1.4 onwards, this window function supports the ORDER BY and Window clauses.

Parameters:

If expr is a table column, it must evaluate to TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, or DECIMAL.

Examples:

This example uses the data in the Sample table scores.

select *, STD(score)
    over (
        partition by subject
        order by score) as std
from scores where subject in ('math');
+------+-------+---------+-------+-------------------+
| id   | name  | subject | score | std               |
+------+-------+---------+-------+-------------------+
|    1 | lily  | math    |  NULL |              NULL |
|    5 | mike  | math    |    70 |                 0 |
|    4 | amy   | math    |    80 | 4.714045207910317 |
|    2 | tom   | math    |    80 | 4.714045207910317 |
|    6 | amber | math    |    92 | 7.794228634059946 |
|    3 | jack  | math    |    95 | 9.068627239003707 |
+------+-------+---------+-------+-------------------+

STDDEV_SAMP

Returns the sample standard deviation of an expression. This function can be used as a window function since v1.5.2.

Syntax:

STDDEV_SAMP(expr) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])

подсказка

From 2.5.13, 3.0.7, 3.1.4 onwards, this window function supports the ORDER BY and Window clauses.

Parameters:

If expr is a table column, it must evaluate to TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, or DECIMAL.

Examples:

This example uses the data in the Sample table scores.

select *, STDDEV_SAMP(score)
    over (
        partition by subject
        order by score
        ) as STDDEV_SAMP
from scores where subject in ('math');
+------+-------+---------+-------+--------------------+
| id   | name  | subject | score | STDDEV_SAMP        |
+------+-------+---------+-------+--------------------+
|    1 | lily  | math    |  NULL |               NULL |
|    5 | mike  | math    |    70 |                  0 |
|    2 | tom   | math    |    80 |  5.773502691896258 |
|    4 | amy   | math    |    80 |  5.773502691896258 |
|    6 | amber | math    |    92 |  8.999999999999998 |
|    3 | jack  | math    |    95 | 10.139033484509259 |
+------+-------+---------+-------+--------------------+

select *, STDDEV_SAMP(score)
    over (
        partition by subject
        order by score
        rows between unbounded preceding and 1 following) as STDDEV_SAMP
from scores where subject in ('math');
+------+-------+---------+-------+--------------------+
| id   | name  | subject | score | STDDEV_SAMP        |
+------+-------+---------+-------+--------------------+
|    1 | lily  | math    |  NULL |                  0 |
|    5 | mike  | math    |    70 | 7.0710678118654755 |
|    2 | tom   | math    |    80 |  5.773502691896258 |
|    4 | amy   | math    |    80 |  8.999999999999998 |
|    6 | amber | math    |    92 | 10.139033484509259 |
|    3 | jack  | math    |    95 | 10.139033484509259 |
+------+-------+---------+-------+--------------------+

COVAR_SAMP

Returns the sample covariance of two expressions. This function is supported from v1.5.2. It is also an aggregate function.

Syntax:

COVAR_SAMP(expr1,expr2) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])

подсказка

From 2.5.13, 3.0.7, 3.1.4 onwards, this window function supports the ORDER BY and Window clauses.

Parameters:

If expr is a table column, it must evaluate to TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, or DECIMAL.

Examples:

This example uses the data in the Sample table scores.

select *, COVAR_SAMP(id, score) 
    over (
        partition by subject
        order by score) as covar_samp
from scores where subject in ('math');
+------+-------+---------+-------+----------------------+
| id   | name  | subject | score | covar_samp           |
+------+-------+---------+-------+----------------------+
|    1 | lily  | math    |  NULL |                 NULL |
|    5 | mike  | math    |    70 |                    0 |
|    2 | tom   | math    |    80 |   -6.666666666666668 |
|    4 | amy   | math    |    80 |   -6.666666666666668 |
|    6 | amber | math    |    92 |                  4.5 |
|    3 | jack  | math    |    95 | -0.24999999999999822 |
+------+-------+---------+-------+----------------------+

select *, COVAR_SAMP(id,score)
    over (
        partition by subject
        order by score
        rows between unbounded preceding and 1 following) as COVAR_SAMP
from scores where subject in ('math');
+------+-------+---------+-------+----------------------+
| id   | name  | subject | score | COVAR_SAMP           |
+------+-------+---------+-------+----------------------+
|    1 | lily  | math    |  NULL |                    0 |
|    5 | mike  | math    |    70 |                   -5 |
|    4 | amy   | math    |    80 |   -6.666666666666661 |
|    2 | tom   | math    |    80 |    4.500000000000004 |
|    6 | amber | math    |    92 | -0.24999999999999467 |
|    3 | jack  | math    |    95 | -0.24999999999999467 |

COVAR_POP

Returns the population covariance of two expressions. This function is supported from v1.5.2. It is also an aggregate function.

Syntax:

COVAR_POP(expr1, expr2) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])

подсказка

From 2.5.13, 3.0.7, 3.1.4 onwards, this window function supports the ORDER BY and Window clauses.

Parameters:

If expr is a table column, it must evaluate to TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, or DECIMAL.

Examples:

This example uses the data in the Sample table scores.

select *, COVAR_POP(id, score)
    over (
        partition by subject
        order by score) as covar_pop
from scores where subject in ('math');
+------+-------+---------+-------+----------------------+
| id   | name  | subject | score | covar_pop            |
+------+-------+---------+-------+----------------------+
|    1 | lily  | math    |  NULL |                 NULL |
|    5 | mike  | math    |    70 |                    0 |
|    2 | tom   | math    |    80 |  -4.4444444444444455 |
|    4 | amy   | math    |    80 |  -4.4444444444444455 |
|    6 | amber | math    |    92 |                3.375 |
|    3 | jack  | math    |    95 | -0.19999999999999857 |
+------+-------+---------+-------+----------------------+

CORR

Returns the Pearson correlation coefficient between two expressions. This function is supported from v1.5.2. It is also an aggregate function.

Syntax:

CORR(expr1, expr2) OVER([partition_by_clause] [order_by_clause] [order_by_clause window_clause])

подсказка

From 2.5.13, 3.0.7, 3.1.4 onwards, this window function supports the ORDER BY and Window clauses.

Parameters:

If expr is a table column, it must evaluate to TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, or DECIMAL.

Examples:

This example uses the data in the Sample table scores.

select *, CORR(id, score)
    over (
        partition by subject
        order by score) as corr
from scores where subject in ('math');
+------+-------+---------+-------+-----------------------+
| id   | name  | subject | score | corr                  |
+------+-------+---------+-------+-----------------------+
|    5 | mike  | math    |    70 | -0.015594571538795355 |
|    1 | lily  | math    |  NULL | -0.015594571538795355 |
|    2 | tom   | math    |    80 | -0.015594571538795355 |
|    4 | amy   | math    |    80 | -0.015594571538795355 |
|    3 | jack  | math    |    95 | -0.015594571538795355 |
|    6 | amber | math    |    92 | -0.015594571538795355 |
+------+-------+---------+-------+-----------------------+

select *, CORR(id,score)
    over (
        partition by subject
        order by score
        rows between unbounded preceding and 1 following) as corr 
from scores where subject in ('math');
+------+-------+---------+-------+-------------------------+
| id   | name  | subject | score | corr                    |
+------+-------+---------+-------+-------------------------+
|    1 | lily  | math    |  NULL | 1.7976931348623157e+308 |
|    5 | mike  | math    |    70 |                      -1 |
|    2 | tom   | math    |    80 |     -0.7559289460184546 |
|    4 | amy   | math    |    80 |     0.29277002188455997 |
|    6 | amber | math    |    92 |   -0.015594571538795024 |
|    3 | jack  | math    |    95 |   -0.015594571538795024 |
+------+-------+---------+-------+-------------------------+

Введение​

Использование​

Синтаксис​

Предложение PARTITION BY​

Предложение ORDER BY​

Предложение Window​

Пример таблицы для оконных функций​

Примеры функций​

AVG()​

ARRAY_AGG()​

COUNT()​

CUME_DIST()​

DENSE_RANK()​

FIRST_VALUE()​

LAST_VALUE()​

LAG()​

LEAD()​

MAX()​

MIN()​

NTILE()​

PERCENT_RANK()​

RANK()​

ROW_NUMBER()​

QUALIFY()​

SUM()​

VARIANCE, VAR_POP, VARIANCE_POP​

VAR_SAMP, VARIANCE_SAMP​

STD, STDDEV, STDDEV_POP​

STDDEV_SAMP​

COVAR_SAMP​

COVAR_POP​

CORR​

Введение

Использование

Синтаксис

Предложение PARTITION BY

Предложение ORDER BY

Предложение Window

Пример таблицы для оконных функций

Примеры функций

AVG()

ARRAY_AGG()

COUNT()

CUME_DIST()

DENSE_RANK()

FIRST_VALUE()

LAST_VALUE()

LAG()

LEAD()

MAX()

MIN()

NTILE()

PERCENT_RANK()

RANK()

ROW_NUMBER()

QUALIFY()

SUM()

VARIANCE, VAR_POP, VARIANCE_POP

VAR_SAMP, VARIANCE_SAMP

STD, STDDEV, STDDEV_POP

STDDEV_SAMP

COVAR_SAMP

COVAR_POP

CORR