MaxCompute: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 句はオプションです。WITH 句には、1 つ以上の共通テーブル式 (CTE) が含まれています。CTE は、ランタイム環境で一時テーブルとして使用されます。後続のクエリで一時テーブルを参照できます。CTE を使用する場合、次のルールに従う必要があります。

• CTE の名前は、WITH 句内で一意である必要があります。

• WITH 句で定義された CTE は、同じ WITH 句で定義された他の CTE のみによって参照できます。

  たとえば、A は WITH 句の最初の CTE、B は同じ WITH 句の 2 番目の CTE です。

  • A が A を参照する場合、参照は無効です。CTE の誤った使用法:

    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

  • A が B を参照し、B が A を参照する場合、参照は無効です。循環参照はサポートされていません。CTE の誤った使用法:

    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 は、col1_name, col2_name, 列式,... の形式です。この形式は、クエリする共通列またはパーティションキー列、あるいはデータのクエリに使用する正規表現 を示します。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      |
    +------------+-------------+------------+------------+

    説明

    col2 の名前が col1 の名前のプレフィックスであり、複数の列を除外する場合、col1 の名前が col2 の名前の前に配置されていることを確認する必要があります。長い列名は短い列名の前に配置されます。たとえば、パーティションテーブルの 2 つのパーティションをクエリする必要はありません。1 つのパーティションの名前は ds、もう 1 つのパーティションの名前は dshh です。ds パーティションの名前は、dshh パーティションの名前のプレフィックスです。したがって、SELECT `(dshhds)?+.+` FROM t; 式は正しいですが、SELECT `(dsdshh)?+.+` FROM t; 式は正しくありません。

• 列名の前に DISTINCT を使用して、列から重複値を除外し、個別の値のみを返します。列名の前に ALL を使用すると、重複値を含む列のすべての値が返されます。DISTINCT が使用されていない場合、ALL が使用されます。

  • 次のステートメントは、sale_detail テーブルから region 列のデータをクエリし、1 つの個別の値のみを返します。サンプルステートメント:

    SELECT DISTINCT region FROM sale_detail;

    次の結果が返されます。

    +------------+
    | region     |
    +------------+
    | china      |
    +------------+

  • 次のステートメントは、DISTINCT オプションの後に複数の列を指定します。DISTINCT オプションは、単一の列ではなく、指定されたすべての列に有効になります。サンプルステートメント:

    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」が返されます。

サンプルステートメント:

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

サンプルステートメント:

-- 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 を使用する場合、次のルールに従う必要があります。

• デスティネーションテーブルの名前を指定します。サンプルステートメント:

  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_condition はオプションです。WHERE 句はフィルタ条件を指定します。where_condition がパーティションテーブルに使用される場合、列プルーニングを実行できます。where_condition を使用する場合、次のルールに従う必要があります。

• where_condition を関係演算子 と共に使用して、指定された条件を満たすデータを取得します。関係演算子 には次のものがあります。

  • >、<、=、>=、<=、および <>

  • LIKE および RLIKE

  • IN、NOT IN

  • BETWEEN…AND

  詳細については、「関係演算子」をご参照ください。

  次のステートメントは、where_condition でスキャンするパーティションを指定します。これにより、全表スキャンが回避されます。サンプルステートメント:

  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 を使用する場合、MaxCompute は UDF を小さなジョブとして実行し、これらのジョブの結果でパーティションをバックフィルします。

  • 実装方法

    • UDF を作成するときに、UDF クラスにアノテーションを追加します。

      @com.aliyun.odps.udf.annotation.UdfProperty(isDeterministic=true)

      説明

      UDF アノテーション 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 句に配置した場合にのみ有効になります。

    • 正しい使用法のサンプルステートメント:

      -- ソーステーブルのクエリに使用する WHERE 句に UDF を配置します。
      SELECT key, value FROM srcp WHERE udf(ds) = 'xx';

    • 誤った使用法のサンプルステートメント:

      -- JOIN 句の ON 条件の後に UDF を配置します。UDF ベースのパーティションプルーニングは有効になりません。
      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 はオプションです。ほとんどの場合、GROUP BY は 集計関数 と共に使用され、指定された共通列、パーティションキー列、または正規表現 に基づいて列をグループ化します。GROUP BY を使用する場合、次のルールに従う必要があります。

• GROUP BY は SELECT よりも優先されます。したがって、GROUP BY の列は、SELECT の入力テーブルの列名、または SELECT の入力テーブルの列によって形成される式で指定できます。GROUP BY を使用する場合、次の点に注意してください。

  • GROUP BY の列が正規表現 で指定されている場合、完全な式を使用する必要があります。

  • SELECT 文で集計関数を使用しない列は、GROUP BY で指定する必要があります。

  例:

  • 次のステートメントは、列名 region によってテーブルデータをグループ化します。この場合、データは 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 列の値に基づいてテーブルデータをグループ化し、各グループの個別の値と合計売上高を返します。サンプルステートメント:

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

• SELECT 文の前に SET hive.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_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_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_condition はオプションです。ORDER BY は、指定された共通列またはパーティションキー列に基づいてすべてのデータレコードをソートするために使用されます。ORDER BY は、指定された定数に基づいてすべてのデータレコードをソートするためにも使用できます。ORDER BY を使用する場合、次のルールに従う必要があります。

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

• デフォルトでは、ORDER BY の後に LIMIT <number> が続き、出力に表示されるデータ行の数が制限されます。ORDER BY の後に LIMIT <number> が続かない場合、エラーが返されます。この LIMIT を回避することもできます。詳細については、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      |
  +------------+

• SELECT 文の前に SET hive.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       |
  +-------------+-------------+

  クエリされたデータには、3 行のデータのみが含まれています。この場合、3 行目のみが返されます。

• 範囲クラスタリングは、グローバルソートの高速化にも使用できます。ORDER BY が使用される一般的なシナリオでは、すべてのソート済みデータが同じインスタンス に配信され、グローバルソートが保証されます。ただし、これらのシナリオでは、同時処理を十分に活用できません。範囲クラスタリングのパーティション化ステップを使用して、同時グローバルソートを実装できます。グローバルソートを実行する場合、データをサンプリングし、データを範囲に分割し、各範囲のデータを並列にソートしてから、グローバルソートの結果を取得する必要があります。詳細については、「グローバルソートの高速化」をご参照ください。

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_condition はオプションです。ほとんどの場合、SORT BY は DISTRIBUTE 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 は各リデューサーに配信されたデータをソートします。

  これにより、各リデューサーの出力データが順番にソートされ、ストレージ圧縮率が向上します。データの読み取り中にデータがフィルタリングされる場合、この方法ではディスクから読み取られるデータ量が削減され、後続のグローバルソートの効率が向上します。サンプルステートメント:

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

次のセクションでは、LIMIT <number> の制限と、これらの制限を回避する方法について説明します。

• ORDER BY は、LIMIT <number> と一緒に使用する必要があります。

  ORDER BY は、単一ノードのすべてのデータをソートします。 デフォルトでは、単一ノードが大量のデータを処理しないように、ORDER BY は LIMIT <number> と一緒に使用されます。 次の方法を使用して、この LIMIT を回避できます。

  • プロジェクトの LIMIT を回避するには、SETPROJECT odps.sql.validate.orderby.limit=false; コマンドを実行します。

  • セッションの制限を回避するには、コミットして、実行する SQL 文で SET odps.sql.validate.orderby.limit=false; コマンドを実行します。

    説明

    この LIMIT を回避した後、単一ノードにソートするデータが大量にある場合、より多くのリソースと時間が消費されます。

• 制限された行が表示されます。

  SELECT 文を LIMIT <number> 句なしで実行した場合、または 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 パラメーターの最大値は 10000 です。 InstanceTunnel の詳細については、「使用上の注意」をご参照ください。

  • プロジェクトデータ保護が有効になっている場合、表示できる行数は READ_TABLE_MAX_ROW パラメーターによって制限されます。 このパラメーターの最大値は 10000 です。

  説明

  SHOW SecurityConfiguration; コマンドを実行して、ProjectProtection の値を表示できます。 ProjectProtection が true に設定されている場合は、ビジネス要件に基づいてプロジェクトデータ保護を無効にするかどうかを決定できます。 SET ProjectProtection=false; コマンドを実行して、プロジェクトデータ保護を無効にすることができます。 デフォルトでは、ProjectProtection は false に設定されています。 プロジェクトデータ保護の詳細については、「プロジェクトデータ保護」をご参照ください。