SELECT 構文の概要 - MaxCompute - Alibaba Cloud ドキュメントセンター

MaxCompute では、SELECT 文を使用してデータをクエリできます。このトピックでは、SELECT コマンドの構文と、ネストされたクエリ、グループクエリ、ソートなどの操作を実行する方法について説明します。

SELECT 文を実行する前に、宛先テーブルに対する `Select` 権限があることを確認してください。詳細については、「MaxCompute の権限」をご参照ください。

これらの文は、次のプラットフォームで実行できます。

機能

SELECT 文は、テーブルから指定された条件を満たすデータをクエリするために使用されます。次の表に、さまざまなシナリオで実行できるクエリ操作を示します。

タイプ	機能
サブクエリ	前のクエリの結果に対してさらにクエリを実行します。
INTERSECT、UNION、EXCEPT	クエリ結果データセットに対して積集合、和集合、または補集合の操作を実行します。
JOIN	`JOIN` 操作を使用してテーブルを結合し、結合条件とクエリ条件を満たすデータを返します。
SEMI JOIN	右テーブルを使用して左テーブルのデータをフィルター処理します。結果セットには左テーブルのデータのみが含まれます。
MAPJOIN ヒント	大きいテーブルと 1 つ以上の小さいテーブルに対する `JOIN` 操作のクエリパフォーマンスを向上させるには、`SELECT` 文を使用して `MAPJOIN` ヒントを明示的に指定します。
SKEWJOIN ヒント	2 つのテーブルに対する JOIN 操作でホットスポットが発生すると、ロングテール問題が発生する可能性があります。JOIN の効率を向上させるには、ホットスポットキーを抽出し、ホットスポットデータと非ホットスポットデータを別々に処理してから、結果をマージします。
LATERAL VIEW	Lateral View をユーザー定義のテーブル値関数 (UDTF) と共に使用して、単一のデータ行を複数の行に分割します。
GROUPING SETS	複数のディメンションからデータを集計および分析します。
SELECT TRANSFORM	`SELECT TRANSFORM` 構文は、指定された子プロセスを開始し、フォーマットされた入力データを標準入力を介してプロセスに送信し、プロセスからの標準出力を解析して出力データを取得します。
Split Size ヒント	Split Size を変更して並列度を制御します。
タイムトラベルクエリと増分クエリ	Delta テーブルは以下をサポートします：タイムトラベルクエリ：特定の時点またはバージョンからソーステーブルの履歴スナップショットをクエリします。増分クエリ：指定された時間またはバージョンの範囲内でソーステーブルから履歴増分データをクエリします。

制限事項

SELECT 文が実行されると、最大 10,000 行の結果が表示され、返される結果のサイズは 10 MB を超えることはできません。この制限は、SELECT 文がサブクエリとして使用される場合には適用されません。この場合、SELECT 句はすべての結果を親クエリに返します。
SELECT 文を使用してパーティションテーブルをクエリする場合、デフォルトでは全表スキャンは禁止されています。
2018 年 1 月 10 日 20:00:00 以降に作成されたプロジェクトでは、デフォルトでプロジェクト内のパーティションテーブルに対して全表スキャンを実行することはできません。パーティションテーブルからデータをクエリする場合は、パーティションを指定する必要があります。この方法により、不要な I/O 操作とコンピューティングリソースの消費が削減されます。また、従量課金方法を使用している場合は、不要なコンピューティングコストも削減されます。
パーティションテーブルで全表スキャンを実行するには、SQL 文の前に SET odps.sql.allow.fullscan=true; コマンドを追加し、それらをまとめて実行のために送信します。たとえば、sale_detail テーブルがパーティションテーブルの場合、全表クエリを実行するには次の文をまとめて実行する必要があります：
```
SET odps.sql.allow.fullscan=true;
SELECT * from sale_detail;
```
クラスター化テーブルをクエリする場合、現在のバージョンでは、単一のテーブルでスキャンされるパーティションが 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 文の句の実行順序の詳細については、「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);

-- sale_detail テーブルにパーティションを追加します。
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china');

-- sale_detail テーブルにデータを挿入します。
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  | 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      |
+------------+-------------+-------------+------------+------------+

WITH 句 (CTE)

オプション。`WITH` 句には、1 つ以上の共通テーブル式 (CTE) が含まれます。CTE は、現在のランタイム環境で一時テーブルとして機能します。このテーブルは、後のクエリで参照できます。CTE には次のルールが適用されます：

同じ `WITH` 句内の CTE は、一意の名前を持つ必要があります。
CTE は、同じ `WITH` 句内で定義された他の CTE によってのみ参照できます。
たとえば、A が最初の CTE で、B が同じ `WITH` 句内の 2 番目の CTE であるとします：
- 「A が A を参照する」条件は無効です。以下は誤ったコマンドの例です。
```
WITH 
A AS (SELECT 1 FROM A) 
SELECT * FROM A;
```
  次の結果が返されます：
```
FAILED: ODPS-0130161:[1,6] Parse exception - recursive cte A is invalid, it must have an initial_part and a recursive_part, which must be connected by UNION ALL
```
- 循環参照はサポートされていません。たとえば、B が A を参照する場合、A は B を参照できません。誤ったコマンドの例：
```
WITH 
A AS (SELECT * FROM B ), 
B AS (SELECT * FROM A ) 
SELECT * FROM B;
```
  次の結果が返されます：
```
FAILED: ODPS-0130071:[1,26] Semantic analysis exception - while resolving view B - [1,51]recursive function call is not supported, cycle is A->B->A
```
- 次の例は、正しいコマンドを示しています。
```
WITH 
A AS (SELECT 1 AS C),
B AS (SELECT * FROM A) 
SELECT * FROM B;
```
  次の結果が返されます。
```
+---+
| c |
+---+
| 1 |
+---+
```

列式 (SELECT_expr)

`select_expr` は必須です。SELECT_expr は col1_name, col2_name, column expression,... の形式を使用します。この式は、クエリしたい列、パーティションキー列、または正規表現を指定します。`select_expr` には次のルールが適用されます：

読み取る列を名前で指定できます。
次の文は、sale_detail テーブルから shop_name 列を読み取ります。
```
SELECT shop_name FROM sale_detail;
```
次の結果が返されます。
```
+------------+
| shop_name  |
+------------+
| s1         |
| s2         |
| s3         |
+------------+
```

アスタリスク (*) を使用してすべての列を表すことができます。また、WHERE 句と共に使用してフィルター条件を指定することもできます。

次のコマンドは、sale_detail テーブルからすべての列を読み取ります。

-- 現在のセッションに対してのみ全表スキャンを有効にします。
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      |
+------------+-------------+-------------+------------+------------+

正規表現を使用できます。

次のコマンドは、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      |
+------------+-------------+------------+------------+
```
説明
複数の列を除外し、ある列名が別の列名のプレフィックスである場合、正規表現では長い方の列名を短い方の前に置く必要があります。たとえば、テーブルにクエリする必要のない 2 つのパーティションがあるとします。1 つのパーティション名は ds で、もう 1 つは dshh です。`ds` は `dshh` のプレフィックスであるため、正しい式は SELECT `(dshh|ds)?+.+` FROM t; です。誤った式は SELECT `(ds|dshh)?+.+` FROM t; です。

列名の前に DISTINCT キーワードを使用して、重複値を削除し、一意の値のみを返すことができます。ALL キーワードは、重複を含む列のすべての値を返します。キーワードを指定しない場合、デフォルトで ALL が使用されます。
- 次の例は、`sale_detail` テーブルの `region` 列をクエリするコマンドを示しています。重複する値が存在する場合、1 つだけが表示されます。
```
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_expr は REPLACE(exp1 [as] col1_name, exp2 [as] col2_name, ...) 形式を使用します。`replace_expr` を使用して、テーブルのほとんどの列からデータを読み取り、いくつかの列のデータを変更できます。たとえば、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` は必須です。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_condition` はオプションです。WHERE 句はフィルター条件を指定します。`WHERE` 句がパーティションテーブルに使用される場合、パーティションプルーニングが実行できます。`where_condition` には次のルールが適用されます：

関係演算子を使用して、指定された条件を満たすデータをフィルターできます。関係演算子には次のものがあります：
- >、<、=、>=、<=、および <>
- 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      |
+------------+-------------+-------------+------------+------------+
```
説明
EXPLAIN 文を実行して、パーティションプルーニングが有効かどうかを確認できます。ユーザー定義関数 (UDF) または JOIN 操作のパーティション条件設定により、パーティションプルーニングが失敗する場合があります。詳細については、「パーティションプルーニングが有効かどうかの確認」をご参照ください。
UDF を使用してパーティションプルーニングを実装できます。UDF はまず小さなジョブとして実行されます。次に、そのジョブの結果が元の文の UDF を置き換えるために使用されます。
- 実装方法
  - UDF を記述する際に、UDF クラスにアノテーションを追加できます。
```
@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 UDF と見なされます。上記の `SET` コマンドは、ジョブの結果でパーティションをバックフィルします。最大 1,000 個のパーティションがジョブの結果でバックフィルできます。UDF クラスにアノテーションを追加すると、1,000 個を超えるパーティションがバックフィルされたことを示すエラーが返される場合があります。このエラーを無視するには、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` はオプションです。ほとんどの場合、GROUP BY は集計関数と共に使用され、指定された列、パーティションキー列、または正規表現に基づいて行をグループ化します。GROUP BY には次のルールが適用されます：

GROUP BY 句は SELECT 句の前に実行されます。したがって、GROUP BY 句の列は、SELECT 文の入力テーブルの列名、またはそのテーブルの列から形成される式で指定できます。GROUP BY 句を使用する際は、次の点に注意してください：

GROUP BY 句で正規表現を使用する場合、列の完全な式を使用する必要があります。
GROUP BY ALL 操作がサポートされています。この操作を使用するには、odps.sql.bigquery.compatible=true; を設定して BigQuery 互換モードを有効にします。
SELECT リスト内の、集計関数にカプセル化されていないすべての列は、GROUP BY 句に含める必要があります。

使用例：

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 句に含める必要があります。そうしないと、エラーが返されます。誤ったコマンドの例：

-- エラー： FAILED: ODPS-0130071:[1,16] Semantic analysis exception - column reference sale_detail.total_price should appear in GROUP BY key
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       |
+------------+-------------+

odps.sql.bigquery.compatible=true; を設定して BigQuery 互換モードを有効にすると、GROUP BY ALL 構文を使用して、すべてのクエリフィールドで自動的にグループ化できます。

-- グループ化のためにすべてのフィールドを明示的にリストします。
SELECT 
    shop_name, 
    customer_id, 
    sale_date, 
    region, 
    SUM(total_price) AS total_sales
FROM sale_detail
GROUP BY shop_name, customer_id, sale_date, region;

-- GROUP BY ALL を使用してすべてのフィールドでグループ化します。これは、グループ化のためにすべてのフィールドを明示的にリストするのと同じです。
SET odps.sql.bigquery.compatible=true; 
SELECT 
    shop_name, 
    customer_id, 
    sale_date, 
    region, 
    SUM(total_price) AS total_sales
FROM sale_detail
GROUP BY ALL;

次の結果が返されます。

+-----------+-------------+-----------+--------+-------------+
| shop_name | customer_id | sale_date | region | total_sales |
+-----------+-------------+-----------+--------+-------------+
| s1        | c1          | 2013      | china  | 100.1       |
| s2        | c2          | 2013      | china  | 100.2       |
| s3        | c3          | 2013      | china  | 100.3       |
+-----------+-------------+-----------+--------+-------------+

SET odps.sql.groupby.position.alias=true; コマンドを実行すると、GROUP BY 句の整数定数は SELECT リストの列の序数として扱われます。サンプル文：

-- このコマンドを次の SELECT 文と共に実行します。
SET odps.sql.groupby.position.alias=true;
-- 1 は region 列を示し、これは次の SELECT 文で読み取られる最初の列です。この文は、region 列の値に基づいてテーブルデータをグループ化し、region 列の異なる値と各グループの総売上を返します。
SELECT region, SUM(total_price) FROM sale_detail GROUP BY 1;

次の結果が返されます。

+------------+------------+
| region     | _c1        |
+------------+------------+
| china      | 300.6      |
+------------+------------+

HAVING 句 (HAVING_condition)

`HAVING` 句はオプションです。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_condition を集計関数と共に使用してデータをフィルターします。
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_condition` はオプションです。ORDER BY は、指定された列、パーティションキー列、または定数に基づいてすべてのデータレコードをソートします。ORDER BY には次のルールが適用されます：

デフォルトでは、データは昇順でソートされます。データを降順でソートするには、DESC キーワードを使用する必要があります。

デフォルトでは、ORDER BY 句は LIMIT 句と共に使用して、出力行数を制限する必要があります。LIMIT 句を含めないと、エラーが返されます。ORDER BY 句を LIMIT 句と共に使用する必要があるという制限を解除するには、「LIMIT での出力行数の制限 > 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 とは異なります。

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      |
+------------+
```

SET odps.sql.orderby.position.alias=true; コマンドを実行すると、ORDER BY 句の整数定数は SELECT リストの列の序数として扱われます。次の文に例を示します：

-- このコマンドを次の SELECT 文と共に実行します。
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 はソートされた結果の最初の `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       |
+-------------+-------------+
```
最初の 2 行をスキップした後に残るのは 1 行だけなので、制限が 3 であっても結果には 1 行しか含まれません。
Range Clustering を使用してグローバルソートを高速化できます。典型的な `ORDER BY` シナリオでは、グローバルな順序を保証するために、すべてのデータを単一のインスタンスでマージして処理する必要があります。このアプローチでは、並列処理の利点を活用できません。Range Clustering の `PARTITION` ステップを使用して、同時グローバルソートを実行できます。このプロセスは、まずデータをサンプリングして範囲に分割し、次に各範囲を同時にソートしてグローバルにソートされた結果を生成します。詳細については、「グローバルソートの高速化」をご参照ください。

ハッシュシャーディングのための DISTRIBUTE BY (DISTRIBUTE_condition)

`distribute_condition` はオプションです。DISTRIBUTE BY は、指定された列の値に基づいてデータにハッシュシャーディングを実行します。

DISTRIBUTE BY は、マップタスクの出力が reduce タスク間でどのようにパーティション分割されるかを制御します。同じグループのデータをまとめて処理したい場合や、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;

次の結果が返されます。

+------------+
| r          | 
+------------+
| china      | 
| china      | 
| china      | 
+------------+

ローカルソートのための 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);

---- 各 Reduce ステージのワーカー数を変更します。
SET odps.stage.reducer.num=2;
SELECT region,total_price FROM sale_detail DISTRIBUTE BY region SORT BY total_price;

次の結果が返されます。

+------------+-------------+
| region     | total_price | 
+------------+-------------+
| shanghai   | NULL        | 
| shanghai   | 100.4       | 
| shanghai   | 100.5       | 
| china      | 100.1       | 
| china      | 100.2       | 
| china      | 100.3       | 
+------------+-------------+

次のコマンドは、`sale_detail` テーブルから `region` と `total_price` 列の値をクエリし、`region` の値に基づいて結果にハッシュシャーディングを実行し、その後、ハッシュシャーディングされた結果を `total_price` で降順にローカルソートします。次の例は、サンプルコマンドを示しています。

-- 各 Reduce ステージのワーカー数を変更します。
SET odps.stage.reducer.num=2;
SELECT region,total_price FROM sale_detail DISTRIBUTE BY region SORT BY total_price DESC;

次の結果が返されます。

+------------+-------------+
| region     | total_price | 
+------------+-------------+
| shanghai   | 100.5       | 
| shanghai   | 100.4       | 
| shanghai   | NULL        | 
| china      | 100.3       | 
| china      | 100.2       | 
| china      | 100.1       | 
+------------+-------------+

SORT BY が DISTRIBUTE BY なしで使用される場合、SORT BY は各 reducer 内のデータをソートします。
これにより、各 reducer の出力データが順序付けられ、ストレージ圧縮率が向上します。また、フィルタリング中にディスクから読み取るデータ量を削減し、後続のグローバルソート操作の効率を向上させることもできます。次の例は、サンプルコマンドを示しています。
```
-- 各 Reduce ステージのワーカー数を設定します。
SET odps.stage.reducer.num=2;
SELECT region,total_price FROM sale_detail 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        | 
+------------+-------------+
```

説明

ORDER BY, DISTRIBUTE BY, or SORT BY 句の列は、SELECT 文の出力列のエイリアスで指定する必要があります。列のエイリアスは中国語で指定できます。
ORDER BY、DISTRIBUTE BY、および `SORT BY` 句は `SELECT` 操作の後に実行されます。したがって、これらの句の列は SELECT 文の出力列である必要があります。
ORDER BY を DISTRIBUTE BY または SORT BY と一緒に使用することはできません。同様に、GROUP BY を DISTRIBUTE BY または SORT BY と一緒に使用することはできません。

出力行数を制限する LIMIT (number)

`LIMIT` はオプションです。LIMIT <number></number> の number は、出力行数を制限する定数です。`number` の値は 32 ビット整数で、最大値は 2,147,483,647 です。

説明

LIMIT 句は分散スキャンの後にデータをフィルターします。したがって、LIMIT を使用してもスキャンされるデータ量は減らず、コンピューティングコストも下がりません。

次のシナリオに遭遇した場合、対応するソリューションを参照できます：

ORDER BY 句は、LIMIT 句なしで使用できるようになりました。
ORDER BY は単一の実行ノードでグローバルソートを実行するため、デフォルトで LIMIT 制限が適用されます。これにより、単一ノードが大量のデータを処理する誤用を防ぎます。シナリオで ORDER BY を LIMIT 制限なしで使用する必要がある場合は、次のいずれかの方法を使用できます：
- プロジェクトレベル：SETPROJECT odps.sql.validate.orderby.limit=false; コマンドを実行して、ORDER BY を LIMIT と共に使用する必要があるという制限を無効にします。
- セッションレベル：SET odps.sql.validate.orderby.limit=false; を設定して、ORDER BY 句を LIMIT 句と共に使用する必要があるという要件を無効にします。このコマンドを SQL 文と共に送信します。
  説明
  ORDER BY を LIMIT と共に使用する要件を無効にすると、単一の実行ノードで大量のデータをソートすると、より多くのリソースを消費し、時間がかかります。
画面表示制限の解除
SELECT 文を LIMIT 句なしで実行した場合、または LIMIT 句で指定された NUMBER が設定された表示制限 (n) を超える場合、結果ウィンドウには最大 n 行が表示されます。
画面表示制限はプロジェクトごとに異なる場合があります。この制限は次の方法で制御できます：
- プロジェクトのデータ保護が無効になっている場合は、`odpscmd_config.ini` ファイルを変更する必要があります。
  `odpscmd_config.ini` ファイルで use_instance_tunnel=true を設定します。instance_tunnel_max_record パラメーターが設定されていない場合、表示できる行数に制限はありません。それ以外の場合、表示できる行数は instance_tunnel_max_record パラメーターの値によって制限されます。instance_tunnel_max_record パラメーターの最大値は 10,000 行です。Instance Tunnel の詳細については、「使用上の注意」をご参照ください。
- プロジェクトのデータ保護が有効になっている場合、表示可能な行数は READ_TABLE_MAX_ROW パラメーターによって制限されます。このパラメーターの最大値は 10,000 です。
説明
SHOW SecurityConfiguration; コマンドを実行して、ProjectProtection プロパティの構成を表示できます。ProjectProtection=true の場合、プロジェクトのデータ保護要件に基づいてデータ保護メカニズムを無効にするかどうかを決定する必要があります。メカニズムを無効にするには、SET ProjectProtection=false; コマンドを実行します。デフォルトでは、ProjectProtection プロパティは有効になっていません。プロジェクトのデータ保護メカニズムの詳細については、「データ保護メカニズム」をご参照ください。

WINDOW 句 (window_clause)

ウィンドウ句の詳細については、「ウィンドウ関数の構文」をご参照ください。