MaxCompute支援通過SELECT
語句查詢資料。本文為您介紹SELECT
命令格式及如何?巢狀查詢、分組查詢、排序等操作。
執行SELECT
操作前需要具備目標表的讀取資料許可權(SELECT)。授權操作請參見MaxCompute許可權。
本文中的命令您可以在如下工具平台執行:
功能介紹
SELECT
語句用於從表中選取滿足指定條件的資料。您可以根據實際情境結合以下功能完成多樣化的查詢操作。
類型 | 功能 |
在某個查詢的執行結果基礎上進一步執行查詢操作時,可以通過子查詢操作實現。 | |
對查詢結果資料集執行取交集、並集或補集操作。 | |
通過 | |
通過右表過濾左表的資料,右表的資料不出現在結果集中。 | |
對一個大表和一個或多個小表執行 | |
當兩張表Join存在熱點,導致出現長尾問題時,您可以通過取出熱點key,將資料分為熱點資料和非熱點資料兩部分處理,最後合并的方式,提高Join效率。 | |
通過Lateral View與UDTF(表產生函數)結合,將單行資料拆成多行資料。 | |
對資料進行多維度彙總分析。 | |
| |
通過修改Split Size來控制並發度數量。 | |
對於Delta類型的表,支援:
|
使用限制
當使用
SELECT
語句時,屏顯最多隻能顯示10000行結果,同時返回結果要小於10 MB。當SELECT
語句作為子句時則無此限制,SELECT
子句會將全部結果返回給上層查詢。SELECT
語句查詢分區表時預設禁止全表掃描。自2018年1月10日20:00:00後,在新建立的專案上執行SQL語句時,預設情況下,針對該專案裡的分區表不允許執行全表掃描操作。在查詢分區表資料時必須指定分區,由此減少SQL的不必要I/O,從而減少計算資源的浪費以及隨用隨付模式下不必要的計算費用。
如果您需要對分區表進行全表掃描,可以在全表掃描的SQL語句前加上命令
set odps.sql.allow.fullscan=true;
,並和SQL語句一起提交執行。假設sale_detail
表為分區表,需要同時執行如下語句進行全表查詢:set odps.sql.allow.fullscan=true; SELECT * from sale_detail;
當查詢聚簇表(cluster表)時,目前版本只對單表掃描分區數小於等於400時進行分桶裁剪最佳化。當分桶裁剪最佳化未生效時,會導致掃描資料增加。如果您使用的是按需付費模式,則導致費用增加;如果您使用訂用帳戶付費模式,則會導致SQL計算效能下降。
命令格式
[with <cte>[, ...] ]
SELECT [all | distinct] <SELECT_expr>[, <except_expr>][, <replace_expr>] ...
from <table_reference>
[where <where_condition>]
[group by {<col_list>|rollup(<col_list>)}]
[having <having_condition>]
[window <window_clause>]
[order by <order_condition>]
[distribute by <distribute_condition> [sort by <sort_condition>]|[ cluster by <cluster_condition>] ]
[limit <number>]
命令中各欄位的執行語序請參見SELECT語序。
樣本資料
為便於理解使用方法,本文為您提供來源資料,基於來源資料提供相關樣本。建立表sale_detail,並添加資料,命令樣本如下。
--建立一張分區表sale_detail。
create table if not exists sale_detail
(
shop_name string,
customer_id string,
total_price double
)
partitioned by (sale_date string, region string);
--向源表增加分區。
alter table sale_detail add partition (sale_date='2013', region='china');
--向源表追加資料。
insert into sale_detail partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
查詢分區表sale_detail中的資料,命令樣本如下:
SELECT * from sale_detail;
--返回結果。
+------------+------------+------------+------------+------------+
| shop_name | price | customer | sale_date | region |
+------------+------------+------------+------------+------------+
| s1 | 100.1 | c1 | 2013 | china |
| s2 | 100.2 | c2 | 2013 | china |
| s3 | 100.3 | c3 | 2013 | china |
+------------+------------+------------+------------+------------+
WITH子句(cte)
可選。WITH子句包含一個或多個常用的運算式CTE。CTE充噹噹前運行環境中的暫存資料表,您可以在之後的查詢中引用該表。CTE使用規則如下:
在同一WITH子句中的CTE必須具有唯一的名字。
在WITH子句中定義的CTE僅對在同一WITH子句中的其他CTE可以使用。
假設A是子句中的第一個CTE,B是子句中的第二個CTE:
A引用A:無效。錯誤命令樣本如下。
with A as (SELECT 1 from A) SELECT * from A;
A引用B,B引用A:無效,不允許循環參考。錯誤命令樣本如下
with A as (SELECT * from B ), B as (SELECT * from A ) SELECT * from B;
正確命令樣本如下。
with
A as (SELECT 1 as C),
B as (SELECT * from A)
SELECT * from B;
返回結果如下。
+---+
| c |
+---+
| 1 |
+---+
列運算式(SELECT_expr)
必填。SELECT_expr
格式為col1_name, col2_name, 列運算式,...
,表示待查詢的普通列、分區列或Regex。列運算式使用規則如下:
用列名指定要讀取的列。
讀取表
sale_detail
的列shop_name
。命令樣本如下。SELECT shop_name from sale_detail;
返回結果如下。
+------------+ | shop_name | +------------+ | s1 | | s2 | | s3 | +------------+
用星號(
*
)代表查詢所有的列。可配合where
子句指定過濾條件。讀取表
sale_detail
中所有的列。命令樣本如下。--開啟全表掃描,僅此Session有效。 set odps.sql.allow.fullscan=true; SELECT * from sale_detail;
返回結果如下。
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | s1 | c1 | 100.1 | 2013 | china | | s2 | c2 | 100.2 | 2013 | china | | s3 | c3 | 100.3 | 2013 | china | +------------+-------------+-------------+------------+------------+
在
where
子句中指定過濾條件。命令樣本如下。SELECT * from sale_detail where shop_name='s1';
返回結果如下。
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | s1 | c1 | 100.1 | 2013 | china | +------------+-------------+-------------+------------+------------+
可以使用Regex。
選出
sale_detail
表中所有列名以sh
開頭的列。命令樣本如下。SELECT `sh.*` from sale_detail;
返回結果如下。
+------------+ | shop_name | +------------+ | s1 | | s2 | | s3 | +------------+
選出
sale_detail
表中列名不為shop_name
的所有列。命令樣本如下。SELECT `(shop_name)?+.+` from sale_detail;
返回結果如下。
+-------------+-------------+------------+------------+ | customer_id | total_price | sale_date | region | +-------------+-------------+------------+------------+ | c1 | 100.1 | 2013 | china | | c2 | 100.2 | 2013 | china | | c3 | 100.3 | 2013 | china | +-------------+-------------+------------+------------+
選出
sale_detail
表中排除shop_name
和customer_id
兩列的其他列。命令樣本如下。SELECT `(shop_name|customer_id)?+.+` from sale_detail;
返回結果如下。
+-------------+------------+------------+ | total_price | sale_date | region | +-------------+------------+------------+ | 100.1 | 2013 | china | | 100.2 | 2013 | china | | 100.3 | 2013 | china | +-------------+------------+------------+
選出
sale_detail
表中排除列名以t
開頭的其他列。命令樣本如下。SELECT `(t.*)?+.+` from sale_detail;
返回結果如下。
+------------+-------------+------------+------------+ | shop_name | customer_id | sale_date | region | +------------+-------------+------------+------------+ | s1 | c1 | 2013 | china | | s2 | c2 | 2013 | china | | s3 | c3 | 2013 | china | +------------+-------------+------------+------------+
說明在排除多個列時,如果col2是col1的首碼,則需保證col1寫在col2的前面(較長的col寫在前面)。例如,一個表有2個分區無需被查詢,一個分區名為
ds
,另一個分區名為dshh
,由於前者是後者的首碼,正確運算式為SELECT `(dshh|ds)?+.+` from t;
;錯誤運算式為SELECT `(ds|dshh)?+.+` from t;
。
在選取的列名前可以使用
distinct
去掉重複欄位,只返回去重後的值。使用all
會返回欄位中所有重複的值。不指定此選項時,預設值為all
。查詢表sale_detail中region列資料,如果有重複值時僅顯示一條。命令樣本如下。
SELECT distinct region from sale_detail;
返回結果如下。
+------------+ | region | +------------+ | china | +------------+
去重多列時,
distinct
的範圍是SELECT
的列集合,不是單個列。命令樣本如下。SELECT distinct region, sale_date from sale_detail;
返回結果如下。
+------------+------------+ | region | sale_date | +------------+------------+ | china | 2013 | +------------+------------+
distinct可以對視窗函數的計算結果進行去重,即distinct可以配合視窗函數使用。命令樣本如下:
set odps.sql.allow.fullscan=true; SELECT distinct sale_date, row_number() over (partition by customer_id order by total_price) as rn from sale_detail;
返回結果如下。
+-----------+------------+ | sale_date | rn | +-----------+------------+ | 2013 | 1 | +-----------+------------+
目前不支援distinct和group by聯合使用,例如執行如下命令會報錯。
SELECT distinct shop_name from sale_detail group by shop_name; --報錯資訊: GROUP BY cannot be used with SELECT DISTINCT
排除列(except_expr)
可選。except_expr
格式為except(col1_name, col2_name, ...)
。當您希望讀取表內大多數列的資料,同時要排除表中少數列的資料時,可以通過SELECT * except(col1_name, col2_name, ...) from ...;
語句實現,表示讀取表資料時會排除指定列(col1、col2)的資料。
命令樣本如下。
--讀取sale_detail表的資料,並排除region列的資料。
SELECT * except(region) from sale_detail;
返回結果如下。
+-----------+-------------+-------------+-----------+
| shop_name | customer_id | total_price | sale_date |
+-----------+-------------+-------------+-----------+
| s1 | c1 | 100.1 | 2013 |
| s2 | c2 | 100.2 | 2013 |
| s3 | c3 | 100.3 | 2013 |
+-----------+-------------+-------------+-----------+
修改列(replace_expr)
可選。replace_expr
格式為replace(exp1 [as] col1_name, exp2 [as] col2_name, ...)
。當您希望讀取表內大多數列的資料,同時要對錶中少數列的資料進行修改時,可以通過SELECT * replace(exp1 as col1_name, exp2 as col2_name, ...) from ...;
實現,表示讀取表資料時會將col1的資料修改為exp1,將col2的資料修改為exp2。
命令樣本如下。
--讀取sale_detail表的資料,並修改total_price、region兩列的資料。
SELECT * replace(total_price+100 as total_price, 'shanghai' as region) from sale_detail;
返回結果如下。
+-----------+-------------+-------------+-----------+--------+
| shop_name | customer_id | total_price | sale_date | region |
+-----------+-------------+-------------+-----------+--------+
| s1 | c1 | 200.1 | 2013 | shanghai |
| s2 | c2 | 200.2 | 2013 | shanghai |
| s3 | c3 | 200.3 | 2013 | shanghai |
+-----------+-------------+-------------+-----------+--------+
目標表資訊(table_reference)
必填。table_reference
表示查詢的目標表資訊。目標表使用規則如下:
直接指定目標表名。命令樣本如下。
SELECT customer_id from sale_detail;
返回結果如下。
+-------------+ | customer_id | +-------------+ | c1 | | c2 | | c3 | +-------------+
嵌套子查詢。命令樣本如下。
SELECT * from (SELECT region,sale_date from sale_detail) t where region = 'china';
返回結果如下。
+------------+------------+ | region | sale_date | +------------+------------+ | china | 2013 | | china | 2013 | | china | 2013 | +------------+------------+
WHERE子句(where_condition)
可選。where
子句為過濾條件。如果表是分區表,可以實現列裁剪。使用規則如下:
配合關係運算子,篩選滿足指定條件的資料。關係運算子包含:
>
、<
、=
、>=
、<=
、<>
like
、rlike
in
、not in
between…and
詳情請參見關係運算子。
在
where
子句中,您可以指定分區範圍,只掃描表的指定部分,避免全表掃描。命令樣本如下。SELECT * from sale_detail where sale_date >= '2008' and sale_date <= '2014'; --等價於如下語句。 SELECT * from sale_detail where sale_date between '2008' and '2014';
返回結果如下。
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | s1 | c1 | 100.1 | 2013 | china | | s2 | c2 | 100.2 | 2013 | china | | s3 | c3 | 100.3 | 2013 | china | +------------+-------------+-------------+------------+------------+
通過UDF實現分區裁剪,將UDF語句先當作一個小作業執行,再將執行的結果替換到原來UDF出現的位置。
實現方式
在編寫UDF的時候,UDF類上加入Annotation。
@com.aliyun.odps.udf.annotation.UdfProperty(isDeterministic=true)
說明com.aliyun.odps.udf.annotation.UdfProperty
定義在odps-sdk-udf.jar檔案中。您需要把引用的odps-sdk-udf版本提高到0.30.x或以上。在SQL語句前增加
set odps.sql.udf.ppr.deterministic = true;
語句,此時SQL中所有的UDF均被視為deterministic
。該操作執行的原理是進行執行結果回填,但是結果回填最多回填1000個分區。因此,如果UDF類加入Annotation,則可能會導致出現超過1000個回填結果的報錯。此時您如果需要忽視此錯誤,可以通過設定set odps.sql.udf.ppr.to.subquery = false;
全域關閉此功能。關閉後,UDF分區裁剪也會失效。
注意事項
使用UDF實現分區裁剪時,UDF必須在查詢表的
where
條件裡才會生效。用UDF實現分區裁剪正確樣本如下。
--UDF必須放在查詢的源表的where條件中: SELECT key, value from srcp where udf(ds) = 'xx';
用UDF實現分區裁剪錯誤樣本如下。
--放在join on後面分區裁剪不會生效 SELECT A.c1, A.c2 from srcp1 A join srcp2 B on A.c1 = B.c1 and udf(A.ds) ='xx';
在列運算式(SELECT_expr)中,如果被重新命名的欄欄位(賦予了列別名)使用了函數,則不能在
where
子句中引用列別名。錯誤命令樣本如下。SELECT task_name ,inst_id ,settings ,GET_JSON_OBJECT(settings, '$.SKYNET_ID') as skynet_id ,GET_JSON_OBJECT(settings, '$.SKYNET_NODENAME') as user_agent from Information_Schema.TASKS_HISTORY where ds = '20211215' and skynet_id is not null limit 10;
GROUP BY分組查詢(col_list)
可選。通常,group by
和彙總函式配合使用,根據指定的普通列、分區列或Regex進行分組。group by
使用規則如下:
group by
操作優先順序高於SELECT
操作,因此group by
的取值是SELECT
輸入表的列名或由輸入表的列構成的運算式。需要注意的是:group by
取值為Regex時,必須使用列的完整運算式。SELECT
語句中沒有使用彙總函式的列必須出現在GROUP BY
中。
使用樣本:
直接使用輸入表列名region作為
group by
的列,即以region值分組。命令樣本如下。SELECT region from sale_detail group by region;
返回結果如下。
+------------+ | region | +------------+ | china | +------------+
以region值分組,返回每一組的銷售額總量。命令樣本如下。
SELECT sum(total_price) from sale_detail group by region;
返回結果如下。
+------------+ | _c0 | +------------+ | 300.6 | +------------+
以region值分組,返回每一組的region值(組內唯一)及銷售額總量。命令樣本如下。
SELECT region, sum (total_price) from sale_detail group by region;
返回結果如下。
+------------+------------+ | region | _c1 | +------------+------------+ | china | 300.6 | +------------+------------+
以
SELECT
列的別名分組,命令樣本如下。SELECT region as r from sale_detail group by r; --等效於如下語句。 SELECT region as r from sale_detail group by region;
返回結果如下。
+------------+ | r | +------------+ | china | +------------+
以列運算式分組,命令樣本如下。
SELECT 2 + total_price as r from sale_detail group by 2 + total_price;
返回結果如下。
+------------+ | r | +------------+ | 102.1 | | 102.2 | | 102.3 | +------------+
SELECT
的所有列中沒有使用彙總函式的列,必須出現在GROUP BY
中,否則返回報錯。錯誤命令樣本如下。SELECT region, total_price from sale_detail group by region;
正確命令樣本如下。
SELECT region, total_price from sale_detail group by region, total_price;
返回結果如下。
+------------+-------------+ | region | total_price | +------------+-------------+ | china | 100.1 | | china | 100.2 | | china | 100.3 | +------------+-------------+
當SQL語句設定了屬性,即
set odps.sql.groupby.position.alias=true;
,group by
中的整型常量會被當作SELECT
的列序號處理。命令樣本如下。--與下一條SQL語句一起執行。 set odps.sql.groupby.position.alias=true; --1代表SELECT的列中第一列即region,以region值分組,返回每一組的region值(組內唯一)及銷售額總量。 SELECT region, sum(total_price) from sale_detail group by 1;
返回結果如下。
+------------+------------+ | region | _c1 | +------------+------------+ | china | 300.6 | +------------+------------+
HAVING子句(having_condition)
可選。通常HAVING
子句與彙總函式一起使用,實現過濾。命令樣本如下。
--為直觀展示資料呈現效果,向sale_detail表中追加資料。
insert into sale_detail partition (sale_date='2014', region='shanghai') values ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
--使用having子句配合彙總函式實現過濾。
SELECT region,sum(total_price) from sale_detail
group by region
having sum(total_price)<305;
返回結果如下。
+------------+------------+
| region | _c1 |
+------------+------------+
| china | 300.6 |
| shanghai | 200.9 |
+------------+------------+
ORDER BY全域排序(order_condition)
可選。order by
用於對所有資料按照指定普通列、分區列或指定常量進行全域排序。order by
使用規則如下:
預設對資料進行升序排序,如果降序排序,需要使用
desc
關鍵字。order by
預設要求帶limit
資料行數限制,沒有limit
會返回報錯。如您需要解除order by
必須帶limit
的限制,詳情請參見LIMIT NUMBER限制輸出行數>解除ORDER BY必須帶LIMIT的限制。查詢表sale_detail的資訊,並按照total_price升序排列前2條。命令樣本如下。
SELECT * from sale_detail order by total_price limit 2;
返回結果如下。
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | s1 | c1 | 100.1 | 2013 | china | | s2 | c2 | 100.2 | 2013 | china | +------------+-------------+-------------+------------+------------+
查詢表sale_detail的資訊,並按照total_price降序排列前2條。命令樣本如下。
SELECT * from sale_detail order by total_price desc limit 2;
返回結果如下。
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | s3 | c3 | 100.3 | 2013 | china | | s2 | c2 | 100.2 | 2013 | china | +------------+-------------+-------------+------------+------------+
在使用
order by
排序時,NULL會被認為比任何值都小,這個行為與MySQL一致,但是與Oracle不一致。查詢表sale_detail的資訊,並按照total_price升序排列前2條。命令樣本如下。
SELECT * from sale_detail order by total_price limit 2;
返回結果如下。
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | s1 | c1 | 100.1 | 2013 | china | | s2 | c2 | 100.2 | 2013 | china | +------------+-------------+-------------+------------+------------+
order by
後面需要加上SELECT
列的別名。當SELECT
某列時,如果沒有指定列的別名,則列名會被作為列的別名。order by
加列的別名。命令樣本如下。SELECT total_price as t from sale_detail order by total_price limit 3; --等效於如下語句。 SELECT total_price as t from sale_detail order by t limit 3;
返回結果如下。
+------------+ | t | +------------+ | 100.1 | | 100.2 | | 100.3 | +------------+
當SQL語句設定了屬性,即
set odps.sql.orderby.position.alias=true;
,order by
中的整型常量會被當作SELECT
的列序號處理。命令樣本如下。--與下一條SQL語句一起執行。 set odps.sql.orderby.position.alias=true; SELECT * from sale_detail order by 3 limit 3;
返回結果如下。
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | s1 | c1 | 100.1 | 2013 | china | | s2 | c2 | 100.2 | 2013 | china | | s3 | c3 | 100.3 | 2013 | china | +------------+-------------+-------------+------------+------------+
offset
可以和order by...limit
語句配合使用,用於指定跳過的行數,格式為order by...limit m offset n
,也可以簡寫為order by...limit n, m
。其中:limit m
控制輸出m行資料,offset n
表示在開始返回資料之前跳過的行數。offset 0
與省略offset子句效果相同。將表sale_detail按照total_price升序排序後,輸出從第3行開始的3行資料。命令樣本如下。
SELECT customer_id,total_price from sale_detail order by total_price limit 3 offset 2; --等效於如下語句。 SELECT customer_id,total_price from sale_detail order by total_price limit 2, 3;
返回結果如下。
+-------------+-------------+ | customer_id | total_price | +-------------+-------------+ | c3 | 100.3 | +-------------+-------------+
由於查詢到的資料從第3行開始僅剩1行資料,不足3行,所以返回結果只有1行。
Range Clustering可以用來做全域排序加速。在普通的ORDER BY情境,為保證全域有序,所有的排序資料合併到一個單獨的Instance運行,這就無法發揮平行處理的優勢。利用Range Clustering的partition步驟,可以實現並發多路全排序。首先對資料取樣並劃分Range,然後對各個Range做並發排序,最後得到的就是全域有序的結果,詳情請參見全域排序加速。
DISTRIBUTE BY雜湊分區(distribute_condition)
可選。distribute by
用於對資料按照某幾列的值做Hash分區。
distribute by
控制Map(讀資料)的輸出在Reducer中是如何劃分的,如果不希望Reducer的內容存在重疊,或需要對同一分組的資料一起處理,您可以使用distribute by
來保證同組資料分發到同一個Reducer中。
必須使用SELECT
的輸出資料行別名,當SELECT
某列時,如果沒有指定列的別名,則列名會被作為列的別名。命令樣本如下:
--查詢表sale_detail中的列region值並按照region值進行雜湊分區。
SELECT region from sale_detail distribute by region;
--等價於如下語句。
SELECT region as r from sale_detail distribute by region;
SELECT region as r from sale_detail distribute by r;
SORT BY局部排序(sort_condition)
可選。通常,配合distribute by
使用。sort by
使用規則如下:
sort by
預設對資料進行升序排序,如果降序排序,需要使用desc
關鍵字。如果
sort by
語句前有distribute by
,sort by
會對distribute by
的結果按照指定的列進行排序。查詢表sale_detail中的列region和total_price的值並按照region值進行雜湊分區,然後按照total_price對雜湊分區結果進行局部升序排序。命令樣本如下。
--為直觀展示資料呈現效果,向sale_detail表中追加資料。 insert into sale_detail partition (sale_date='2014', region='shanghai') values ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5); SELECT region,total_price from sale_detail distribute by region sort by total_price;
返回結果如下。
+------------+-------------+ | region | total_price | +------------+-------------+ | shanghai | NULL | | china | 100.1 | | china | 100.2 | | china | 100.3 | | shanghai | 100.4 | | shanghai | 100.5 | +------------+-------------+
查詢表sale_detail中的列region和total_price的值並按照region值進行雜湊分區,然後按照total_price對雜湊分區結果進行局部降序排序。命令樣本如下。
SELECT region,total_price from sale_detail distribute by region sort by total_price desc;
返回結果如下。
+------------+-------------+ | region | total_price | +------------+-------------+ | shanghai | 100.5 | | shanghai | 100.4 | | china | 100.3 | | china | 100.2 | | china | 100.1 | | shanghai | NULL | +------------+-------------+
如果
sort by
語句前沒有distribute by
,sort by
會對每個Reduce中的資料進行局部排序。保證每個Reduce的輸出資料都是有序的,從而增加儲存壓縮率,同時讀取時如果有過濾,能夠減少真正從磁碟讀取的資料量,提高後續全域排序的效率。命令樣本如下。
SELECT region,total_price from sale_detail sort by total_price desc;
返回結果如下。
+------------+-------------+ | region | total_price | +------------+-------------+ | china | 100.3 | | china | 100.2 | | china | 100.1 | | shanghai | 100.5 | | shanghai | 100.4 | | shanghai | NULL | +------------+-------------+
order by|distribute by|sort by
的取值必須是SELECT
語句的輸出資料行,即列的別名。列的別名可以為中文。在MaxCompute SQL解析中,
order by|distribute by|sort by
執行順序在SELECT
操作之後,因此它們的取值只能為SELECT
語句的輸出資料行。order by
不和distribute by
、sort by
同時使用,group by
也不和distribute by
、sort by
同時使用。
LIMIT限制輸出行數(number)
可選。limit <number>
中的number
是常數,用於限制輸出行數,取值範圍為int32位取值範圍,即最大值不可超過2,147,483,647。
limit
基於分布式系統對資料進行掃描後過濾,您無法通過limit
減少返回資料量進而減少計算費用。
當您涉及到如下情境時,可參考對應解決方案處理:
解除
order by
必須帶limit
的限制。因為
order by
需要對單個執行節點做全域排序,所以預設帶limit
限制,避免誤用導致單點處理大量資料。如果您的使用情境確實需要order by
放開limit
限制,可以通過如下兩種方式實現:Project層級:設定
setproject odps.sql.validate.orderby.limit=false;
關閉order by
必須帶limit
的限制。Session層級:設定
set odps.sql.validate.orderby.limit=false;
關閉order by
必須帶limit
的限制,需要與SQL語句一起提交。說明如果關閉
order by
必須帶limit
的限制,在單個執行節點有大量資料排序的情況下,資源消耗或處理時間長度等效能表現會受到影響。
解除屏顯限制
當使用無
limit
的SELECT
語句或limit
的number
數量超過設定的屏顯上限時,如果您直接從屏顯視窗查看結果,最多隻能輸出屏顯上限設定的行數。每個專案空間的屏顯上限可能不同,您可以參考如下方法控制:
如果關閉了專案空間資料保護,修改odpscmd_config.ini檔案。
設定odpscmd_config.ini檔案中的
use_instance_tunnel=true
,如果不配置instance_tunnel_max_record
參數,則屏顯行數不受限制;否則,屏顯行數受instance_tunnel_max_record
參數值限制。instance_tunnel_max_record
參數值上限為10000行。Instance Tunnel詳情請參見使用說明。如果開啟了專案空間資料保護,屏顯行數受
READ_TABLE_MAX_ROW
參數值限制,配置上限為10000行。
說明您可以執行
show SecurityConfiguration;
命令查看ProjectProtection
屬性配置。如果ProjectProtection=true
,根據專案空間資料保護需求判斷是否關閉資料保護機制。如果可以關閉,通過set ProjectProtection=false;
命令關閉。ProjectProtection
屬性預設不開啟。專案空間資料保護機制詳情請參見資料保護機制。
視窗子句(window_clause)
詳細視窗子句資訊,請參見視窗函數文法。
Split Size Hint
可選。您可通過調整Split Size來控制並發度,調整計算效能。Split Size功能可以作用到表層級。指定的值單位為MB,預設值為256MB。
使用情境。
當您發現作業有很多子任務都在等待資源,沒有辦法申請到更多資源的情況,可以通過調高Split Size值,減少並發度,可以節省啟停子任務的時間。
當並發度比較低,例如並發度只有幾百,而且當前執行的子任務運行很久都沒有出結果,但是資源集區中還有很多資源時,可以調低值來提高並發度,降低整個作業的已耗用時間。
注意事項。
此Hint對於Cluster表,如果最佳化中會使用Cluster的分桶屬性,那麼Split Size Hint會失效。
Split Size的值按照256MB的倍數進行調整,例如128MB、512MB等。
一個SQL中有重複讀同張表,那麼Hint會被合并成為指定的最小值。
SQL中有兩個讀表src的地方,一個Hint為1MB一個為10MB,那麼最後會按照1MB來切分。
SQL中有兩個讀表src的地方,一個Hint為1MB一個沒有,那麼最後會按照1MB來切分。
使用樣本。
--設定split size大小為1MB,此hint會在讀表src時,按照1M的大小來切分task SELECT a.key from src a /*+split_size(1)*/ join src2 b on a.key=b.key;