Sqlite常用函数

聚合函数

sum(X)      组中所有非 NULL 值的总和
total(X)    同sum(X)，不同的是如果全部为NULL，sum返回NULL，total返回0.0
max/min(X)  返回组中所有值的最大/小值
avg(X)      组中所有非 NULL 值的平均值
        无法转换为数字的字符串和 BLOB 被解释为 0
        结果始终是一个浮点值，即使所有输入都是整数

count(X)     返回组中不为NULL的数据量

group_concat(X)
group_concat(X,Y)
string_agg(X,Y)
             将组 X 中的所有非 NULL 值视为字符串并使用 Y 作为分隔符链接起来，Y默认为逗号 ","
             group_concat与 MySQL 兼容，string_agg是别名函数，与PostgreSQL 和 SQLServer 兼容

Sqlite库信息相关

sqlite_compileoption_get(N)
    返回用于构建 SQLite 的第 N 个编译选项，如果 N 超出范围，则返回 NULL

sqlite_compileoption_used(X)
    返回在构建过程中是否使用了 X 编译选项

sqlite_offset(X)
    返回在某一列中读取值的记录开头的字节偏移量
    如果 X 不是普通表中的列，则 返回 NULL

sqlite_source_id()
    返回一个字符串，标识用于构建 SQLite 库的源代码的特定版本

sqlite_version()
    返回正在运行的 SQLite 库的版本字符串

数字相关

random()     返回一个-9223372036854775808和+9223372036854775807之间的伪随机整数
abs(X)       绝对值，null返回null，无法转换为数值的字符串或blob返回0.0
max/min(X,Y,...)
             返回所有参数中的最大/小值，所有参数都为 NULL 返回 NULL
round(X)
round(X,Y)
             返回一个保留 num2 位小数点的浮点数
             如果 num2 参数被省略或为负数，则被视为 0
sign(X)      判断num的正负，负、零或正分别返回-1、0 或+1
             NULL或者一个不能被转换为数字的字符串或blob返回NULL

字符串相关

lower(X)     转换为小写
upper(X)     转换为大写

instr(X,Y)   查找 Y 在 X 中第一次出现时的从 1 开始的字符索引
        如果在 X 中找不到 Y，则返回 0
        X 或 Y 为NULL，则结果为 NULL
        如果 X 和 Y 都是 BLOB，返回 Y 第一次出现的字节索引(从1开始)
        X 和 Y 非 NULL 且不是 BLOB，那么都被解释为字符串

concat(X,...)        将所有非 NULL 参数以字符串形式进行连接
concat_ws(SEP,X,...) 将所有非 NULL 参数以字符串形式使用SEP作为分隔符进行连接
        SEP为 NULL 则返回 NULL
char(X1,X2,...,XN)   返回由 Unicode 代码点值分别为整数 X1 到 XN 的字符组成的字符串


format(FORMAT,...)
printf(FORMAT,...)
        格式化输出
        只有格式化参数，没有具体的参数返回null
        如果参数少于格式化中的参数，缺少的按null处理，转换为数值0或空字符串
        常用的格式：
        d / i   十进制整数
        u       十进制无符号整数
        f       双精度浮点数
        e / R   双精度指数表示
        x / X   十六进制整数
        o       八进制整数
        s / z   字符串
        q / Q   字符串，会将字符串中的单引号加倍以可以安全地出现在SQL字符串文字中
        n       默认忽略并且不消耗参数位


trim/ltrim/rtrim(X[,Y])
            从 X 的 两侧/左侧/右侧 删除 Y 中出现的任何字符，Y默认为空格


quote(X)    转义字符串以便可以在SQL中安全的使用
        BLOB编码为十六进制字符

replace(X,Y,Z)  将 X 中的所有子字符串 Y 替换为 Z


substr(X,Y[,Z])
substring(X,Y[,Z])
    截取 X 从第 Y 个字符(从1开始)，长度为 Z 的字符串
        省略 Z 默认到字符串末尾
        Y 为负数表示从右侧数第几个字符
        Z 为负数表示第 Y 个字符之前的 abs(Z) 个字符

length(X)        返回X的码点(不是字节)数量
octet_length(X)  返回X的字节长度
        X 为 NULL 返回 NULL
        blob 返回 blob 中的字节数
        X 是数字，返回 X 的字符串表示的长度

BLOB相关

hex(X)         将其参数解释为一个BLOB并返回大写十六进制表示字符串
randomblob(N)  返回一个包含伪随机字节的 N 字节 blob
        如果 N 小于 1，则返回 1 字节的随机 blob
unhex(X[,Y])   将 X 作为十六进制字符串并返回其 BLOB 值
        如果 X 包含任何不是十六进制字符且不在 Y 中的字符，则返回 NULL
        字符串 Y 表示要忽略的非十六进制字符，默认为空
        如果 X 或 Y 为 NULL，则返回 NULL
zeroblob(N)    返回由 N 个 0x00 字节组成的 BLOB
        用于为稍后使用写入的 BLOB 保留空间
        SQLite可以非常有效地管理这些zeroblob

选择相关

coalesce(X,Y,...)   返回第一个非NULL参数的副本
        如果所有参数都为 NULL，则返回 NULL
        必须至少有 2 个参数
ifnull(X,Y)         返回第一个非NULL参数的副本
        等效于两个参数的 coalesce()
nullif(X,Y)         参数不同时返回第一个参数，参数相同返回NULL

表达式相关

glob(X,Y)     等价于 Y GLOB X
        name GLOB '*helium*' 等价于 glob('*helium*',name)
like(X,Y[,Z]) 等价于 Y LIKE X [ESCAPE Z]
        name LIKE '%neon%' 等价于 like('%neon%',name)

逻辑相关

if(B1,V1,...)
iif(B1,V1,...)
    返回与第一个真布尔值相关联的值，至少需要两个参数
    if()函数是 iif()的别名函数
    接受两两成对的参数，每对的第一个是布尔值，第二个是布尔值为真时的返回值
    参数数量是偶数，所有布尔参数都为 false 返回 NULL
    参数数量为奇数，最后一个参数是在所有之前的布尔参数均为 false 时返回的值
    实际上是 CASE 表达式的一种简写形式，if(X，Y，Z)等价于 CASE WHEN X THEN Y ELSE Z END
    函数使用短路求值，参数仅在计算最终结果所必需的情况下进行计算

其他函数

changes()            返回最近完成的行更改语句影响的行数
total_changes()      返回自打开当前数据库连接以来行更改语句影响的总行数
last_insert_rowid()  插入最后一行的 rowid

typeof(X)            返回表示 X 数据类型的字符串

likelihood(X,Y)      向查询规划器提供一个提示，X 为真的概率大约为 Y
        是一个空操作，代码生成器会将其优化掉，以便在运行时不消耗CPU周期
        Y 必须是介于 0.0 和 1.0 之间的浮点常数
likely(X)       likelihood(X，0.0625)的简写
unlikely(X)     likelihood(X，0.9375)的简写

日期和时间函数

在Sqlite中，并没有日期和时间相关的数据类型，但是Sqlite为我们准备了一些函数用来将某些特定格式的字符串或数字作为日期和时间来解析

date(time-value, modifier, ...)      返回 YYYY-MM-DD
time(time-value, modifier, ...)      返回 HH：MM：SS
datetime(time-value, modifier, ...)  返回 YYYY-MM-DD HH：MM：SS
julianday(time-value, modifier, ...) 返回儒略日float64
        从公元前 4714 年 11 月 24 日格林威治时间中午开始的天数的小数
unixepoch(time-value, modifier, ...) 返回时间戳
strftime(format, time-value, modifier, ...)
    自定义时间字符串的格式
    %Y    4位年
    %m    有前导0的月
    %d    有前导0的日      %e    无前导0的日

    %H    有前导0的24小时   %k   无前导0的24小时
    %I    有前导0的12小时   %l   无前导0的12小时
    %M    有前导0的分
    %S    有前导0的秒       %f   小数秒：SS.SSS
    %p/%P    ap或pm

    %s    时间戳
    %j    一年中的第几天
    %u    星期(1-7)，7表示星期日
    %w    星期(0-6)，0表示星期日

    %F    YYYY-MM-DD
    %T    HH:MM:SS
    %R    时:分   HH:MM

timediff(time-value, time-value)
    返回一个描述了为达到时间 A 而必须添加到 B 的时间量的字符串
    格式为： ±YYYY-MM-DD HH:MM:SS.SSS

time-value 支持的格式

当不传入任何参数时默认传入"now"，表示获取当前日期和时间

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
    以上格式在最后可以选择性的添加"[+-]HH：MM"表示增加或减少指定的时间

now

DDDDDDDDDD  (时间戳)

modifier：日期时间修饰符

在日式和时间函数中，在time-value后面可以附加若干个修改日期时间的修饰符

每个修饰符都是应用于其左侧时间值的转换，修饰符是从左到右应用的

±float64 day[s]
±float64 hour[s]
±float64 minute[s]
±float64 second[s]
±float64 month[s]
±float64 year[s]
    以上语句中的复数形式的字符"s"可以省略

±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

ceiling
floor
    一个月有长有短，那么就造成
        2024-02-29后一年的日期是2025-02-28 还是 2025-03-01
        2023-12-31后两个月的日期是2024-02-29 还是 2024-03-02
        ceiling选择较晚的日期，默认为该值
        floor选择较晚日期前一个月的最后一天

start of month
start of year
start of day
    将日期向后移动至月、年、天的开始日期和时间

weekday N
    将日期向前推进到指定的周
    星期天是 0，星期一是 1

unixepoch
    只能紧跟在 DDDDDDDDDD 格式的时间值之后
    DDDDDDDDDD被解释为儒略日数字


auto
    必须紧跟在初始时间值数字之后
    根据时间值的大小被解释为儒略历或时间戳：
        在 0.0 和 5373484.499999 之间被解释为儒略历日数
        在-210866760000 到 253402300799 的数值解释为时间戳
        其他数值 NULL 返回

localtime
    假设其左侧的时间值是通用协调时间（UTC），并调整该时间值为本地时间
    如果跟在一个不是 UTC 的时间之后，那么行为是未定义的

utc
    与localtime相反
    假设其左侧的时间值是本地时间，并调整该时间值为UTC
    如果左边的时间不是本地时间，那么结果是未定义的

subsec
subsecond
    缩写为subsec，增加输出的分辨率，
    datetime（）、time（） 和 unixepoch（），以及 strftime（） 中的“%s”格式字符串。
    修饰符对其他日期/时间函数没有影响。
    当前的Sqlite版本实现将分辨率从秒到毫秒
    subsecond和subsec可以作为日期/时间函数的第一个参数出现（或strftime（）格式字符串之后的第一个参数）
    当这种情况发生时，通常在第一个参数中的时间值被理解为"now"
    如当前时间时间戳的捷径：SELECT unixepoch('subsec');