SQLite 函数

聚合函数

数值函数

更多函数参考：https://www.sqlite.org/lang_mathfunc.html

逻辑函数

字符串函数

时间函数

• timestring 时间字符串
• modifier 修饰符
• format 格式化

上面的几个时间函数，将时间字符串作为参数，时间字符串后面跟零个或多个 modifier 修饰符。

时间字符串

时间字符串可以使用下面任意一种格式：

修饰符

时间字符串后边可跟着零个或多个的修饰符，这将改变有上述五个函数返回的日期和/或时间。任何上述五大功能返回时间。修饰符应从左到右使用，下面列出了可在 SQLite 中使用的修饰符：

格式化

SQLite 提供了非常方便的函数用来格式化任何日期和时间。

时间函数示例

-- 查看当前时间，以及 当前时间 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 略少。

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 示例
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|

值类

在同一分区，基于排序结果，对值的各种操作：主要关注 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|

其他函数

参考资料
SQLite 官方文档：https://sqlite.org/docs.html