MaxCompute：Select文法介紹

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY order_condition]
[DISTRIBUTE BY distribute_condition [SORT BY sort_condition] ]
[LIMIT number]

• Select操作從表中讀取資料，要讀的列可以用列名指定，或者用*代表所有的列，一個簡單的Select語句，如下所示：

  select * from sale_detail;

  若您唯讀取sale_detail的一列shop_name，如下所示：

  select shop_name from sale_detail;

  在where中可以指定過濾的條件，如下所示：

  select * from sale_detail where shop_name like 'hang%';

  當使用Select語句屏顯時，目前最多隻能顯示10000行結果。當Select作為子句時，無此限制，Select子句會將全部結果返回給上層查詢。
• select分區表時禁止全表掃描。

  2018-01-10 20點後建立的新項目，預設情況下執行SQL時，針對該project裡的分區表不允許全表掃描，必須有分區條件指定需要掃描的分區，由此減少SQL的不必要I/O，從而減少計算資源的浪費，同時也減少了不必要的後付費模式的計算費用（後付費模式中，資料輸入量是計量計費參數之一）。

  例如表定義是 t1(c1,c2) partitioned by(ds)，在新項目裡執行如下語句會被禁止，返回error：

  Select * from t1 where c1=1;
  Select * from t1 where (ds=‘20180202’ or c2=3);
  Select * from t1 left outer join t2 on a.id =b.id and a.ds=b.ds and b.ds=‘20180101);  
  --Join進行關聯時，若分區剪裁條件放在where中，則分區剪裁生效，若放在on條件中，從表的分區剪裁會生效，主表則進行全表掃描。

  若實在需要對分區表進行全表掃描，可以在對分區表全表掃描的SQL語句前加一個set語句set odps.sql.allow.fullscan=true;，並和SQL語句一起提交執行。假設sale_detail表為分區表，則要全表掃描需同時提交如下簡單查詢命令：

  set odps.sql.allow.fullscan=true;
  select * from sale_detail;

  如果需要整個項目都允許全表掃描，可以通過開關自行開啟或關閉（true/false），命令如下：

  setproject odps.sql.allow.fullscan=true;

• 在table_reference中支援使用嵌套子查詢，如下所示：

  select * from (select region from sale_detail) t where region = 'shanghai';

• where子句支援的過濾條件，如下表所示：

  過濾條件	描述
  > 、 < 、 =、 >=、 <=、 <>	關係操作符
  like、rlike	like和rlike的source和pattern參數均僅接受String類型。
  in、not in	如果在in/not in條件後加子查詢，子查詢只能返回一列值，且傳回值的數量不能超過1000。

  在Select語句的where子句中，您可以指定分區範圍，這樣可以僅僅掃描表的指定部分，避免全表掃描。如下所示：

  SELECT sale_detail.* FROM sale_detail WHERE sale_detail.sale_date >= '2008' AND sale_detail.sale_date <= '2014';

  MaxCompute SQL的where子句支援between…and條件查詢，上述SQL可以重寫如下：

  SELECT sale_detail.* FROM sale_detail WHERE sale_detail.sale_date BETWEEN '2008' AND  '2014';

• distinct：如果有重複資料行時，在欄位前使用distinct，會將重複欄位去重，只返回一個值，而使用all將返回欄位中所有重複的值，不指定此選項時預設效果和all相同。
  使用distinct只返回一行記錄，如下所示：

  select distinct region from sale_detail;
  select distinct region, sale_date from sale_detail;
  -- distinct多列，distinct的範圍是 Select 的列集合，不是單個列。

• group by：分組查詢，一般group by和彙總函式配合使用。在Select中包含彙總函式時有以下規則：
  • 用group by的key可以是輸入表的列名。
  • 也可以是由輸入表的列構成的運算式，不允許是Select語句的輸出資料行的別名。

  • 規則i的優先順序高於規則ii。當規則i和規則ii發生衝突時，即group by的key既是輸入表的列或運算式，又是Select的輸出資料行，以規則i為準。

  樣本如下：

  select region from sale_detail group by region;
  -- 直接使用輸入表列名作為group by的列，可以運行
  select sum(total_price) from sale_detail group by region;
  -- 以region值分組，返回每一組的銷售額總量，可以運行
  select region, sum(total_price) from sale_detail group by region;
  -- 以region值分組，返回每一組的region值(組內唯一)及銷售額總量，可以運行
  select region as r from sale_detail group by r;
  -- 使用select列的別名運行，報錯返回
  select 2 + total_price as r from sale_detail group by 2 + total_price;
  -- 必須使用列的完整運算式
  select region, total_price from sale_detail group by region;
  -- 報錯返回，select的所有列中，沒有使用彙總函式的列，必須出現在group by中
  select region, total_price from sale_detail group by region, total_price;
  -- 可以運行

  之所以有這樣的限制，是因為在SQL解析中，group by操作通常是先於Select操作的，因此group by只能接受輸入表的列或運算式為key。
  说明 關於彙總函式的詳情請參見彙總函式。
• order by：對所有資料按照某幾列進行全域排序。如果您希望按照降序對記錄進行排序，可以使用DESC關鍵字。由於是全域排序，order by必須與limit共同使用。在使用order by排序時，Null會被認為比任何值都小，這個行為與MySQL一致，但是與Oracle不一致。
  與group by不同，order by後面必須加Select列的別名，當Select某列時，如果沒有指定列的別名，將列名作為列的別名。

  select * from sale_detail order by region;
  -- 報錯返回，order by沒有與limit共同使用
  select * from sale_detail order by region limit 100;
  select region as r from sale_detail order by region limit 100;
  -- 報錯返回，order by後面必須加列的別名。
  select region as r from sale_detail order by r limit 100;

  [limit number]的number是常數，限制輸出行數。當使用無limit的Select語句直接從螢幕輸出查看結果時，最多隻輸出10000行。每個項目空間的這個屏顯最大限制限制可能不同，可以通過setproject命令控制。
• distribute by：對資料按照某幾列的值做Hash分區，必須使用Select的輸出資料行別名。

  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：局部排序，語句前必須加distribute by。實際上sort by是對distribute by的結果進行局部排序。必須使用Select的輸出資料行別名。

  select region from sale_detail distribute by region sort by region;
  select region as r from sale_detail sort by region;
  -- 沒有distribute by，報錯退出。

• order by不和distribute by/sort by共用，同時group by也不和distribute by/sort by共用，必須使用Select的輸出資料行別名。