MaxCompute：Sintaks SELECT

Pernyataan SELECT digunakan untuk mengkueri data yang memenuhi kondisi tertentu dari sebuah tabel. Tabel berikut menjelaskan operasi kueri yang dapat dilakukan dalam skenario berbeda.

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 menyisipkan data ke dalam tabel ini.

-- 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');

-- Sisipkan 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  | 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 WITH bersifat opsional dan berisi satu atau lebih ekspresi tabel umum (CTE). CTE digunakan sebagai tabel sementara dalam lingkungan runtime. Anda dapat merujuk tabel sementara 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 yang didefinisikan dalam klausa WITH yang sama.

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

  • Jika A merujuk A, 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. 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

Pernyataan sampel 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 wajib. SELECT_expr berada dalam format col1_name, col2_name, ekspresi kolom,.... Format ini menunjukkan kolom umum atau kolom kunci partisi yang ingin Anda kueri, atau ekspresi reguler yang Anda gunakan untuk mengkueri 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. Pernyataan sampel:

  SELECT shop_name FROM sale_detail;

  Hasil berikut dikembalikan:

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

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

  • Pernyataan berikut mengkueri data dari semua kolom tabel sale_detail. Pernyataan sampel:

    -- 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 tanda bintang (*) dengan WHERE untuk menentukan kondisi filter. Pernyataan sampel:

    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 mengkueri data dari semua kolom yang namanya dimulai dengan sh dari tabel sale_detail. Pernyataan sampel:

    SELECT `sh.*` FROM sale_detail;

    Hasil berikut dikembalikan:

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

  • Pernyataan berikut mengkueri data dari semua kolom yang namanya bukan shop_name dari tabel sale_detail. Pernyataan sampel:

    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 mengkueri data dari semua kolom kecuali kolom yang namanya shop_name dan customer_id dari tabel sale_detail. Pernyataan sampel:

    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 mengkueri data dari semua kolom kecuali kolom yang namanya dimulai dengan t dari tabel sale_detail. Pernyataan sampel:

    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 dikueri. 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 mengkueri data kolom region dari tabel sale_detail dan hanya mengembalikan satu nilai unik. Pernyataan sampel:

    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. Pernyataan sampel:

    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. Pernyataan sampel:

    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 cannot be used with SELECT DISTINCT" dikembalikan.

Pernyataan sampel:

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

Pernyataan sampel:

-- 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 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 wajib. TABLE_reference menentukan tabel yang ingin Anda kueri. Saat menggunakan table_reference, Anda harus mematuhi aturan berikut:

• Tentukan nama tabel tujuan. Pernyataan sampel:

  SELECT customer_id FROM sale_detail;

  Hasil berikut dikembalikan:

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

• Gunakan subquery bersarang. Pernyataan sampel:

  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 menentukan kondisi filter. Jika where_condition digunakan untuk tabel terpartisi, pemangkasan kolom dapat dilakukan. Saat menggunakan where_condition, Anda harus mematuhi 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. Pernyataan sampel:

  SELECT * 
  FROM sale_detail
  WHERE sale_date >= '2008' AND sale_date <= '2014';
  -- Pernyataan di atas 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 efektif. Fungsi pengguna yang ditentukan umum (UDF) atau pengaturan kondisi partisi dari 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 Anda 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 menjadi 0.30.X atau lebih baru.

    • Tambahkan SET odps.sql.udf.ppr.deterministic = true; sebelum pernyataan SQL yang ingin Anda eksekusi. Kemudian, semua UDF dalam pernyataan SQL dianggap sebagai UDF deterministik. Perintah SET sebelumnya mengisi ulang partisi dengan hasil pekerjaan. Maksimal 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 mengkueri tabel. Pemangkasan partisi berbasis UDF hanya berlaku saat Anda menempatkan UDF dalam klausa WHERE.

    • Pernyataan sampel penggunaan yang benar:

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

    • Pernyataan sampel 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 alias menggunakan fungsi, alias tersebut tidak dapat dirujuk dalam klausa WHERE. Contoh Pernyataan Sampel 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 tertentu, kolom kunci partisi, atau ekspresi reguler yang ditentukan. 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 dari SELECT atau ekspresi yang dibentuk oleh kolom tabel input dari SELECT. Saat menggunakan GROUP BY, perhatikan poin-poin berikut:

  • 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. Dalam hal ini, 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 dan 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 di atas 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 serta 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. Dalam banyak kasus, klausa HAVING digunakan dengan fungsi agregat untuk menyaring data. Pernyataan sampel:

-- 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 dikembalikan:

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

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

• Secara default, data diurutkan dalam urutan menaik. Jika Anda ingin mengurutkan data dalam urutan menurun, kata kunci DESC diperlukan.

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

  • Pernyataan berikut mengkueri data dari tabel sale_detail, mengurutkan catatan data secara menaik berdasarkan nilai kolom total_price, lalu menampilkan dua catatan pertama. Pernyataan sampel:

    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 mengkueri data dari tabel sale_detail, mengurutkan catatan data secara menurun berdasarkan nilai kolom total_price, lalu menampilkan dua catatan pertama. Pernyataan sampel:

    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 merupakan nilai terkecil saat menggunakan ORDER BY untuk mengurutkan data. Hal ini berlaku di MySQL, tetapi tidak berlaku 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 yang diperoleh adalah sebagai berikut:

  +------------+-------------+-------------+------------+------------+
  | 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 tidak ditentukan dalam pernyataan SELECT, nama kolom tersebut akan digunakan sebagai alias. Contoh pernyataan:

  Pernyataan berikut menambahkan alias kolom keluaran 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 akan 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 dengan klausa ORDER BY...LIMIT untuk menentukan jumlah baris yang dilewati. Formatnya adalah ORDER BY...LIMIT m OFFSET n, yang dapat disingkat menjadi ORDER BY...LIMIT n, m. LIMIT m menentukan bahwa m baris data dikembalikan. OFFSET n menentukan bahwa n baris dilewati sebelum data dikembalikan. Jika Anda tidak ingin melewati baris, Anda dapat menggunakan OFFSET 0 dalam pernyataan yang ingin Anda eksekusi. Anda juga dapat mengeksekusi pernyataan tanpa menentukan klausa OFFSET.

  Pernyataan berikut mengurutkan data tabel sale_detail secara ascending berdasarkan nilai kolom total_price dan menampilkan tiga baris data 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 data. Dalam hal ini, hanya baris ketiga yang dikembalikan.

• Pengelompokan rentang juga dapat digunakan untuk mempercepat pengurutan global. Dalam skenario umum di mana ORDER BY digunakan, semua data yang diurutkan didistribusikan ke instance yang sama untuk memastikan pengurutan global. Namun, pemrosesan bersamaan tidak dapat dimanfaatkan sepenuhnya dalam skenario ini. Anda dapat menggunakan langkah partisi dari pengelompokan rentang untuk mengimplementasikan pengurutan global secara bersamaan. Jika Anda ingin melakukan pengurutan global, Anda harus mensample data dan membagi data ke dalam rentang, mengurutkan data di setiap rentang secara paralel, dan kemudian mendapatkan hasil dari 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 dan alias kolom tersebut tidak ditentukan, 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. Jika Anda ingin mengurutkan data dalam urutan menurun, kata kunci DESC diperlukan.

• Jika SORT BY didahului oleh DISTRIBUTE BY, SORT BY 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 kueri 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 kueri 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, SORT BY mengurutkan data yang didistribusikan ke setiap reducer.

  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 dan 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 menjelaskan batasan dari LIMIT <number> dan cara mengatasi batasan tersebut.

• ORDER BY harus digunakan 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. Anda dapat mengatasi LIMIT ini dengan menggunakan metode berikut:

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

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

    Catatan

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

• Baris yang dibatasi ditampilkan.

  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 dapat 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 menjadi 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.