MPP计算引擎 SELECT语法是什么?_问答-阿里云开发者社区

语法描述

[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT ] select_expr [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
[ HAVING condition]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count | ALL ] ]

其中 from_item 为以下之一

table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]

from_item join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

并且 join_type 为以下之一

[ INNER ] JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
FULL [ OUTER ] JOIN
CROSS JOIN

并且 grouping_element 为以下之一

()
expression
GROUPING SETS ( ( column [, ...] ) [, ...] )
CUBE ( column [, ...] )
ROLLUP ( column [, ...] )

WITH 从句

这个 WITH 从句定义了一个命名好的关系以供在一个查询里面使用。它可以扁平化嵌套查询或者简化子查询。例如，下面的查询是等价的:

SELECT a, b
FROM (
SELECT a, MAX(b) AS b FROM t GROUP BY a
) AS x;
WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
SELECT a, b FROM x;

也同意适用于多子查询的情况:

WITH
t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a),
t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a)
SELECT t1.*, t2.*
FROM t1
JOIN t2 ON t1.a = t2.a;

另外, 在 WITH 分句中定义的关系可以互相连接:

WITH
x AS (SELECT a FROM t),
y AS (SELECT a AS b FROM x),
z AS (SELECT b AS c FROM y)
SELECT c FROM z;

GROUP BY从句

这个 GROUP BY 分句对 SELECT 语句的输出进行分组，分组中是匹配值的数据行。一个简单的GROUP BY 分句可以包含由输入列组成的任何表达式或者或列序号（从1开始）.
以下查询是等价的。他们都对 nationkey 列进行分组，第一个查询使用列序号，第二个查询使用列名:

SELECT count(*), nationkey FROM customer GROUP BY 2;
SELECT count(*), nationkey FROM customer GROUP BY nationkey;

在查询语句中没有指定列名的情况下， GROUP BY 子句也可以将输出进行分组。例如，以下查询使用列 mktsegment进行分组，统计出 customer 表的行数。:

SELECT count(*) FROM customer GROUP BY mktsegment;

_col0
-------
29968
30142
30189
29949
29752
(5 rows)

在 SELECT 语句中使用 GROUP BY 子句时，所有输出的列要么是聚会函数，要么是GROUP BY 子句中的列。

Complex Grouping Operations

AnalyticDB MPP 同样也支持复杂的聚合函数使用 GROUPING SETS, CUBE 和 ROLLUP 语法。这个语法允许用户执行那些需要在一个单一查询上在多个列上聚合的分析查询。复杂的分组运算不支持在由输入列组成的表达式上做分组。只有列名和序列号是被允许的。
复杂的分组运算符经常等价于 UNION ALL 和 GROUP BY 的组合表达式, 正如下面的例子所示。然而当参与聚合的数据源不是确定性的时候，这个等价就不存在了。

GROUPING SETS

允许用户去指定多个包含列的列表去做分组。那些不属于分组列集合中的子列表中的列被设置为 NULL。

SELECT * FROM shipping;

origin_state | origin_zip | destination_state | destination_zip | package_weight
--------------+------------+-------------------+-----------------+----------------
California | 94131 | New Jersey | 8648 | 13
California | 94131 | New Jersey | 8540 | 42
New Jersey | 7081 | Connecticut | 6708 | 225
California | 90210 | Connecticut | 6927 | 1337
California | 94131 | Colorado | 80302 | 5
New York | 10002 | New Jersey | 8540 | 3
(6 rows)

GROUPING SETS 语法上可以参考下面的示范:

SELECT origin_state, origin_zip, destination_state, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
(origin_state),
(origin_state, origin_zip),
(destination_state));

origin_state | origin_zip | destination_state | _col0
--------------+------------+-------------------+-------
New Jersey | NULL | NULL | 225
California | NULL | NULL | 1397
New York | NULL | NULL | 3
California | 90210 | NULL | 1337
California | 94131 | NULL | 60
New Jersey | 7081 | NULL | 225
New York | 10002 | NULL | 3
NULL | NULL | Colorado | 5
NULL | NULL | New Jersey | 58
NULL | NULL | Connecticut | 1562
(10 rows)

前一个查询可以被认为和一个包含多个 GROUP BY 的 UNION ALL 在逻辑上是等价的:

SELECT origin_state, NULL, NULL, sum(package_weight)
FROM shipping GROUP BY origin_state
UNION ALL
SELECT origin_state, origin_zip, NULL, sum(package_weight)
FROM shipping GROUP BY origin_state, origin_zip
UNION ALL
SELECT NULL, NULL, destination_state, sum(package_weight)
FROM shipping GROUP BY destination_state;

然而，带有复杂分组语法的查询(GROUPING SETS, CUBE 或者 ROLLUP) 将只从潜在的数据源读一次，然而带有UNIONALL的查询读了潜在的数据源3次。这就是为什么当数据源不确定的时候，带着UNIONALL的查询有可能会产生不一致的结果。

CUBE

CUBE 运算符为一个指定的列的集合生成所有可能的分组集合。例如, 下面的查询:

SELECT origin_state, destination_state, sum(package_weight)
FROM shipping
GROUP BY CUBE (origin_state, destination_state);

和下面的查询等价:

SELECT origin_state, destination_state, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
(origin_state, destination_state),
(origin_state),
(destination_state),
());

origin_state | destination_state | _col0
--------------+-------------------+-------
California | New Jersey | 55
California | Colorado | 5
New York | New Jersey | 3
New Jersey | Connecticut | 225
California | Connecticut | 1337
California | NULL | 1397
New York | NULL | 3
New Jersey | NULL | 225
NULL | New Jersey | 58
NULL | Connecticut | 1562
NULL | Colorado | 5
NULL | NULL | 1625
(12 rows)

ROLLUP

这个 ROLLUP 运算符为一个指定列的集合生成所有可能的subtotals。例如下面的查询:

SELECT origin_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY ROLLUP (origin_state, origin_zip);

origin_state | origin_zip | _col2
--------------+------------+-------
California | 94131 | 60
California | 90210 | 1337
New Jersey | 7081 | 225
New York | 10002 | 3
California | NULL | 1397
New York | NULL | 3
New Jersey | NULL | 225
NULL | NULL | 1625
(8 rows)

和下面的查询是等价的:

SELECT origin_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS ((origin_state, origin_zip), (origin_state), ());

合并多个分组表达式

多个分组表达式在同一个查询被翻译成跨产品语义。例如, 下面的查询:

SELECT origin_state, destination_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY
GROUPING SETS ((origin_state, destination_state)),
ROLLUP (origin_zip);

可以被重写为:

SELECT origin_state, destination_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY
GROUPING SETS ((origin_state, destination_state)),
GROUPING SETS ((origin_zip), ());

在逻辑上和下面的查询等价:

SELECT origin_state, destination_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
(origin_state, destination_state, origin_zip),
(origin_state, destination_state));

origin_state | destination_state | origin_zip | _col3
--------------+-------------------+------------+-------
New York | New Jersey | 10002 | 3
California | New Jersey | 94131 | 55
New Jersey | Connecticut | 7081 | 225
California | Connecticut | 90210 | 1337
California | Colorado | 94131 | 5
New York | New Jersey | NULL | 3
New Jersey | Connecticut | NULL | 225
California | Colorado | NULL | 5
California | Connecticut | NULL | 1337
California | New Jersey | NULL | 55
(10 rows)

ALL 和 DISTINCT 这两个量词决定了是否每一个重复的分组集合都产生出唯一的输出行。这个尤其在多个复杂分组集合被组合在一个查询里的时候有用。例如，下面的查询:

SELECT origin_state, destination_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY ALL
CUBE (origin_state, destination_state),
ROLLUP (origin_state, origin_zip);

和下面的查询等价:

SELECT origin_state, destination_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
(origin_state, destination_state, origin_zip),
(origin_state, origin_zip),
(origin_state, destination_state, origin_zip),
(origin_state, origin_zip),
(origin_state, destination_state),
(origin_state),
(origin_state, destination_state),
(origin_state),
(origin_state, destination_state),
(origin_state),
(destination_state),
());

然后, 如果查询使用 DISTINCT 量词在 GROUP BY 的时候

SELECT origin_state, destination_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY DISTINCT
CUBE (origin_state, destination_state),
ROLLUP (origin_state, origin_zip);

只有唯一的分组集合将被生成出来:

SELECT origin_state, destination_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
(origin_state, destination_state, origin_zip),
(origin_state, origin_zip),
(origin_state, destination_state),
(origin_state),
(destination_state),
());

默认的量词是 ALL.

GROUPING Operation

grouping(col1, …, colN) -> bigint
这个分组操作符返回一个由bit set转换成的decimal, 指定了哪些列在一个分组中出现。这个必须要和 GROUPINGSETS, ROLLUP, CUBE 或者 GROUP BY连接使用。并且他的参数必须严格和在 GROUPING SETS, ROLLUP,CUBE 或者 GROUP BY分句中引用的值相匹配。为了计算特定行的结果位集，将位分配给参数列，最右边的列是最低有效位。对于一个给定分组，一个bit被设置成0如果相应的列包含在分组中，否则设置成1。例如, 下面的分组:

SELECT origin_state, origin_zip, destination_state, sum(package_weight),
grouping(origin_state, origin_zip, destination_state)
FROM shipping
GROUP BY GROUPING SETS (
(origin_state),
(origin_state, origin_zip),
(destination_state));

origin_state | origin_zip | destination_state | _col3 | _col4
--------------+------------+-------------------+-------+-------
California | NULL | NULL | 1397 | 3
New Jersey | NULL | NULL | 225 | 3
New York | NULL | NULL | 3 | 3
California | 94131 | NULL | 60 | 1
New Jersey | 7081 | NULL | 225 | 1
California | 90210 | NULL | 1337 | 1
New York | 10002 | NULL | 3 | 1
NULL | NULL | New Jersey | 58 | 6
NULL | NULL | Connecticut | 1562 | 6
NULL | NULL | Colorado | 5 | 6
(10 rows)

上面的第一个分组的结果只包含 origin_state 列并且排除了 origin_zip 和destination_state 这两列。这个bit set在这种情况被构建成 011 这里最重要的位代表了origin_state.

HAVING 从句

HAVING 子句与聚合函数以及 GROUP BY 子句共同使用，用来控制选择分组。 HAVING 子句去掉不满足条件的分组。在分组和聚合计算完成后，HAVING 对分组进行过滤。以下示例查询 customer 表，并进行分组，查出账户余额大于指定值的记录:

SELECT count(*), mktsegment, nationkey,
CAST(sum(acctbal) AS bigint) AS totalbal
FROM customer
GROUP BY mktsegment, nationkey
HAVING sum(acctbal) > 5700000
ORDER BY totalbal DESC;

_col0 | mktsegment | nationkey | totalbal
-------+------------+-----------+----------
1272 | AUTOMOBILE | 19 | 5856939
1253 | FURNITURE | 14 | 5794887
1248 | FURNITURE | 9 | 5784628
1243 | FURNITURE | 12 | 5757371
1231 | HOUSEHOLD | 3 | 5753216
1251 | MACHINERY | 2 | 5719140
1247 | FURNITURE | 8 | 5701952
(7 rows)

UNION | INTERSECT | EXCEPT从句

UNION INTERSECT 和 EXCEPT 都是全集合操作符。这些分句被用来组合多于一个查询语句的结果最终形成一个结果:
query UNION [ALL | DISTINCT] query
query INTERSECT [DISTINCT] query
query EXCEPT [DISTINCT] query
参数 ALL 或 DISTINCT 控制最终结果集包含哪些行。如果指定参数 ALL ，则包含全部行，即使行完全相同。如果指定参数 DISTINCT ，则合并结果集，结果集只有唯一不重复的行。如果不指定参数，执行时默认使用 DISTINCT。参数 ALL 不支持使用 INTERSECT 或者EXCEPT
多个集合操作符会从做到有的被处理，除非顺序通过括弧被显示指定。另外，INTERSECT 比 EXCEPT and UNION 有更高的优先级，这意味着 AUNION B INTERSECT C EXCEPT D 和这个表达式是相同的 A UNION (B INTERSECT C) EXCEPT D.

UNION

UNION 把所有结果集两个结果集合并起来。下面是一个最简单的可能使用 UNION 分句的例子。它选择了值 13 并且合并了第二个选择的值42，把他们结合起来:

SELECT 13
UNION
SELECT 42;

_col0
-------
13
42
(2 rows)

下面的查询演示了 UNION 和 UNION ALL 之间的不同。它选择了值 13 并把它和第二次查询钻展的值 42 和 13 做合并:

SELECT 13
UNION
SELECT * FROM (VALUES 42, 13);

_col0
-------
13
42
(2 rows)

SELECT 13
UNION ALL
SELECT * FROM (VALUES 42, 13);

_col0
-------
13
42
13
(2 rows)

INTERSECT

INTERSECT 只返回那些在同时在第一个和第二个查询里面都出现的行的结合。下面的例子是一个最简单的可能使用 INTERSECT 分句的例子。它选择了值 13 和 42 并把他们和第二个查询选择的值 13 做合并。既然 42 值在第一个查询的结果集中, 它并不会被包含在最终的结果集里面:

SELECT * FROM (VALUES 13, 42)
INTERSECT
SELECT 13;

_col0
-------
13
(2 rows)

EXCEPT

EXCEPT 返回那些行仅存在于第一个查询结果集不在第二个查询结果集中。下面是最简单的使用 EXCEPT 分句的例子。它选择了值 13 和 42并把他们和第二个查询选择的值 13 做合并。既然 13 也同时存在在第二个查询结果集中, 它不会被包含在最终的结果集中:

SELECT * FROM (VALUES 13, 42)
EXCEPT
SELECT 13;

_col0
-------
42
(2 rows)

ORDER BY Clause

ORDER BY 分句被用来排序一个结果集通过一个或者多个输出表达式:
ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [,...]
每个表达式由列名或列序号（从1开始）组成。 ORDER BY 子句作为查询的最后一步，在 GROUP BY 和 HAVING 子句之后。

LIMIT Clause

LIMIT 分句限制了最终结果集的行数。 LIMIT ALL 和略去 LIMIT 分句的结果一样。以下示例为查询一个大表， LIMIT子句限制它只输出5行（因为查询没有 ORDER BY，所以随意返回几行）:
SELECT orderdate FROM orders LIMIT 5;

o_orderdate
-------------
1996-04-14
1992-01-15
1995-02-01
1995-11-12
1992-04-26
(5 rows)

TABLESAMPLE

有多种抽样方法:
BERNOULLI

查出的每行记录都源于表样本，使用样本百分比概率。当使用 BERNOULLI 方法对表进行抽样时，会扫描表的所有物理块，并跳过某些行。（基于样本百分比与运行时随机计算之间的比较）结果中每行记录的概率都是独立的。这不会减少从磁盘读取抽样表所需要的时间。如果对抽样输出做处理，它可能对整体查询时间有影响。

SYSTEM

这种抽样方法将表划分为逻辑数据段，并按此粒度进行抽样。这种抽样方法要么从特定数据段查询全部行，要么跳过它。（基于样本百分比与运行时随机计算之间的比较）系统抽样选取哪些行，取决于使用哪种连接器。例如，使用Hive，它取决于HDFS上的数据是怎样存储的。这种方法无法保证独立抽样概率。

Note
这两种方法都不能确定返回行数的范围。

示例:

SELECT *
FROM users TABLESAMPLE BERNOULLI (50);
SELECT *
FROM users TABLESAMPLE SYSTEM (75);

通过join进行抽样:

SELECT o.*, i.*
FROM orders o TABLESAMPLE SYSTEM (10)
JOIN lineitem i TABLESAMPLE BERNOULLI (40)
ON o.orderkey = i.orderkey;

[font='iconfont'] UNNEST

UNNEST 用于展开数组类型或 map类型的子查询。数组展开为单列，map展开为双列（键，值）。 UNNEST 以使用多个参数，它们展开为多列，行数与最大的基础参数一样（其他列填空）。 UNNEST 可以带一个可选择的 WITH ORDINALITY 分句,在这个情况下一个额外的序号列将被在最后添加上。 UNNEST 通常与 JOIN 一起使用也可以引用 JOIN 左侧的关系列。
使用单独的列:

SELECT student, score
FROM tests
CROSS JOIN UNNEST(scores) AS t (score);

使用多列:

SELECT numbers, animals, n, a
FROM (
VALUES
(ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']),
(ARRAY[7, 8, 9], ARRAY['cow', 'pig'])
) AS x (numbers, animals)
CROSS JOIN UNNEST(numbers, animals) AS t (n, a);

numbers | animals | n | a
-----------+------------------+------+------
[2, 5] | [dog, cat, bird] | 2 | dog
[2, 5] | [dog, cat, bird] | 5 | cat
[2, 5] | [dog, cat, bird] | NULL | bird
[7, 8, 9] | [cow, pig] | 7 | cow
[7, 8, 9] | [cow, pig] | 8 | pig
[7, 8, 9] | [cow, pig] | 9 | NULL
(6 rows)

WITH ORDINALITY 分句:

SELECT numbers, n, a
FROM (
VALUES
(ARRAY[2, 5]),
(ARRAY[7, 8, 9])
) AS x (numbers)
CROSS JOIN UNNEST(numbers) WITH ORDINALITY AS t (n, a);

numbers | n | a
-----------+---+---
[2, 5] | 2 | 1
[2, 5] | 5 | 2
[7, 8, 9] | 7 | 1
[7, 8, 9] | 8 | 2
[7, 8, 9] | 9 | 3
(5 rows)

Joins

它允许你去合并来自多个关联的数据。

交叉连接

CROSS JOIN 反回关联两边的笛卡尔积。CROSS JOIN 要么被通过 CROSS JOIN 语法要么通过FROM分句中通过关联指定。
下面两个查询是等价的:

SELECT *
FROM nation
CROSS JOIN region;

SELECT *
FROM nation, region;

nation 表包含了25行 region 表包含了 5 行, 所以结果两个表最终产生了 125 行:

SELECT n.name AS nation, r.name AS region
FROM nation AS n
CROSS JOIN region AS r
ORDER BY 1, 2;

nation | region
----------------+-------------
ALGERIA | AFRICA
ALGERIA | AMERICA
ALGERIA | ASIA
ALGERIA | EUROPE
ALGERIA | MIDDLE EAST
ARGENTINA | AFRICA
ARGENTINA | AMERICA
...
(125 rows)

限定列名

当列名在两个关联的表中有相同的名称，它们的引用必须通过关联的别名引用（如果关联有别名），或者通过关联的表名引用:

SELECT nation.name, region.name
FROM nation
CROSS JOIN region;
SELECT n.name, r.name
FROM nation AS n
CROSS JOIN region AS r;
SELECT n.name, r.name
FROM nation n
CROSS JOIN region r;

下面的查询将会返回错误Column ‘name’ is ambiguous:

SELECT name
FROM nation
CROSS JOIN region;

子查询

一个子查询是一个包含了查询的表达式。子查询当它引用子查询之外的列时是相关的。逻辑上来说,子查询会被被它的外围查询逐行评估。被引用的列将因此是固定的在子查询的评估过程中。

Note
对于向关联的子查询是受限的，并不是每一个形式都是支持的。

EXISTS

EXISTS 断言决定是否一个子查询可以返回任何行:

SELECT name
FROM nation
WHERE EXISTS (SELECT * FROM region WHERE region.regionkey = nation.regionkey)

IN

IN 断言决定一个子查询返回的值是否在一个被给定的结果集中。IN 的结果依照对 nulls 的标准结果。子查询必须产生仅仅一列:

SELECT name
FROM nation
WHERE regionkey IN (SELECT regionkey FROM region)

标量子查询

标量子查询是一个非关联的子查询，他会返回零或者1行数据。如果这个子查询返回了多以一行的数据，那将是个错误。如果子查询没有返回任何行，则返回的结果是NULL:

SELECT name
FROM nation
WHERE regionkey = (SELECT max(regionkey) FROM region)

MPP计算引擎 SELECT语法是什么?

相关课程

相关电子书

相关实验场景

MPP计算引擎 SELECT语法是什么?

相关课程

相关文章

相关电子书

相关实验场景