505 阿裏雲技術社區[雲棲]

4.1 Select語句基本語法__第四章 DML_使用手冊_分析型數據庫-阿裏雲

通過DML進行計算是用戶在分析型數據庫中最常用的操作。目前分析型數據庫對DML支持使用SELECT進行查詢，以及支持INSERT、DELETE等數據修改操作（INSERT/DELETE見4.3節）。另外本節的內容需要配合附錄一：函數的使用與4.2中特殊語法，以及4.4中的雙計算引擎和Hint的說明來閱讀。

分析型數據庫中SELECT語句的基本結構如下：

SELECT
    [DISTINCT]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE filter_condition]
    [GROUP BY {col_name | expr | position}
    [HAVING having_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [UNION ALL (SELECT select_expr..)]
    [{UNION|INTERSECT|MINUS} (SELECT select_expr..)]
    [LIMIT {row_count}]

下麵，我們將一個完整的SELECT分為幾個部分加以介紹：列投射部分（列和列表達式）、FROM/JOIN子句（表掃描、表連接與子查詢）、WHERE子句（過濾表達式）、GROUP BY/HAVING子句（分組和分組後過濾）、ORDER BY子句（排序）、兩個查詢間的UNION [ALL]/INTERSECT/MINUS、LIMIT子句（返回行數限製）。

列投射（列和列表達式）

分析型數據庫中，SELECT語句中的列投射的基本結構為：

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子句（表掃描、連接與子查詢）

用法：

FROM table_references

或

FROM table_referencesA as table_aliasA JOIN table_referencesB as table_aliasB on join_conditions

或

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

或

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）是多值列類型支持
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子句的用法：

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子句執行後對分組後的結果進行過濾。支持使用列投射和聚合表達式後的結果進行過濾。

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行。

最後更新：2016-11-24 11:23:46

4.1 Select語句基本語法__第四章 DML_使用手冊_分析型數據庫-阿裏雲

列投射（列和列表達式）

FROM/JOIN子句（表掃描、連接與子查詢）

WHERE子句（過濾表達式）

GROUP BY/HAVING子句（分組和分組後過濾）

ORDER BY子句（排序）

UNION [ALL]/INTERSECT/MINUS子句

LIMIT子句（返回行數限製）

上一篇： 3.6 ECU管理__第三章 DDL_使用手冊_分析型數據庫-阿裏雲

下一篇： 4.2 邏輯表達式和特殊語法__第四章 DML_使用手冊_分析型數據庫-阿裏雲

相關內容

熱門內容

最新內容