文档

逻辑数仓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),返回值为4。

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');

  • 本页导读 (1)