逻辑数仓支持的SQL语法_数据管理-阿里云帮助中心

逻辑数仓功能可以打通不同引擎，不同实例，跨区域，跨存储的OLAP、OLTP之间的壁垒，基于统一的SQL语法为用户提供跨库数据查询和数据同步的能力。本文介绍逻辑数仓支持的SQL语法。

背景信息

逻辑数仓的查询语法和函数基本兼容MySQL，因此可以使用MySQL的语法编写查询语句和函数，同时，还对逻辑数仓的语法进行扩展，支持创建和删除逻辑视图。

逻辑数仓的详细功能，请参见核心功能。

使用场景

当您需要对业务数据进行快捷分析时，可以在原始数据上（如：MySQL中的库表）定义一个逻辑视图并直接查询。如果需要基于原有分析结果做进一步分析，您还可以基于该逻辑视图级联地定义新的逻辑视图。

逻辑视图

创建逻辑视图

创建逻辑视图的语法结构如下：

CREATE VIEW <db_name>.<view_name> AS <query_statement>;


说明项	说明
`db_name`	DBLink名称。
`view_name`	自定义逻辑视图名称。
`query_statement`	SELECT语句：可通过`dblink.schema.table`引用逻辑数仓中已导入的库表。例如： `CREATE VIEW public.joined_view AS SELECT tb1.id AS id, tb2.name AS name FROM dblink_order.db1.tb1 JOIN dblink_product.product_db.tb2 ON tb1.id = tb2.id;` 支持级联定义，可通过`public.viewname`引用其他已定义的逻辑视图。例如： `CREATE VIEW public.filtered_view AS SELECT * FROM public.joined_VIEW WHERE id > 100;` 支持关联查询，可将其他已定义的逻辑视图与已导入的库表进行关联查询。例如： `CREATE VIEW filtered_view2 AS SELECT * FROM public.joined_view v1 JOIN dblink_order.db1.tb1 t1 ON v1.id = t1.id where t1.id>100;`

查询逻辑视图

所有逻辑视图均保存在名为public公共库的Schema中，因此查询逻辑视图时，需要在视图名称前带上前缀public.。

例如，查询名为filtered_view的逻辑视图，语法结构如下：

SELECT * FROM public.filtered_view;

删除逻辑视图

例如，删除逻辑视图的语法结构如下：

DROP VIEW public.view_name;

查看所有逻辑视图

例如，查看public库下的所有逻辑视图，语法结构如下：

SHOW VIEWS public;

其他

查看虚拟库列表

例如，显示所有已创建的虚拟库（包括public公共库），语法结构如下：

SHOW CATALOGS;

运算符

逻辑运算符


函数	说明	示例
AND	逻辑与	`a > 1 AND b < 10`
OR	逻辑或	`a > 1 OR b < 10`
NOT	逻辑非	`NOT(a > 1)`

比较运算符


函数	说明	示例
>	大于。	`a > 1`
<	小于。	`a < 1`
=	等于。	`a = 1`
>=	大于或等于。	`a >= 1`
<=	小于或等于。	`a <= 1`
<>	不等于。	`a <> 1`
!=	不等于。	`a != 1`
is null	是否为null值。	`a is null`
is not null	是否为非null。	`a is not null`
is distinct from	对比两个值的null安全运算符，避免返回空结果，返回结果必定为true或false。	`select null is distinct from null`
is not distinct from	对比两个值的NULL安全运算符，避免返回空结果，返回结果必定为true或false。	`select null is not distinct from null`
greatest(value1, value2, .., valuen)	返回所有值中的最大值。	-
least(value1, value2, .., valuen)	返回所有值中的最大值。	-
all	比较单个值与子查询返回值。	`select 21 < all (values 19, 20, 21)`
any/some	比较单个值与子查询返回值。	`select 'hello' = any (values 'hello', 'world')`
like	在where子句中搜索列中的指定模式。	`select * from (values ('abc'), ('bcd'), ('cde')) as t (name) where name like '_b%'` 说明 `%` 代表 0, 1或多个字符。 `_` 代表一个字符。

数值运算符


函数	说明
+	加法
-	减法
*	乘法
/	除法

函数

数学函数


函数	返回值类型	说明	示例
mod(x)	默认与x类型一致	取模。	`mod(101, 4)`
pow(x,y)	默认与x类型一致	指数运算。	`pow(3, 2)`
sqrt(x)	默认与x类型一致	平方根。	`sqrt(9)`
abs(x)	默认与x类型一致	绝对值。	`abs(3)`
cbrt(x)	double	立方根。	`cbrt(3)`
ceil(x)	-	ceiling函数的别名。	-
ceiling(x)	默认与x类型一致	返回大于给定数值表达式的最小整数。	`ceiling(5.5)`，返回值为6。
degrees(x)	double	将弧度转换为度数。	`degrees(1)`，返回值为57。
e(x)	double	返回欧拉常数。	-
exp(x)	double	返回取x次幂的欧拉常数。	-
floor(x)	默认与x类型一致	返回比x小的最大整数。	`floor(5.5`)，返回值为5。
from_base(string, radix)	bigint	将String的值转译成base-radix的数值。	-
ln(x)	double	返回x的自然对数。	-
log2(x)	double	返回以x为底2的对数。	`log2(2)`，返回值为1。
log10(x)	double	返回以x为底10的对数。	`log10(10)`，返回值为1。
pi()	-	返回常量pi。	-
power(x, p)	double	返回x的p次方。	-
rand()	-	random函数的别名。	-
random()	double	返回`[0,1.0)`范围内的伪随机值。	-
secure_rand()	-	secure_random的别名。	-
secure_random()	double	返回`[0, 1.0)`范围内安全、加密的随机值。	-
secure_random(lower, upper)	-	返回`[lower, upper)`范围内加密安全的随机值。	-
round(x)	默认与x类型一致	返回x四舍五入后最接近的整数。	`round(3.5)`，返回值为3.0。
round(x, d)	默认与x类型一致	返回x四舍五入到d位小数。	`round(3.14159, 2)`，返回值为3.14。
sign(x)	默认与x类型一致	返回x的符号。当x为正数时，返回1。当x为0时，返回0。当x为负数时，返回-1。当x为NaN时，返回NaN。	`sign(1)`，返回值为1。
to_base(x, radix)	varchar	返回x的以base-radix形式的字符串。	-
truncate(x)	double	返回x删除小数点后的整数。	truncate(2.5)，返回值为2。 truncate(-2.5)，返回值为-2。
truncate(x, n)	double	返回截取到小数点后n位的x。n可以为负数，以截取小数点左边的n位。	truncate(REAL '12.333', -1)，返回值为10.0。 truncate(REAL '12.333', 0)，返回值为12.0。 truncate(REAL '12.333', 1)，返回值为12.3。
acos(x)	double	返回x的反余弦。	-
asin(x)	double	返回x的反正弦。	-
atan(x)	double	返回x的反正切。	-
atan2(y, x)	double	返回y/x的反正切。	-
cos(x)	double	返回x的余弦。	-
cosh(x)	double	返回x的双曲余弦。	-
sin(x)	double	返回x的正弦。	-
tan(x)	double	返回x的正切。	-
tanh(x)	double	返回x的双曲正切。	-
infinity()	double	返回表示正无穷大的常数。	-
is_finite(x)	boolean	判断x是否为有限值。	-
is_infinite(x)	boolean	判断x是否为无限值。	-
is_nan(x)	boolean	判断x是否为非数字。	-
nan()	double	返回表示非数字的常量。	-

字符串函数


函数	返回值类型	说明	示例
replace	varchar	替换	`replace(name, 'old', 'new')`
md5	varchar	MD5哈希	`md5(name)`
\|\|	varchar	字符串拼接	`name1 \|\| name2`
trim	varchar	移除首尾字符	`trim('a' FROM name)`
lower	varchar	转小写	`lower('aBc')`
upper	varchar	转大写	`upper('abc')`
char_length	bigint	字符串长度	`char_length('abc')`
chr(n)	varchar	以单个字符串形式返回Unicode码n位。	-
codepoint(string)	integer	返回字符串中唯一字符的Unicode码。	-
concat(string1, ..., stringN)	varchar	字符串拼接函数，连接两个或多个字符串，从而组成一个新的字符串。	`concat('hello', ' ', 'world')`，拼接后为'hello world'。
hamming_distance(string1, string2)	bigint	返回string1和string2的汉明距离，即对应字符不同的位置数。说明两字符串的长度必须相同。	-
left(string, length)	varchar	返回从string左侧取length个字符后的字符串。	-
length(string)	bigint	返回string的长度。	-
levenshtein_distance(string1, string2)	bigint	返回string1和string2的Levenshtein编辑距离，即将string1改为string2所需的单字符编辑（插入、删除或替换）的最小数量。	-
lower(string)	varchar	将string转换为小写。	`lower('ABC')`，返回值为'abc'。
lpad(string, size, padstring)	varchar	使用padstring在string左侧添加字符串，使得string的大小变为size，如果size小于string的长度，结果将被截断为size大小，size不能为负，padstring必须非空。	-
ltrim(string)	varchar	删除string的前导空格。	-
replace(string, search)	varchar	从string中删除所有search。	-
replace(string, search, replace)	varchar	将string中所有的search替换成replace。	`replace('hello old', 'old', 'new')`，返回值为'hello new'。
reverse(string)	varchar	返回string的逆序字符串。	`reverse('abc')`，返回值为'cba'。
rpad(string, size, padstring)	varchar	使用padstring在string右侧添加字符串，使得string的大小变为size。如果size小于string的长度，结果将被截断为size大小。size不能为负，padstring必须非空。	-
rtrim(string)	varchar	删除string末尾的空格。	-
split(string, delimiter)	-	使用delimiter拆分string并返回数组。	-
split(string, delimiter, limit)	-	使用delimiter拆分string并返回大小最大为limit的数组。	-
strpos(string, substring)	bigint	返回string中第一个substring的起始位置。从第一个字符开始遍历，如果未找到，则返回0。	-
strpos(string, substring, instance)	bigint	返回string中第n个子字符串substring的位置。instance必须是一个正数，从第一个字符开始遍历，如果未找到，则返回0。	-
strrpos(string, substring)	bigint	返回string中最后一个子字符串substring的起始位置。从第一个字符开始遍历，如果未找到，则返回0。	-
strrpos(string, substring, instance)	bigint	返回string中从末尾开始第n个子字符串substring的位置。instance必须是一个正数，从第一个字符开始遍历，如果未找到，则返回0。	-
position(substring IN string)	bigint	返回string中第一个子字符串substring的起始位置。从第一个字符开始遍历，如果未找到，则返回0。	-
substring_index(str, delim, count)	varchar	返回分隔符delim出现次数count前字符串str的子字符串。	`substring_index('www.mysql.com', '.', 2)`
substr	-	substring函数的同名函数。	-
substring(string FROM start)	varchar	返回字符串string从start开始截取的子字符串。	`substring('foobarbar' FROM 4)`，返回值为barbar。
substring(string, start)	varchar	返回string从start开始截取的子字符串。	-
substring(string, start, length)	varchar	返回string从start开始截取length长度的子字符串。	-
trim(string)	varchar	删除字符串string中首尾的空格。	-
upper(string)	varchar	将字符串string中的小写字母转换为大写字母。	-
to_utf8(string)	varchar	将字符串string编译为UTF-8编码的二进制。	-
from_utf8(binary)	varchar	将二进制字符串解码为UTF-8编码格式，并使用默认字符U+FFFD替换无效的UTF-8字符。	-
from_utf8(binary, replace)	varchar	将二进制字符串解码为UTF-8编码格式，并使用自定义字符串替换无效的UTF-8字符。	-
key_sampling_percent(Varchar)	varchar	根据给定字符串的哈希值生成一个在0~1.0之间的值（double类型）。	-

聚合函数

聚合函数在GROUP BY语句中使用。


函数	返回值类型	说明
count(*)	bigint	返回行数
avg(x)	double	返回均值
sum(x)	bigint	返回总和
max(x)	bigint	返回最大值
min(x)	bigint	返回最小值
count(x)	bigint	返回非空值的个数
bool_and(boolean)	boolean	所有输入值都为true时，返回true，否则返回false。
bool_or(boolean)	boolean	输入值中有一个为true，则返回true，否则返回false。
count_if(x)	boolean	返回输入值为true的个数。
histogram	-	返回一个map，包含每个输入值出现的次数。

日期函数


函数	返回值类型	说明	示例
current_date	date	返回查询开始时的当前日期。	`SELECT current_date`。
current_time	time with time zone	返回查询开始时的当前时间。	-
current_timestamp	timestamp with time zone	返回当前日期、时间和时区。	-
current_timezone()	varchar	以IANA（例如，America / Los_Angeles）定义的格式返回当前时区，或以UTC的固定偏移量（例如+08：35）返回当前时区。	`SELECT current_timezone()` 。
date(x)	date	与`CAST(x AS date)`函数功能相同，可实现将x的类型转换为Date。	-
date_add(date, interval expr unit)	date	兼容MySQL语法的date_add函数。使用该函数可返回在date后增加interval的日期。	`date_add('2018-05-01', INTERVAL 1 DAY)`，返回值为`'2018-05-02'`。
date_diff(unit, timestamp1, timestamp2)	bigint	返回timestamp1与timestamp2的差值，该差值的单位为unit。unit的具体单位取值，请参见间隔函数。	-
date_trunc(unit, x)	-	返回x截取到单位unit之后的值。unit的具体单位取值，请参见间隔函数。	-
date_format(timestamp, format)	varchar	使用format指定的格式，将timestamp格式化成字符串。	`DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s')`。
date_parse(string, format)	date	使用format将string解析为时间戳。	-
last_day_of_month(x)	date	返回日期x当月的最后一天。	`last_day_of_month(current_date)`。
from_iso8601_timestamp(string)	timestamp with time zone	将ISO 8601格式化的字符串解析为具有时区的时间戳。	-
from_iso8601_date(string)	date	将ISO 8601格式的字符串解析为日期。	-
from_unixtime(unixtime)	timestamp	返回unixtime时间戳。	-
from_unixtime(unixtime, string)	timestamp with time zone	返回指定格式的unixtime时间戳。	-
from_unixtime(unixtime, hours, minutes)	timestamp with time zone	返回为hours和minutes对应时区的unixtime时间戳。	-
localtime	time	返回查询开始时的当前时间。	-
localtimestamp	timestamp	返回查询开始时的当前时间戳。	-
now()	timestamp with time zone	current_timestamp的别名。	-
to_iso8601(x)	varchar	将x格式化为ISO 8601字符串。x可以是date、timestamp或带时区的timestamp。	-
to_milliseconds(interval)	bigint	转换为以毫秒为单位的时间间隔interval。	-
to_unixtime(timestamp)	double	转换为unix时间戳。	-
day(x)	bigint	返回指定日期在当月的天数。	-
day_of_month(x)		`day(x)`函数的别名。
day_of_week(x)		返回指定日期对应的星期值，值范围从1（星期一）到7（星期天）。
day_of_year(x)		返回指定日期对应一年中的第几天，值范围从1到366。
dow(x)		day_of_week函数的别名。
doy(x)		day_of_year函数的别名
hour(x)		返回指定日期对应的小时，值范围从1到23。
millisecond(x)		返回指定时间的毫秒数。
minute(x)		返回指定时间的分钟数。
month(x)		返回指定日期对应的月份。
quarter(x)		返回指定日期对应的分钟。
second(x)		返回指定日期对应的秒数。
timezone_hour(timestamp)		返回从指定时间戳对应时区偏移的小时数。
timezone_minute(timestamp)		返回从指定时间戳对应时区偏移的分钟数。
week(x)		返回指定日期对应一年中的ISO week，值范围从1到53。
week_of_year(x)		week函数的别名。
year(x)		返回指定日期对应的年份。
year_of_week(x)		返回指定日期对应的ISO week的年份。
yow(x)		year_of_week函数的别名。

间隔函数支持的间隔单位如下：


单位	描述
millisecond	Milliseconds
second	Seconds
minute	Minutes
hour	Hours
day	Days
week	Weeks
month	Months
quarter	Quarters of a year
year	Years

条件表达式

标准的CASE表达式有两种形式。

遍历搜索每一个value，当valueN与expression的值相等，返回对应条件中的result，否则返回ELSE分支的result。
```
CASE expression
     WHEN value1 THEN result1
     [WHEN value2 THEN result2]
     [ELSE result3]
END
```

遍历搜索每一个condition（条件表达式），返回condition为true的result（返回结果）。

CASE WHEN condition1 THEN result1
     [WHEN condition2 THEN result2]
     [ELSE result3]
END


函数	说明	示例
if(condition, true_value)	若condition为true，则返回true_value，否则返回NULL。	`if(true, 1)`，返回值为1。 `if(false, 1)，返回值为NULL。`
if(condition, true_value, false_value)	若condition为true，则返回true_value，否则返回false_value。	`if(true, 1, 2)`，返回值为1。 `if(false, 1, 2)`，返回值为2。
coalesce(value1, value2[, ...])	返回第一个非NULL值。	`coalesce(NULL, NULL, 1)`，返回值为1。
ifnull(expr1, expr2)	若expr1不为NULL，则返回expr1，否则返回expr2。	`ifnull(1,0)`，返回值为1。 `ifnull(NULL,10)`，返回值为10。
nullif(expr1, expr2)	若expr1等于expr2，则返回NULL，否则返回expr1。	-
try(expression)	若expression中存在语法报错，则返回NULL。可以识别的三种报错：除数为0 类型转换（Cast）失败或函数参数无效数值越界	`coalesce(try(total_cost / packages), 0)`

其他函数


函数	返回值类型	说明	示例
json_extract(json, json_path)	-	从JSON中返回json_path指定的值。返回值为JSON类型。	`SELECT json_extract(json, '$.store.book');`
regexp_like(string, pattern)	boolean	计算正则表达式pattern并确定其是否包含在string中。	`SELECT regexp_like('1a 2b 14m', '\d+b');`