聚合函数
函数 | 作用 |
---|---|
AVG(x) | 返回 x 列的平均值 |
COUNT(x) / COUNT(*) | 统计条数,x 表示统计 x 列 不为null 的条数,* 表示统计总条数 |
GROUP_CONCAT(X [,y]) | 使用 "," 拼接 x 列,若传递 y,则使用 x 拼接 y 列 |
MAX(x) | 返回 x 列中的最大值 |
MIN(x) | 返回 x 列中的最小值 |
SUM(x) | 返回 x 列的和,如果输入全是null,则返回 null |
TOTAL(x) | 返回 x 列的和,如果输入全是null,则返回 0.00 |
数值函数
更多函数参考:https://www.sqlite.org/lang_mathfunc.html
函数 | 作用 |
---|---|
ABS(x) | 返回 x 的绝对值 |
random() | 返回 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数 |
MOD(x,y) | 返回 x/y 的模 |
pi() | 返回 π 的近似值 |
MAX(X1,X2[,...Xn]) | 返回传入参数中的最大值 |
MIN(X1,X2[,...Xn]) | 返回传入参数中的最小值 |
CEIL(x) | 返回大于X的最小整数值,3.35.0+ |
FLOOR(x) | 返回小于x的最大整数值,3.35.0+ |
ROUND(x,y) | 返回参数x的四舍五入到小数点y位的值,若忽略 y 则默认为 0 |
POW(x,y) / POWER(x,y) | 返回 x 的 y 次方 |
逻辑函数
函数 | 作用 |
---|---|
coalesce(X1,X2[,...Xn]) | 返回列表中第一个非 NULL 的值,若全是NULL,则返回NULL,至少需要两个参数 |
IFNULL(x,y) | 如果 X 为 null,返回 y ,否则返回 x |
NULLIF(x,y) | 如果 x 与 y 不相同,则返回 x ,否则返回 NULL |
IIF(x,y,z) | 如果 x 为真 则返回 y 否则返回 z |
字符串函数
函数 | 作用 |
---|---|
replace(str,x,y) | 用字符串 y 替换字符串 str 中出现的所有字符串 x |
instr(x,y) | 返回字符串 y 在 x 中出现的位置,不存在返回0 ,x、y 任意一个为NULL 则返回null |
length(str) | 返回字符串 str 的长度 |
substr(str,x,y) | 返回字符串 str 中 x 位置开始的 y 个字符,y 省略则返回剩下全部 |
upper(str) | 返回 str 所有字符的大写 |
lower(str) | 返回 str 所有字符的小写 |
ltrim(str[,x]) | 删除字符串 str 左侧的空格,如果传递字符串 x 则删除左侧的 x 字符串 |
rtrim(str[,x]) | 删除字符串 str 右侧的空格,如果传递字符串 x 则删除右侧的 x 字符串 |
trim(str[,x]) | 删除字符串 str 左右两侧的空格,如果传递字符串 x 则删除左右两侧的 x 字符串 |
like('%x%', str) | 模糊查询字符串 x 在字符串 y 中是否存在 |
时间函数
函数 | 作用 |
---|---|
date(timestring, modifier, modifier...) | 以 YYYY-MM-DD 格式返回日期 |
time(timestring, modifier, modifier...) | 以 HH:MM:SS 格式返回日期 |
datetime(timestring, modifier, modifier...) | 以 YYYY-MM-DD HH:MM:SS 格式返回日期 |
julianday(timestring, modifier, modifier...) | 返回从格林尼治时间的公元前 4714年 11 月 24日 正午算起的天数 |
unixepoch(timestring, modifier...) | 返回 自 1970-01-01 00:00:00 算起的秒数 |
strftime(format, timestring, modifier, modifier) | 根据指定字符串格式化日期 |
timediff(timestringA, timestringB) | 返回 从时间B 到时间A 相差时间的修饰符 |
- timestring 时间字符串
- modifier 修饰符
- format 格式化
上面的几个时间函数,将时间字符串作为参数,时间字符串后面跟零个或多个 modifier 修饰符。
时间字符串
时间字符串可以使用下面任意一种格式:
时间字符串 | 示例 |
---|---|
YYYY-MM-DD | 2022-01-15 |
YYYY-MM-DD HH:MM | 2022-01-15 22:30 |
YYYY-MM-DD HH:MM:SS.SSS | 2022-01-15 22:35:02.100 |
MM-DD-YYYY HH:MM | 15-01-2022 12:30 |
HH:MM | 12:10 |
YYYY-MM-DDT HH:MM | 2022-01-15 22:36 |
HH:MM:SS | 22:30:01 |
YYYYMMDD HHMMSS | 20220115 223701 |
now | 当前日期 2022-01-15 |
修饰符
时间字符串后边可跟着零个或多个的修饰符,这将改变有上述五个函数返回的日期和/或时间。任何上述五大功能返回时间。修饰符应从左到右使用,下面列出了可在 SQLite 中使用的修饰符:
修饰符 | 描述 |
---|---|
±N days / day | ±N 天 |
±N hours | ±N 小时 |
±N minutes | ±N 分钟 |
±N seconds | ±N 秒 |
±N months | ±N 月 |
±N years | ±N 年 |
±HH:MM | ±时:分 |
±HH:MM:SS | ±时:分:秒 |
±HH:MM:SS.SSS | ±时:分:秒.毫秒 |
±YYYY-MM-DD | ±年-月 |
±YYYY-MM-DD HH:MM | ±年-月-日 时:分 |
±YYYY-MM-DD HH:MM:SS | ±年-月-日 时:分:秒 |
±YYYY-MM-DD HH:MM:SS.SSS | ±年-月-日 时:分:秒.毫秒 |
start of month | 月初 |
start of year | 年初 |
start of day | 一天的开始 |
weekday N | 返回下一个为周 N 的日期和时间,取值范围 0 ~ 6,0 表示星期天,1 表示星期一 |
unixepoch | 返回 自 1970-01-01 00:00:00 算起的秒数 |
julianday | 强制将时间值数字解释为儒略日数字。 强制采用 DDDDDDDDD 时间值格式,如果使用任何其他时间值格式,则会导致返回 NULL。 |
auto | 自动修饰 |
localtime | 当地时间 |
utc | 世界标准时间 |
subsec\subsecond | 亚秒/亚秒级 |
格式化
SQLite 提供了非常方便的函数用来格式化任何日期和时间。
替换 | 描述 |
---|---|
%d | 一月中的第几天,01-31 |
%f | 带小数部分的秒,SS.SSS |
%H | 小时,00-23 |
%j | 一年中的第几天,001-366 |
%J | 儒略日数,DDDD.DDDD |
%m | 月,00-12 |
%M | 分,00-59 |
%s | 从 1970-01-01 算起的秒数 |
%S | 秒,00-59 |
%w | 一周中的第几天,0-6 (0 is Sunday) |
%W | 一年中的第几周,01-53 |
%Y | 年,YYYY |
%% | % |
时间函数示例
-- 查看当前时间,以及 当前时间 2天的修饰符
sqlite> SELECT date('now'),date('now','+2 days');
date('now') date('now','+2 days')
----------- ---------------------
2023-10-15 2023-10-17
-- 计算当前月份的最后一天
sqlite> SELECT date('now','start of month','+1 month','-1 day');
date('now','start of month','+1 month','-1 day')
------------------------------------------------
2023-10-31
-- 获取当前时间戳
sqlite> select unixepoch('now'),strftime('%s','now');
unixepoch('now') strftime('%s','now')
---------------- --------------------
1697379431 1697379431
-- 获取指定时间戳的 utc 时间 以及本地时间
sqlite> SELECT datetime(1697379898, 'unixepoch'),datetime(1697379898, 'unixepoch','localtime');
datetime(1697379898, 'unixepoch') datetime(1697379898, 'unixepoch','localtime')
--------------------------------- ---------------------------------------------
2023-10-15 14:24:58 2023-10-15 22:24:58
-- NOW 返回的已经是标准的 utc 时间(没加时区)了,如果再使用一次 UTC 修饰符,则会再计算一次
-- 例如 中国是东八区,NOW 时间是 当地时间 -8小时,如果再次使用 UTC 修饰符,则会在 NOW 时间的基础上 再 -8 小时。
sqlite> SELECT datetime('now', 'utc');
datetime('now', 'utc')
----------------------
2023-10-15 06:33:09
JSON 函数和运算符
SQLite 版本从 3.38.0 (2022-02-22)版本开始,JOSN 函数与运算符内置到 SQLite 中,在之前的版本里,JSON 函数是一个扩展,只有在包含 -DSQLITE_ENABLE_JSON1
编译选项时才会包含在构建中。
SQLite (3.45.0之前)将 JSON 存储为普通文本,为了保证 db文件的向后兼容性,意味着 SQLite 只能存储 Null、整数、浮点数、文本、和 BLOB 值,无法添加第六种 JSON 类型。
JSONB
从 3.45.0 (2024-01-15)版本开始,SQLite 重写了所有 JSON 函数,改为新的内部解析树格式 (JSONB),通过将 SQLite 的 JSON 内部二进制表示形式直接存储在数据库中,应用程序可以在读取和更新 JSON 值时绕过解析和呈现 JSON 的开销。内部 JSONB 格式使用的磁盘空间也比文本 JSON 略少。
人话:JSONB 的解析、操作、存储都应该通过 SQLite 进行,从应用层面应无感的,不要直接操作 JSONB,防止因为不规范操作 JSONB 导致 SQLite 读取时无法解析。
JSON 参数说明
JSON 参数
对于接受 JSON 作为第一个参数的函数,该参数可以是 JSON 对象、数组、数字、字符串或 null。
- SQLite 数值(整数和浮点)和 Null 值分别被解析为数字和空值。
- SQLite 字符串可以理解为 JSON 对象、数组或字符串。如果传入错误格式的 JSON 字符串,该函数通常会抛出错误(
json_valid()
和json_quote()
例外)。
PATH 参数
对于接受 PATH 参数的函数,该PATH 必须格式正确,否则该函数会抛出错误。正确的 PATH 格式是一个字符串,以 $
开头,后面跟零个、一个或多个 .key
或者 [index]
实例。
数组索引说明:
- 数组索引通常为非负整数 N、在这种情况下,选择数组的第 N 个元素则是从左侧 0 下标开始。
- 数组索引也可以使用 "#-N" 的形式,这样就会从右边开始选择第 N 个元素,例如 "#-1" 表示选择数组中的最后一个元素。
有时数组索引仅使用 "#" 也很有用,可以将新的值附加到 JSON 数组的末尾,例如:
sqlite> select json_set('["a","b","c"]','$[#]','e'); json_set('["a","b","c"]','$[#]','e') ------------------------------------ ["a","b","c","e"]
VALUE 参数
对于接受 VALUE 参数的函数,这些参数通常被理解为被引用的文本字符串,并在结果中成为 JSON 字符串的值。即使输入的值是一个 JOSN 字符串,也会被解析为文本字符串而非 JSON 对象。
但如果值来自于另一个 JOSN 函数返回的结果或 ->
运算符(不包括 ->>
),则该参数将被理解为 JSON 对象,而且会完整插入 JSON 对象中,而不是作为字符串引入。
如下所示:
# 字符串和函数
sqlite> select json_set('["a","b","c"]','$[#]','[1,2,3]');
json_set('["a","b","c"]','$[#]','[1,2,3]')
------------------------------------------
["a","b","c","[1,2,3]"]
sqlite> select json_set('["a","b","c"]','$[#]',json('[1,2,3]'));
json_set('["a","b","c"]','$[#]',json('[1,2,3]'))
------------------------------------------------
["a","b","c",[1,2,3]]
# 运算符 -> 和 ->>
sqlite> select json_set('["a","b","c"]','$[#]','[1,2,3]'->'$');
json_set('["a","b","c"]','$[#]','[1,2,3]'->'$')
-----------------------------------------------
["a","b","c",[1,2,3]]
sqlite> select json_set('["a","b","c"]','$[#]','[1,2,3]'->>'$');
json_set('["a","b","c"]','$[#]','[1,2,3]'->>'$')
------------------------------------------------
["a","b","c","[1,2,3]"]
需要明确的是:
- json 参数的值无论来自何处,都会被解释为 JSON 对象。
- value 参数的值只有来自于 另一个JOSN 函数或
->
操作符时,才会被解析为 JSON 对象,否则被视为 JSON 字符串。
在处理 JOSN 字符串参数(JSON 字符串 与 JOSN 对象互转)时,SQLite 不会对 Unicode 进行转义,只会当作纯文本来处理(例如 \uXXXX
这样的Unicode)。
JSON 函数
函数名 | 说明 |
---|---|
json(x) | 将 JSON 字符串转为 JSON 对象,若字符串 x 不是合法 JSON 对象则抛出错误 |
json_array([x...]) | 接受零个或多个参数 x,转为 JSON 数组,当任何参数为 BLOB 时会抛出错误。 |
json_array_length(x[,p]) | 返回 JOSN 数组 x 中的元素数量,若 x 不是 JSON 数组 则返回 0 若传递参数 P,则返回对应路径的数组长度,若 P 路径不存在则返回 NULL |
json_extract(x,p1[,...pN]) | 从参数 x 的 JSON 参数中 按照路径 p1[,...pN] 提取一个或多个值,转为 JSON 对象返回。 |
json_insert(x,[...<p,v>]) | 在参数 x 中创建指定路径(参数p)和值(参数v), 其中路径与参数成对出现,允许零对或多对。 仅路径不存在时插入,若已存在则不会修改 |
json_replace(x,[...<p,v>]) | 在参数 x 中修改指定路径(参数p)的值(参数v), 其中路径与参数成对出现,允许零对或多对。 仅路径存在时会修改,若不存在则不会创建 |
json_set(x[,...<p,v>]) | 在参数 x 中设置指定路径(参数p)的值(参数v), 其中路径与参数成对出现,允许零对或多对。 路径不存在时创建,路径存在时则修改 |
json_object([...,<k,v>]) | 接受零对或多对 key-value 参数,并返回由这些参数组成格式正确的 JSON 对象。 每对的第一个参数为 key 第二个参数为 value, 当前版本中 key 重复不会引发错误,但在以后的迭代中可能会发生更改。 |
json_patch(t,p) | 将参数 P 与 参数 T 进行合并,返回一个新的 JSON 对象。 路径一致时,使用 P 的 value 替换 T 的值,当值替换为 NULL 时表示在合并后删除该元素。 该函数通常是 json_set() 或 json_remove() 函数的替代品, MergePatch 将 JSON Array 对象视为原子对象,只会替换不会追加。 |
json_remove(x[,...p]) | 将参数 x 中的 p路径属性进行删除,允许传递一个零个或多个路径。 没有传递路径时,将返回格式化后的 X |
json_type(x[,p]) | 返回 参数 x 中路径 P 的元素类型,返回类型可能是 NULL ,true ,false ,integer ,read ,array ,object 。 不传递参数 P 时,返回最外层元素类型,传递路径不存在时返回 NULL |
json_error_position(x) | SQLite 3.42.0+,返回 JSON 参数第一个错误字符的位置,若没有错误字符则返回 0 json_valid(x) 严格要求参数符合规范 JSON,而 json_error_position(x) 允许 JSON5 扩展规范 |
json_valid(x) | 如果参数 x 的 JSON 格式符合 RFC-7159 JSON 规范(不包含 JOSN5 扩展),则返回 1 否则返回 0 |
json_quote(x) | 将参数 x (数字或字符串)转换为相应的 JSON 表示形式。 如果 x 是另一个 JSON 函数的返回值,则直接返回不做处理。 |
json_group_array(x) | 聚合函数,GROUP BY 分组后将 x 列转为 JSON 数组返回,类似于 MySQL 的 GROUP_CONCAT() 。 |
json_group_object(name,value) | 聚合函数,GROUP BY 分组后 返回一个由 name 列和 value 列组成的 json 对象 |
json_each(x[,p]) | 将参数 x 的 JSON 参数转为查询结果集,如果不传递参数 p 时,默认只解析第一个,类似于 MySQL 的 json_table() 函数 |
json_tree(x[,p]) | 用法与 json_each() 类似,区别在于 json_tree() 会递归遍历子元素。 |
# json 示例
sqlite> select json('{"a" : "123",b:[1,2,3]}');
json('{"a" : "123",b:[1,2,3]}')
-------------------------------
{"a":"123","b":[1,2,3]}
# json_array 示例
sqlite> select json_array(1,2,3,4,'6');
json_array(1,2,3,4,'6')
-----------------------
[1,2,3,4,"6"]
# json_array_length 示例
sqlite> select json_array_length('[1,2,3,4]');
json_array_length('[1,2,3,4]')
------------------------------
4
# json_error_position 示例
sqlite> select json_error_position('[1,2,3,4]');
json_error_position('[1,2,3,4]')
--------------------------------
0
# json_extract 示例
sqlite> select json_extract('{"a":123,"b":[4,5,6]}','$.b');
json_extract('{"a":123,"b":[4,5,6]}','$.b')
-------------------------------------------
[4,5,6]
# json_insert 示例
sqlite> select json_insert(json('{"a":123,"b":[4,5,6]}'),'$.c','两双筷子');
json_insert(json('{"a":123,"b":[4,5,6]}'),'$.c','两双筷子')
-------------------------------------------------------
{"a":123,"b":[4,5,6],"c":"两双筷子"}
# json_replace 示例
sqlite> select json_replace(json('{"a":123,"b":[4,5,6]}'),'$.a','两双筷子');
json_replace(json('{"a":123,"b":[4,5,6]}'),'$.a','两双筷子')
--------------------------------------------------------
{"a":"两双筷子","b":[4,5,6]}
# json_set 示例
sqlite> select json_set(json('{"a":123,"b":[4,5,6]}'),'$.d','两双筷子');
json_set(json('{"a":123,"b":[4,5,6]}'),'$.d','两双筷子')
----------------------------------------------------
{"a":123,"b":[4,5,6],"d":"两双筷子"}
# json_object 示例
sqlite> select json_object('name','dbkuaizi','age',35,'blog','https://www.dbkuaizi.com');
json_object('name','dbkuaizi','age',35,'blog','https://www.dbkuaizi.com')
------------------------------------------------------------
{"name":"dbkuaizi","age":35,"blog":"https://www.dbkuaizi.com"}
# json_patch 示例
sqlite> select json_patch('{"a":123,"b":"dbkuaizi"}','{"a":233,"b":null,"c":"两双筷子"}');
json_patch('{"a":123,"b":"dbkuaizi"}','{"a":233,"b":null,"c":"两双筷子"}')
------------------------------------------------------------
{"a":233,"c":"两双筷子"}
# json_remove 示例
sqlite> select json_remove('{"a":123,"b":"dbkuaizi"}','$.b');
json_remove('{"a":123,"b":"dbkuaizi"}','$.b')
---------------------------------------------
{"a":123}
# json_type 示例
sqlite> select json_type('{"a":123,"b":["https://","www.","dbkuaizi",".com"]}','$.b');
json_type('{"a":123,"b":["https://","www.","dbkuaizi",".com"]}','$.b')
------------------------------------------------------------
array
# json_error_position 示例
sqlite> select json_error_position('[1,2,3,,4]');
json_error_position('[1,2,3,,4]')
---------------------------------
8
# json_valid 示例
sqlite> select json_valid('{"a":123,"b":}');
json_valid('{"a":123,"b":}')
----------------------------
0
# json_quote 示例
sqlite> select json_quote('{"a":123}');
json_quote('{"a":123}')
-----------------------
"{\"a\":123}"
# json_each 示例
sqlite> select * from json_each('[1,"2",["a","b","c"]]');
key value type atom id parent fullkey path
--- ------------- ------- ---- -- ------ ------- ----
0 1 integer 1 1 $[0] $
1 2 text 2 2 $[1] $
2 ["a","b","c"] array 3 $[2] $
# json_tree 示例
sqlite> select * from json_tree('[1,"2",["a","b","c"]]');
key value type atom id parent fullkey path
--- --------------------- ------- ---- -- ------ ------- ----
[1,"2",["a","b","c"]] array 0 $ $
0 1 integer 1 1 0 $[0] $
1 2 text 2 2 0 $[1] $
2 ["a","b","c"] array 3 0 $[2] $
0 a text a 4 3 $[2][0] $[2]
1 b text b 5 3 $[2][1] $[2]
2 c text c 6 3 $[2][2] $[2]
# json_group_array 示例,获取不同数据类型的 id
sqlite> select type,json_group_array(id) from json_tree('[1,"2",["a","b","c"]]') group by type;
type json_group_array(id)
------- --------------------
array [0,3]
integer [1]
text [2,4,5,6]
# json_group_object 示例,获取不同数据类型的 id 与父 id
sqlite> select type,json_group_object(id,parent) from json_tree('[1,"2",["a","b","c"]]') group by type;
type json_group_object(id,parent)
------- ----------------------------
array {"0":null,"3":0}
integer {"1":0}
text {"2":0,"4":3,"5":3,"6":3}
JSON 运算符
从 SQLite 3.38.0 (2022-02-22) 开始,支持使用 ->
和 ->>
运算符获取 JSON 的子元素,在使用方法上兼容 MySQL 和 PostgreSQL。
->
运算符返回所选节点的 JSON 对象表示形式,节点不存在返回NULL
。->>
运算符 返回所选节点的 SQL TEXT、INTEGER、REAL 或 NULL 值,当指定节点不存在时 返回 NULL。
sqlite> select '{"a":"123","b":[4,5,6]}' -> '$.a';
'{"a":"123","b":[4,5,6]}' -> '$.a'
----------------------------------
"123"
sqlite> select '{"a":"123","b":[4,5,6]}' ->> '$.a';
'{"a":"123","b":[4,5,6]}' ->> '$.a'
-----------------------------------
123
sqlite> select '{"a":123,"b":[4,5,6]}' -> '$.b[2]';
'{"a":123,"b":[4,5,6]}' -> '$.b[2]'
-----------------------------------
6
窗口函数
SQLite 自 3.25.0 版本开始支持窗口函数,窗口函数是一种 SQL 特性,专门用于聚合、排序、分析数据等场景。虽然绝大多数窗口函数能做到的查询,不使用窗口函数也能实现,但实现 SQL 的要复杂很多。
窗口函数通常可以在单个查询中完成多个操作,避免了子查询或表连接,因此在大部分分析场景下,比传统 SQL 效率更高。
总之 SQLite 引入窗口函数是非常重要的改进,方便开发者处理更复杂的数据分析类 SQL 查询场景。
窗口函数与普通聚合函数的区别在于:如果函数具有 OVER 语句,就是窗口函数。
窗口函数示例数据表
我们模拟一张学生的各科考试成绩表,由于
-- 建表语句
CREATE TABLE pupil_grade (
/* 姓名 */
name INTEGER,
/* 学科 */
subject text not null,
/* 分数,不得小于 0 */
grade INTEGER not null default 0 check(grade > 0)
);
-- 示例数据
INSERT INTO pupil_grade (name, subject, grade)
VALUES('两双筷子', 'php', 80),
('两双筷子', 'golang', 60),
('两双筷子', 'sql', 85),
('dbkuaizi', 'php', 98),
('dbkuaizi', 'golang', 77),
('dbkuaizi', 'sql', 55),
('一只筷子', 'php', 65),
('一只筷子', 'golang', 69),
('一只筷子', 'sql', 43);
举个栗子
我们用一个例子,尽可能直观的展现窗口函数的特点。
sqlite> SELECT *,row_number() OVER(PARTITION BY subject ORDER BY grade desc) as top FROM pupil_grade;
name subject grade top
-------- ------- ----- ---
dbkuaizi golang 77 1
两双筷子 golang 60 2
一只筷子 golang 54 3
dbkuaizi php 98 1
两双筷子 php 80 2
一只筷子 php 65 3
两双筷子 sql 85 1
dbkuaizi sql 55 2
一只筷子 sql 43 3
所谓窗口就是将数据分为一个一个组,然后在各自的组内分表进行数据的操作。
例如上面这个例子,以 学科 为分组(也称分区)条件,在每个组中用 分数 进行倒叙排序,再使用窗口函数 row_number()
给分区内的每一行分配序号,得到上面的结果集。
对这个例子延申一下,外面包一层查询,就可以轻松获取到不同科目的分数第 N 名。也可以扩展如下:
- 公司各部门薪资最高的员工
- 每个用户第 N 个订单之类的场景
sqlite> select * from (
(x1...> SELECT *,row_number() OVER(PARTITION BY subject ORDER BY grade desc) as top FROM pupil_grade
(x1...> ) where top = 1;
name subject grade top
-------- ------- ----- ---
dbkuaizi golang 77 1
dbkuaizi php 98 1
两双筷子 sql 85 1
取每学科平均分:
sqlite> SELECT *,avg(grade) OVER(PARTITION BY subject) as top FROM pupil_grade;
name subject grade top
-------- ------- ----- ----------------
两双筷子 golang 60 63.6666666666667
dbkuaizi golang 77 63.6666666666667
一只筷子 golang 54 63.6666666666667
两双筷子 php 80 81.0
dbkuaizi php 98 81.0
一只筷子 php 65 81.0
两双筷子 sql 85 61.0
dbkuaizi sql 55 61.0
一只筷子 sql 43 61.0
PARTITION BY
窗口函数中,使用 PARTITION BY
将结果集划分为多个分区,每个分区内 PARTITION BY
子句的所有项具有相同的值。如果没有 PARTITION BY
子句,则整个结果集就是单个分区。窗口函数处理的逻辑是根据每个分区单独执行的。
内置窗口函数
除了聚合窗口函数外,SQLite 还有一组仿照 PostgreSQL 支持的内置窗口函数。一共有 11 个,便于理解我划分为两类:
序号类
在同一个分区中,按照不同条件对数据进行编号,主要关注 subject
字段和对应函数的结果关系。
SELECT
*,
row_number() over w as `row_number`,
rank() over w as `rank`,
dense_rank() over w as `dense_rank`,
percent_rank() over w as `percent_rank`,
cume_dist() over w as `cume_dist`,
ntile(2) over w as `ntile`
FROM
pupil_grade
WINDOW w AS (PARTITION BY subject ORDER BY grade desc);
# 执行结果
name |subject|grade|row_number|rank|dense_rank|percent_rank|cume_dist |ntile|
-----------+-------+-----+----------+----+----------+------------+------------------+-----+
dbkuaizi |golang | 77| 1| 1| 1| 0.0|0.3333333333333333| 1|
两双筷子 |golang | 60| 2| 2| 2| 0.5|0.6666666666666666| 1|
一只筷子 |golang | 54| 3| 3| 3| 1.0| 1.0| 2|
两双筷子 |php | 80| 1| 1| 1| 0.0|0.6666666666666666| 1|
dbkuaizi |php | 80| 2| 1| 1| 0.0|0.6666666666666666| 1|
一只筷子 |php | 65| 3| 3| 2| 1.0| 1.0| 2|
两双筷子 |sql | 85| 1| 1| 1| 0.0|0.3333333333333333| 1|
dbkuaizi |sql | 55| 2| 2| 2| 0.5|0.6666666666666666| 1|
一只筷子 |sql | 43| 3| 3| 3| 1.0| 1.0| 2|
函数名 | 说明 |
---|---|
row_number() | 当前分区行号,若定义 ORDER BY 子句,则以定义顺序编号,若值一样或无 ORDER BY 则任意顺序编号。 |
rank() | 当分数为 80 时序号都为 1,下一个分数 65 序号为 3 ,掠过了序号 2,出现了间隙。(注意 PHP 分区的 rank) |
dense_rank() | 当分数为 80 时序号都为 1,下一个分数 65 序号为 2 ,没有间隙。(注意 PHP 分区的 dense_rank) |
percent_rank() | 返回 0.0 ~ 1.0 的值,等价于 (rank - 1) / (partition-rows - 1) 。 其中 rank 是内置窗口函数 rank() 返回的值,partition-rows 是分区中的总行数。 如果分区仅包含一行,则此函数返回 0.0。 |
cume_dist() | 累计分布,计算方式为: row_number() / 分区总行数, 返回 0 ~ 1。 |
ntile(N) | 将分区内的数据,尽可能平均的分为 N 个组,一般用于大量数据的处理场景,需要将数据分配给 N 个进程时。 注意:由于记录的数据不一定被N整除,所以每组的记录数量不一定完全一致。 |
值类
在同一分区,基于排序结果,对值的各种操作:主要关注 subject
字段和对应函数的结果关系
SELECT
*,
lag(grade,1,0) over w as `lag`,
lead(grade,1,0) over w as `lead`,
first_value(grade) over w as `fv`,
last_value(grade) over w as `lv`,
nth_value(grade,2) over w as `nv`
FROM
pupil_grade
WINDOW w AS (PARTITION BY subject ORDER BY grade desc);
# 执行结果
name |subject|grade|lag|lead|fv|lv|nv|
-----------+-------+-----+---+----+--+--+----+
dbkuaizi |golang | 77| 0| 60|77|77|NULL|
两双筷子 |golang | 60| 77| 54|77|60| 60|
一只筷子 |golang | 54| 60| 0|77|54| 60|
两双筷子 |php | 80| 0| 80|80|80| 80|
dbkuaizi |php | 80| 80| 65|80|80| 80|
一只筷子 |php | 65| 80| 0|80|65| 80|
两双筷子 |sql | 85| 0| 55|85|85|NULL|
dbkuaizi |sql | 55| 85| 43|85|55| 55|
一只筷子 |sql | 43| 55| 0|85|43| 55|
函数名 | 说明 |
---|---|
lag(expr[,offset,default]) | 返回当前行前一行的值,如果没有前一行(当前行为第一行),则返回 Null offset: 默认为前一行,可以指定前 N 行,default: 若指定行不存在时返回此值而不是 Null |
lead(expr[,offset,default]) | 返回当前行后一行的值,如果没有后一行(当前行为最后一行),则返回 Null offset: 默认为后一行,可以指定后 N 行,default: 若指定行不存在时返回此值而不是 Null |
first_value(expr) | 返回当前行所在分区的第一行值,注意:逐行计算所以是以当前行为基础 |
last_value(expr) | 返回当前行所在分区的最后一行的值,注意:逐行计算所以是以当前行为基础,所以看起来是当前行。 若要获取分区最后一行,使用: rows between unbounded preceding and unbounded following |
nth_value(expr,N) | 返回分区的 第 N 行 |
其他函数
函数 | 作用 |
---|---|
typeof(X) | 返回数据类型 |
sqlite_version() | 返回 SQLite 版本 |
changes() | 返回最后完成的插入、更新、删除 SQL 所影响的行数 |
last_insert_rowid() | 返回最后完成的插入行的 ID |
参考资料
SQLite 官方文档:https://sqlite.org/docs.html