CUME_DIST() over_clause

Возвращает кумулятивное распределение значения внутри группы значений; то есть процент значений раздела, меньших или равных значению в текущей строке. Это представляет собой количество строк, предшествующих или равных текущей строке в порядке окна раздела окна, деленное на общее количество строк в разделе окна. Значения возврата находятся в диапазоне от 0 до 1.

Эта функция должна использоваться с ORDER BY для сортировки строк раздела в желаемом порядке. Без ORDER BY все строки являются равными и имеют значение N/N = 1, где N — размер раздела.

over_clause описана в разделе 14.20.2, «Концепции и синтаксис функций окна».

Следующий запрос показывает для набора значений в столбце val значение CUME_DIST() для каждой строки, а также значение процентильного ранга, возвращаемое аналогичной функцией PERCENT_RANK(). Для справки запрос также отображает номера строк с помощью ROW_NUMBER():

mysql> SELECT
         val,
         ROW_NUMBER()   OVER w AS 'row_number',
         CUME_DIST()    OVER w AS 'cume_dist',
         PERCENT_RANK() OVER w AS 'percent_rank'
       FROM numbers
       WINDOW w AS (ORDER BY val);
+------+------------+--------------------+--------------+
| val  | row_number | cume_dist          | percent_rank |
+------+------------+--------------------+--------------+
|    1 |          1 | 0.2222222222222222 |            0 |
|    1 |          2 | 0.2222222222222222 |            0 |
|    2 |          3 | 0.3333333333333333 |         0.25 |
|    3 |          4 | 0.6666666666666666 |        0.375 |
|    3 |          5 | 0.6666666666666666 |        0.375 |
|    3 |          6 | 0.6666666666666666 |        0.375 |
|    4 |          7 | 0.8888888888888888 |         0.75 |
|    4 |          8 | 0.8888888888888888 |         0.75 |
|    5 |          9 |                  1 |            1 |
+------+------------+--------------------+--------------+

DENSE_RANK() over_clause

Возвращает ранг текущей строки в ее разделе без пробелов. Равные значения считаются ничьей и получают один и тот же ранг. Эта функция присваивает последовательные ранги группам равных значений; в результате группы размером более одного не создают несмежные номера ранга. Пример см. в описании функции RANK().

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

over_clause описана в разделе 14.20.2, «Концепции и синтаксис функций окна».

FIRST_VALUE(expr) [null_treatment] over_clause

Возвращает значение expr из первой строки фрейма окна.

over_clause описана в разделе 14.20.2, «Концепции и синтаксис функций окна». null_treatment описана во введении к разделу.

Следующий запрос демонстрирует FIRST_VALUE(), LAST_VALUE() и два случая NTH_VALUE():

mysql> SELECT
         time, subject, val,
         FIRST_VALUE(val)  OVER w AS 'first',
         LAST_VALUE(val)   OVER w AS 'last',
         NTH_VALUE(val, 2) OVER w AS 'second',
         NTH_VALUE(val, 4) OVER w AS 'fourth'
       FROM observations
       WINDOW w AS (PARTITION BY subject ORDER BY time
                    ROWS UNBOUNDED PRECEDING);
+----------+---------+------+-------+------+--------+--------+
| time     | subject | val  | first | last | second | fourth |
+----------+---------+------+-------+------+--------+--------+
| 07:00:00 | st113   |   10 |    10 |   10 |   NULL |   NULL |
| 07:15:00 | st113   |    9 |    10 |    9 |      9 |   NULL |
| 07:30:00 | st113   |   25 |    10 |   25 |      9 |   NULL |
| 07:45:00 | st113   |   20 |    10 |   20 |      9 |     20 |
| 07:00:00 | xh458   |    0 |     0 |    0 |   NULL |   NULL |
| 07:15:00 | xh458   |   10 |     0 |   10 |     10 |   NULL |
| 07:30:00 | xh458   |    5 |     0 |    5 |     10 |   NULL |
| 07:45:00 | xh458   |   30 |     0 |   30 |     10 |     30 |
| 08:00:00 | xh458   |   25 |     0 |   25 |     10 |     30 |
+----------+---------+------+-------+------+--------+--------+

Каждая функция использует строки в текущем фрейме, который, согласно определению окна, показанному, простирается от первой строки раздела до текущей строки. Для вызовов NTH_VALUE() текущий фрейм не всегда включает запрашиваемую строку; в таких случаях возвращаемое значение — NULL.

LAG(expr [, N[, default]]) [null_treatment] over_clause

Возвращает значение expr из строки, отстающей (предшествующей) текущей строке на N строк в пределах ее раздела. Если такой строки нет, возвращаемое значение — default. Например, если N равно 3, возвращаемое значение — default для первых трех строк. Если N или default отсутствуют, значения по умолчанию равны 1 и NULL соответственно.

N должно быть целочисленной неотрицательной константой. Если N равно 0, expr вычисляется для текущей строки.

N не может быть NULL и должно быть целым числом в диапазоне от 0 до 263 включительно в любом из следующих форматов:

over_clause описана в разделе 14.20.2, «Концепции и синтаксис функций окна». null_treatment описана во введении к разделу.

LAG() (и аналогичная функция LEAD()) часто используются для вычисления различий между строками. Следующий запрос показывает набор упорядоченных по времени наблюдений и для каждого из них значения LAG() и LEAD() из смежных строк, а также разницы между текущей и смежными строками:

mysql> SELECT
         t, val,
         LAG(val)        OVER w AS 'lag',
         LEAD(val)       OVER w AS 'lead',
         val - LAG(val)  OVER w AS 'lag diff',
         val - LEAD(val) OVER w AS 'lead diff'
       FROM series
       WINDOW w AS (ORDER BY t);
+----------+------+------+------+----------+-----------+
| t        | val  | lag  | lead | lag diff | lead diff |
+----------+------+------+------+----------+-----------+
| 12:00:00 |  100 | NULL |  125 |     NULL |       -25 |
| 13:00:00 |  125 |  100 |  132 |       25 |        -7 |
| 14:00:00 |  132 |  125 |  145 |        7 |       -13 |
| 15:00:00 |  145 |  132 |  140 |       13 |         5 |
| 16:00:00 |  140 |  145 |  150 |       -5 |       -10 |
| 17:00:00 |  150 |  140 |  200 |       10 |       -50 |
| 18:00:00 |  200 |  150 | NULL |       50 |      NULL |
+----------+------+------+------+----------+-----------+

В примере вызовы LAG() и LEAD() используют значения по умолчанию N и default соответственно, равные 1 и NULL.

Первая строка показывает, что происходит, когда для LAG() нет предыдущей строки: функция возвращает значение default (в данном случае NULL). Последняя строка показывает то же самое, когда для LEAD() нет следующей строки.

LAG() и LEAD() также служат для вычисления сумм, а не разностей. Рассмотрим этот набор данных, который содержит первые несколько чисел ряда Фибоначчи:

mysql> SELECT n FROM fib ORDER BY n;
+------+
| n    |
+------+
|    1 |
|    1 |
|    2 |
|    3 |
|    5 |
|    8 |
+------+

Следующий запрос показывает значения LAG() и LEAD() для строк, примыкающих к текущей строке. Он также использует эти функции для добавления к значению текущей строки значений из предшествующей и последующей строк. Эффект заключается в генерации следующего числа в ряду Фибоначчи и следующего за ним числа:

mysql> SELECT
         n,
         LAG(n, 1, 0)      OVER w AS 'lag',
         LEAD(n, 1, 0)     OVER w AS 'lead',
         n + LAG(n, 1, 0)  OVER w AS 'next_n',
         n + LEAD(n, 1, 0) OVER w AS 'next_next_n'
       FROM fib
       WINDOW w AS (ORDER BY n);
+------+------+------+--------+-------------+
| n    | lag  | lead | next_n | next_next_n |
+------+------+------+--------+-------------+
|    1 |    0 |    1 |      1 |           2 |
|    1 |    1 |    2 |      2 |           3 |
|    2 |    1 |    3 |      3 |           5 |
|    3 |    2 |    5 |      5 |           8 |
|    5 |    3 |    8 |      8 |          13 |
|    8 |    5 |    0 |     13 |           8 |
+------+------+------+--------+-------------+

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

Для аргумента строк этой функции нельзя использовать отрицательное значение.

LAST_VALUE(expr) [null_treatment] over_clause

Возвращает значение expr из последней строки фрейма окна.

over_clause описана в разделе 14.20.2, «Концепции и синтаксис функций окна». null_treatment описана во введении к разделу.

Пример см. в описании функции FIRST_VALUE().

LEAD(expr [, N[, default]]) [null_treatment] over_clause

Возвращает значение expr из строки, предшествующей (следующей) текущей строке на N строк в пределах своего раздела. Если такой строки нет, возвращается default. Например, если N равно 3, возвращается значение default для последних трех строк. Если N или default отсутствуют, по умолчанию используются значения 1 и NULL соответственно.

N должно быть целочисленной неотрицательной константой. Если N равно 0, значение expr вычисляется для текущей строки.

N не может быть NULL и должно быть целым числом в диапазоне от 0 до 263 включительно, в любом из следующих форматов:

over_clause описывается в разделе 14.20.2, «Функции окон и синтаксис». null_treatment описывается в начале раздела.

Пример см. в описании функции LAG().

Использование отрицательного значения для аргумента rows этой функции запрещено.

NTH_VALUE(expr, N) [from_first_last] [null_treatment] over_clause

Возвращает значение expr из N-й строки фрейма окна. Если такой строки нет, возвращается NULL.

N должно быть положительной целочисленной константой.

from_first_last является частью стандарта SQL, но реализация MySQL допускает только FROM FIRST (что также является значением по умолчанию). Это означает, что вычисления начинаются с первой строки окна. FROM LAST парсится, но приводит к ошибке. Чтобы получить тот же эффект, что и FROM LAST (начало вычислений с последней строки окна), используйте ORDER BY для сортировки в обратном порядке.

over_clause описывается в разделе 14.20.2, «Функции окон и синтаксис». null_treatment описывается в начале раздела.

Пример см. в описании функции FIRST_VALUE().

Нельзя использовать NULL для аргумента строки этой функции.

NTILE(N) over_clause

Разделяет раздел на N групп (корзин), присваивает каждой строке в разделе номер своей корзины и возвращает номер корзины текущей строки в пределах ее раздела. Например, если N равно 4, NTILE() разделяет строки на четыре корзины. Если N равно 100, NTILE() разделяет строки на 100 корзин.

N должно быть положительной целочисленной константой. Значения номера корзины находятся в диапазоне от 1 до N.

N не может быть NULL и должно быть целым числом в диапазоне от 0 до 263 включительно, в любом из следующих форматов:

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

over_clause описывается в разделе 14.20.2, «Функции окон и синтаксис».

В следующем запросе показаны для набора значений в столбце val процентильные значения, полученные в результате деления строк на две или четыре группы. Для справки запрос также отображает номера строк, используя ROW_NUMBER():

mysql> SELECT
         val,
         ROW_NUMBER() OVER w AS 'row_number',
         NTILE(2)     OVER w AS 'ntile2',
         NTILE(4)     OVER w AS 'ntile4'
       FROM numbers
       WINDOW w AS (ORDER BY val);
+------+------------+--------+--------+
| val  | row_number | ntile2 | ntile4 |
+------+------------+--------+--------+
|    1 |          1 |      1 |      1 |
|    1 |          2 |      1 |      1 |
|    2 |          3 |      1 |      1 |
|    3 |          4 |      1 |      2 |
|    3 |          5 |      1 |      2 |
|    3 |          6 |      2 |      3 |
|    4 |          7 |      2 |      3 |
|    4 |          8 |      2 |      4 |
|    5 |          9 |      2 |      4 |
+------+------------+--------+--------+

Конструкт NTILE(NULL) запрещен.

PERCENT_RANK() over_clause

Возвращает процент значений в разделе, меньших значения в текущей строке, исключая наибольшее значение. Значения возврата находятся в диапазоне от 0 до 1 и представляют собой относительный ранг строки, вычисленный по следующей формуле, где rank — ранг строки, а rows — количество строк раздела:

(rank - 1) / (rows - 1)

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

over_clause описывается в разделе 14.20.2, «Функции окон и синтаксис».

Пример см. в описании функции CUME_DIST().

RANK() over_clause

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

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

over_clause описывается в разделе 14.20.2, «Функции окон и синтаксис».

Следующий запрос демонстрирует разницу между RANK(), которая генерирует ранги с разрывами, и DENSE_RANK(), которая генерирует ранги без разрывов. В запросе показаны значения рангов для каждого элемента набора значений в столбце val, содержащем некоторые дубликаты. RANK() присваивает дубликатам (равнозначным строкам) одинаковое значение ранга, а следующее большее значение имеет ранг, больший на количество дубликатов минус один. DENSE_RANK() также присваивает дубликатам одинаковое значение ранга, но следующее большее значение имеет ранг, больший на единицу. Для справки запрос также отображает номера строк, используя ROW_NUMBER():

mysql> SELECT
         val,
         ROW_NUMBER() OVER w AS 'row_number',
         RANK()       OVER w AS 'rank',
         DENSE_RANK() OVER w AS 'dense_rank'
       FROM numbers
       WINDOW w AS (ORDER BY val);
+------+------------+------+------------+
| val  | row_number | rank | dense_rank |
+------+------------+------+------------+
|    1 |          1 |    1 |          1 |
|    1 |          2 |    1 |          1 |
|    2 |          3 |    3 |          2 |
|    3 |          4 |    4 |          3 |
|    3 |          5 |    4 |          3 |
|    3 |          6 |    4 |          3 |
|    4 |          7 |    7 |          4 |
|    4 |          8 |    7 |          4 |
|    5 |          9 |    9 |          5 |
+------+------------+------+------------+

ROW_NUMBER() over_clause

Возвращает номер текущей строки в пределах ее раздела. Номера строк находятся в диапазоне от 1 до количества строк раздела.

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

ROW_NUMBER() присваивает равнозначным строкам разные номера строк. Чтобы присвоить равнозначным строкам одно и то же значение, используйте RANK() или DENSE_RANK(). Пример см. в описании функции RANK().

over_clause описывается в разделе 14.20.2, «Функции окон и синтаксис».