小. 快速. 可靠.
选择任意三个.
视窗功能

1. Introduction to Window Functions

窗口函数是一种SQL函数,其中输入值是从SELECT语句的结果集中的一个或多个行的"窗口"中获取的.

通过OVER子句,窗口函数与其他SQL函数有所区别. 如果函数具有OVER子句,则它是窗口函数. 如果它缺少OVER子句,则它是一个普通的聚合或标量函数. 窗口函数在函数和OVER子句之间也可能有FILTER子句.

窗口函数的语法如下:

window-function-invocation:

syntax diagram window-function-invocation

expr:

filter-clause:

syntax diagram filter-clause

window-defn:

与普通函数不同,窗口函数不能使用DISTINCT关键字. 而且,Window函数只能出现在结果集中和SELECT语句的ORDER BY子句中.

窗口函数有两种: 聚合窗口函数内置窗口函数 . 每个聚合窗口函数也可以像普通的聚合函数一样工作,只需省略OVER和FILTER子句即可. 此外,通过添加适当的OVER子句,SQLite的所有内置聚合函数都可以用作聚合窗口函数. 应用程序可以使用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 y")中,按照" ORDER BY"子句的顺序为每行分配连续的整数. 请注意,这不会影响从整个查询返回结果的顺序. 最终输出的顺序仍由附加到SELECT语句的ORDER BY子句(在本例中为" ORDER BY x")控制.

也可以使用WINDOW子句将命名的window-defn子句添加到SELECT语句中,然后在窗口函数调用中通过名称进行引用. 例如,以下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. Aggregate Window Functions

本节中的所有示例均假定数据库的填充如下:

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")之间的所有行组成,其中包括所有行,这些行根据window-defn中的ORDER BY子句进行排序 (在这种情况下为" ORDER BY a"). 例如,(a = 3)的行的帧由(2,'B','two'),(3,'C','three')和(4,'D','one '). 因此,该行的group_concat(b,'.')的结果为'BCD'.

SQLite的所有聚合函数都可以用作聚合窗口函数. 也可以创建用户定义的聚合窗口函数 .

2.1. The PARTITION BY Clause

为了计算窗口函数,将查询的结果集分为一个或多个"分区". 分区由window-defn中的PARTITION BY子句的所有项具有相同值的所有行组成. 如果没有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 Specifications

帧规范确定聚合窗口函数读取哪些输出行. 框架规格包括四个部分:

以下是语法详细信息:

frame-spec:

syntax diagram frame-spec

expr:

可以省略结束帧边界(如果也省略围绕开始帧边界的BETWEEN和AND关键字),在这种情况下,结束帧边界默认为CURRENT ROW.

如果帧类型是RANGE或GROUPS,则所有ORDER BY表达式具有相同值的行将被视为"对等". 或者,如果没有ORDER BY术语,则所有行都是对等体. 对等点始终在同一帧内.

默认的帧规格为:

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. Frame Type

共有三种帧类型:ROWS,GROUPS和RANGE. 帧类型决定如何测量帧的开始和结束边界.

ROWS和GROUPS帧类型的相似之处在于,它们都通过相对于当前行进行计数来确定帧的范围. 区别在于,ROWS计算单个行,而GROUPS计算对等组. RANGE帧类型不同. RANGE帧类型通过查找相对于当前行的某个值带内的表达式值来确定帧的范围.

2.2.2. Frame Boundaries

有五种描述开始和结束帧边界的方法:

  1. 无限制的先例
    框架边界是分区中的第一行.

  2. <expr>开始
    <expr>必须是非负常数数值表达式. 边界是当前行之前<expr>"单位"的行. 这里"单位"的含义取决于帧类型:

    • ROWS→帧边界是当前行之前<expr>行的行,如果当前行之前少于<expr>行,则为分区的第一行. <expr>必须为整数.

    • GROUPS→ "组"是一组对等行-对ORDER BY子句中的每个术语都具有相同值的行. 帧边界是包含当前行的组之前的<expr>组的组,如果当前行之前的<expr>组少于该组,则为分区的第一组. 对于帧的开始边界,使用组的第一行,对于帧的结束边界,使用组的最后行. <expr>必须为整数.

    • RANGE→对于这种形式, window-defn的ORDER BY子句必须有一个术语. 将该ORDER BY术语称为" X". 令X i为分区中第i行的X表达式的值,令X c为当前行中X的值. 非正式地,RANGE边界是X i在X c的<expr>内的第一行. 更确切地说:

      1. 如果X i或X c为非数字,则边界为表达式" X i IS X c "为真的第一行.
      2. 否则,如果ORDER BY是ASC,则边界是X i > = X c- <expr>的第一行.
      3. 否则,如果ORDER BY是DESC,则边界是X i <= X c- <expr>的第一行.
      对于这种形式,<expr>不必为整数. 只要它是常数且为非负数,它就可以计算为实数.
    The boundary description "0 PRECEDING" always means the same thing as "CURRENT ROW".
  3. 当前行
    当前行. 对于RANGE和GROUPS帧类型,除非EXCLUDE子句明确排除,否则当前行的对等项也包括在该帧中. 无论是否将CURRENT ROW用作开始或结束帧边界,这都是正确的.

  4. <expr>正在关注
    除了边界是当前行之后而不是当前行之前的<expr>个单位之外,这与" <expr> PRECEDING"相同.

  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. The EXCLUDE Clause

可选的EXCLUDE子句可以采用以下四种形式中的任何一种:

下面的示例演示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. The FILTER Clause

filter-clause:

syntax diagram filter-clause

expr:

is true are included in the window frame. 如果提供了FILTER子句,则窗口框架中仅包含为true的行. 聚合窗口仍然为每行返回一个值,但是FILTER表达式得出的结果不是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. Built-in Window Functions

除了聚合窗口函数外,SQLite还具有一组基于PostgreSQL支持的内置窗口函数.

内置窗口函数采用与聚合窗口函数相同的方式支持任何PARTITION BY子句-每个选定的行都分配给一个分区,并且每个分区都被单独处理. 下面介绍任何ORDER BY子句影响每个内置窗口函数的方式. 某些窗口函数(rank(),deny_rank(),percent_rank()和ntile())使用"对等组"的概念(同一分区内的行对所有ORDER BY表达式具有相同的值). 在这些情况下, 帧规范是否指定ROWS,GROUPS或RANGE都没有关系. 出于内置窗口函数处理的目的,所有ORDER BY表达式具有相同值的行被视为对等体,而与帧类型无关.

Most built-in window functions ignore the frame-spec, the exceptions being first_value(), last_value() and nth_value(). It is a syntax error to specify a FILTER clause as part of a built-in window function invocation.

SQLite支持以下11种内置窗口功能:

row_number()

当前分区内的行号. 行以窗口定义中ORDER BY子句定义的顺序从1开始编号,否则以任意顺序编号.

rank()

每个组中第一个对等方的row_number()-当前行的行距(带间隔). 如果没有ORDER BY子句,则所有行均被视为对等,并且此函数始终返回1.

dense_rank()

当前行在其分区内的对等组的编号-当前行的级别(不带空格). 分区按窗口定义中ORDER BY子句定义的顺序从1开始编号. 如果没有ORDER BY子句,则所有行均被视为对等,并且此函数始终返回1.

percent_rank()

- 1)/( - 1), where is the value returned by built-in window function rank() and is the total number of rows in the partition. 尽管有名称,此函数始终返回介于0.0和1.0之间的值,该值等于( -1)/( -1),其中是内置窗口函数rank()返回的值,而是分区中的总行数. 如果分区仅包含一行,则此函数返回0.0.

cume_dist()

累积分布. / , where is the value returned by row_number() for the last peer in the group and the number of rows in the partition. 计算为 / ,其中是row_number()返回的组中最后一个对等方的值, 表示数.

ntile(N)

is handled as an integer. 参数作为整数处理. to each group, in the order defined by the ORDER BY clause, or in arbitrary order otherwise. 此函数将分区尽可能均匀地划分为N个组,并按照ORDER BY子句定义的顺序或其他任意顺序为每个组分配1到之间的整数. 如有必要,首先出现较大的群体. 此函数返回分配给当前行所属组的整数值.

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

against the previous row in the partition. lag()函数的第一种形式返回针对分区中的前一行评估表达式的结果. 或者,如果没有上一行(因为当前行是第一行),则为NULL.

argument is provided, then it must be a non-negative integer. 如果提供了参数,则它必须是一个非负整数. against the row rows before the current row within the partition. 在这种情况下,返回的值是针对分区中当前行之前的行行评估的结果. is 0, then is evaluated against the current row. 如果为0,则针对当前行评估 . rows before the current row, NULL is returned. 如果当前行之前没有行行,则返回NULL.

如果还提供了 ,则如果由标识的行不存在,则返回它而不是NULL.

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

against the next row in the partition. lead()函数的第一种形式针对分区中的下一行返回对表达式求值的结果. 或者,如果没有下一行(因为当前行是最后一行),则为NULL.

argument is provided, then it must be a non-negative integer. 如果提供了参数,则它必须是一个非负整数. against the row rows after the current row within the partition. 在这种情况下,返回的值是针对分区中当前行之后的行行评估的结果. is 0, then is evaluated against the current row. 如果为0,则针对当前行评估 . rows after the current row, NULL is returned. 如果当前行之后没有行行,则返回NULL.

如果还提供了 ,则如果由标识的行不存在,则返回它而不是NULL.

first_value(expr)

该内置的窗口函数以与聚合窗口函数相同的方式计算每一行的窗口框架. evaluated against the first row in the window frame for each row. 它针对每一行返回针对窗口框架中第一行评估的值.

last_value(expr)

该内置的窗口函数以与聚合窗口函数相同的方式计算每一行的窗口框架. evaluated against the last row in the window frame for each row. 它返回针对每行在窗口框架中的最后一行评估的的值.

nth_value(expr, N)

该内置的窗口函数以与聚合窗口函数相同的方式计算每一行的窗口框架. evaluated against the row of the window frame. 它返回针对窗口框架的第行评估的的值. 在窗口框架中,行从1开始按ORDER BY子句定义的顺序编号(如果存在的话),否则以任意顺序编号. th row in the partition, then NULL is returned. 如果分区中没有第行,则返回NULL.

本节中的所有示例均假设以下数据:

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

以下示例说明了五个排名函数的行为-row_number(),rank(),densage_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(). 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. Window Chaining

窗口链接是一种快捷方式,可以根据另一个窗口定义一个窗口. 具体来说,速记允许新窗口隐式复制基础窗口的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()函数使用的窗口等效于"在未绑定的前导和当前行之间按c行排序". 为了使用窗口链接,必须满足以下所有条件:

下面的两个SQL片段是相似的,但并不完全等效,因为如果窗口" win"的定义包含框架规范,则后者将失败.

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

5. User-Defined Aggregate Window Functions

可以使用sqlite3_create_window_function ()API创建用户定义的聚合窗口函数. 实现聚合窗口功能与普通聚合功能非常相似. 任何用户定义的聚合窗口函数也可以用作普通聚合. 要实现用户定义的聚合窗口函数,应用程序必须提供四个回调函数:

Callback Description
xStep 窗口聚合和旧式聚合功能实现均需要此方法. 调用它可以向当前窗口添加一行. 与要添加的行相对应的函数参数(如果有)将传递到xStep的实现.
xFinal 窗口聚合和旧式聚合功能实现均需要此方法. 调用它以返回聚合的当前值(由当前窗口的内容确定),并释放由先前对xStep的调用分配的任何资源.
xValue 此方法仅是必需的窗口聚合函数,而不是旧式聚合函数实现. 调用它以返回聚合的当前值. 与xFinal不同,该实现不应删除任何上下文.
xInverse 此方法仅是必需的窗口聚合函数,而不是旧式聚合函数实现. 调用它可以从当前窗口中删除一行. 函数参数(如果有)对应于要删除的行.

下面的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. Because
*/
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
  );
}

下面的示例使用上述C代码实现的sumint()函数. 对于每一行,窗口由上一行(如果有),当前行和下一行(同样,如果有)组成:

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()以获得具有(x ='a')的行的sumint()值. 该窗口当前包含值4和5,因此结果为9.
  4. xStep(3) -在当前窗口中添加" 3".
  5. xValue() -调用xValue()以获取具有(x ='b')的行的sumint()值. 该窗口当前包含值4、5和3,因此结果为12.
  6. xInverse(4) -从窗口中删除" 4".
  7. xStep(8) -在当前窗口中添加" 8". 现在,该窗口包含值5、3和8.
  8. xValue() - invoked to obtain the value for the row with (x='c'). In this case, 16.
  9. xInverse(5) -从窗口中删除值" 5".
  10. xStep(1) -将值" 1"添加到窗口.
  11. xValue() -调用以获得行(x ='d')的值.
  12. xInverse(3) -从窗口中删除值" 3". 现在,该窗口仅包含值8和1.
  13. xValue() -调用以获得行(x ='d')的值. 9.

6. History

Windowsite支持最初是在SQLite 版本3.25.0 (2018-09-15)中添加的. SQLite开发人员使用PostgreSQL窗口函数文档作为窗口函数应如何工作的主要参考. 针对PostgreSQL运行了许多测试用例,以确保窗口函数在SQLite和PostgreSQL中以相​​同的方式运行.

在SQLite 版本3.28.0 (2019-04-16)中,对Windows函数的支持进行了扩展,以包括EXCLUDE子句,GROUPS框架类型,窗口链接,并支持其中的" <expr> PRECEDING"和" <expr> FOLLOWING"边界. RANGE帧.

by  ICOPY.SITE