MaxCompute：Sintaks SELECT

Pernyataan SELECT digunakan untuk mengambil data yang memenuhi kondisi tertentu dari sebuah tabel. Tabel berikut menunjukkan operasi kueri yang dapat dilakukan dalam berbagai skenario.

Topik ini menyediakan data sumber sampel dan pernyataan sampel untuk menunjukkan cara menyiapkan data sumber. Pernyataan sampel berikut menunjukkan cara membuat tabel sale_detail dan memasukkan data ke dalam tabel tersebut.

-- Buat tabel terpartisi bernama 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);

-- Tambahkan partisi ke tabel sale_detail.
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china');

-- Masukkan data ke dalam tabel 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);

Kueri data dalam tabel sale_detail. Pernyataan sampel:

SELECT * FROM sale_detail;
-- Hasil berikut dikembalikan:
+------------+------------+------------+------------+------------+
| 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      |
+------------+------------+------------+------------+------------+

Klausa WITH bersifat opsional dan dapat berisi satu atau lebih ekspresi tabel umum (CTE). CTE digunakan sebagai tabel sementara dalam lingkungan runtime dan dapat dirujuk dalam kueri berikutnya. Saat menggunakan CTE, Anda harus mematuhi aturan berikut:

• Nama CTE harus unik dalam klausa WITH.

• CTE yang didefinisikan dalam klausa WITH hanya dapat dirujuk oleh CTE lain dalam klausa WITH yang sama.

  Sebagai contoh, A adalah CTE pertama dan B adalah CTE kedua dalam klausa WITH yang sama.

  • Jika A merujuk dirinya sendiri, referensi tersebut tidak valid. Penggunaan CTE yang salah:

    WITH 
    A AS (SELECT 1 FROM A) 
    SELECT * FROM A;

    Hasil berikut dikembalikan:

    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

  • Jika A merujuk B dan B merujuk A, referensi tersebut tidak valid karena referensi melingkar tidak didukung. Penggunaan CTE yang salah:

    WITH 
    A AS (SELECT * FROM B ), 
    B AS (SELECT * FROM A ) 
    SELECT * FROM B;

    Hasil berikut dikembalikan:

    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

Contoh pernyataan penggunaan yang benar:

WITH 
A AS (SELECT 1 AS C),
B AS (SELECT * FROM A) 
SELECT * FROM B;

Hasil berikut dikembalikan:

+---+
| c |
+---+
| 1 |
+---+

select_expr diperlukan. SELECT_expr berformat col1_name, col2_name, ekspresi kolom,..., yang menunjukkan kolom umum, kolom kunci partisi, atau ekspresi reguler untuk menanyakan data. Saat menggunakan select_expr, Anda harus mematuhi aturan berikut:

• Tentukan nama kolom dari mana Anda ingin membaca data.

  Pernyataan berikut membaca data kolom shop_name dari tabel sale_detail. Contoh pernyataan:

  SELECT shop_name FROM sale_detail;

  Hasil berikut dikembalikan:

  +------------+
  | shop_name  |
  +------------+
  | s1         |
  | s2         |
  | s3         |
  +------------+

• Gunakan asterisk (*) untuk mewakili semua kolom. Anda juga dapat menggunakan asterisk (*) dengan klausa WHERE untuk menentukan kondisi filter.

  • Pernyataan berikut menanyakan data dari semua kolom tabel sale_detail. Contoh pernyataan:

    -- Aktifkan pemindaian tabel penuh hanya untuk sesi saat ini.
    SET odps.sql.allow.fullscan=true;
    SELECT * FROM sale_detail;

    Hasil berikut dikembalikan:

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

  • Pernyataan berikut menggunakan asterisk (*) dengan WHERE untuk menentukan kondisi filter. Contoh pernyataan:

    SELECT * FROM sale_detail WHERE shop_name='s1';

    Hasil berikut dikembalikan:

    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | s1         | c1          | 100.1       | 2013       | china      |
    +------------+-------------+-------------+------------+------------+

• Gunakan ekspresi reguler.

  • Pernyataan berikut menanyakan data dari semua kolom yang namanya dimulai dengan sh dari tabel sale_detail. Contoh pernyataan:

    SELECT `sh.*` FROM sale_detail;

    Hasil berikut dikembalikan:

    +------------+
    | shop_name  |
    +------------+
    | s1         |
    | s2         |
    | s3         |
    +------------+

  • Pernyataan berikut menanyakan data dari semua kolom yang namanya bukan shop_name dari tabel sale_detail. Contoh pernyataan:

    SELECT `(shop_name)?+.+` FROM sale_detail;

    Hasil berikut dikembalikan:

    +-------------+-------------+------------+------------+
    | customer_id | total_price | sale_date  | region     |
    +-------------+-------------+------------+------------+
    | c1          | 100.1       | 2013       | china      |
    | c2          | 100.2       | 2013       | china      |
    | c3          | 100.3       | 2013       | china      |
    +-------------+-------------+------------+------------+

  • Pernyataan berikut menanyakan data dari semua kolom kecuali kolom yang namanya shop_name dan customer_id dari tabel sale_detail. Contoh pernyataan:

    SELECT `(shop_name|customer_id)?+.+` FROM sale_detail;

    Hasil berikut dikembalikan:

    +-------------+------------+------------+
    | total_price | sale_date  | region     |
    +-------------+------------+------------+
    | 100.1       | 2013       | china      |
    | 100.2       | 2013       | china      |
    | 100.3       | 2013       | china      |
    +-------------+------------+------------+

  • Pernyataan berikut menanyakan data dari semua kolom kecuali kolom yang namanya dimulai dengan t dari tabel sale_detail. Contoh pernyataan:

    SELECT `(t.*)?+.+` FROM sale_detail;

    Hasil berikut dikembalikan:

    +------------+-------------+------------+------------+
    | shop_name  | customer_id | sale_date  | region     |
    +------------+-------------+------------+------------+
    | s1         | c1          | 2013       | china      |
    | s2         | c2          | 2013       | china      |
    | s3         | c3          | 2013       | china      |
    +------------+-------------+------------+------------+

    Catatan

    Jika nama col2 adalah awalan dari nama col1 dan Anda ingin mengecualikan beberapa kolom, pastikan nama col1 ditempatkan sebelum nama col2. Nama kolom yang lebih panjang ditempatkan sebelum nama kolom yang lebih pendek. Misalnya, dua partisi dari tabel terpartisi tidak perlu ditanyakan. Satu partisi bernama ds dan yang lainnya bernama dshh. Nama partisi ds adalah awalan untuk nama partisi dshh. Oleh karena itu, ekspresi SELECT `(dshhds)?+.+` FROM t; benar, tetapi ekspresi SELECT `(dsdshh)?+.+` FROM t; salah.

• Gunakan DISTINCT sebelum nama kolom untuk menyaring nilai duplikat dari kolom tersebut dan hanya mengembalikan nilai unik. Jika Anda menggunakan ALL sebelum nama kolom, semua nilai kolom, termasuk nilai duplikat, akan dikembalikan. Jika DISTINCT tidak digunakan, ALL digunakan.

  • Pernyataan berikut menanyakan data dari kolom region tabel sale_detail dan hanya mengembalikan satu nilai unik. Contoh pernyataan:

    SELECT DISTINCT region FROM sale_detail;

    Hasil berikut dikembalikan:

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

  • Pernyataan berikut menentukan beberapa kolom setelah opsi DISTINCT. Opsi DISTINCT berlaku pada semua kolom yang ditentukan alih-alih satu kolom. Contoh pernyataan:

    SELECT DISTINCT region, sale_date FROM sale_detail;

    Hasil berikut dikembalikan:

    +------------+------------+
    | region     | sale_date  |
    +------------+------------+
    | china      | 2013       |
    +------------+------------+

  • Dalam pernyataan berikut, DISTINCT digunakan bersama dengan fungsi jendela. DISTINCT digunakan untuk menghapus duplikat hasil komputasi fungsi jendela. Contoh pernyataan:

    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;

    Hasil berikut dikembalikan:

    +-----------+------------+
    | sale_date | rn         |
    +-----------+------------+
    | 2013      | 1          |
    +-----------+------------+

    Dalam pernyataan berikut, DISTINCT tidak dapat digunakan bersama dengan GROUP BY. Misalnya, jika Anda mengeksekusi pernyataan berikut, kesalahan akan dikembalikan.

    SELECT DISTINCT shop_name FROM sale_detail GROUP BY shop_name;
    -- Pesan kesalahan "GROUP BY tidak dapat digunakan dengan SELECT DISTINCT" dikembalikan.

Contoh pernyataan:

-- Baca data dari semua kolom, kecuali kolom region, di tabel sale_detail.
SELECT * EXCEPT(region) FROM sale_detail;

Hasil berikut dikembalikan:

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

Contoh pernyataan:

-- Baca data dari tabel sale_detail dan modifikasi data di kolom total_price dan region.
SELECT * REPLACE(total_price+100 AS total_price, 'shanghai' AS region) FROM sale_detail;

Hasil berikut akan dikembalikan:

+-----------+-------------+-------------+-----------+--------+
| 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 diperlukan. TABLE_reference menentukan tabel yang ingin Anda tanyakan. Saat menggunakan table_reference, patuhi aturan berikut:

• Tentukan nama tabel tujuan. Contoh pernyataan:

  SELECT customer_id FROM sale_detail;

  Hasil berikut dikembalikan:

  +-------------+
  | customer_id |
  +-------------+
  | c1          |
  | c2          |
  | c3          |
  +-------------+

• Gunakan subquery bersarang. Contoh pernyataan:

  SELECT * FROM (SELECT region,sale_date FROM sale_detail) t WHERE region = 'china';

  Hasil berikut dikembalikan:

  +------------+------------+
  | region     | sale_date  |
  +------------+------------+
  | china      | 2013       |
  | china      | 2013       |
  | china      | 2013       |
  +------------+------------+

where_condition bersifat opsional. Klausa WHERE digunakan untuk menentukan kondisi filter. Jika where_condition diterapkan pada tabel terpartisi, pemangkasan kolom dapat dilakukan. Saat menggunakan where_condition, Anda harus mengikuti aturan berikut:

• Gunakan where_condition dengan operator relasional untuk mendapatkan data yang memenuhi kondisi tertentu. Operator relasional meliputi:

  • >, <, =, >=, <=, dan <>.

  • LIKE dan RLIKE.

  • IN, NOT IN.

  • BETWEEN…AND.

  Untuk informasi lebih lanjut, lihat Operator relasional.

  Pernyataan berikut menentukan partisi yang ingin Anda pindai dalam where_condition. Ini mencegah pemindaian tabel penuh. Contoh pernyataan:

  SELECT * 
  FROM sale_detail
  WHERE sale_date >= '2008' AND sale_date <= '2014';
  -- Pernyataan sebelumnya setara dengan pernyataan berikut:
  SELECT * 
  FROM sale_detail 
  WHERE sale_date BETWEEN '2008' AND '2014';

  Hasil berikut dikembalikan:

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

  Catatan

  Anda dapat mengeksekusi pernyataan EXPLAIN untuk memeriksa apakah pemangkasan partisi berlaku. Fungsi yang ditentukan pengguna (UDF) umum atau pengaturan kondisi partisi JOIN dapat menyebabkan pemangkasan partisi gagal. Untuk informasi lebih lanjut, lihat Periksa apakah pemangkasan partisi efektif.

• Gunakan pemangkasan partisi berbasis UDF. Jika Anda menggunakan UDF, MaxCompute mengeksekusi UDF sebagai pekerjaan kecil dan mengisi ulang partisi dengan hasil pekerjaan tersebut.

  • Metode implementasi:

    • Tambahkan anotasi ke kelas UDF saat menulis UDF.

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

      Catatan

      Anotasi UDF com.aliyun.odps.udf.annotation.UdfProperty didefinisikan dalam file odps-sdk-udf.jar. Untuk menggunakan anotasi ini, Anda harus memperbarui versi referensi odps-sdk-udf ke 0.30.X atau lebih baru.

    • Tambahkan SET odps.sql.udf.ppr.deterministic = true; sebelum pernyataan SQL yang ingin dieksekusi. Kemudian, semua UDF dalam pernyataan SQL dianggap sebagai UDF deterministic. Perintah SET sebelumnya mengisi ulang partisi dengan hasil pekerjaan. Maksimum 1.000 partisi dapat diisi ulang dengan hasil pekerjaan. Jika Anda menambahkan anotasi ke kelas UDF, kesalahan yang menunjukkan lebih dari 1.000 partisi diisi ulang mungkin dikembalikan. Untuk mengabaikan kesalahan ini, Anda dapat menjalankan perintah SET odps.sql.udf.ppr.to.subquery = false;. Setelah Anda menjalankan perintah ini, pemangkasan partisi berbasis UDF tidak lagi berlaku.

  • Perhatian:

    Saat menggunakan UDF untuk melakukan pemangkasan partisi, UDF harus ditempatkan dalam klausa WHERE yang digunakan untuk menanyakan tabel. Pemangkasan partisi berbasis UDF hanya berlaku jika UDF ditempatkan dalam klausa WHERE.

    • Contoh pernyataan penggunaan yang benar:

      -- Tempatkan UDF dalam klausa WHERE yang digunakan untuk menanyakan tabel sumber.
      SELECT key, value FROM srcp WHERE udf(ds) = 'xx';

    • Contoh pernyataan penggunaan yang salah:

      -- Tempatkan UDF setelah kondisi ON dalam klausa JOIN. Pemangkasan partisi berbasis UDF tidak berlaku.
      SELECT A.c1, A.c2 FROM srcp1 A  JOIN srcp2  B ON A.c1 = B.c1 AND udf(A.ds) ='xx';

• Dalam Ekspresi kolom (SELECT_expr), jika kolom yang diganti nama alias kolom menggunakan fungsi, alias kolom tidak dapat dirujuk dalam klausa WHERE. Contoh pernyataan yang salah:

  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 bersifat opsional. Dalam banyak kasus, GROUP BY digunakan dengan fungsi agregat untuk mengelompokkan kolom berdasarkan kolom umum yang ditentukan, kolom kunci partisi, atau ekspresi reguler. Saat menggunakan GROUP BY, Anda harus mematuhi aturan berikut:

• GROUP BY memiliki prioritas lebih tinggi daripada SELECT. Oleh karena itu, kolom dalam GROUP BY dapat ditentukan oleh nama kolom tabel input SELECT atau ekspresi yang dibentuk oleh kolom tabel input SELECT. Perhatikan poin-poin berikut saat menggunakan GROUP BY:

  • Jika kolom dalam GROUP BY ditentukan oleh ekspresi reguler, ekspresi lengkap harus digunakan.

  • Kolom yang tidak menggunakan fungsi agregat dalam pernyataan SELECT harus ditentukan dalam GROUP BY.

  Contoh:

  • Pernyataan berikut mengelompokkan data tabel berdasarkan nama kolom region. Data dikelompokkan berdasarkan nilai kolom region. Pernyataan sampel:

    SELECT region FROM sale_detail GROUP BY region;

    Hasil berikut dikembalikan:

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

  • Pernyataan berikut mengelompokkan data tabel berdasarkan nilai kolom region dan mengembalikan total penjualan setiap grup. Pernyataan sampel:

    SELECT SUM(total_price) FROM sale_detail GROUP BY region;

    Hasil berikut dikembalikan:

    +------------+
    | _c0        |
    +------------+
    | 300.6      |
    +------------+

  • Pernyataan berikut mengelompokkan data tabel berdasarkan nilai kolom region dan mengembalikan nilai unik serta total penjualan setiap grup. Pernyataan sampel:

    SELECT region, SUM (total_price) FROM sale_detail GROUP BY region;

    Hasil berikut dikembalikan:

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

  • Pernyataan berikut mengelompokkan data tabel berdasarkan alias kolom keluaran dalam pernyataan SELECT. Pernyataan sampel:

    SELECT region AS r FROM sale_detail GROUP BY r;
    -- Pernyataan sebelumnya setara dengan pernyataan berikut:
    SELECT region AS r FROM sale_detail GROUP BY region;

    Hasil berikut dikembalikan:

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

  • Pernyataan berikut mengelompokkan data tabel berdasarkan ekspresi kolom. Pernyataan sampel:

    SELECT 2 + total_price AS r FROM sale_detail GROUP BY 2 + total_price;

    Hasil berikut dikembalikan:

    +------------+
    | r          |
    +------------+
    | 102.1      |
    | 102.2      |
    | 102.3      |
    +------------+

  • Jika beberapa kolom dalam pernyataan SELECT tidak menggunakan fungsi agregat, kolom-kolom ini harus ditentukan dalam GROUP BY. Jika tidak, kesalahan akan dikembalikan. Pernyataan sampel penggunaan yang salah:

    SELECT region, total_price FROM sale_detail GROUP BY region;

    Pernyataan sampel penggunaan yang benar:

    SELECT region, total_price FROM sale_detail GROUP BY region, total_price;

    Hasil berikut dikembalikan:

    +------------+-------------+
    | region     | total_price |
    +------------+-------------+
    | china      | 100.1       |
    | china      | 100.2       |
    | china      | 100.3       |
    +------------+-------------+

• Jika Anda menambahkan perintah SET hive.groupby.position.alias=true; sebelum pernyataan SELECT, konstanta integer dalam klausa GROUP BY dianggap sebagai nomor kolom dalam pernyataan SELECT. Pernyataan sampel:

  -- Jalankan perintah ini dengan pernyataan SELECT berikut.
  SET odps.sql.groupby.position.alias=true;
  -- 1 menunjukkan kolom region, yaitu kolom pertama yang dibaca oleh pernyataan SELECT berikut. Pernyataan ini mengelompokkan data tabel berdasarkan nilai kolom region dan mengembalikan nilai unik kolom region dan total penjualan setiap grup.
  SELECT region, SUM(total_price) FROM sale_detail GROUP BY 1;

  Hasil berikut dikembalikan:

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

having_condition bersifat opsional. Klausa HAVING umumnya digunakan bersama dengan fungsi agregat untuk menyaring data. Contoh pernyataan:

-- Masukkan data ke dalam tabel sale_detail untuk menampilkan efek rendering data.
INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai') VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
-- Gunakan having_condition dengan fungsi agregat untuk menyaring data.
SELECT region,SUM(total_price) FROM sale_detail 
GROUP BY region 
HAVING SUM(total_price)<305;

Hasil berikut akan dikembalikan:

+------------+------------+
| region     | _c1        |
+------------+------------+
| china      | 300.6      |
| shanghai   | 200.9      |
+------------+------------+

order_condition bersifat opsional. ORDER BY digunakan untuk mengurutkan semua catatan data berdasarkan kolom tertentu atau kunci partisi yang ditentukan. ORDER BY juga dapat digunakan untuk mengurutkan data berdasarkan konstanta tertentu. Saat menggunakan ORDER BY, Anda harus mematuhi aturan berikut:

• Secara default, data diurutkan dalam urutan menaik. Untuk mengurutkan data secara menurun, gunakan kata kunci DESC.

• Secara default, ORDER BY harus diikuti oleh LIMIT <number> untuk membatasi jumlah baris data yang ditampilkan di output. Jika ORDER BY tidak diikuti oleh LIMIT <number>, sistem akan mengembalikan kesalahan. Anda dapat mengatasi batasan LIMIT ini. Untuk informasi lebih lanjut, lihat LIMIT.

  • Pernyataan berikut mengambil data dari tabel sale_detail, mengurutkan catatan secara menaik berdasarkan nilai kolom total_price, dan menampilkan dua catatan pertama. Contoh pernyataan:

    SELECT * FROM sale_detail ORDER BY total_price LIMIT 2;

    Hasil berikut dikembalikan:

    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | s1         | c1          | 100.1       | 2013       | china      |
    | s2         | c2          | 100.2       | 2013       | china      |
    +------------+-------------+-------------+------------+------------+

  • Pernyataan berikut mengambil data dari tabel sale_detail, mengurutkan catatan secara menurun berdasarkan nilai kolom total_price, dan menampilkan dua catatan pertama. Contoh pernyataan:

    SELECT * FROM sale_detail ORDER BY total_price DESC LIMIT 2;

    Hasil berikut dikembalikan:

    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | s3         | c3          | 100.3       | 2013       | china      |
    | s2         | c2          | 100.2       | 2013       | china      |
    +------------+-------------+-------------+------------+------------+

• NULL dianggap sebagai nilai terkecil saat menggunakan ORDER BY untuk mengurutkan data. Hal ini berlaku di MySQL, tetapi tidak di Oracle.

  Pernyataan berikut mengambil data dari tabel sale_detail, mengurutkan catatan secara menaik berdasarkan nilai kolom total_price, dan menampilkan dua catatan pertama. Contoh pernyataan:

  SELECT * FROM sale_detail ORDER BY total_price LIMIT 2;

  Hasil berikut dikembalikan:

  +------------+-------------+-------------+------------+------------+
  | shop_name  | customer_id | total_price | sale_date  | region     |
  +------------+-------------+-------------+------------+------------+
  | s1         | c1          | 100.1       | 2013       | china      |
  | s2         | c2          | 100.2       | 2013       | china      |
  +------------+-------------+-------------+------------+------------+

• ORDER BY diikuti oleh alias kolom keluaran dari pernyataan SELECT. Jika alias kolom keluaran untuk pernyataan SELECT tidak ditentukan, nama kolom tersebut akan digunakan sebagai alias. Contoh pernyataan:

  Pernyataan berikut menambahkan alias kolom setelah ORDER BY. Contoh pernyataan:

  SELECT total_price AS t FROM sale_detail ORDER BY total_price LIMIT 3;
  -- Pernyataan sebelumnya setara dengan pernyataan berikut: 
  SELECT total_price AS t FROM sale_detail ORDER BY t LIMIT 3;

  Hasil berikut dikembalikan:

  +------------+
  | t          |
  +------------+
  | 100.1      |
  | 100.2      |
  | 100.3      |
  +------------+

• Jika Anda menambahkan perintah SET hive.orderby.position.alias=true; sebelum pernyataan SELECT, konstanta integer dalam klausa ORDER BY akan dianggap sebagai nomor kolom dalam pernyataan SELECT. Contoh pernyataan:

  -- Jalankan perintah ini dengan pernyataan SELECT berikut.
  SET odps.sql.orderby.position.alias=true;
  SELECT * FROM sale_detail ORDER BY 3 LIMIT 3;

  Hasil berikut dikembalikan:

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

• Klausa OFFSET dapat digunakan bersama dengan ORDER BY...LIMIT untuk menentukan jumlah baris yang dilewati. Formatnya adalah ORDER BY...LIMIT m OFFSET n, atau disingkat menjadi ORDER BY...LIMIT n, m. LIMIT m menentukan bahwa m baris data dikembalikan, sedangkan OFFSET n menentukan bahwa n baris dilewati sebelum data dikembalikan. Jika tidak ingin melewati baris, gunakan OFFSET 0 atau abaikan klausa OFFSET.

  Pernyataan berikut mengurutkan data tabel sale_detail secara ascending berdasarkan nilai kolom total_price dan menampilkan tiga baris mulai dari baris ketiga. Contoh pernyataan:

  SELECT customer_id,total_price FROM sale_detail ORDER BY total_price LIMIT 3 OFFSET 2;
  -- Pernyataan di atas setara dengan pernyataan berikut: 
  SELECT customer_id,total_price FROM sale_detail ORDER BY total_price LIMIT 2, 3;

  Hasil berikut dikembalikan:

  +-------------+-------------+
  | customer_id | total_price |
  +-------------+-------------+
  | c3          | 100.3       |
  +-------------+-------------+

  Data yang di-query hanya berisi tiga baris. Dalam hal ini, hanya baris ketiga yang dikembalikan.

• Pengelompokan rentang dapat digunakan untuk mempercepat pengurutan global. Dalam skenario umum penggunaan ORDER BY, semua data yang diurutkan didistribusikan ke instance yang sama untuk memastikan pengurutan global. Namun, pemrosesan konkuren tidak sepenuhnya dimanfaatkan dalam skenario ini. Anda dapat menggunakan langkah partisi dari pengelompokan rentang untuk mengimplementasikan pengurutan global secara konkuren. Untuk melakukan pengurutan global, sampling data diperlukan untuk membagi data ke dalam rentang, mengurutkan data di setiap rentang secara paralel, dan mendapatkan hasil pengurutan global. Untuk informasi lebih lanjut, lihat Pemercepatan pengurutan global.

Alias kolom output dari pernyataan SELECT harus ditentukan. Jika Anda mengeksekusi pernyataan SELECT untuk menanyakan data kolom tanpa menentukan alias kolom, nama kolom akan digunakan sebagai alias. Contoh pernyataan:

-- Pernyataan berikut menanyakan nilai kolom region dari tabel sale_detail dan melakukan partisi hash pada data berdasarkan nilai kolom region. 
SELECT region FROM sale_detail DISTRIBUTE BY region;
-- Pernyataan di atas setara dengan pernyataan berikut: 
SELECT region AS r FROM sale_detail DISTRIBUTE BY region;
SELECT region AS r FROM sale_detail DISTRIBUTE BY r;

Kondisi_sort bersifat opsional. Dalam banyak kasus, SORT BY digunakan bersama dengan DISTRIBUTE BY. Saat menggunakan SORT BY, Anda harus mematuhi aturan berikut:

• Secara default, data diurutkan dalam urutan menaik. Untuk mengurutkan data dalam urutan menurun, gunakan kata kunci DESC.

• Jika SORT BY didahului oleh DISTRIBUTE BY, maka SORT BY akan mengurutkan hasil dari DISTRIBUTE BY berdasarkan nilai kolom tertentu.

  • Pernyataan berikut meminta nilai kolom region dan total_price dari tabel sale_detail, melakukan partisi hash pada hasil query berdasarkan nilai kolom region, lalu mengurutkan hasil partisi dalam urutan menaik berdasarkan nilai kolom total_price. Contoh pernyataan:

    -- Masukkan data ke dalam tabel sale_detail untuk menampilkan efek rendering data. 
    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;

    Hasil berikut dikembalikan:

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

  • Pernyataan berikut meminta nilai kolom region dan total_price dari tabel sale_detail, melakukan partisi hash pada hasil query berdasarkan nilai kolom region, lalu mengurutkan hasil partisi dalam urutan menurun berdasarkan nilai kolom total_price. Contoh pernyataan:

    SELECT region,total_price FROM sale_detail DISTRIBUTE BY region SORT BY total_price DESC;

    Hasil berikut dikembalikan:

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

• Jika SORT BY tidak didahului oleh DISTRIBUTE BY, maka SORT BY akan mengurutkan data yang didistribusikan ke setiap reducer.

  Hal ini memastikan bahwa data keluaran dari setiap reducer diurutkan secara teratur dan meningkatkan rasio kompresi penyimpanan. Jika data difilter selama pembacaan data, metode ini mengurangi jumlah data yang dibaca dari disk serta meningkatkan efisiensi pengurutan global berikutnya. Contoh pernyataan:

  SELECT region,total_price FROM sale_detail SORT BY total_price DESC;

  Hasil berikut dikembalikan:

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

Berikut ini dijelaskan batasan dari LIMIT <number> serta cara mengatasinya:

• ORDER BY harus digunakan bersama dengan LIMIT <number>.

  ORDER BY mengurutkan semua data dari satu node. Secara default, ORDER BY digunakan dengan LIMIT <number> untuk mencegah satu node memproses sejumlah besar data. Batasan LIMIT ini dapat diatasi dengan metode berikut:

  • Untuk mengatasi batas LIMIT pada sebuah proyek, jalankan perintah SETPROJECT odps.sql.validate.orderby.limit=false;.

  • Untuk mengatasi batas pada sesi tertentu, commit dan jalankan perintah SET odps.sql.validate.orderby.limit=false; bersama dengan Pernyataan SQL yang ingin dieksekusi.

    Catatan

    Setelah mengatasi batas LIMIT, jika satu node memiliki sejumlah besar data untuk diurutkan, lebih banyak sumber daya dan waktu akan dikonsumsi.

• Jumlah baris yang ditampilkan dibatasi.

  Jika Anda mengeksekusi pernyataan SELECT tanpa klausa LIMIT <number> atau angka yang ditentukan dalam klausa LIMIT <number> melebihi jumlah maksimum (n) baris yang dapat ditampilkan, maka maksimal n baris akan ditampilkan.

  Jumlah maksimum baris yang dapat ditampilkan bervariasi berdasarkan proyek. Anda dapat menggunakan salah satu metode berikut untuk mengontrol jumlah maksimum:

  • Jika perlindungan data proyek dinonaktifkan, modifikasi file odpscmd config.ini.

    Atur use_instance_tunnel ke true dalam file odpscmd config.ini. Jika parameter instance_tunnel_max_record tidak dikonfigurasi, jumlah baris yang dapat ditampilkan tidak dibatasi. Sebaliknya, jumlah baris yang dapat ditampilkan dibatasi oleh parameter instance_tunnel_max_record. Nilai maksimum dari parameter instance_tunnel_max_record adalah 10000. Untuk informasi lebih lanjut tentang InstanceTunnel, lihat Catatan penggunaan.

  • Jika perlindungan data proyek diaktifkan, jumlah baris yang dapat ditampilkan dibatasi oleh parameter READ_TABLE_MAX_ROW. Nilai maksimum dari parameter ini adalah 10000.

  Catatan

  Anda dapat menjalankan perintah SHOW SecurityConfiguration; untuk melihat nilai dari ProjectProtection. Jika ProjectProtection disetel ke true, Anda dapat memutuskan apakah akan menonaktifkan perlindungan data proyek berdasarkan kebutuhan bisnis Anda. Anda dapat menjalankan perintah SET ProjectProtection=false; untuk menonaktifkan perlindungan data proyek. Secara default, ProjectProtection disetel ke false. Untuk informasi lebih lanjut tentang perlindungan data proyek, lihat Perlindungan data proyek.