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

开发者社区> 问答> 正文

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

nicenelly 2017-10-26 15:20:42 1529
通过DML进行计算是用户在分析型数据库中最常用的操作。目前分析型数据库对DML支持使用SELECT进行查询,以及支持INSERT、DELETE等数据修改操作(INSERT/DELETE见4.2.1节)。另外本节的内容需要配合附录二、4.1中的多计算引擎以及4.2.7中Hint的说明来阅读。
分析型数据库中SELECT语句的基本结构如下:<PRE prettyprinted? linenums>
  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子句(过滤表达式)、GROUP BY/HAVING子句(分组和分组后过滤)、ORDER BY子句(排序)、两个查询间的UNION [ALL]/INTERSECT/MINUS、LIMIT子句(返回行数限制)。

列投射(列和列表达式)


分析型数据库中,SELECT语句中的列投射的基本结构为:<PRE prettyprinted? linenums>
  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) AS X:指定表名和列名,表来自子查询且是别名引用,列属于子查询返回的列(<column>完全匹配)
  • SELECT X.x FROM (SELECT A.a AS x, b FROM A JOIN B ...) AS X:指定表名和列名,表来自子查询且是别名引用,列属于子查询返回的列且带别名
  • 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 by b:其中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) FROM A:系统内置聚合函数(UDF_SYS_...)
  • SELECT SUM(CASE WHEN ... THEN ... ELSE ... END):聚合表达式(SUM(...))套列普通表达式(CASE WHEN ...)做为其子表达式
  • SELECT SUM(a) / COUNT(a) FROM A:列普通表达式(/)套聚合表达式(做为其子表达式)

0.8版本中不支持但是0.9版本的Full MPP Mode支持(详见4.4节):
  • SELECT a + COUNT(*) FROM A:普通表达式(+)不能同时套普通表达式(a)和聚合表达式(COUNT(...));
  • SELECT SUM(COUNT(*)):聚合表达式不能套聚合表达式做为其子表达式;


FROM/JOIN子句(表扫描、连接与子查询)


用法:<PRE prettyprinted? linenums>
  1. FROM table_references

或<PRE prettyprinted? linenums>
  1. FROM table_referencesA as table_aliasA JOIN table_referencesB as table_aliasB on join_conditions

或<PRE prettyprinted? linenums>
  1. FROM (SELECT ...) as aliasA JOIN table_referencesB as table_aliasB on join_conditions

或<PRE prettyprinted? linenums>
  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 AND 10:对于表达式(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 where c=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 AND UDF_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子句的用法:<PRE prettyprinted? linenums>
  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) LIMIT 100:分区表达式(s)不是分区列(a)同时又有TOP-N(ORDER BY COUNT(a) LIMIT 100)0.8版本下计算的结果可能是不精确的(详见4.3节)

HAVING子句的用法:
HAVING子句用于在GROUP BY子句执行后对分组后的结果进行过滤。支持使用列投射和聚合表达式后的结果进行过滤。<PRE prettyprinted? linenums>
  1. HAVING having_condition

例如:
  • SELECT a, sum(length(s)) AS x FROM A JOIN B ON A.a = B.b GROUP BY a HAVING (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 LIMIT 100:按照分区列(a)排序并返回TOP-N(100)
  • SELECT a FROM A ORDER BY (a + b) DESC LIMIT 100:按照非分区列(a)排序并返回TOP-N(100)
  • SELECT a, COUNT(a) FROM A GROUP BY a ORDER BY x:按照分区列(a)分组聚合再排序

0.8版本中不支持但是0.9版本的Full MPP Mode支持(详见4.4节):
  • SELECT a + b, COUNT(1) AS x FROM A GROUP BY a + b ORDER BY x:0.8版本下按照非分区列表达式(a+b)分组聚合再取TOP-N是非精确的(详见4.3)


UNION [ALL]/INTERSECT/MINUS子句


UNION/INTERSECT/MINUS 0.8版本下仅支持操作字段为分区列,但是0.9版本的Full MPP Mode无限制(详见4.4节)。
UNION ALL无任何限制。

LIMIT子句(返回行数限制)


示例:
  • SELECT ... LIMIT 100:返回行限制为100行

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

需要注意的是,分析型数据库会对通过SELECT语句进行查询的返回行数做全局的最大限制,公共云上目前为10000行,若不加LIMIT或LIMIT行数超过10000,则只会返回10000行。
SQL OLAP 索引
分享到
取消 提交回答
全部回答(0)
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

推荐文章
相似问题