SQL语法
目录
1.语法支持
2.运算符
3.内置函数
3.1类型转换函数
3.2聚合函数
3.3字符串函数
3.4数学函数
3.5估算函数
3.6日期时间函数
3.7条件函数
4.附录
4.1 日期格式
4.2 关键字
语法支持
BLS 支持基础的 SELECT 查询,具体查询语法是
SELECT
select_expr [, select_expr] ...
[FROM subquery [AS] table_id]
[WHERE where_condition]
[GROUP BY {col_name | expr}, ... ]
[HAVING where_condition]
[ORDER BY {col_name | expr} [ASC | DESC], ...]
[LIMIT [offset,] row_count]
其中,where_condition 是一个执行结果为布尔值的条件表达式。不需要子查询的时候不写 FROM 子句。
字段名大小写敏感,且尽量避免使用关键字,如果使用了关键字要加反引号,例如:`action`。
运算符
一元前缀运算
操作符 | 示例 | 描述 |
---|---|---|
+/- | -A | 改变参数的符号 |
二元运算
操作符 | 示例 | 描述 |
---|---|---|
+ | A + B | 加法运算 |
- | A - B | 减法运算 |
* | A * B | 乘法运算 |
/ | A / B | 除法运算 |
% | A % B | 模数运算,结果是 A 除以 B 的余数 |
-> | column->path | json_extract()的简写,从指定列的JSON字符串中提取指定path的内容,例如 json->"$.b" |
关系运算
操作符 | 示例 | 描述 |
---|---|---|
= | A = B | 如果 A 等于 B,返回 TRUE,否则返回 FALSE。如果 A 与 B 的类型不可比较,返回 NULL |
!= | A != B | 如果 A 不等于 B,返回 TRUE,否则返回 FALSE。如果 A 与 B 的类型不可比较,返回 NULL |
> | A > B | 如果 A 大于 B,返回 TRUE,否则返回 FALSE。如果 A 与 B 的类型不可比较,返回 NULL |
>= | A >= B | 如果 A 大于等于 B,返回 TRUE,否则返回 FALSE。如果 A 与 B 的类型不可比较,返回 NULL |
< | A < B | 如果 A 小于 B,返回 TRUE,否则返回 FALSE。如果 A 与 B 的类型不可比较,返回 NULL |
<= | A <= B | 如果 A 小于等于 B,返回 TRUE,否则返回 FALSE。如果 A 与 B 的类型不可比较,返回 NULL |
[NOT] LIKE | A LIKE pattern | 如果 A [不]符合 pattern,返回 TRUE,否则返回 FALSE |
IS [NOT] NULL | A IS NULL | 如果 A [不]为 NULL,返回 TRUE,否则返回 FALSE |
IS [NOT] TRUE/FALSE | A IS TRUE | 如果 A [不]为 TRUE/FALSE,返回 TRUE,否则返回 FALSE |
BETWEEN | EXPR BETWEEB A AND B | 如果表达式 EXPR 的值大于等于 A 且小于等于 B,则返回 TRUE,否则返回 FALSE,等价于 EXPRESSION >= A AND EXPRESSION <= B |
逻辑运算
操作符 | 示例 | 描述 |
---|---|---|
[NOT] IN | A IN (val1, val2, ...) | 如果 A [不]等于 任何一个参数值,返回 TRUE,否则返回 FALSE |
AND | A AND B | 如果 A 和 B 都为 TRUE,返回 TRUE,否则返回 FALSE。如果 A 或 B 不是布尔类型,返回 NULL |
OR | A OR B | 如果 A 或 B 为 TRUE,返回 TRUE,否则返回 FALSE。如果 A 或 B 不是布尔类型,返回 NULL |
NOT | NOT A | 如果 A 为 FALSE,返回 TRUE,否则返回 FALSE。如果 A 不是布尔类型,返回 NULL |
内置函数
类型转换函数
函数签名 | 返回值 | 描述 | 示例 |
---|---|---|---|
cast(expr as <type>) | <type> | 将 expr 的值转换成 <type> 类型,<type> 支持 BIGINT, DECIMAL, VARCHAR, TIMESTAMP | >select cast("123" as BIGINT) 123 |
聚合函数
函数签名 | 返回值 | 描述 | 示例 |
---|---|---|---|
count(*),count(expr),count(DISTINCT expr) | Int | 计算符合条件的结果行数 | >select count(*) 10 |
sum(col) | T | 计算元素的和 | >select sum(num) 983 |
avg(col) | Double | 计算元素的平均值 | >select avg(num) 73.14 |
max(col) | T | 计算元素的最大值 | >select max(num) 99 |
min(col) | T | 计算元素的最小值 | >select min(num) 62 |
first(col) | T | 计算元素的首个值 | >select first(num) 87 |
last(col) | T | 计算元素的最后一个值 | >select last(num) 95 |
字符串函数
函数签名 | 返回值 | 描述 | 示例 |
---|---|---|---|
reverse(String str) | String | 返回顺序反转的字符串 | >select reverse("hello") olleh |
lower(String str) | String | 返回小写格式字符串 | >select lower("fOoBaR") foobar |
upper(String str) | String | 返回大写格式字符串 | >select upper("fOoBaR") FOOBAR |
capitalize(String str) | String | 返回所有单词首字母大写格式的字符串 | >select upper("fOoBaR") FOoBaR |
substring(String str, Int start [, Int len]) | String | 返回原字符串从 start 位置开始,长度为 len 的子串。start 从 1 开始,支持负数,此时从结尾开始反向计算位置。len 参数不传表示截取到字符串结尾 | >select substr("fOoBaR", 2, 4) OoBa >select substr("fOoBaR", -3, 2) Ba |
substr(String str, Int start [, Int len]) | String | substring() 的别名 | |
replace(String str, String OLD, String NEW) | String | 返回 OLD 子串被替换为 NEW 子串的字符串 str | >select replace("abcdef", "abc", "cba") cbadef |
length(String str) | Int | 返回字符串的长度 | >replace("abcdef", "abc", "cba") cbadef |
locate(String substr, String str) | Int | 返回字符串 str 中 substr 的首个出现位置,如果没有则返回 0 | >select locate(".", "3.14") 2 |
position(String substr, String str) | Int | locate() 的别名 | |
concat(String A, String B...) | String | 返回所有参数按照传入顺序拼接成的字符串 | >select concat("foo", "bar") foobar |
json_extract(String json, String path) | T | 从JSON字符串中提取指定path的内容 | >select json_extract("{"a": 1, "b": 2}", "$.a") 1 |
regexp_like(String str, String regexp) | Boolean | 字符串是否匹配给定的正则表达式 | >select regexp_like("abc", "[a-z]+") true |
regexp_extract(String str, String regexp) | String | 从字符串中提取出第一个符合正则表达式的子串 | >select regexp_extract("abc", "[a-z]+") abc |
regexp_extract_all(String str, String regexp) | Array<String> | 从字符串中提取出所有符合正则表达式的子串 | >select regexp_extract_all("abc22abc", "[a-z]+") [abc,abc] |
数学函数
函数签名 | 返回值 | 描述 | 示例 |
---|---|---|---|
abs(Double a), abs(Int a) | Double/Int | 计算绝对值 | >select abs(-2) 2 |
sqrt(Double a) | Double | 计算平方根 | >select sqrt(100) 10 |
greatest(T v1, T v2, ...) | T | 计算参数中的最大值,如果任何一个参数是 Null,则返回 Null | >select greatest(1, 3.14, -5) 3.14 |
least(T v1, T v2, ...) | T | 计算参数中的最小值,如果任何一个参数是 Null,则返回 Null | >select least(1, 3.14, -5) -5 |
rand() | Double | 返回一个0到1之间的随机数,数据集的每一行得到的随机数不同 | >select rand() 0.3 |
ceil(Double a) | Int | 返回大于等于 a 且最接近 a 的整数 | >select ceil(3.14) 4 |
floor(Double a) | Int | 返回小于等于 a 且最接近 a 的整数 | >select floor(3.14) 3 |
log(Double a) | Double | 计算以2为底的对数值 | >select log(32) 5 |
ln(Double a) | Double | 计算自然对数 | >select ln(100) 4.61512051684126 |
pow(Double a, Double p) | Double | 计算 a 的 p 次方 | >select pow(2, 5) 32 |
估算函数
函数签名 | 返回值 | 描述 | 示例 |
---|---|---|---|
percentile(Double x, Double percentage01, Double percentage02...) | Array<Double> | 对x进行正序排列,返回处于percentage01、percentage02...位置的x | >select percentile(latency, 0.1, 0.2) [0.22, 0.35] |
日期时间函数
基本函数
函数签名 | 返回值 | 描述 | 示例 |
---|---|---|---|
now() | DateTime | 返回当前本地时间 | >select now() 2020-01-16T08:30:50Z |
current_timestamp() | DateTime | now() 的别名 | |
unix_timestamp([String/DateTime date[, String format]]) | Int | 将日期时间字符串或 DateTime 类型数值按照 format 格式转换成 Unix timestamp。默认支持 ISO8601 格式,根据字符串中的时区解析。如果根据 format 格式解析,将使用本地时区。 | >select unix_timestamp("2019-11-11T11:11:11Z") 1573470671 >select unix_timestamp("2019-11-11 11:11:11", "%Y-%m-%d %H:%i:%s") 1573441871 |
from_unixtime(Int unixtime[, String format]) | String | 将 unixtime(从 1970-01-01 00:00:00 UTC 到现在到秒数)转换成表示本地时间的字符串,默认格式为"1970-01-01 00:00:00",可以通过 format 指定字符串格式。支持的 date_format 请参考附录 | >select from_unixtime(0) 1970-01-01 08:00:00 >select from_unixtime(unix_timestamp("2019-11-11T11:11:11+08:00")) 2019-11-11 11:11:11 |
str_to_date(String str, String format) | DateTime | 根据 format 解析日期时间字符串 str | >select str_to_date("2019-11-11 11:11:11", "%Y-%m-%d %H:%i:%s") 2019-11-11T03:11:11Z |
year(String/DateTime date) | Int | 返回日期 date 的年份 | >select year("2019-11-07T09:09:16+08:00") 2019 |
month(String/DateTime date) | Int | 返回日期 date 的月份 | >select year("2019-11-07T09:09:16+08:00") 11 |
day(String/DateTime date) | Int | dayofmonth() 的别名 | |
weekday(String/DateTime date) | Int | 返回日期 date 在一星期中的位置 (0 = 星期一, 1 = 星期二, ... 6 = 星期日) | >select weekday("2019-11-07T09:09:16+08:00") 3 |
dayofyear(String/DateTime date) | Int | 返回日期 date 在一年中的位置,可选值从 1 到 366 | >select dayofyear("2019-11-07T09:09:16+08:00") 311 |
dayofmonth(String/DateTime date) | Int | 返回日期 date 在一个月中的位置 | >select dayofmonth("2019-11-07T09:09:16+08:00") 7 |
dayofweek(String/DateTime date) | Int | 返回日期 date 在一星期中的位置 (1 = 星期日, 2 = 星期一, ... 7 = 星期六) | >select dayofweek("2019-11-07T09:09:16+08:00") 5 |
时间分组函数
函数描述:持按固定时间间隔对日志数据进行分组聚合统计,例如统计每5分钟的访问次数等场景(目前只新架构日志集支持该函数,新架构预计在Q2铺全)
函数格式:histogram(time_column, interval)
参数说明:
参数 | 说明 |
---|---|
time_column | 时间列(KEY),例如 @timestamp,该列的值必须为毫秒的 long 类型 unix 时间戳或 timestamp 类型的日期时间表达式。如果时间列不符合上述要求,可以使用 cast 函数将 ISO8601 格式的时间字符串转换为 timestamp 类型,例如cast('2020-08-19T03:18:29.000Z' as timestamp) 注意:时间列使用 timestamp 时,其对应的日期时间表达式需要为 UTC+0 时区。如果日期时间表达式本身为其他时区,需通过计算调整为 UTC+0 时区。例如原始时间为北京时间(UTC+8)时,使用cast('2020-08-19T03:18:29.000Z' as timestamp) - interval 8 hour进行调整。 |
interval | 固定时间间隔,支持单位为 second(秒)、minute(分)、hour(小时)、day(天)、week(周)、month(月)。例如时间间隔5分钟,即 interval 5 minute。 |
示例:
统计每5分钟访问次数 PV 值:select histogram(cast(@timestamp as timestamp),interval 5 minute) as t,count(*) group by t order by t
条件函数
函数签名 | 返回值 | 描述 | 示例 |
---|---|---|---|
if(Boolean testCondition, T valueTrue, T valueFalseOrNull) | T | 如果测试条件为 true,返回 ValueTrue,否则返回 ValueFalseOrNull | >select if(2>1, 1, 0) 1 |
nullif(T a, T b) | T | 如果 a = b,返回 Null,否则返回 a | >select nullif(1, 1) null |
coalesce(T v1, T v2, ...) | T | 返回第一个不是 Null 的值,如果参数都是 Null,返回 Null | >select coalesce(null, 0, false, 1) 0 |
CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END | T | 如果 a = b,返回 c;如果 a = d,返回 e;否则返回 f | >select case substring("abc", 1, 1) when "a" then "a" when "b" then "b" else "c" end a |
CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END | T | 如果 a = true,返回 b;如果 c = true,返回 d;否则返回 e | >select case when substring("abc", 1, 1) = "a" then "a" when 2 > 1 then "b" else "c" end a |
附录
日期格式
时间函数支持的 date_format
占位符 | 描述 |
---|---|
%a | Abbreviated weekday name (Sun..Sat) |
%b | Abbreviated month name (Jan..Dec) |
%c | Month, numeric (0..12) |
%D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) |
%d | Day of the month, numeric (00..31) |
%e | Day of the month, numeric (0..31) |
%f | Microseconds (000000..999999) |
%H | Hour (00..23) |
%h | Hour (01..12) |
%I | Hour (01..12) |
%i | Minutes, numeric (00..59) |
%j | Day of year (001..366) |
%k | Hour (0..23) |
%l | Hour (1..12) |
%M | Month name (January..December) |
%m | Month, numeric (00..12) |
%p | AM or PM |
%r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
%S | Seconds (00..59) |
%s | Seconds (00..59) |
%T | Time, 24-hour (hh:mm:ss) |
%W | Weekday name (Sunday..Saturday) |
%w | Day of the week (0=Sunday..6=Saturday) |
%Y | Year, numeric, four digits |
%y | Year, numeric (two digits) |
%% | A literal % character |
关键字
#
_binary
_utf8mb4
A - F
accessible action add against all alter analyze and as asc asensitive auto_increment before begin between bigint binary bit blob bool boolean both by call cascade case cast change char character charset check collate collation column columns comment commit committed condition constraint continue convert create cross current_date current_time current_timestamp current_user cursor database databases date datetime day_hour day_microsecond day_minute day_second dec decimal declare default delayed delete desc describe descriptor deterministic distinct distinctrow div double drop duplicate each else elseif enclosed end engines enum escape escaped exists exit expansion explain false fetch fields float float4 float8 flush for force foreign from full fulltext
G - N
generated geometry geometrycollection get global grant group group_concat having high_priority hour_microsecond hour_minute hour_second if ignore in index infile inner inout insensitive insert int int1 int2 int3 int4 int8 integer interval into io_after_gtids is isolation iterate join json json_extract key key_block_size keys kill language last_insert_id leading leave left less level like limit linear lines linestring load localtime localtimestamp lock long longblob longtext loop low_priority master_bind match maxvalue mediumblob mediumint mediumtext middleint minute_microsecond minute_second mod mode modifies multilinestring multipoint multipolygon names natural nchar next no no_write_to_binlog not null numeric
O - S
off offset on only optimize optimizer_costs option optionally or order out outer outfile partition plugins point polygon precision primary procedure processlist query read read_write reads real references regexp release rename reorganize repair repeat repeatable replace require resignal restrict return revoke right rlike rollback schema schemas second_microsecond select sensitive separator serializable session set share show signal signed smallint spatial specific sql sql_big_result sql_cache sql_calc_found_rows sql_no_cache sql_small_result sqlexception sqlstate sqlwarning ssl start starting status stored straight_join stream string substr substring
T - Z
table tables terminated text than then time timestamp timestampadd timestampdiff tinyblob tinyint tinytext to trailing transaction trigger true truncate uncommitted undo union unique unlock unsigned update usage use using utc_date utc_time utc_timestamp values varbinary varchar varcharacter variables varying view vindex vindexes virtual vitess_keyspaces vitess_shards vitess_tablets vitess_target vschema vschema_tables warnings when where while with write xor year year_month zerofill