全部产品
分析型数据库

4.1 Select语句基本语法

更新时间:2017-06-07 13:26:11   分享:   

通过DML进行计算是用户在分析型数据库中最常用的操作。目前分析型数据库对DML支持使用SELECT进行查询,以及支持INSERT、DELETE等数据修改操作(INSERT/DELETE见4.3节)。另外本节的内容需要配合附录一:函数的使用与4.2中特殊语法,以及4.4中的双计算引擎和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子句(过滤表达式)、GROUP BY/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) 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类型的取值使用10)列
  • 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 ...:常用算术操作符(+-*/, %),其中表达式(ab)是合法的值、基本列或列表达式,但必须是数值类型
  • SELECT min(a), UDF_EXAMPLE(a, s) ...:系统内置或用户自定义函数,可以是值、基本列或列表达式,但数据类型要符合函数参数要求
  • SELECT CASE WHEN a > 0 THEN a + b ELSE 0 END ...:CASE-WHEN表达式,其中表达式(a > 0)是合法的 行过滤表达式(参看后面),THENELSE取值是合法的 列表达式 而且数据类型相同(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子句(表扫描、连接与子查询)

用法:

  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 AND 10:对于表达式(a)是数字类型(longintshortdoubleboolean)和时间类型(datetimetimestamp)支持
  • 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子句的用法:

  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子句执行后对分组后的结果进行过滤。支持使用列投射和聚合表达式后的结果进行过滤。

  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表达式是基于列表达式 (asum(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行。

本文导读目录
本文导读目录
以上内容是否对您有帮助?