1. Введение

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

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

Синтаксис для функции окна похож на это:

window-function-invocation: hide

В отличие от обычных функций, функции окна не могут использовать ключевое слово DISTINCT. Кроме того, функции окна могут появиться только в наборе результатов и в пункте ORDER BY оператора SELECT.

Функции окна существуют двух вариантов: совокупные и встроенные. Каждая совокупная функция окна может также работать обычной агрегатной функцией, просто опуская пункты FILTER и OVER. Кроме того, все встроенные агрегатные функции SQLite могут использоваться в качестве совокупной функции окна, добавляя соответствующий пункт OVER. Запросы могут зарегистрировать новые совокупные функции окна, используя sqlite3_create_window_function(). Встроенные функции окна, однако, требуют обработки особого случая в планировщике запроса и следовательно новые функции окна, которые показывают исключительные свойства, найденные во встроенных функциях окна, не могут быть добавлены приложением.

Вот пример, использующий встроенную функцию окна row_number():

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;

row_number() назначает последовательные целые числа на каждую строку в порядке пункта "ORDER BY" в window-defn (в этом случае "ORDER BY y"). Обратите внимание на то, что это не затрагивает порядок, в котором результаты возвращены из полного запроса. Порядком окончательного результата все еще управляет пункт ORDER BY, приложенный к оператору SELECT (в этом случае "ORDER BY x").

Названные пункты window-defn могут также быть добавлены к оператору SELECT, используя пункт WINDOW и затем упомянуты по имени в рамках вызовов функции окна. Например, следующий оператор SELECT содержит два названных пункта window-defs, "win1" и "win2":

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;

Пункт WINDOW, когда есть, появляется после любого пункта HAVING, но перед любым ORDER BY.

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'  );

Совокупная функция окна подобна обычной агрегатной функции, кроме добавления, что она не изменяет количество возвращенных строк. Вместо этого для каждой строки результат совокупной функции окна состоит в том, как будто соответствующей совокупностью управляли по всем строкам в "window frame", определенной пунктом 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: hide

Заканчивающая граница структуры может быть опущена (если ключевые слова 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 = true, сравнивая два значения. Другими словами, группа состоит из всех пэров строки.

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

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

2.2.2. Границы структуры

Есть пять способов описать старт и окончание границ структуры:

1. UNBOUNDED PRECEDING
   Граница структуры это первая строка в разделе.

2. <expr> PRECEDING
   <expr> должен быть неотрицательным постоянным числовым выражением. Граница это строка, которая является <expr> "единиц" до текущей строки. Значение "единиц" здесь зависит от типа структуры:

   • ROWS → граница структуры является строкой, которая является <expr> строками перед текущей строкой или первой строкой разделения, если есть меньше, чем <expr> строк перед текущей. <expr> должен быть целым числом.

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

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

     1. Если X_i или X_c не число, то граница это первая строка, для которой выражение "X_i IS X_c " = true.
     2. Иначе если ORDER BY = ASC, граница это первая строка, для которой X_i>=X_c -<expr>.
     3. Иначе если ORDER BY = DESC, граница это первая строка, для которой X_i<=X_c +<expr>.
     Для этой формы <expr> <expr> не должно быть целое число. Это может быть оценено к вещественному числу, пока это постоянно и неотрицательно.
   Граничное описание "0 PRECEDING" всегда означает то же самое как "CURRENT ROW".

3. CURRENT ROW
   Текущая строка. Для типов структуры RANGE и GROUPS, пэры текущей строки также включены в структуру, если определенно не исключены пунктом EXCLUDE. Это верно независимо от того, используется ли CURRENT ROW в качестве начала или окончания границы структуры.

4. <expr> FOLLOWING
   Это совпадает с" <expr> PRECEDING" за исключением того, что граница <expr> единиц после текущей, а не перед текущей строкой.

5. UNBOUNDED FOLLOWING
   Граница структуры это последняя строка в разделе.

Заканчивающая граница структуры может не принять форму, которая кажется выше в вышеупомянутом списке, чем стартовая граница структуры.

В следующем примере рамка окна для каждой строки состоит из всех строк от текущей строки до конца набора, где строки сортированы согласно "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: hide

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

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

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

dense_rank()

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

percent_rank()

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

cume_dist()

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

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

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

6. История

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

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