SELECT 查询的基本语法结构
跨库联查目前仅支持
SELECT 查询。SELECT [ ALL | DISTINCT ] select_expression [, ...][ FROM from_item [, ...] ][ WHERE condition ][ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ][ HAVING condition][ WINDOW window_definition_list][ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ][ ORDER BY expression [ ASC | DESC ] [, ...] ][ OFFSET count [ ROW | ROWS ] ][ LIMIT { count | ALL } ][ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
详细解释
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 必须非空。 | - |
rtrim(string) | varchar | 删除 string 末尾的空格。 | - |
split(string, delimiter) | - | 使用 delimiter 拆分 string 并返回数组。 | - |
split(string, delimiter, limit) | - | 使用 delimiter 拆分 string 并返回大小最大为 limit 的数组。 | - |
split_part(string, delimiter, index) | varchar | 使用 delimiter 切分 string，并返回第 index 个字符串。 | - |
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 之后的值。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 | 返回指定日期对应一年中的 ISO week，值范围从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 clerkORDER 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 行，第三个参数为默认值。 |
