数据库管理跨库联查 SQL 语法介绍

一、SELECT 查询的基本语法结构

跨库联查目前仅支持 SELECT 查询。

二、详细解释

SELECT [ ALL | DISTINCT ] select_expression [, …]

·SELECT：指定要检索的列或计算表达式。

·ALL：（默认）返回所有记录。

·DISTINCT：返回唯一记录，去除重复结果。

·select_expression：要选择的列名、表达式或计算结果，可以包含一个或多个，用逗号分隔。

FROM from_item [, …]

·FROM：指定查询的数据源，如表、视图或子查询。

·from_item：一个或多个数据源，支持表名、视图、子查询等。

WHERE condition

WHERE：用于筛选记录，condition 是条件表达式，只有满足条件的记录会被包含在结果集中。

GROUP BY [ ALL | DISTINCT ] grouping_element [, …]

·GROUP BY：将结果集按一个或多个列进行分组，通常用于聚合函数。

·ALL：（默认）对所有记录进行分组。

·DISTINCT：对唯一记录进行分组。

·grouping_element：用于分组的列或表达式。

HAVING condition

HAVING：对分组后的结果进行筛选，类似于 WHERE，但作用于聚合结果。

WINDOW window_definition_list

WINDOW：定义窗口函数的窗口，这些窗口可以用于计算复杂的聚合，如移动平均。

{ UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select

·UNION：合并多个查询的结果，去重（使用 ALL 保留所有记录）。

·INTERSECT：返回所有查询结果的交集，仅包含在所有查询中出现的记录。

·EXCEPT：返回第一个查询的结果中存在但其他查询中不存在的记录。

·ALL 和 DISTINCT：控制重复记录的处理。

ORDER BY expression [ ASC | DESC ] [, …]

·ORDER BY：对结果集进行排序。

·expression：排序依据的列或表达式。

·ASC：升序（默认）。

·DESC：降序。

OFFSET count [ ROW | ROWS ]

·OFFSET：跳过结果集的前 count 行。

·ROW 和 ROWS：指定跳过的行数，ROW 和 ROWS 通常可以互换使用。

LIMIT { count | ALL }

·LIMIT：限制返回的记录数量。

·count：返回的记录数。

·ALL：不限制返回记录数（默认，通常可以省略）。

FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

·FETCH：控制从结果集中返回的行数，并可以选择是否包含具有相同排序值的行。

·FIRST 或 NEXT：获取结果集中的第一行或接下来的行。

·count：要返回的行数。

·ROW和 ROWS：指定返回的行数。

·ONLY：只返回指定数量的行。

·WITH TIES：包括与最后一行具有相同排序值的记录。

三、关键字及标识符

关键字标识符

下表列出了 MPP 中所有保留的关键字及其在 SQL 标准中的状态。这些保留关键字必须用双引号括起来才能用作标识符。

Keyword	SQL:2016
ALTER	reserved
AND	reserved
AS	reserved
BETWEEN	reserved
BY	reserved
CASE	reserved
CAST	reserved
CONSTRAINT	reserved
CREATE	reserved
CROSS	reserved
CUBE	reserved
CURRENT_CATALOG	reserved
CURRENT_DATE	reserved
CURRENT_PATH	reserved
CURRENT_ROLE	reserved
CURRENT_SCHEMA	reserved
CURRENT_TIME	reserved
CURRENT_TIMESTAMP	reserved
CURRENT_USER	reserved
DEALLOCATE	reserved
DELETE	reserved
DESCRIBE	reserved
DISTINCT	reserved
DROP	reserved
ELSE	reserved
END	reserved
ESCAPE	reserved
EXCEPT	reserved
EXISTS	reserved
EXTRACT	reserved
FALSE	reserved
FOR	reserved
FROM	reserved
FULL	reserved
GROUP	reserved
GROUPING	reserved
HAVING	reserved
IN	reserved
INNER	reserved
INSERT	reserved
INTERSECT	reserved
INTO	reserved
IS	reserved
JOIN	reserved
JSON_ARRAY	reserved
JSON_EXISTS	reserved
JSON_OBJECT	reserved
JSON_QUERY	reserved
JSON_TABLE	reserved
JSON_VALUE	reserved
LEFT	reserved
LIKE	reserved
LISTAGG	reserved
LOCALTIME	reserved
LOCALTIMESTAMP	reserved
NATURAL	reserved
NORMALIZE	reserved
NOT	reserved
NULL	reserved
ON	reserved
OR	reserved
ORDER	reserved
OUTER	reserved
PREPARE	reserved
RECURSIVE	reserved
RIGHT	reserved
ROLLUP	reserved
SELECT	reserved
SKIP	reserved
TABLE	reserved
THEN	reserved
TRIM	reserved
TRUE	reserved
UESCAPE	reserved
UNION	reserved
UNNEST	reserved
USING	reserved
VALUES	reserved
WHEN	reserved
WHERE	reserved
WITH	reserved

标识符是用来识别 DMC 数据源、数据库、表、列、函数或其他对象名称的标记。

四、标识符命名规范

必须以字母开头，随后可以包括字母数字字符和下划线。包含其他字符的标识符必须用双引号（"）括起来。当用双引号括起来时，标识符可以使用任何字符。在带引号的标识符中，用另一个前导双引号转义双引号（"）。标识符不区分大小写。

五、有效的标识符示例

tablename
SchemaName
example_dmc_resource.a_schema."table$partitions"
"identifierWith""double""quotes"

六、必须使用双引号括起来才能使用的标识符示例

"table-name"
"123DbName"
"colum$name@field"

七、运算符

逻辑运算符比较运算符数值运算符

函数	说明	示例
AND	如果值都为 true，那么结果为 true	a AND b
OR	只要有一个值为 true，那么结果为 true	a OR b
NOT	如果值为 false，那么返回 true	NOT a

函数	说明	示例
<	小于	a > 1
>	大于	a < 1
<=	小于等于	a = 1
>=	大于等于	a >= 1
=	等于	a <= 1
<>	不等于	a <> 1
!=	不等于	a != 1

函数	说明
+	加法
–	减法
*	乘法
/	除法
%	取模

八、函数

数学函数字符串函数聚合函数日期函数开窗函数

函数	返回值类型	说明	示例
mod(n, m)	默认与 x 类型一致	返回 n 除以 m 的余数。	mod(101, 4)
sqrt(x)	double	平方根。	sqrt(9)
abs(x)	默认与 x 类型一致	绝对值。	abs(3)
cbrt(x)	double	立方根。	cbrt(3)
ceil(x)	默认与 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。
ln(x)	double	返回 x 的自然对数。	–
log(b, x)	double	返回以 b 为底的x的对数。	–
log2(x)	double	返回以 x 为底2的对数。	log2(2)，返回值为1。
log10(x)	double	返回以 x 为底10的对数。	log10(10)，返回值为1。
pi()	double	返回常量 pi。	–
pow(x,p)	double	等价于 power()。	pow(3, 2)
power(x, p)	double	返回 x 的 p 次方。	–
radians(x)	double	将角度 x（以度为单位）转换为弧度。	–
round(x)	默认与 x 类型一致	返回 x 四舍五入到最接近的整数。	–
round(x, d)	默认与 x 类型一致	返回 x 四舍五入到小数点后 d 位。	–
sign(x)	默认与 x 类型一致	返回 x 所对应的正负号，x > 0 返回1；x< 0，返回-1；x=0，返回0。	–
truncate(x)	double	返回截取小数点后的数。	truncate(4.9)，返回值为4
rand()	double	random函数的别名。	–
random()	double	返回[0,1.0)范围内的随机值。	–
random(n)	默认与 x 类型一致	返回[0,n)范围内的随机值。	–
random(m, n)	默认与 x 类型一致	返回[0,m)范围内的随机值。	–

函数	返回值类型	说明	示例
chr(n)	varchar	以单个字符串形式返回 Unicode码 n 位。	–
codepoint(string)	integer	返回字符串中唯一字符的Unicode 码。	–
concat(string1, …, stringN)	varchar	字符串拼接函数，连接两个或多个字符串，从而组成一个新的字符串。	concat('hello', ' ', 'world')，拼接后为'hello world'。
concat_ws(string0, string1, …, stringN)	varchar	使用 string0 作为分隔符返回 string1、string2、…、stringN 的串联。如果 string0 为 null，则返回值为 null/ 分隔符后面的参数中提供的任何空值都将被跳过。	–
concat_ws(string0, array(varchar))	varchar	使用 string0 作为分隔符返回数组中元素的串联. 如果 string0 为 null，则返回值为 null。数组中的任何空值都会被跳过。	–
format(format, args…)	varchar	查看 format()。
hamming_distance(string1, string2)	bigint	返回 string1和 string2的汉明距离，即对应字符不同的位置数。说明：两字符串的长度必须相同。	–
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 的前导空格。	–
position(substring IN string)	bigint	返回 string 中 substring 的第一个出现的位置，位置从1开始。如果未找到，则返回0。	–
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 必须非空。	SELECT rpad('123', 5, '0');
rtrim(string)	varchar	删除 string 末尾的空格。	–
split(string, delimiter)	–	将字符串 string 按照指定的分隔符 delimiter 拆分为多个子字符串，并返回一个数组。	–
split(string, delimiter, limit)	–	使用 delimiter 拆分 string 并返回大小最大为 limit 的数组。	–
split_part(string, delimiter, index)	varchar	将字符串 string 按照指定的分隔符 delimiter 拆分为多个部分，并返回第 index 个部分。索引从 1 开始。	–
split_to_map(string, entryDelimiter, keyValueDelimiter)	–	返回 entryDelimiter 和 keyValueDelimiter 拆分字符串后的 map。	select split_to_map('a:1,b:2', ',', ':')，返回值为{'a':'1','b':'2'}。
strpos(string, substring)	bigint	返回 string 中第一个 substring 的起始位置。从第一个字符开始遍历，如果未找到，则返回0。	–
strpos(string, substring, instance)	bigint	返回 string 中第 n 个子字符串substring 的位置。instance 必须是一个正数，从第一个字符开始遍历，如果未找到，则返回0。	–
starts_with(string, substring)	boolean	判断 string 是否是以 substring 开头的字符串。	–
substr(string, start)	varchar	substring 函数的同名函数。	–
substr(string, start, length)	varchar	substring 函数的同名函数。	–
substring(string, start)	varchar	返回 string 从 start 开始截取的子字符串。	–
substring(string, start, length)	varchar	返回 string 从 start 开始截取 length 长度的子字符串。	–
translate(source, from, to)	varchar	把 source 中的所有 from 替换为 to。	–
trim(string)	varchar	删除字符串 string 中首尾的空格。	–
trim([ [ specification ] [ string ] FROM ] source )	varchar	删除 source 中指定的任何前导和/或尾随字符。	–
upper(string)	varchar	将字符串 string 中的小写字母转换为大写字母。

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

函数	返回值类型	说明
any_value(x)	默认与 x 类型一致	返回任意非空值 x（如果存在）。x 可以是任何有效的表达式。这允许从不直接属于聚合的列返回值，包括在查询中使用这些列表达式。
arbitrary(x)	默认与 x 类型一致	等同于 any_value()。
array_agg(x)	默认与 x 类型一致	返回由输入 x 元素创建的数组。
avg(x)	double	返回均值。
sum(x)	bigint	返回总和。
max(x)	bigint	返回最大值。
max(x, n)	array	返回输入中最大的 n 个值。
max_by(x, y)	默认与 x 类型一致	返回 x 中与 y 的最大值相关连的值。
max_by(x, y, n)	array	按照 y 降序排列，返回 x 中 n 个与 y 中 n 个最小值相关联的值。
min(x)	bigint	返回最小值。
min(x, n)	min_by(x, y, n)	返回输入中最小的 n 个值。
min_by(x, y)	默认与 x 类型一致	返回 x 中与 y 的最小值相关连的值。
min_by(x, y, n)	array	按照 y 升序排列，返回 x 中 n 个与 y 中 n 个最小值相关联的值。
checksum(x)	varbinary	返回输入值的校验和。
bool_and(boolean)	boolean	所有输入值都为 true 时，返回 true，否则返回false。
bool_or(boolean)	boolean	输入值中有一个为 true，则返回 true，否则返回 false。
every(boolean)	boolean	等同于 bool_and()。
count(*)	bigint	返回行数。
count(x)	bigint	返回非空值的个数。
count_if(x)	boolean	返回输入值为 true 的个数。
geometric_mean(x)	double	返回几何平均值。
listagg(x, separator)	varchar	返回字符串，由 separator 把每个 x 连接起来。
histogram	–	返回一个 map，包含每个输入值出现的次数。

函数	返回值类型	说明	示例
current_date	date	返回当前 query 启动时的日期	SELECT current_date
current_time	time with time zone	返回当前 query 启动时的时间	–
current_timestamp	timestamp with time zone	返回当前 query 启动时的时间戳，精度为毫秒级。	–
current_timezone()	varchar	以 IANA（例如，America / Los_Angeles）定义的格式返回当前时区，或以 UTC 的固定偏移量（例如+08：35）返回当前时区。	SELECT current_timezone()
date(x)	date	与 CAST(x AS date) 函数功能相同，可实现将x的类型转换为 Date。	–
date_add(unit, value, timestamp)	–	添加一个时间间隔 value 到 timestamp 上，时间间隔的单位为 unit，可以使用负值作为减法	SELECT date_add('second', 86, TIMESTAMP '2020-03-01 00:00:00')，返回值为2020-03-01 00:01:26.000
date_diff(unit, timestamp1, timestamp2)	bigint	返回 timestamp1与 imestamp2的差值，该差值的单位为 unit。unit 的具体单位取值，请参见间隔函数。	SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00 UTC', TIMESTAMP '2020-03-02 00:00:00 UTC')，返回值为24
human_readable_seconds(double)	varchar	格式化 double 代表的秒数为可读的时间字符串，包括 weeks, days, hours, minutes, 和 seconds。	SELECT human_readable_seconds(3762)，返回值为1 hour, 2 minutes, 42 seconds
parse_duration(string)	interval	解析字符串 string 为单位为 value unit 的时间间隔。	-SELECT parse_duration('3.81 d')，返回值为3 19:26:24.000
date_trunc(unit, x)	–	将日期或时间戳 x 截断到指定的时间单位 unit，并返回截断后的结果。	–
date_format(timestamp, format)	varchar	使用format指定的格式，将timestamp格式化成字符串。	SELECT date_format(TIMESTAMP '2022-10-20 05:10:00', '%m-%d-%Y %H')，返回值为10-20-2022 05
date_parse(string, format)	date	使用 format 将 string 解析为时间戳。	SELECT date_parse('2022/10/20/05', '%Y/%m/%d/%H')，返回值为2022-10-20 05:00:00.000
last_day_of_month(x)	date	返回日期 x 当月的最后一天。	last_day_of_month(current_date)
from_iso8601_timestamp(string)	timestamp with time zone	将 ISO 8601格式化的字符串解析为具有时区的时间戳。	SELECT from_iso8601_timestamp('2020-05-11')，返回值为2020-05-11 00:00:00.000 America/Vancouver
from_iso8601_timestamp_nanos(string)	timestamp with time zone	将 ISO 8601格式化的字符串解析为具有时区的时间戳，精度为纳秒级。	SELECT from_iso8601_timestamp_nanos('2020-05-11T11:15:05')，返回值为2020-05-11 11:15:05.000000000 America/Vancouver
from_iso8601_date(string)	date	将 ISO 8601格式的字符串解析为日期。	SELECT from_iso8601_date('2020-W10')，返回值为2020-03-02
from_unixtime(unixtime)	timestamp with time zone	返回 unix 时间戳。	from_unixtime(1475996660)，返回值为2016-10-09 15:04:20.000
from_unixtime(unixtime, zone)	timestamp with time zone	返回 unixtime 的时间戳，string 指定时区	–
from_unixtime(unixtime, hours, minutes)	timestamp with time zone	返回为 hours 和 minutes 对应时区的unixtime 时间戳。	–
from_unixtime_nanos(unixtime)	timestamp with time zone	返回 unix 时间戳，精度为纳米级。	SELECT from_unixtime_nanos(DECIMAL '1234')，返回值为1970-01-01 00:00:00.000001234 UTC
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 时间戳。	–
extract(field FROM x)	bigint	返回 field 从 x 中。	–
day(x)	bigint	返回指定日期在当月的天数。	–
day_of_month(x)	bigint	day(x) 函数的别名。	–
day_of_week(x)	bigint	返回指定日期对应的星期值，值范围从1（星期一）到7（星期天）。	–
day_of_year(x)	bigint	返回指定日期对应一年中的第几天，值范围从1到366。	–
dow(x)	bigint	day_of_week 函数的别名。	–
doy(x)	bigint	day_of_year 函数的别名	–
hour(x)	bigint	返回指定日期对应的小时，值范围从1到23。	–
millisecond(x)	bigint	返回指定时间的毫秒数。	–
minute(x)	bigint	返回指定时间的分钟数。	–
month(x)	bigint	返回指定日期对应的月份。	–
quarter(x)	bigint	返回指定日期对应的分钟。	–
second(x)	bigint	返回指定日期对应的秒数。	–
timezone_hour(timestamp)	bigint	返回从指定时间戳对应时区偏移的小时数。	–
timezone_minute(timestamp)	bigint	返回从指定时间戳对应时区偏移的分钟数。	–
week(x)	bigint	返回指定日期 x 对应的 ISO 周数，取值范围为1至53。	–
week_of_year(x)	bigint	week 函数的别名。	–
year(x)	bigint	返回指定日期对应的年份。	–

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

单位	描述
millisecond	毫秒
second	秒
minute	分钟
hour	小时
day	天
week	星期
month	月
quarter	季度
year	年

九、聚合函数

所有的聚合函数都可以通过添加 OVER 子句来作为开窗函数。聚合函数会计算当前窗口的每一行数据。

示例：以下查询生成每个店员每天订单价格的滚动总和。

SELECT clerk, orderdate, orderkey, totalprice, sum(totalprice) OVER (PARTITION BY clerk ORDER BY orderdate) AS rolling_sumFROM ordersORDER BY clerk, orderdate, orderkey

十、Ranking functions

函数	返回值类型	说明
cume_dist()	bigint	计算某个值在分区中相对于所有值的位置。
dense_rank()	bigint	计算某个值在一组值中的排名，如果出现排名相等，与函数 rank 不同，dense_rank 不会在排名序列中产生空位。
ntile(n)	bigint	将窗口分区的行划分为 n 个桶，返回行所在的桶数，范围从1到 n。
percent_rank()	double	计算某个值在一组值中的百分比排名。返回值以0到1之间的小数表示。
rank()	bigint	计算某个值在一组值中的排名。如果出现排名相等，则在排名序列中留出空位。
ow_number()	bigint	为每一行分配一个唯一的连续编号。

十一、Value functions

函数	返回值类型	说明
first_value(x)	默认与 x 类型一致	返回分区中某列第一条数据的值。
last_value(x)	默认与 x 类型一致	返回分区中某列最后一条数据的值。
nth_value(x, offset)	默认与 x 类型一致	返回距窗口头第 n 行的值。n 从1开始。如果 ignoreNulls=true，查找第 n 行时将跳过 null。否则，每一行都计入 n。如果不存在这样的第 n 行（例如，当 n 为10时，窗口大小小于10），则返回null。第一个参数为列名，第二个参数为之前第 n 行。
lead(x[, offset[, default_value]])	默认与 x 类型一致	返回窗口中当前行向下第 n 行的值。n 默认值为1，default 默认值为 null。如果第 n 行的值为 null，则返回 null。如果不存在这样的偏移行（例如，当偏移量为1时，窗口的最后一行没有任何向下行），则返回 default 。第一个参数为列名，第二个参数为之前第 n 行，第三个参数为默认值.
lag(x[, offset[, default_value]])	默认与 x 类型一致	返回窗口中当前行向上第 n 行的值。n 默认值为1，default 默认值为 null。如果第 n 行的值为 null，则返回 null。如果不存在这样的偏移行（例如，当偏移量为1时，窗口的第一行没有任何向上行），则返回 default。第一个参数为列名，第二个参数为之前第 n 行，第三个参数为默认值。

数据库管理 跨库联查 SQL 语法介绍