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

通过DML进行计算是用户在分析型数据库中最常用的操作。目前分析型数据库对DML支持使用SELECT进行查询，以及支持INSERT、DELETE等数据修改操作（INSERT/DELETE见4.2.1节）。另外本节的内容需要配合附录二、4.1中的多计算引擎以及4.2.7中Hint的说明来阅读。
分析型数据库中SELECT语句的基本结构如下：

1. SELECT
2.     [DISTINCT]
3.     select_expr [, select_expr ...]
4.     [FROM table_references
5.     [WHERE filter_condition]
6.     [GROUP BY {col_name | expr | position}
7.     [HAVING having_condition]
8.     [ORDER BY {col_name | expr | position}
9.       [ASC | DESC], ...]
10.     [UNION ALL (SELECT select_expr..)]
11.     [{UNION|INTERSECT|MINUS} (SELECT select_expr..)]
12.     [LIMIT {row_count}]

下面，我们将一个完整的SELECT分为几个部分加以介绍：列投射部分（列和列表达式）、FROM/JOIN子句（表扫描、表连接与子查询）、WHERE子句（过滤表达式）、GROUPBY/HAVING子句（分组和分组后过滤）、ORDER BY子句（排序）、两个查询间的UNION[ALL]/INTERSECT/MINUS、LIMIT子句（返回行数限制）。

列投射（列和列表达式）


分析型数据库中，SELECT语句中的列投射的基本结构为：

1. SELECT [DISTINCT] select_expr [, select_expr ...]

其中 select_expr 可以是基本列（直接从表或子查询中选出的），也可以是列表达式（包括算数计算、聚合函数、系统UDF等）。
当 select_expr 为基本列时：
基本列是表中的列，这里的表可以是物理表也可以是虚表（物理表的别名引用或子查询产生的表）。
例如：

• SELECT A.a FROM A：指定表名和列名，表来自FROM子句，列属于FROM子句指定的表
• SELECT a FROM A：仅指定列名，列属于FROM子句指定的表
• SELECT A.a AS x FROM A：指定表名和列名（带别名），表来自FROM子句，列属于FROM子句
• SELECT a AS x FROM A：仅指定列名（带别名），表来自FROM子句，列属于FROM子句
• SELECT b, a FROM A JOIN B ...，仅指定列名，列属于FROM子句指定的第一个包含该列的表
• SELECT A.a FROM (SELECT A.a, b FROM A JOIN B...)，指定表名和列名，列属于子查询返回的列（<table>.<column>完全匹配）
• SELECT a FROM (SELECT a, b FROM A JOIN B...)，仅指定列名，列属于子查询返回的列(<column>完全匹配）
• SELECT X.a FROM (SELECT a FROM A) ASX：指定表名和列名，表来自子查询且是别名引用，列属于子查询返回的列(<column>完全匹配）
• SELECT X.x FROM (SELECT A.a AS x, b FROM A JOIN B ...) ASX：指定表名和列名，表来自子查询且是别名引用，列属于子查询返回的列且带别名
• SELECT count(distinct a) from A where b = 123,a不是分区列，且经过where条件筛选后数据量不超过100万条
• SELECT NULL ...：空值列
• SELECT 1, 2 ...：常量值列
• SELECT true...：常量值（boolean类型的取值使用1或0）列
• SELECT * ...：通配符代表所有列

暂不支持：

• SELECT A.* ...：通配符代表特定表的所有列
• SELECT a, b, a ...：重复列

0.8版本中不支持但是0.9版本的Full MPP Mode支持（详见4.4节）：

• SELECT distinct a | count(distinct a) from A group byb：其中a不是分区列
• SELECT distinct a | count(distinct a) from A where b = 123,a不是分区列，且经过where条件筛选后数据量超过100万条

当 select_expr 中使用列表达式时：
例如：

• SELECT a + b...：常用算术操作符（+，-，*，/,%），其中表达式（a，b）是合法的值、基本列或列表达式，但必须是数值类型
• SELECT min(a), UDF_EXAMPLE(a, s)...：系统内置或用户自定义函数，可以是值、基本列或列表达式，但数据类型要符合函数参数要求
• SELECT CASE WHEN a > 0 THEN a + b ELSE 0 END...：CASE-WHEN表达式，其中表达式（a > 0）是合法的行过滤表达式（参看后面），THEN和ELSE取值是合法的列表达式 而且数据类型相同（int）
• SELECT a IS NULL, a IS NOT TRUE, a >= 0, s LIKE '%foo'...：合法的 关系表达式 （参看后面）
• SELECT ! a, NOT s ...：合法的 逻辑表达式（参看后面）

暂不支持：

• SELECT a IS [NOT] TRUE, a BETWEEN 1 AND 2, a IN (1, 2), a CONTAINS (1,2, 3)：不支持部分关系表达
• SELECT a XOR b：不支持逻辑表达式 （参看4.3节）
• SELECT a & b FROM A, B：不支持位操作

当 select_expr 为列聚集函数表达式时：
包含任何聚集函数的列表达式，即聚集表达式：
例如：

• SELECT COUNT(*)：常用聚合函数，详见附件中函数表
• SELECT UDF_SYS_COUNT_COLUMN(a) FROMA：系统内置聚合函数（UDF_SYS_...）
• SELECT SUM(CASE WHEN ... THEN ... ELSE ...END)：聚合表达式（SUM(...)）套列普通表达式（CASE WHEN...）做为其子表达式
• SELECT SUM(a) / COUNT(a) FROMA：列普通表达式（/）套聚合表达式（做为其子表达式）

0.8版本中不支持但是0.9版本的Full MPP Mode支持（详见4.4节）：

• SELECT a + COUNT(*) FROMA：普通表达式（+）不能同时套普通表达式（a）和聚合表达式（COUNT(...)）；
• SELECT SUM(COUNT(*))：聚合表达式不能套聚合表达式做为其子表达式；

FROM/JOIN子句（表扫描、连接与子查询）


用法：

1. FROM table_references

或

1. FROM table_referencesA as table_aliasA JOIN table_referencesB as table_aliasB on join_conditions

或

1. FROM (SELECT ...) as aliasA JOIN table_referencesB as table_aliasB on join_conditions

或

1. FROM (SELECT ...) as aliasA JOIN (SELECT ...)as aliasB on join_conditions

例如：

• FROM A：指定表
• FROM (SELECT ...)：子查询
• FROM A JOIN B ON A.a = B.b JOIN C ON A.a =C.c：单表或多表连接，默认是符合条件记录的笛卡尔集。需要注意的是，目前分析型数据库中使用JOIN时若一方是物理表，那么物理表参与JOIN的列必须是分区列并且已建立HASH索引
• FROM (SELECT ...) AS X JOIN A on X.x = A.a：表、子查询和JOIN结合使用
• SELECT ... FROM A JOIN (SELECT ...)：在V0.6.x及后续版本支持
• SELECT ... FROM (SELECT ...) JOIN (SELECT...)：两个子查询之间的Join，在V0.6.x及后续版本支持

不支持或语义错误：

• SELECT ... FROM A FULL JOIN B：不支持全连接；
• SELECT ... FROM A RIGHT JOIN B：不支持右连接，需要转换为左连接；
• SELECT ... FROM A SEMI JON B：不支持半连接；
• SELECT ... FROM A, B：单表或多表连接，但没有ON条件
• SELECT ... FROM A, B WHERE A.a =B.b：单表或多表连接在WHERE子句中有隐含ON条件，但是没有on子句的，暂不支持

0.8版本中两个表关联可运行的充要条件（四原则）：
（1）两个表均为事实表且在同一个表组，或两个表中有一个是维度表。
（2）两个表均为事实表且拥有相同的一级分区列，或两个表中有一个是维度表。
（3）两个表均为事实表且关联条件(on)中至少含有一个条件是两个表各自的分区列的等值关联条件，或两个表中有一个是维度表。
（4）关联条件（on）中的条件两端有有效的HashMap索引
0.9版本中两个表关联可加速运行的条件：
（1）两个表均为事实表且在同一个表组，或两个表中有一个是维度表。
（2）两个表均为事实表且拥有相同的一级分区列，或两个表中有一个是维度表。
（3）两个表均为事实表且关联条件(on)中至少含有一个条件是两个表各自的分区列的等值关联条件，或两个表中有一个是维度表。
0.9版本若使用Full MPP Mode支持任意形态的表关联（详见4.4节），该功能目前邀请少数客户测试，后续全面开放。

WHERE子句（过滤表达式）


在过滤表达式中，单个条件之间可以用AND和OR进行连接，支持括号决定条件表达式的优先级，亦支持表达式嵌套。
像普通的SQL一样，单个条件的组成是一个布尔判断。暨无论如何组合，单个条件最终是一个返回True/False的表达式。在最简单的情况下，单个条件由左侧的主体、中间的比较操作符和右侧的断言值组成。
例如：column_name IS NOT NULL 中， column_name 为布尔主体，IS NOT为比较操作符，NULL为断言值。
分析型数据库中比较操作符支持 = 、 >= 、 > 、 <= 、 < 、 <> 、 !=等基本操作符，也支持IS、IN、CONTAINS、BETWEEN…AND… 、LIKE这样的关系操作符，并且关系操作符均支持其反义的版本（IS NOT 、NOT IN 、 NOT LIKE等）。
布尔主体可以是一个列，也可以是表达式的嵌套。
例如：

• WHERE a > 0：关系表达式（>，>=，<，<=，=，<>，!=）
• WHERE a IS [NOT] NULL：关系表达式，判断是否为空值
• WHERE a BETWEEN 1 AND10：对于表达式（a）是数字类型（long，int，short，double，boolean）和时间类型（date，time，timestamp）支持
• WHERE s LIKE'%sample'：对于表达式（s）是字符串（string）类型支持
• WHERE a IN (1,2,3)：对于表达式（a）是多值列类型，或普通类型支持，但IN ()>中的默认值不能超过400个。
• WHERE a IN (select a from dim_table wherec=1)：对于对于表dim_table是维度表，支持IN后带子查询（V0.6.2之后的版本支持）
• WHERE a CONTAINS (1,2,3)：对于表达式（a）是多值列类型支持
• WHERE a > 0 AND b > 0：逻辑表达式
• WHERE (a >0 OR b > 0) AND c > 0：支持括号决定条件表达式的优先级
• WHERE (CASE WHEN (a + b) > 0 THEN 0 ELSE 1 END) != 0 ANDUDF_EXAMPLE(a, b) > 0：表达式嵌套。

不支持或语义错误：

• WHERE EXISTS (SELECT...)：不支持
• WHERE a = ANY (SELECT ...)：不支持
• WHERE a = ALL (SELECT ...)：不支持

0.8版本中不支持但是0.9版本的Full MPP Mode支持（详见4.4节）：

• WHERE a IN (select a from fact_table where c=1):fact_table不是维度表的情况下0.8版本不支持

GROUP BY/HAVING子句（分组和分组后过滤）


GROUP BY子句的用法：

1. GROUP BY {col_expr}

col_expr为列（表中的列或子查询、别名产生的列）。
例如：

• `… FROM A GROUP BY a：仅指定列
• `… FROM A GROUP BY A.a：同时指定表名和列
• GROUP BY s, a IS NULL, b + c, UDF_EXAMPLE(d)：一个或多个列表达式
• SELECT a, SUM(a) FROM A GROUP BY a, s：可以包含不属于列透视的列表达式（s`）
• `… FROM (SELECT A.a AS x, b FROM A JOIN B …) AS X JOIN C … GROUP BY b, c,X.x：包含在子查询或多表连接的一个或多个列表达式
• `SELECT CASE WHEN a > 1 THEN ‘Y’ ELSE ‘N’ END AS x … GROUP BY x：别名引用产生的列表达式
• SELECT a, b, c ... GROUP BY 3, 1：按位置引用列投射的列表达式

0.8版本中不支持但是0.9版本的Full MPP Mode支持（详见4.4节）：

• ... FROM A GROUP BY s ORDER BY COUNT(a) LIMIT100：分区表达式（s）不是分区列（a）同时又有TOP-N（ORDER BYCOUNT(a) LIMIT 100）0.8版本下计算的结果可能是不精确的（详见4.3节）

HAVING子句的用法：
HAVING子句用于在GROUP BY子句执行后对分组后的结果进行过滤。支持使用列投射和聚合表达式后的结果进行过滤。

1. HAVING having_condition

例如：

• SELECT a, sum(length(s)) AS x FROM A JOIN B ON A.a = B.b GROUP BY aHAVING (CASE WHEN (x / a > 1) THEN a ELSE 0 END) !=0：分组表达式（a）是分区列，HAVING表达式是基于列表达式（a，sum(length(s))，s）的行过滤表达式

0.8版本中不支持但是0.9版本的Full MPP Mode支持（详见4.4节）：

• SELECT s, count(a) AS x FROM A GROUP BY s HAVING x >1：分组表达式（s）不是分区列（a）

ORDER BY子句（排序）


例如：

• SELECT a FROM A ORDER BY a DESC LIMIT100：按照分区列（a）排序并返回TOP-N（100）
• SELECT a FROM A ORDER BY (a + b) DESC LIMIT100：按照非分区列（a）排序并返回TOP-N（100）
• SELECT a, COUNT(a) FROM A GROUP BY a ORDER BYx：按照分区列（a）分组聚合再排序

0.8版本中不支持但是0.9版本的Full MPP Mode支持（详见4.4节）：

• SELECT a + b, COUNT(1) AS x FROM A GROUP BY a + b ORDER BYx：0.8版本下按照非分区列表达式（a+b）分组聚合再取TOP-N是非精确的（详见4.3）

UNION[ALL]/INTERSECT/MINUS子句


UNION/INTERSECT/MINUS 0.8版本下仅支持操作字段为分区列，但是0.9版本的Full MPPMode无限制（详见4.4节）。
UNION ALL无任何限制。

LIMIT子句（返回行数限制）


示例：

• SELECT ... LIMIT 100：返回行限制为100行

不支持：

• SELECT ... LIMIT 100, 50：不支持带偏移量的返回行限制

需要注意的是，分析型数据库会对通过SELECT语句进行查询的返回行数做全局的最大限制，公共云上目前为10000行，若不加LIMIT或LIMIT行数超过10000，则只会返回10000行。