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

Предпосылки

Оконная функция — это специальный класс встроенных функций. Подобно агрегатной функции, она также выполняет вычисления на нескольких входных строках для получения одного значения данных. Разница в том, что оконная функция обрабатывает входные данные в пределах определенного окна, а не использует метод "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] ]

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

В этом разделе создается пример таблицы 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(expr) [OVER (*analytic_clause*)]

Примеры:

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

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).

COUNT()

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

Синтаксис:

COUNT(expr) [OVER (analytic_clause)]

Примеры:

Подсчитать количество оценок по математике, которые больше 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 |
+------+-------+---------+-------+-------------+

CUME_DIST()

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

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

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

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

Синтаксис:

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() не имеет пропущенных номеров. Например, если есть две единицы, третий номер 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 поддерживается начиная с версии 1.5.0. Используется для определения того, исключаются ли значения NULL из expr из вычисления. По умолчанию значения NULL включаются, что означает, что возвращается NULL, если первое значение в отфильтрованном результате равно NULL. Если вы указываете IGNORE NULLS, возвращается первое не-null значение в отфильтрованном результате. Если все значения равны NULL, возвращается NULL, даже если вы указываете IGNORE NULLS.

Примеры:

Вернуть первое значение 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 |
+------+-------+---------+-------+-------+

LAST_VALUE()

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

Синтаксис:

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

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

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

Примеры:

Возвращает последнюю 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 |
+------+-------+---------+-------+------+

LAG()

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

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

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

Синтаксис:

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

Параметры:

expr: поле, которое вы хотите вычислить.
offset: смещение. Должно быть положительным целым числом. Если этот параметр не указан, по умолчанию используется 1.
default: значение по умолчанию, возвращаемое, если соответствующая строка не найдена. Если этот параметр не указан, по умолчанию используется NULL. default поддерживает любое выражение, тип которого совместим с expr.
IGNORE NULLS поддерживается начиная с версии 1.5.0. Используется для определения того, включаются ли значения NULL из expr в результат. По умолчанию значения NULL включаются при подсчете строк offset, что означает, что возвращается NULL, если значение целевой строки равно NULL. См. Пример 1. Если вы указываете IGNORE NULLS, значения NULL игнорируются при подсчете строк offset, и система продолжает поиск offset не-null значений. Если offset не-null значений не может быть найдено, возвращается 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 система не может найти два не-NULL значения для каждой из них в предыдущих строках, и возвращается значение по умолчанию 0.

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

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 поддерживается начиная с версии 1.5.0. Используется для определения того, включаются ли значения NULL из expr в результат. По умолчанию значения NULL включаются при подсчете строк offset, что означает, что возвращается NULL, если значение целевой строки равно NULL. См. Пример 1. Если вы указываете IGNORE NULLS, значения NULL игнорируются при подсчете строк offset, и система продолжает поиск offset не-null значений. Если offset не-null значений не может быть найдено, возвращается 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 система не может найти два не-null значения в последующих строках, и возвращается значение по умолчанию 0.

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

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. Эта функция полезна для вычисления процентилей и анализа распределения данных. Она поддерживается начиная с версии 1.5.0.

Синтаксис:

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() будет появляться как пропущенный номер. Например, если появляются две связанные единицы, третий номер 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 в агрегатных функциях. Эта функция поддерживается начиная с версии 1.5.0.

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 поддерживает только следующие три оконные функции: ROW_NUMBER(), RANK() и DENSE_RANK().
Порядок выполнения предложений в запросе с QUALIFY оценивается в следующем порядке:

FROM
WHERE
GROUP BY
HAVING
Window
QUALIFY
DISTINCT
ORDER BY
LIMIT

SUM()

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

Синтаксис:

SUM(expr) [OVER (analytic_clause)]

Примеры:

Группировать данные по subject и вычислить сумму оценок всех строк в группе. В этом примере используются данные из Примера таблицы 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 |
+------+-------+---------+-------+------+

VARIANCE, VAR_POP, VARIANCE_POP

Возвращает дисперсию генеральной совокупности выражения. VAR_POP и VARIANCE_POP являются псевдонимами VARIANCE. Эти функции могут использоваться как оконные функции начиная с версии 1.5.0.

Синтаксис:

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

подсказка

Начиная с версий 2.5.13, 3.0.7, 3.1.4, эта оконная функция поддерживает предложения ORDER BY и Window.

Параметры:

Если expr является столбцом таблицы, он должен оцениваться как TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE или DECIMAL.

Примеры:

В этом примере используются данные из Примера таблицы 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

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

Синтаксис:

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

подсказка

Начиная с версий 2.5.13, 3.0.7, 3.1.4, эта оконная функция поддерживает предложения ORDER BY и Window.

Параметры:

Если expr является столбцом таблицы, он должен оцениваться как TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE или DECIMAL.

Примеры:

В этом примере используются данные из Примера таблицы 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

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

Синтаксис:

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

подсказка

Начиная с версий 2.5.13, 3.0.7, 3.1.4, эта оконная функция поддерживает предложения ORDER BY и Window.

Параметры:

Если expr является столбцом таблицы, он должен оцениваться как TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE или DECIMAL.

Примеры:

В этом примере используются данные из Примера таблицы 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

Возвращает выборочное стандартное отклонение выражения. Эта функция может использоваться как оконная функция начиная с версии 1.5.0.

Синтаксис:

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

подсказка

Начиная с версий 2.5.13, 3.0.7, 3.1.4, эта оконная функция поддерживает предложения ORDER BY и Window.

Параметры:

Если expr является столбцом таблицы, он должен оцениваться как TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE или DECIMAL.

Примеры:

В этом примере используются данные из Примера таблицы 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

Возвращает выборочную ковариацию двух выражений. Эта функция поддерживается начиная с версии 1.5.0. Она также является агрегатной функцией.

Синтаксис:

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

подсказка

Начиная с версий 2.5.13, 3.0.7, 3.1.4, эта оконная функция поддерживает предложения ORDER BY и Window.

Параметры:

Если expr является столбцом таблицы, он должен оцениваться как TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE или DECIMAL.

Примеры:

В этом примере используются данные из Примера таблицы 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

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

Синтаксис:

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

подсказка

Начиная с версий 2.5.13, 3.0.7, 3.1.4, эта оконная функция поддерживает предложения ORDER BY и Window.

Параметры:

Если expr является столбцом таблицы, он должен оцениваться как TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE или DECIMAL.

Примеры:

В этом примере используются данные из Примера таблицы 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

Возвращает коэффициент корреляции Пирсона между двумя выражениями. Эта функция поддерживается начиная с версии 1.5.0. Она также является агрегатной функцией.

Синтаксис:

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

подсказка

Начиная с версий 2.5.13, 3.0.7, 3.1.4, эта оконная функция поддерживает предложения ORDER BY и Window.

Параметры:

Если expr является столбцом таблицы, он должен оцениваться как TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE или DECIMAL.

Примеры:

В этом примере используются данные из Примера таблицы 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()​

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()

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