1 窗口函数简介
窗口函数是一个SQL函数,其中输入 值取自 SELECT语句结果集中一行或多行的“窗口”。
窗口功能与 纯量函数 和 聚合函数 由 出现OVER子句。 如果函数有OVER子句, 那么它是一个窗口函数。 如果它缺少OVER子句,那么它是一个 普通聚合或标量函数。 窗口函数也可能 在函数和OVER子句之间有一个FILTER子句。
窗口函数的语法如下所示:
窗口功能调用:
隐藏
窗口-功能
(
快递
)
过滤子句
超过
窗口名称
窗-深
,
*
快递:
显示
文字值
绑定参数
方案名称
.
表格名称
.
列名
一元运算符
快递
快递
二进制运算符
快递
函数-名称
(
函数自变量
)
过滤子句
附加条款
(
快递
)
,
铸件
(
快递
AS公司
类型名称
)
快递
COLLATE公司
排序规则名称
快递
不是
喜欢
GLOB公司
REGEXP公司
匹配
快递
快递
逃生
快递
快递
ISNULL(ISNULL)
不为空
不是
无效的
快递
是
不是
与众不同
发件人
快递
快递
不是
协议双方:
快递
和
快递
快递
不是
英寸
(
select-stmt(选择-测试)
)
快递
,
方案名称
.
表格功能
(
快递
)
表格名称
,
不是
存在
(
select-stmt(选择-测试)
)
案例
快递
什么时候
快递
然后
快递
ELSE公司
快递
结束
提升函数
函数参数:
显示
订购术语:
显示
快递
COLLATE公司
排序规则名称
DESC公司
ASC公司
空
第一
空
最后
文字值:
显示
当前时间戳
数字-文字
串文字
blob-literal(blob-文字)
无效的
真的
错误的
当前时间(_T)
当前日期(_D)
附加条款:
显示
超过
窗口名称
(
基本窗口名称
隔板
由
快递
,
订单
由
订购术语
,
特定帧
)
特定帧:
隐藏
组
协议双方:
无约束
之前
和
无约束
以下
范围
排
无约束
之前
快递
之前
当前
世界其他地区
快递
之前
当前
世界其他地区
快递
以下
快递
之前
当前
世界其他地区
快递
以下
排除
当前
世界其他地区
排除
集团
排除
领带
排除
不
其他
订购术语:
显示
快递
核对
排序规则名称
DESC公司
ASC公司
零点
第一
空
最后
raise函数:
显示
RAISE(升起)
(
回降
,
错误消息
)
IGNORE公司
中止
失败
select-stmt(选择-测试):
显示
与
递归
通用表格表达
,
选择
与众不同
结果列
,
所有
发件人
表或子查询
连词
,
哪里
快递
集团
由
快递
有
快递
,
窗户
窗口名称
AS公司
窗口定义
,
价值观
(
快递
)
,
,
复合运算符
选择-核心
订单
由
极限
快递
订购术语
,
抵消
快递
,
快递
通用表格表达:
显示
表格名称
(
列名
)
AS公司
不是
材料化
(
select-stmt(选择-测试)
)
,
复合运算符:
显示
联合条款:
显示
订购期限:
显示
快递
COLLATE公司
排序规则名称
DESC公司
ASC公司
空
第一
空
最后
结果列:
显示
表或子查询:
显示
方案名称
.
表格名称
AS公司
桌上躺椅
索引
由
索引名称
不是
索引
表格-功能-名称
(
快递
)
,
AS公司
桌上躺椅
(
选择stmt
)
(
表或子查询
)
,
连词
类型名称:
显示
名称
(
签名号码
,
签名号码
)
(
签名号码
)
签名号码:
显示
过滤条款:
隐藏
窗口定义:
隐藏
(
基本翼名称
隔板
由
快递
,
订单
由
订购术语
,
特定帧
)
特定帧:
隐藏
组
协议双方:
无约束
之前
和
无约束
以下
范围
排
无边界的
之前
快递
在前
当前
世界其他地区
快递
之前
当前
世界其他地区
快递
以下
快递
之前
当前
世界其他地区
快递
以下
排除
当前
世界其他地区
排除
集团
排除
领带
排除
不
其他
订购术语:
显示
快递
COLLATE公司
排序规则名称
DESC公司
ASC公司
空
第一
空
最后
与普通函数不同,窗口函数 无法使用DISTINCT关键字。 此外,Window函数只能出现在结果集中和 SELECT语句的ORDER BY子句。
窗口功能有两种: 聚合窗口函数 和 内置窗口函数 .每个聚合窗口函数 也可以用作普通聚合函数,只需省略 OVER和FILTER子句。 此外,所有内置 聚合函数 SQLite的 通过添加适当的OVER子句聚合窗口函数。 应用程序可以使用注册新的聚合窗口函数 这个 sqlite3_create_window_function() 接口。 然而,内置窗口函数需要特殊情况 在查询规划器中处理,从而处理新的窗口函数 显示了内置 应用程序无法添加窗口函数。
下面是一个使用内置row_number()的示例 窗口功能:
创建表t0(x整数主键,y文本); 插入t0值(1,'aaa'),(2,'ccc'),(3,'bbb'); --以下SELECT语句返回:
--
--x|y|行编号 ----------------------- --1|aaa|1
--2|ccc|3
--3|bbb|2个
-- 选择x,y,row_number()OVER(ORDER BY y)作为row_num FROM t0 ORDER B x;
row_number()窗口函数 为每个 中“order BY”子句的顺序行 窗-深 (在本例中为“按y订购”)。 请注意 这不会影响返回结果的顺序 整体查询。 最终输出的顺序为 仍然由附加到SELECT的ORDER by子句控制 语句(在本例中为“ORDER BY x”)。
已命名 窗-深 子句也可以添加到SELECT 语句,然后在窗口中按名称引用 函数调用。 例如,以下SELECT语句包含 两个命名的 窗口-默认 子句“win1”和“win2”:
选择x,y,row_number()覆盖 赢1 ,等级()以上 赢2 自t0 窗户 赢1 AS(在无边界前一行和当前行之间按y范围排序), 赢2 AS(按y顺序按x划分) 按x订购;
当出现WINDOW子句时,它位于任何HAVING子句之后,并且 在任何订单之前。
2 聚合窗口函数
本节中的示例都假设数据库填充为 跟随:
创建表t1(一个整数主键,b,c); 插入t1值(1,'A','one'), (2,‘B’,‘2’), (3,‘C’,‘三’), (4,‘D’,‘one’), (5,‘E’,‘2’), (6,‘F’,‘三’), (7,‘G’,‘one’);
聚合窗口函数类似于 普通聚合函数 ,除 将其添加到查询中不会更改返回的行数。 相反, 对于每一行,聚合窗口函数的结果就像 在“窗口框架”中的所有行上运行相应的聚合 由OVER子句指定。
--以下SELECT语句返回:
--
--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
-- 选择a,b,group_concat(b,'.')OVER( 按前1行和后1行之间的行排序 )AS组_来自t1;
在上面的示例中,窗口框架由 前一行(“前一行”)和下一行(包括“后一行”, 其中,行是根据 窗-深 (在本例中为“ORDER BY a”)。 例如,(a=3)行的框架由行(2,‘B’,‘two’)组成, (3,‘C’,‘三’)和(4,‘D’,‘一’)。 group_concat(b,'.')的结果 因此该行为“B.C.D”。
所有SQLite的 聚合函数 可以 用作聚合窗口函数。 也可以 创建用户定义的聚合窗口函数 .
2.1. 按条款分割
为了计算窗口函数,结果集 将查询划分为一个或多个“分区”。 分区由 PARTITION BY子句中所有项的值相同的所有行 在中 窗-深 .如果没有PARTITION BY子句, 那么查询的整个结果集就是一个分区。 每个分区单独执行窗口函数处理。
例如:
--以下SELECT语句返回:
--
--c|a|b|组压缩 --------------------------------- --一个|1|A|A.D.G
--一个|4|D|D.G
--一个|7|G|G
--三个|3|C|C.F
--三个|6|F|F
--两个|2|B|B.E
--两个|5|E|E
-- 选择c,a,b,group_concat(b,'.')OVER( 按当前行和无边界行之间的范围按c顺序划分 )AS组_压缩 从t1开始按c,a排序;
在上面的查询中,“PARTITION BY c”子句打破了 结果设置为三个分区。 第一个分区具有 三行c=='one'。 第二个分区有两行 c=='tree',第三个分区有两行c=='两'。
在上面的示例中,每个分区的所有行都是 在最终输出中组合在一起。 这是因为PARTITION BY 子句是整个查询上ORDER BY子句的前缀。 但这并没有 就是这样。 分区可以由分散的行组成 大约在结果集中随意。 例如:
--以下SELECT语句返回:
--
--c|a|b|组压缩 --------------------------------- --一个|1|A|A.D.G
--两个|2|B|B.E
--三个|3|C|C.F
--一个|4|D|D.G
--两个|5|E|E
--三个|6|F|F
--一个|7|G|G
-- 选择c,a,b,group_concat(b,'.')OVER( 按当前行和无边界行之间的范围按c顺序划分 )AS组_压缩 从t1订购a;
2.2。 框架规格
这个 特定帧 确定哪些输出行是 由聚合窗口函数读取。 这个 特定帧 由四部分组成:
框架类型-ROWS、RANGE或GROUPS, 起始帧边界, 结束帧边界, EXCLUDE子句。
以下是语法详细信息:
特定帧:
隐藏
组
协议双方:
无约束
之前
和
无约束
以下
范围
排
无约束
之前
快递
之前
当前
行
快递
之前
当前
行
快递
以下
快递
之前
当前
世界其他地区
快递
以下
排除
当前
世界其他地区
排除
集团
排除
领带
排除
不
其他
快递:
显示
文字值
绑定参数
方案名称
.
表格名称
.
列名
一元运算符
快递
快递
二进制运算符
快递
函数-名称
(
函数参数
)
筛选子句
附加条款
(
快递
)
,
铸件
(
快递
AS公司
类型名称
)
快递
COLLATE公司
排序规则名称
快递
不是
喜欢
GLOB公司
REGEXP公司
匹配
快递
快递
逃生
快递
快递
ISNULL(ISNULL)
不为空
不是
无效的
快递
是
不是
与众不同
发件人
快递
快递
不是
协议双方:
快递
和
快递
快递
不是
英寸
(
select-stmt(选择-测试)
)
快递
,
方案名称
.
表格功能
(
快递
)
表格名称
,
不是
存在
(
select-stmt(选择-测试)
)
案例
快递
什么时候
快递
然后
快递
ELSE公司
快递
结束
raise函数
filter子句:
显示
函数参数:
显示
订购术语:
显示
快递
COLLATE公司
排序规则名称
DESC公司
ASC公司
空
第一
空
最后
文字值:
显示
当前时间戳
数字-文字
串文字
blob-literal(blob-文字)
无效的
真的
错误的
当前时间(_T)
当前日期(_D)
附加条款:
显示
超过
窗口名称
(
基本窗口名称
隔板
由
快递
,
订单
由
订购术语
,
特定帧
)
订购术语:
显示
快递
COLLATE公司
排序规则名称
DESC公司
ASC公司
空
第一
空
最后
raise函数:
显示
RAISE(升起)
(
回降
,
错误消息
)
IGNORE公司
中止
失败
select-stmt(选择-测试):
显示
与
递归
通用表格表达
,
选择
与众不同
结果列
,
所有
发件人
表或子查询
连词
,
哪里
快递
集团
由
快递
有
快递
,
窗户
窗口名称
AS公司
窗口定义
,
价值观
(
快递
)
,
,
复合运算符
选择核心
订单
由
极限
快递
订购术语
,
抵消
快递
,
快递
通用表格表达:
显示
表格名称
(
列名
)
AS公司
不是
材料化
(
select-stmt(选择-测试)
)
,
复合运算符:
显示
联合条款:
显示
订购术语:
显示
快递
COLLATE公司
排序规则名称
DESC公司
ASC公司
空
第一
空
最后
结果列:
显示
表或子查询:
显示
方案名称
.
表格名称
AS公司
桌上躺椅
索引
由
索引名称
不是
索引
表格-功能-名称
(
快递
)
,
AS公司
桌上躺椅
(
选择stmt
)
(
表或子查询
)
,
联接条款
窗口定义:
显示
(
基本翼名称
隔板
由
快递
,
订单
由
订购术语
,
特定帧
)
类型名称:
显示
名称
(
签名号码
,
签名号码
)
(
签名号码
)
签名号码:
显示
可以省略结束帧边界(如果 围绕起始帧边界的BETWEEN和and关键字 也省略了), 在这种情况下,结束帧边界默认为CURRENT ROW。
如果帧类型为RANGE或GROUPS,则具有相同值的行 所有ORDER BY表达式都被视为“对等”。 或者,如果没有ORDER BY 术语,所有行都是对等行。 同龄人总是在同一框架内。
默认值 特定帧 是:
默认值表示聚合窗口函数读取所有 从分区开始到(包括) 当前行及其对等行。 这意味着具有相同值的行 所有ORDER BY表达式对于 窗口功能(因为窗口框架相同)。 例如:
--以下SELECT语句返回:
--
--a|b|c|组压缩 ----------------------------- --1|A|one|A.D.G
--2|B|two|A.D.G.C.F.B.E
--3|C|3|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|一个|A.D.G
-- 选择a、b、c, group_concat(b,'.')结束(按c排序)作为group_concat 从t1订购a;
2.2.1. 框架类型
有三种帧类型:ROWS、GROUPS和RANGE。 框架类型决定了开始和结束边界的方式 测量框架的。
排 : ROWS框架类型意味着开始和结束边界 对于帧,通过计算相对的单个行数来确定 到当前行。
组 : GROUPS框架类型意味着开始和结束边界 通过相对于当前组计数“组”来确定。 “组”是一组所有行的值都相等的行 窗口ORDER BY子句的所有术语。 (“等价物”是指 这个 IS操作员 比较两个值时为true。) 换句话说,一个组由一行中的所有对等方组成。
范围 : RANGE框架类型要求 窗口只有一个术语。 称之为“X”。 使用 RANGE框架类型,框架的元素由 计算分区中所有行的表达式X的值 并将X值在某一特定范围内的行框化 当前行的X值的范围。 参见说明 在“ <expr>前面 “边界 详细信息请参见以下规范。
ROWS和GROUPS框架类型相似 两者都通过相对于以下对象的计数来确定帧的范围 当前行。 不同的是,ROWS计算个人 行和GROUPS统计对等组。 RANGE帧类型不同。 RANGE帧类型通过以下方式确定帧的范围 查找某个范围内的表达式值 相对于当前行的值。
2.2.2. 框架边界
有五种方法可以描述开始和结束帧边界:
无约束前导 框架边界是第一个 中的行 隔板 .
<expr>前面 <expr>必须是非负常量数值表达式。 边界是<expr>“units”之前的行 当前行。 此处“单位”的含义取决于 框架类型:
排 → 帧边界是<expr>的行 当前行之前的行,或 如果行数少于<expr>,则进行分区 在当前行之前< expr>必须是整数。
组 → “组”是一组对等行,这些行都具有 ORDER BY子句中每个术语的值相同。 框架边界是<expr>的组 组位于包含当前行的组之前,或 分区的第一组(如果数量较少) <expr>组位于当前行之前。 对于帧的起始边界,第一个 使用组中的行作为结束边界 在帧中,使用组的最后一行。 <expr>必须是整数。
范围 → 对于此表单 窗-深 必须有一个单间 术语。 称之为ORDER BY术语“X”。 让 X(X) 我 是X的值 分区和let中第i行的表达式 X(X) c(c) 是的X值 当前行。 非正式地,RANGE边界是第一行 其中X 我 在中 X的<expr> c(c) . 更准确地说:
如果是X 我 或 X(X) c(c) 是非数字的,那么 边界是表达式所在的第一行 “X” 我 IS X(信息系统X) c(c) " 是真的。 否则,如果ORDER BY是ASC,则边界 是其中的第一行 X(X) 我 >=X c(c) -<经验>。 否则,如果ORDER BY是DESC,则边界 是其中的第一行 X(X) 我 <=X c(c) +<表达>。 对于此表单,<expr>不必是 整数。 它可以计算为实数,只要 它是常数,非负的。 边界描述“0 PRECEDING”始终表示相同 称为“当前行”。 当前行 当前行。 对于RANGE和GROUPS框架类型, 当前行的对等点也包括在帧中, 除非EXCLUDE条款明确排除。 无论是否使用CURRENT ROW,这都是正确的 作为开始或结束帧边界。
<expr>以下 这与“<expr>PRECEDING”相同,只是 边界是电流之后的<expr>单位 而不是在当前行之前。
无约束跟随 帧边界是最后一个 中的行 隔板 .
结束帧边界的形式不能高于 上面的列表比起始帧边界更大。
在下面的示例中,每行的窗口框架包括 从当前行到集合末尾的行,在这里对行进行排序 根据“ORDER BY a”。
--以下SELECT语句返回:
--
--c|a|b|组压缩 --------------------------------- --一个|1|A|A.D.G.C.F.B.E
--一个|4|D|D.G.C.F.B.E
--一个|7|G|G.C.F.B.E
--三|3|C|C.F.B.E
--三个|6|F|F.B.E
--两个|2|B|B.E
--两个|5|E|E
-- 选择c,a,b,group_concat(b,'.')OVER( 按c排序,在当前行和以下无边界行之间的行 )AS组_压缩 从t1开始按c,a排序;
2.2.3. 排除条款
可选的EXCLUDE条款可以采用以下四种形式中的任何一种:
不包括其他 :这是默认设置。 在这种情况下没有 根据窗口框架的开始和结束定义,将行从窗口框架中排除 框架边界。
不包括当前行 :在这种情况下,当前行是 从窗框中排除。 当前行的对等方保留在中 GROUPS和RANGE帧类型的帧。
排除组 :在这种情况下,当前行和所有其他行 当前行的对等行将从帧中排除。 什么时候? 处理EXCLUDE子句、所有具有相同ORDER BY值的行或所有 分区中的行(如果没有ORDER BY子句)被认为是对等的, 即使框架类型为ROWS。
排除领带 :在这种情况下,当前行是 帧,但排除当前行的对等点。
下面的示例演示了各种 EXCLUDE条款的形式:
--以下SELECT语句返回:
--
--c|a|b|no_others|current_row|grp|ties公司
--一个|1|A|A.D.G|D.G||A
--一个|4|D|A.D.G|A.G||D
--一个|7|G|A.D.G|A.D||G
--三个|3|C|A.D.G.C.F|A.D.G|A.D.G | A.D.G.C
--三个|6|F|A.D.G.C.F|A.D.G.C|A.D.G|A.D.G
--两个|2|B|A.D.G.C.F.B.E|A.D.GC.F.E|A.D.G.C.F|A.D.G.C.F.B
--两个|5|E|A.D.G.C.F.B.E|A.D.G.C.F.B|A.D.D.G.C.F|A.D.GC.F.E
-- 选择c、a、b, group_concat(b,'.')结束( 无边界前一行和当前行之间按c组排序不包括其他行 )作为其他人, group_concat(b,'.')结束( 无边界前一行和当前行之间按c组排序,不包括当前行 )AS电流, group_concat(b,'.')结束( 在无边界前一行和当前行排除组之间按c组排序 )AS集团, group_concat(b,'.')结束( 无边界前行和当前行之间按c组排序 )AS领带 从t1开始按c,a排序;
2.3. FILTER条款
过滤条款:
隐藏
快递:
显示
文字值
绑定参数
方案名称
.
表格名称
.
列名
一元运算符
快递
快递
二进制运算符
快递
函数名称
(
函数参数
)
过滤子句
附加条款
(
快递
)
,
铸件
(
快递
AS公司
类型名称
)
快递
COLLATE公司
排序规则名称
快递
不是
喜欢
GLOB公司
REGEXP公司
匹配
快递
快递
逃生
快递
快递
ISNULL(ISNULL)
不为空
不是
无效的
快递
是
不是
与众不同
发件人
快递
快递
不是
协议双方:
快递
和
快递
快递
不是
英寸
(
select-stmt(选择-测试)
)
快递
,
架构名称
.
表格功能
(
快递
)
表格名称
,
不是
存在
(
select-stmt(选择-测试)
)
案例
快递
什么时候
快递
然后
快递
ELSE公司
快递
结束
raise函数
函数参数:
显示
订购术语:
显示
快递
COLLATE公司
排序规则名称
DESC公司
ASC公司
空
第一
空
最后
文字值:
显示
当前时间戳
数字-文字
串文字
blob-literal(blob-文字)
无效的
真的
错误的
当前时间(_T)
当前日期
附加条款:
显示
超过
窗口名称
(
基本翼名称
隔板
由
快递
,
订单
由
订购术语
,
特定帧
)
特定帧:
显示
组
协议双方:
无约束
之前
和
无约束
以下
范围
排
无约束
之前
快递
之前
当前
世界其他地区
快递
在前
当前
世界其他地区
快递
以下
快递
之前
当前
世界其他地区
快递
以下
排除
当前
世界其他地区
排除
集团
排除
领带
排除
不
其他
订购术语:
显示
快递
COLLATE公司
排序规则名称
DESC公司
ASC公司
空
第一
空
最后
raise函数:
显示
RAISE(升起)
(
回降
,
错误消息
)
IGNORE公司
中止
失败
select-stmt(选择-测试):
显示
与
递归
通用表格表达
,
选择
与众不同
结果列
,
所有
发件人
表或子查询
联接条款
,
哪里
快递
集团
由
快递
有
快递
,
窗户
窗口名称
AS公司
窗-深
,
价值观
(
快递
)
,
,
复合运算符
选择-核心
订单
由
极限
快递
订购术语
,
抵消
快递
,
快递
通用表格表达:
显示
表格名称
(
列名
)
AS公司
不是
材料化
(
select-stmt(选择-测试)
)
,
复合运算符:
显示
联合条款:
显示
订购术语:
显示
快递
COLLATE公司
排序规则名称
DESC公司
ASC公司
空
第一
空
最后
结果列:
显示
表或子查询:
显示
方案名称
.
表格名称
AS公司
桌上躺椅
索引
由
索引名称
不是
编入索引的
表格-功能-名称
(
快递
)
,
AS公司
表别名
(
select-stmt(选择-测试)
)
(
表或子查询
)
,
连词
窗口定义:
显示
(
基本翼名称
隔板
由
快递
,
订单
由
订购术语
,
特定帧
)
特定帧:
显示
组
协议双方:
无约束
之前
和
无约束
以下
范围
排
无边界的
之前
快递
之前
当前
世界其他地区
快递
之前
当前
世界其他地区
快递
以下
快递
之前
当前
世界其他地区
快递
以下
排除
当前
世界其他地区
排除
集团
排除
领带
排除
不
其他
类型名称:
显示
名称
(
签名号码
,
签名号码
)
(
签名号码
)
签名号码:
显示
如果提供了FILTER子句,则只有 快递 是 true包含在窗口框架中。 聚合窗口仍返回 值,但FILTER表达式计算结果为 任何行的窗口框架中都不包括true以外的内容。 例如:
--以下SELECT语句返回:
--
--c|a|b|组压缩 --------------------------------- --一个|1|A|A
--两个|2|B|A
--三个|3|C|A.C
--一个|4|D|A.C.D
--两个|5|E|A.C.D
--三个|6|F|A.C.D.F
--一个|7|G|A.C.D.F.G
-- 选择c,a,b,group_concat(b,'.')FILTER(WHERE c!='two')OVER( 按订购 )AS组_压缩 从t1订购a;
3 内置车窗功能
除了聚合窗口函数外,SQLite还具有一组内置的 窗口函数基于 PostgreSQL支持的 .
内置窗口函数以相同的方式尊重任何PARTITION BY子句 作为聚合窗口的功能-每个选定的行被分配给一个分区 每个分区都是单独处理的。 任何ORDER BY的方式 子句影响每个内置窗口函数的描述如下。 一些 窗口函数(rank()、dense_rank( “对等组”的概念(同一分区中具有 所有ORDER BY表达式的值相同)。 在这些情况下,这无关紧要 是否 特定帧 指定ROWS、GROUPS或RANGE。 对于内置窗口函数处理,具有相同值的行 对于所有ORDER BY表达式,无论帧类型如何,都被视为对等。
大多数内置窗口函数忽略 特定帧 ,异常为first_value(), last_value()和nth_value(。 指定FILTER是一个语法错误 子句作为内置窗口函数调用的一部分。
SQLite支持以下11个内置窗口函数:
row_number()
当前分区中的行数。 行是 按中order by子句定义的顺序从1开始编号 窗口定义,否则按任意顺序。
等级()
每个组中第一个对等方的row_number()- 具有间隙的当前行。 如果没有ORDER BY子句,则所有行 被视为对等,此函数始终返回1。
密度_等级()
当前行在其分区内的对等组的数量- 当前行的排名,没有空格。 行从开始编号 按照窗口中order by子句定义的顺序从1开始 定义。 如果没有ORDER BY子句,则所有行都是 考虑到对等点,此函数始终返回1。
百分比银行()
不管名称如何,此函数始终返回0.0之间的值 和1.0等于( 等级 - 1)/( 隔板辊 -1),其中 等级 是内置窗口函数rank()返回的值 和 隔板辊 是中的总行数 分区。 如果分区只包含一行,则此函数 返回0.0。
cume_dist()
累积分布。 计算公式为 行-编号 / 隔板辊 ,其中 行-编号 是 rownnumber()为组中最后一个对等方返回的值 和 隔板辊 分区中的行数。
直到(N)
参数 N个 作为整数处理。 此函数将 尽可能均匀地划分为N组,并分配一个整数 介于1和之间 N个 按照order定义的顺序发送给每个组 BY子句或其他任意顺序。 如有必要,扩大团队 先发生。 此函数返回指定给 当前行所属的组。
滞后(expr) 滞后(expr,offset) 滞后(expr、offset、default)
函数的第一种形式返回计算结果 表达 快递 针对分区中的前一行。 或者,如果 没有前一行(因为当前行是第一行),即NULL。
如果 抵消 提供了参数,则它必须是 非负整数。 在这种情况下,返回的值是结果 评估的 快递 对着那排 抵消 前的行 分区中的当前行。 如果 抵消 为0,则 快递 根据当前行进行计算。 如果没有行 抵消 当前行之前的行,返回NULL。
如果 违约 也提供了,然后返回它,而不是 如果由标识的行为NULL 抵消 不存在。
铅(expr) 导程(expr,offset) 潜在客户(expr、offset、default)
lead()函数的第一种形式返回计算结果 表达 快递 与分区中的下一行相对。 或者,如果 没有下一行(因为当前行是最后一行),即NULL。
如果 抵消 提供了参数,则它必须是 非负整数。 在这种情况下,返回的值就是结果 评估的 快递 对着那排 抵消 后面的行 分区中的当前行。 如果 抵消 为0,则 快递 根据当前行进行计算。 如果没有行 抵消 当前行之后的行,返回NULL。
如果 违约 也提供了,然后返回它,而不是 如果由标识的行为NULL 抵消 不存在。
第一个值(expr)
此内置窗口函数计算每个窗口的窗口框架 行的方式与聚合窗口函数相同。 它返回 的值 快递 根据窗口框架中的第一行进行计算 对于每一行。
last_value(表达式)
此内置窗口函数计算每个窗口的窗口框架 行的方式与聚合窗口函数相同。 它返回 的值 快递 根据窗口框架中的最后一行进行计算 对于每一行。
第N个值(expr,N)
此内置窗口函数计算每个窗口的窗口框架 行的方式与聚合窗口函数相同。 它返回 的值 快递 根据行进行计算 N个 窗口的 框架。 行在窗口框架内从1开始编号 order by子句定义的顺序(如果有),或 其他任意命令。 如果没有 N个 中的第行 分区,则返回NULL。
本节中的示例使用 先前定义的T1表 以及以下T2表:
创建表t2(a,b); 插入t2值('a','one'), (‘a’,‘2’), (‘a’,‘3’), (‘b’,‘4’), ('c','5'), ('c','6');
以下示例说明了五个排名的行为 函数-行_编号()、等级()、密度等级()和百分比等级() cume_dist()。
--以下SELECT语句返回:
--
--a |row_number | rank | dense_rank | percent_rank | cume_dist ------------------------------------------------------------------ --a|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
-- 选择AS, row_number()胜出作为row_num, rank()胜出AS排名, dense_rank()战胜AS dense_rank, percent_rank()胜出AS percent_bank, cume_dist()以cume_dist获胜 自t2 WINDOW win AS(ORDER BY a);
下面的示例使用ntile()将六行分为两组( ntile(2)调用)和分为四组(ntile(4)调用)。 对于整个(2) 是分配给每个组的三行。 对于untile(4),有两组 两组一组。 两个较大的组首先出现。
--以下SELECT语句返回:
--
--a | b | ntile2 | ntile4 ---------------------------------- --一个|1 |1
--两个|1|1
--一个|三个|1|2
--b|four|2|2
--c|5|2|3
--c|6|2|4
-- 选择AS, b AS b, 直到(2)战胜胜利为止, 直到(4)战胜胜利为止 自t2 WINDOW win AS(ORDER BY a);
下一个示例演示了lag()、lead()、first_value()和last_value( 和nth_value()。 这个 特定帧 被忽略 lag()和lead(),但受first_value()和last_value( 和nth_value()。
--以下SELECT语句返回:
--
--b|lead|lag|first_value|last_value|nth_value_3 ------------------------------------------------------------- --A|C|NULL|A|A|NULL(空)
--B|D|A|A|B|空
--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(通用)
-- 选择b作为b, 领先(b,2,'n/a')胜出领先, 滞后(b)超过胜利作为滞后, first_value(b)战胜赢得第一名, last_value(b)战胜win AS last_value, nth_value(b,3)战胜AS nth_value_3 自t1 WINDOW WIND AS(在无边界前一行和当前行之间按b行排序)
4 窗户链
窗口链接是一种允许用术语定义一个窗口的速记方法 另一个。 具体来说,这个简写允许新窗口隐式地 复制基本窗口的PARTITION BY子句和ORDER BY子句(可选)。 对于 示例如下:
选择group_concat(b,'.')OVER( 在无边界前一行和当前行之间赢得行 ) 自t1 WINDOW win AS(PARTITION BY a ORDER BY c)窗口获胜
group_concat()函数使用的窗口是等效的 至“在无边界的前一行之间按c行的顺序划分 AND CURRENT ROW”(与当前行)。为了使用窗口 链接时,必须满足以下所有条件:
新窗口定义不能包含PARTITION BY子句。 这个 PARTITION BY子句(如果有)必须由基地提供 窗户规格。
如果基本窗口有ORDER BY子句,则将其复制到新的 窗口。 在这种情况下,新窗口不能指定ORDER BY子句。 如果基本窗口没有ORDER BY子句,则可以指定一个作为部分 新窗口定义的。
基本窗口不能指定框架规范。 框架 规范只能在新的窗口规范中给出。
下面的两个SQL片段类似,但并不完全相同,因为 如果窗口“win”的定义包含框架,则后者将失败 规范。
选择group_concat(b,'.')胜出。。。 选择group_concat(b,'.')OVER(win)。。。
5 用户定义的聚合窗口函数
用户定义的聚合窗口函数可以使用 sqlite3_创建_window_函数 ()API。 实现聚合窗口 函数与普通聚合函数非常相似。 任何用户定义的 聚合窗口函数也可用作普通聚合。 收件人 实现应用程序必须实现的用户定义聚合窗口函数 提供四个回调函数:
回拨 描述 x步骤 窗口聚合和遗留聚合都需要此方法 功能实现。 调用它将行添加到当前 窗口。 对应于行的函数参数(如果有) 将添加的传递给xStep的实现。 x最终 窗口聚合和遗留聚合都需要此方法 函数实现。 它被调用以返回当前值 骨料的数量(由当前窗口的内容确定), 并释放之前调用xStep所分配的任何资源。 x值 此方法仅适用于窗口聚合函数。 现场 此方法的区别在于窗口聚合函数与 遗留聚合函数。 调用此方法以返回当前 聚合的值。 与xFinal不同,实现不应该 删除任何上下文。 x反向 此方法仅适用于窗口聚合函数,而非传统函数 聚合函数实现。 它被调用以删除最旧的 当前从当前窗口聚合xStep的结果。 函数参数(如果有)是 传递给要删除的行的xStep。
下面的C代码实现了一个名为 sumin()。 这与内置sum()函数的工作方式相同,除了 如果传递的参数不是整数,则会引发异常 值。
下面的示例使用由上面的 C代码。 对于每一行,窗口由前一行(如果有)、当前行和下一行(同样,如果有)组成:
创建表t3(x,y); 插入t3值('a',4), ('b',5), ('c',3), ('d',8), (‘e’,1); --假设使用上述脚本填充数据库
--以下SELECT语句返回:
--
--x |总和 -------------- --a |9
--b | 12
--抄送|16
--日期| 12
--第9页
-- 选择x,sumin(y)OVER( 按前面1行和后面1行之间的x行排序 )汇总(_y) 从t3按x排序;
在处理上述查询时,SQLite调用sumin回调 跟随:
x步骤(4) -在当前窗口中添加“4”。 x步骤(5) -在当前窗口中添加“5”。 x值() -调用xValue()获取 带有(x='a')的行。 窗口当前由值4和5组成, 结果是9。 x步骤(3) -在当前窗口中添加“3”。 x值() -调用xValue()获取的sumint()值 带有(x='b')的行。 窗口当前由值4、5和 3,所以结果是12。 x反向(4) -从窗口中删除“4”。 x步骤(8) -在当前窗口中添加“8”。 窗口现在包括 值5、3和8。 x值() -调用以获取具有(x='c')的行的值。 在这种情况下,是16。 x反向(5) -从窗口中删除值“5”。 x步骤(1) -在窗口中添加值“1”。 x值() -调用以获取行(x='d')的值。 x反向(3) -从窗口中删除值“3”。 现在打开窗户 仅包含值8和1。 xFinal() -调用以回收任何分配的资源并 获取行的值(x='e')。 9. .
如果用户要通过调用sqlite3_reset()放弃查询执行,或 SQLite调用xFinal()之前, 然后在sqlite3_reset()中自动调用xFinal(),或者 调用sqlite3_finalize()以回收任何分配的资源,甚至 尽管该值不是必需的。 在这种情况下,xFinal返回的任何错误 实现被悄悄丢弃。
6 历史
窗口功能支持首次通过发布添加到SQLite 版本3.25.0 (2018-09-15)。 SQLite开发人员使用 这个 PostgreSQL 窗口函数 文档作为窗口如何工作的主要参考 应该举止得体。 许多测试用例都是针对PostgreSQL运行的 以确保车窗功能在两种情况下的操作方式相同 SQLite和PostgreSQL。
在SQLite中 版本3.28.0 (2019-04-16), windows函数支持被扩展为包括EXCLUDE子句, GROUPS框架类型、窗口链接和对的支持 “<expr>前”和“<expr>后”边界 RANGE帧中。
此页面上次修改时间 2024-04-16 17:22:18 联合技术公司