Функции окон

1. Введение в функции окон

Функция окна — это SQL-функция, где входные значения берутся из «окна» одной или нескольких строк в наборе результатов оператора SELECT.

Функции окон отличаются от скалярных функций и агрегирующих функций наличием клаузы OVER. Если функция содержит клаузу OVER, то это функция окна. Если клаузы OVER нет, то это обычная агрегирующая или скалярная функция. Функции окон также могут иметь клаузу FILTER между функцией и клаузой OVER.

Синтаксис функции окна выглядит следующим образом:

вызов-функции-окна: скрыть

CREATE TABLE t0(x INTEGER PRIMARY KEY, y TEXT);
INSERT INTO t0 VALUES (1, 'aaa'), (2, 'ccc'), (3, 'bbb');

-- The following SELECT statement returns:
-- 
--   x | y | row_number
-----------------------
--   1 | aaa | 1         
--   2 | ccc | 3         
--   3 | bbb | 2         
-- 
SELECT x, y, row_number() OVER (ORDER BY y) AS row_number FROM t0 ORDER BY x;

SELECT x, y, row_number() OVER win1, rank() OVER win2
FROM t0
WINDOW win1 AS (ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
       win2 AS (PARTITION BY y ORDER BY x)
ORDER BY x;

2. Агрегирующие оконные функции

Примеры в этом разделе предполагают, что база данных заполнена следующим образом:

CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
INSERT INTO t1 VALUES   (1, 'A', 'one'  ),
                        (2, 'B', 'two'  ),
                        (3, 'C', 'three'),
                        (4, 'D', 'one'  ),
                        (5, 'E', 'two'  ),
                        (6, 'F', 'three'),
                        (7, 'G', 'one'  );

Агрегирующая оконная функция похожа на обычную агрегатную функцию, за исключением того, что её добавление в запрос не изменяет количество возвращаемых строк. Вместо этого, для каждой строки результат агрегирующей оконной функции такой же, как если бы соответствующая агрегатная функция была применена ко всем строкам в "оконной рамке", заданной в предложении OVER.

-- The following SELECT statement returns:
-- 
--   a | b | group_concat
-------------------------
--   1 | A | A.B         
--   2 | B | A.B.C       
--   3 | C | B.C.D       
--   4 | D | C.D.E       
--   5 | E | D.E.F       
--   6 | F | E.F.G       
--   7 | G | F.G         
-- 
SELECT a, b, group_concat(b, '.') OVER (
  ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS group_concat FROM t1;

В примере выше оконная рамка состоит из всех строк между предыдущей строкой ("1 PRECEDING") и последующей строкой ("1 FOLLOWING"), включая эти строки, где строки отсортированы в соответствии с предложением ORDER BY в window-defn (в этом случае "ORDER BY a"). Например, рамка для строки с (a=3) состоит из строк (2, 'B', 'two'), (3, 'C', 'three') и (4, 'D', 'one'). Поэтому результат group_concat(b, '.') для этой строки — 'B.C.D'.

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

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

Для вычисления оконных функций результат набора запроса делится на один или несколько "разделов". Раздел состоит из всех строк, у которых одинаковое значение для всех терминов предложения PARTITION BY в window-defn. Если предложения PARTITION BY нет, весь результат набора запроса является одним разделом. Обработка оконных функций выполняется отдельно для каждого раздела.

Например:

-- The following SELECT statement returns:
-- 
--   c     | a | b | group_concat
---------------------------------
--   one   | 1 | A | A.D.G       
--   one   | 4 | D | D.G         
--   one   | 7 | G | G           
--   three | 3 | C | C.F         
--   three | 6 | F | F           
--   two   | 2 | B | B.E         
--   two   | 5 | E | E           
-- 
SELECT c, a, b, group_concat(b, '.') OVER (
  PARTITION BY c ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS group_concat
FROM t1 ORDER BY c, a;

В запросе выше предложение "PARTITION BY c" разбивает результат набора на три раздела. Первый раздел содержит три строки с c=='one'. Второй раздел содержит две строки с c=='three', а третий — две строки с c=='two'.

В приведённом примере все строки для каждого раздела объединяются в окончательном выводе. Это происходит потому, что предложение PARTITION BY является префиксом предложения ORDER BY в целом запросе. Но это необязательно. Раздел может состоять из строк, разбросанных по результатам запроса произвольно. Например:

-- The following SELECT statement returns:
-- 
--   c     | a | b | group_concat
---------------------------------
--   one   | 1 | A | A.D.G       
--   two   | 2 | B | B.E         
--   three | 3 | C | C.F         
--   one   | 4 | D | D.G         
--   two   | 5 | E | E           
--   three | 6 | F | F           
--   one   | 7 | G | G           
-- 
SELECT c, a, b, group_concat(b, '.') OVER (
  PARTITION BY c ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS group_concat
FROM t1 ORDER BY a;

2.2. Спецификации рамок

frame-spec определяет, какие выходные строки считываются агрегирующей оконной функцией. frame-spec состоит из четырёх частей:

• Тип рамки — ROWS, RANGE или GROUPS,
• Начальная граница рамки,
• Конечная граница рамки,
• Предложение EXCLUDE.

Вот подробности синтаксиса:

frame-spec: скрыть

Конечная граница кадра может быть опущена (если ключевые слова BETWEEN и AND, окружающие начальную границу кадра, также опущены), в этом случае конечная граница кадра по умолчанию устанавливается в CURRENT ROW.

Если тип кадра — RANGE или GROUPS, то строки с одинаковыми значениями для всех выражений ORDER BY считаются «сравнимыми». Или, если нет терминов ORDER BY, все строки являются сравнимыми. Сравнимые строки всегда находятся в рамках одного кадра.

По умолчанию frame-spec:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS

По умолчанию это означает, что агрегатные оконные функции считывают все строки с начала раздела до и включая текущую строку и её сравнимые строки. Это подразумевает, что строки, имеющие одинаковые значения для всех выражений ORDER BY, также будут иметь одинаковое значение для результата оконной функции (так как оконный кадр одинаков). Например:

-- The following SELECT statement returns:
-- 
--   a | b | c | group_concat
-----------------------------
--   1 | A | one   | A.D.G       
--   2 | B | two   | A.D.G.C.F.B.E
--   3 | C | three | A.D.G.C.F   
--   4 | D | one   | A.D.G       
--   5 | E | two   | A.D.G.C.F.B.E
--   6 | F | three | A.D.G.C.F   
--   7 | G | one   | A.D.G       
-- 
SELECT a, b, c,
       group_concat(b, '.') OVER (ORDER BY c) AS group_concat
FROM t1 ORDER BY a;

2.2.1. Тип кадра

Существует три типа кадров: ROWS, GROUPS и RANGE. Тип кадра определяет, как измеряются начальная и конечная границы кадра.

• ROWS: Тип кадра ROWS означает, что начальная и конечная границы кадра определяются подсчётом отдельных строк относительно текущей строки.

• GROUPS: Тип кадра GROUPS означает, что начальные и конечные границы определяются подсчётом «групп» относительно текущей группы. «Группа» — это набор строк, у которых одинаковые значения для всех терминов в оконном операторе ORDER BY. («Одинаковые» означает, что оператор IS верен при сравнении двух значений.) Другими словами, группа состоит из всех сравнимых строк строки.

• RANGE: Тип кадра RANGE требует, чтобы в операторе ORDER BY оконной функции был ровно один термин. Назовём этот термин «X». С типом кадра RANGE элементы кадра определяются путём вычисления значения выражения X для всех строк в разделе и выделения тех строк, для которых значение X находится в определённом диапазоне значений X для текущей строки. См. описание в разделе "<expr> PRECEDING" ниже для подробностей.

Типы кадров ROWS и GROUPS схожи тем, что оба определяют объём кадра путём подсчёта относительно текущей строки. Разница в том, что ROWS считает отдельные строки, а GROUPS — сравнимые группы. Тип кадра RANGE отличается. Тип кадра RANGE определяет объём кадра, выискивая значения выражения, находящиеся в некотором диапазоне относительно текущей строки.

2.2.2. Границы кадра

Существует пять способов описания начальной и конечной границ кадра:

1. БЕСПРЯЖЕНИЕ ПРЕДШЕСТВУЮЩЕЕ
   Граница фрейма — это первая строка в разделе.

2. <выражение> ПРЕДШЕСТВУЮЩЕЕ
   <выражение> должно быть неотрицательным константным числовым выражением. Граница — это строка, которая находится на <выражение> "единиц" перед текущей строкой. Значение "единиц" здесь зависит от типа фрейма:

   • СТРОКИ → Граница фрейма — это строка, которая находится на <выражение> строк перед текущей строкой, или первая строка раздела, если строк перед текущей строкой меньше <выражение>. <выражение> должно быть целым числом.

   • ГРУППЫ → "Группа" — это набор однородных строк — строк, которые имеют одинаковые значения для каждого элемента в условии ORDER BY. Граница фрейма — это группа, которая находится на <выражение> групп перед группой, содержащей текущую строку, или первая группа раздела, если групп перед текущей строкой меньше <выражение>. Для начальной границы фрейма используется первая строка группы, а для конечной границы фрейма — последняя строка группы. <выражение> должно быть целым числом.

   • ДИАПАЗОН → Для этого варианта условие ORDER BY window-defn должно содержать один элемент. Назовём этот элемент ORDER BY "X". Пусть X_i — значение выражения X для i-й строки в разделе, а X_c — значение X для текущей строки. Неформально, граница диапазона — это первая строка, для которой X_i находится в пределах <выражение> от X_c. Более точно:

     1. Если либо X_i, либо X_c не являются числовыми, то граница — это первая строка, для которой выражение "X_i IS X_c" истинно.
     2. В противном случае, если ORDER BY — ASC, то граница — это первая строка, для которой X_i>=X_c-<выражение>.
     3. В противном случае, если ORDER BY — DESC, то граница — это первая строка, для которой X_i<=X_c+<выражение>.
     Для этого варианта <выражение> не обязательно должно быть целым числом. Оно может быть вещественным числом, если оно постоянно и неотрицательно.
   Описание границы "0 ПРЕДШЕСТВУЮЩИХ" всегда означает то же, что и "ТЕКУЩАЯ СТРОКА".

3. ТЕКУЩАЯ СТРОКА
   Текущая строка. Для фреймов типа RANGE и GROUPS в фрейм также включаются строки-соперники текущей строки, если они не исключены явно с помощью условия EXCLUDE. Это верно независимо от того, используется ли ТЕКУЩАЯ СТРОКА в качестве начальной или конечной границы фрейма.

4. <выражение> СЛЕДУЮЩИХ
   Это то же самое, что и "<выражение> ПРЕДШЕСТВУЮЩИХ", за исключением того, что граница находится на <выражение> единиц после, а не перед текущей строкой.

5. БЕСПРЯЖЕНИЕ СЛЕДУЮЩЕЕ
   Граница фрейма — это последняя строка в разделе.

Конечная граница фрейма не может иметь вид, который находится выше в списке выше, чем начальная граница фрейма.

В следующем примере фрейм окна для каждой строки состоит из всех строк от текущей строки до конца набора, где строки отсортированы в соответствии с "ORDER BY a".

-- The following SELECT statement returns:
-- 
--   c     | a | b | group_concat
---------------------------------
--   one   | 1 | A | A.D.G.C.F.B.E
--   one   | 4 | D | D.G.C.F.B.E 
--   one   | 7 | G | G.C.F.B.E   
--   three | 3 | C | C.F.B.E     
--   three | 6 | F | F.B.E       
--   two   | 2 | B | B.E         
--   two   | 5 | E | E           
-- 
SELECT c, a, b, group_concat(b, '.') OVER (
  ORDER BY c, a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS group_concat
FROM t1 ORDER BY c, a;

2.2.3. Условие EXCLUDE

Необязательное условие EXCLUDE может иметь любой из следующих четырёх видов:

• EXCLUDE NO OTHERS: Это значение по умолчанию. В этом случае из фрейма окна не исключается ни одна строка, как определено его начальной и конечной границами.

• EXCLUDE CURRENT ROW: В этом случае текущая строка исключается из фрейма окна. Строки-соперники текущей строки остаются во фрейме для типов фреймов GROUPS и RANGE.

• EXCLUDE GROUP: В этом случае текущая строка и все другие строки, являющиеся соперниками текущей строки, исключаются из фрейма. При обработке условия EXCLUDE все строки с одинаковыми значениями ORDER BY или все строки в разделе, если нет условия ORDER BY, считаются соперниками, даже если тип фрейма — ROWS.

• EXCLUDE TIES: В этом случае текущая строка входит во фрейм, но соперники текущей строки исключаются.

Следующий пример демонстрирует влияние различных вариантов условия EXCLUDE:

-- The following SELECT statement returns:
-- 
--   c    | a | b | no_others     | current_row | grp       | ties
--  one   | 1 | A | A.D.G         | D.G         |           | A
--  one   | 4 | D | A.D.G         | A.G         |           | D
--  one   | 7 | G | A.D.G         | A.D         |           | G
--  three | 3 | C | A.D.G.C.F     | A.D.G.F     | A.D.G     | A.D.G.C
--  three | 6 | F | A.D.G.C.F     | A.D.G.C     | A.D.G     | A.D.G.F
--  two   | 2 | B | A.D.G.C.F.B.E | A.D.G.C.F.E | A.D.G.C.F | A.D.G.C.F.B
--  two   | 5 | E | A.D.G.C.F.B.E | A.D.G.C.F.B | A.D.G.C.F | A.D.G.C.F.E
-- 
SELECT c, a, b,
  group_concat(b, '.') OVER (
    ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS
  ) AS no_others,
  group_concat(b, '.') OVER (
    ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW
  ) AS current_row,
  group_concat(b, '.') OVER (
    ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP
  ) AS grp,
  group_concat(b, '.') OVER (
    ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES
  ) AS ties
FROM t1 ORDER BY c, a;

2.3. Условие FILTER

filter-clause: скрыть

Если указано предложение FILTER, то в рамку окна включаются только строки, для которых expr истинно. Агрегатное окно по-прежнему возвращает значение для каждой строки, но те, для которых выражение FILTER вычисляется как не истинное, не включаются в рамку окна ни для одной строки. Например:

-- The following SELECT statement returns:
-- 
--   c     | a | b | group_concat
---------------------------------
--   one   | 1 | A | A           
--   two   | 2 | B | A           
--   three | 3 | C | A.C         
--   one   | 4 | D | A.C.D       
--   two   | 5 | E | A.C.D       
--   three | 6 | F | A.C.D.F     
--   one   | 7 | G | A.C.D.F.G   
-- 
SELECT c, a, b, group_concat(b, '.') FILTER (WHERE c!='two') OVER (
  ORDER BY a
) AS group_concat
FROM t1 ORDER BY a;

3. Встроенные функции окон

Помимо агрегатных функций окон, SQLite поддерживает набор встроенных функций окон, основанных на функциях окон PostgreSQL.

Встроенные функции окон обрабатывают любой оператор PARTITION BY так же, как и агрегатные функции окон — каждая строка, для которой производится выборка, относится к определённому разбиению, и каждое разбиение обрабатывается отдельно. Способ, которым любой оператор ORDER BY влияет на каждую встроенную функцию окна, описан ниже. Некоторые из функций окон (rank(), dense_rank(), percent_rank() и ntile()) используют понятие «группы равных элементов» (строки в рамках одного разбиения, которые имеют одинаковые значения для всех выражений ORDER BY). В этих случаях не имеет значения, указывает ли frame-spec ROWS, GROUPS или RANGE. Для целей обработки встроенных функций окон строки с одинаковыми значениями для всех выражений ORDER BY считаются равными, независимо от типа фрейма.

Большинство встроенных функций окон игнорируют frame-spec, исключения составляют first_value(), last_value() и nth_value(). Указание оператора FILTER в вызове встроенной функции окна является синтаксической ошибкой.

SQLite поддерживает следующие 11 встроенных функций окон:

row_number()

Номер строки в текущем разбиении. Строки нумеруются, начиная с 1, в порядке, определённом оператором ORDER BY в определении окна, или в произвольном порядке в противном случае.

rank()

Номер строки первого равного элемента в каждой группе — ранг текущей строки с разрывами. Если оператор ORDER BY отсутствует, все строки считаются равными, и эта функция всегда возвращает 1.

dense_rank()

Номер группы равных элементов текущей строки в её разбиении — ранг текущей строки без разрывов. Строки нумеруются, начиная с 1, в порядке, определённом оператором ORDER BY в определении окна. Если оператор ORDER BY отсутствует, все строки считаются равными, и эта функция всегда возвращает 1.

percent_rank()

Несмотря на название, эта функция всегда возвращает значение от 0,0 до 1,0, равное (ранг - 1)/(строки-в-разбиении - 1), где ранг — значение, возвращаемое встроенной функцией окна rank(), а строки-в-разбиении — общее количество строк в разбиении. Если в разбиении только одна строка, эта функция возвращает 0,0.

cume_dist()

Кумулятивное распределение. Вычисляется как номер-строки/строки-в-разбиении, где номер-строки — значение, возвращаемое row_number() для последнего равного элемента в группе, а строки-в-разбиении — количество строк в разбиении.

ntile(N)

Аргумент N обрабатывается как целое число. Эта функция делит разбиение на N групп как можно равномернее и присваивает целое число от 1 до N каждой группе в порядке, определённом оператором ORDER BY, или в произвольном порядке в противном случае. Если необходимо, большие группы появляются первыми. Эта функция возвращает целое значение, присвоенное группе, к которой относится текущая строка.

lag(expr)
lag(expr, offset)
lag(expr, offset, default)

Первый вариант функции lag() возвращает результат вычисления выражения expr для предыдущей строки в разбиении. Или, если предыдущей строки нет (потому что текущая строка — первая), NULL.

Если аргумент offset указан, он должен быть неотрицательным целым числом. В этом случае возвращаемое значение — результат вычисления expr для строки, которая находится на расстоянии offset строк перед текущей строкой в разбиении. Если offset равен 0, то expr вычисляется для текущей строки. Если нет строки, находящейся на расстоянии offset строк перед текущей строкой, возвращается NULL.

Если также указан default, он возвращается вместо NULL, если строка, определённая offset, не существует.

lead(expr)
lead(expr, offset)
lead(expr, offset, default)

Первый вариант функции lead() возвращает результат вычисления выражения expr для следующей строки в разбиении. Или, если следующей строки нет (потому что текущая строка — последняя), NULL.

Если аргумент offset указан, он должен быть неотрицательным целым числом. В этом случае возвращаемое значение — результат вычисления expr для строки, которая находится на расстоянии offset строк после текущей строки в разбиении. Если offset равен 0, то expr вычисляется для текущей строки. Если нет строки, находящейся на расстоянии offset строк после текущей строки, возвращается NULL.

Если также указан default, он возвращается вместо NULL, если строка, определённая offset, не существует.

first_value(expr)

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

last_value(expr)

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

nth_value(expr, N)

Эта встроенная функция окна вычисляет фрейм окна для каждой строки так же, как и агрегатная функция окна. Она возвращает значение expr, вычисленное для строки с номером N в фрейме окна. Строки нумеруются внутри фрейма окна, начиная с 1, в порядке, определённом оператором ORDER BY, если он присутствует, или в произвольном порядке в противном случае. Если в разбиении нет N-й строки, возвращается NULL.

В примерах в этом разделе используется таблица T1, определённая ранее, а также следующая таблица T2:

CREATE TABLE t2(a, b);
INSERT INTO t2 VALUES('a', 'one'),
                     ('a', 'two'),
                     ('a', 'three'),
                     ('b', 'four'),
                     ('c', 'five'),
                     ('c', 'six');

Следующий пример демонстрирует поведение пяти функций ранжирования — row_number(), rank(), dense_rank(), percent_rank() и cume_dist().

-- The following SELECT statement returns:
-- 
--   a | row_number | rank | dense_rank | percent_rank | cume_dist
------------------------------------------------------------------
--   a |          1 |    1 |          1 |          0.0 |       0.5
--   a |          2 |    1 |          1 |          0.0 |       0.5
--   a |          3 |    1 |          1 |          0.0 |       0.5
--   b |          4 |    4 |          2 |          0.6 |       0.66
--   c |          5 |    5 |          3 |          0.8 |       1.0
--   c |          6 |    5 |          3 |          0.8 |       1.0
-- 
SELECT a                        AS a,
       row_number() OVER win    AS row_number,
       rank() OVER win          AS rank,
       dense_rank() OVER win    AS dense_rank,
       percent_rank() OVER win  AS percent_rank,
       cume_dist() OVER win     AS cume_dist
FROM t2
WINDOW win AS (ORDER BY a);

В примере ниже используется ntile() для разделения шести строк на две группы (вызов ntile(2)) и на четыре группы (вызов ntile(4)). Для ntile(2) по три строки присваиваются каждой группе. Для ntile(4) две группы по две и две группы по одной. Более крупные группы из двух появляются первыми.

-- The following SELECT statement returns:
-- 
--   a | b     | ntile_2 | ntile_4
----------------------------------
--   a | one   |       1 |       1
--   a | two   |       1 |       1
--   a | three |       1 |       2
--   b | four  |       2 |       2
--   c | five  |       2 |       3
--   c | six   |       2 |       4
-- 
SELECT a                        AS a,
       b                        AS b,
       ntile(2) OVER win        AS ntile_2,
       ntile(4) OVER win        AS ntile_4
FROM t2
WINDOW win AS (ORDER BY a);

Следующий пример демонстрирует lag(), lead(), first_value(), last_value() и nth_value(). frame-spec игнорируется как lag(), так и lead(), но учитывается first_value(), last_value() и nth_value().

-- The following SELECT statement returns:
-- 
--   b | lead | lag  | first_value | last_value | nth_value_3
-------------------------------------------------------------
--   A | C    | NULL | A           | A          | NULL       
--   B | D    | A    | A           | B          | NULL       
--   C | E    | B    | A           | C          | C          
--   D | F    | C    | A           | D          | C          
--   E | G    | D    | A           | E          | C          
--   F | n/a  | E    | A           | F          | C          
--   G | n/a  | F    | A           | G          | C          
-- 
SELECT b                          AS b,
       lead(b, 2, 'n/a') OVER win AS lead,
       lag(b) OVER win            AS lag,
       first_value(b) OVER win    AS first_value,
       last_value(b) OVER win     AS last_value,
       nth_value(b, 3) OVER win   AS nth_value_3
FROM t1
WINDOW win AS (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

4. Цепочки окон

Цепочка окон — это сокращение, которое позволяет определить одно окно в терминах другого. В частности, сокращение позволяет новому окну неявно скопировать операторы PARTITION BY и необязательно ORDER BY базового окна. Например, в следующем:

SELECT group_concat(b, '.') OVER (
  win ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
FROM t1
WINDOW win AS (PARTITION BY a ORDER BY c)

окно, используемое функцией group_concat(), эквивалентно "PARTITION BY a ORDER BY c ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW". Для использования цепочки окон должны выполняться все следующие условия:

• Новое определение окна не должно включать оператор PARTITION BY. Оператор PARTITION BY, если он есть, должен быть предоставлен спецификацией базового окна.

• Если у базового окна есть оператор ORDER BY, он копируется в новое окно. В этом случае новое окно не должно указывать оператор ORDER BY. Если у базового окна нет оператора ORDER BY, он может быть указан в новом определении окна.

• Базовое окно не может указывать спецификацию фрейма. Спецификация фрейма может быть указана только в новом определении окна.

Два фрагмента SQL ниже похожи, но не полностью эквивалентны, так как последний фрагмент завершится с ошибкой, если определение окна «win» содержит спецификацию фрейма.

SELECT group_concat(b, '.') OVER win ...
SELECT group_concat(b, '.') OVER (win) ...

5. Пользовательские агрегатные функции окон

Пользовательские агрегатные функции окон могут быть созданы с помощью API sqlite3_create_window_function(). Реализация агрегатной функции окна очень похожа на обычную агрегатную функцию. Любая пользовательская агрегатная функция окна также может быть использована как обычная агрегатная функция. Для реализации пользовательской агрегатной функции окна приложение должно предоставить четыре функции обратного вызова:

Следующий код C реализует простую агрегатную функцию окна под названием sumint(). Она работает так же, как и встроенная функция sum(), за исключением того, что она генерирует исключение, если получает аргумент, не являющийся целым числом.

/*
** xStep for sumint().
**
** Add the value of the argument to the aggregate context (an integer).
*/
static void sumintStep(
  sqlite3_context *ctx,
  int nArg,
  sqlite3_value *apArg[]
){
  sqlite3_int64 *pInt;

  assert( nArg==1 );
  if( sqlite3_value_type(apArg[0])!=SQLITE_INTEGER ){
    sqlite3_result_error(ctx, "invalid argument", -1);
    return;
  }
  pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, sizeof(sqlite3_int64));
  if( pInt ){
    *pInt += sqlite3_value_int64(apArg[0]);
  }
}

/*
** xInverse for sumint().
**
** This does the opposite of xStep() - subtracts the value of the argument
** from the current context value. The error checking can be omitted from
** this function, as it is only ever called after xStep() (so the aggregate
** context has already been allocated) and with a value that has already
** been passed to xStep() without error (so it must be an integer).
*/
static void sumintInverse(
  sqlite3_context *ctx,
  int nArg,
  sqlite3_value *apArg[]
){
  sqlite3_int64 *pInt;
  assert( sqlite3_value_type(apArg[0])==SQLITE_INTEGER );
  pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, sizeof(sqlite3_int64));
  *pInt -= sqlite3_value_int64(apArg[0]);
}

/*
** xFinal for sumint().
**
** Return the current value of the aggregate window function. Because
** this implementation does not allocate any resources beyond the buffer
** returned by sqlite3_aggregate_context, which is automatically freed
** by the system, there are no resources to free. And so this method is
** identical to xValue().
*/
static void sumintFinal(sqlite3_context *ctx){
  sqlite3_int64 res = 0;
  sqlite3_int64 *pInt;
  pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, 0);
  if( pInt ) res = *pInt;
  sqlite3_result_int64(ctx, res);
}

/*
** xValue for sumint().
**
** Return the current value of the aggregate window function.
*/
static void sumintValue(sqlite3_context *ctx){
  sqlite3_int64 res = 0;
  sqlite3_int64 *pInt;
  pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, 0);
  if( pInt ) res = *pInt;
  sqlite3_result_int64(ctx, res);
}

/*
** Register sumint() window aggregate with database handle db.
*/
int register_sumint(sqlite3 *db){
  return sqlite3_create_window_function(db, "sumint", 1, SQLITE_UTF8, 0,
      sumintStep, sumintFinal, sumintValue, sumintInverse, 0
  );
}

Следующий пример использует функцию sumint(), реализованную с помощью приведенного выше кода C. Для каждой строки окно состоит из предыдущей строки (если есть), текущей строки и следующей строки (если есть):

CREATE TABLE t3(x, y);
INSERT INTO t3 VALUES('a', 4),
                     ('b', 5),
                     ('c', 3),
                     ('d', 8),
                     ('e', 1);

-- Assuming the database is populated using the above script, the 
-- following SELECT statement returns:
-- 
--   x | sum_y
--------------
--   a | 9    
--   b | 12   
--   c | 16   
--   d | 12   
--   e | 9    
-- 
SELECT x, sumint(y) OVER (
  ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS sum_y
FROM t3 ORDER BY x;

При обработке запроса выше SQLite вызывает функции обратного вызова sumint следующим образом:

1. xStep(4) - добавить "4" в текущее окно.
2. xStep(5) - добавить "5" в текущее окно.
3. xValue() - вызвать xValue() для получения значения sumint() для строки с (x='a'). Текущее окно содержит значения 4 и 5, поэтому результат равен 9.
4. xStep(3) - добавить "3" в текущее окно.
5. xValue() - вызвать xValue() для получения значения sumint() для строки с (x='b'). Текущее окно содержит значения 4, 5 и 3, поэтому результат равен 12.
6. xInverse(4) - удалить "4" из окна.
7. xStep(8) - добавить "8" в текущее окно. Теперь окно содержит значения 5, 3 и 8.
8. xValue() - вызвано для получения значения для строки с (x='c'). В этом случае, 16.
9. xInverse(5) - удалить значение "5" из окна.
10. xStep(1) - добавить значение "1" в окно.
11. xValue() - вызвано для получения значения для строки (x='d').
12. xInverse(3) - удалить значение "3" из окна. Теперь окно содержит только значения 8 и 1.
13. xFinal() - вызвано для освобождения выделенных ресурсов и получения значения для строки (x='e'). 9. .

Если пользователь прервет выполнение запроса, вызвав sqlite3_reset() или sqlite3_finalize() для дескриптора оператора, прежде чем SQLite вызовет xFinal(), то xFinal() вызывается автоматически изнутри вызова sqlite3_reset() или sqlite3_finalize() для освобождения выделенных ресурсов, даже если значение не требуется. В этом случае любое возвращенное ошибку реализацией xFinal игнорируется.

6. История

Поддержка функций окна была впервые добавлена в SQLite с выпуском версии 3.25.0 (2018-09-15). Разработчики SQLite использовали документацию функций окна PostgreSQL в качестве основного руководства по поведению функций окна. Было выполнено множество тестовых случаев с PostgreSQL для обеспечения того, что функции окна работают одинаково как в SQLite, так и в PostgreSQL.

В SQLite версии 3.28.0 (2019-04-16) поддержка функций окон была расширена, чтобы включить предложение EXCLUDE, типы фреймов GROUPS, цепочки окон и поддержку границ «<expr> PRECEDING» и «<expr> FOLLOWING» в фреймах RANGE.

Эта страница была последняя изменена 01.10.2024 13:46:27 UTC