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 berbagai skenario.

Topik ini menyediakan data sumber dan contoh terkait untuk membantu Anda memahami cara menggunakan perintah. Perintah contoh berikut membuat tabel `sale_detail` dan menambahkan data ke dalamnya.

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

Contoh berikut menunjukkan perintah untuk mengkueri data di tabel partisi `sale_detail`:

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

Opsional. Klausa `WITH` berisi satu atau beberapa ekspresi tabel umum (CTE). CTE bertindak sebagai tabel sementara dalam lingkungan runtime saat ini dan dapat direferensikan dalam kueri selanjutnya. Aturan berikut berlaku untuk CTE:

• CTE dalam klausa `WITH` yang sama harus memiliki nama unik.

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

  Sebagai contoh, asumsikan A adalah CTE pertama dan B adalah CTE kedua dalam klausa `WITH` yang sama:

  • Kondisi 'A mereferensikan A' tidak valid. Berikut adalah contoh perintah 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

  • Referensi melingkar tidak didukung. Misalnya, A tidak dapat mereferensikan B jika B mereferensikan A. Contoh perintah 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 berikut menunjukkan perintah 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 diisi. SELECT_expr menggunakan format col1_name, col2_name, column expression,.... Ekspresi ini menentukan kolom, kolom kunci partisi, atau ekspresi reguler yang ingin Anda kueri. Aturan berikut berlaku untuk `select_expr`:

• Anda dapat menentukan kolom yang akan dibaca berdasarkan namanya.

  Pernyataan berikut membaca kolom shop_name dari tabel sale_detail.

  SELECT shop_name FROM sale_detail;

  Hasil berikut dikembalikan.

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

• Anda dapat menggunakan tanda bintang (*) untuk merepresentasikan semua kolom. Anda juga dapat menggunakannya bersama klausa WHERE untuk menentukan kondisi filter.

  • Perintah berikut membaca semua kolom dari tabel sale_detail.

    -- Aktifkan full table scan 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      |
    +------------+-------------+-------------+------------+------------+

  • Anda dapat menentukan kondisi filter dalam klausa WHERE. Pernyataan berikut memberikan contohnya.

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

• Anda dapat menggunakan ekspresi reguler.

  • Perintah berikut memilih semua kolom dari tabel sale_detail yang namanya diawali dengan sh.

    SELECT `sh.*` FROM sale_detail;

    Hasil berikut dikembalikan.

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

  • Pernyataan berikut memilih semua kolom yang namanya bukan shop_name dari tabel sale_detail.

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

  • Perintah berikut memilih semua kolom dari tabel sale_detail kecuali shop_name dan customer_id. Pernyataan berikut memberikan contohnya.

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

  • Perintah berikut memilih semua kolom dari tabel sale_detail kecuali kolom yang namanya diawali dengan t. Perintah berikut memberikan contohnya.

    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 Anda ingin mengecualikan beberapa kolom dan salah satu nama kolom merupakan awalan dari nama kolom lain, tempatkan nama kolom yang lebih panjang sebelum yang lebih pendek dalam ekspresi reguler. Sebagai contoh, sebuah tabel memiliki dua partisi yang tidak perlu Anda kueri: satu bernama ds dan yang lain dshh. Karena `ds` merupakan awalan dari `dshh`, ekspresi yang benar adalah SELECT `(dshh|ds)?+.+` FROM t;, sedangkan ekspresi yang salah adalah SELECT `(ds|dshh)?+.+` FROM t;.

• Anda dapat menggunakan kata kunci DISTINCT sebelum nama kolom untuk menghapus nilai duplikat dan hanya mengembalikan nilai unik. Kata kunci ALL mengembalikan semua nilai dalam kolom, termasuk duplikat. Jika tidak ditentukan, ALL digunakan secara default.

  • Contoh berikut menunjukkan perintah yang mengkueri kolom `region` dalam tabel `sale_detail`. Jika terdapat nilai duplikat, hanya satu yang ditampilkan.

    SELECT DISTINCT region FROM sale_detail;

    Hasil berikut dikembalikan.

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

  • Ketika menghapus duplikat dari beberapa kolom, kata kunci DISTINCT berlaku untuk seluruh set kolom dalam klausa SELECT, bukan untuk masing-masing kolom. Pernyataan berikut adalah contohnya.

    SELECT DISTINCT region, sale_date FROM sale_detail;

    Hasil berikut dikembalikan.

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

  • Anda dapat menggunakan DISTINCT bersama fungsi jendela. Dalam kasus ini, DISTINCT menghapus hasil duplikat yang dikembalikan oleh fungsi jendela. Pernyataan berikut memberikan contohnya:

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

    Anda tidak dapat menggunakan DISTINCT dan GROUP BY dalam kueri yang sama. Sebagai contoh, jika Anda mengeksekusi perintah berikut, pesan error akan dikembalikan.

    SELECT DISTINCT shop_name FROM sale_detail GROUP BY shop_name;
    -- Pesan error "GROUP BY cannot be used with SELECT DISTINCT" dikembalikan.

Contoh berikut menunjukkan perintah contoh.

-- Baca data dari semua kolom, kecuali kolom region, dalam 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 berikut menunjukkan perintah yang membaca data dari tabel `sale_detail` dan memodifikasi data dalam 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 diisi. TABLE_reference menentukan tabel yang ingin Anda kueri. Aturan berikut berlaku untuk `table_reference`:

• Anda dapat langsung menentukan nama tabel target. Contoh berikut menunjukkan perintah contoh.

  SELECT customer_id FROM sale_detail;

  Hasil berikut dikembalikan.

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

• Anda dapat menggunakan subkueri bersarang. Contoh berikut menunjukkan perintah contoh.

  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 klausa `WHERE` digunakan untuk tabel partisi, pemangkasan partisi (partition pruning) dapat dilakukan. Aturan berikut berlaku untuk `where_condition`:

• Anda dapat menggunakan operator relasional untuk memfilter data yang memenuhi kondisi tertentu. Operator relasional meliputi:

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

  • LIKE dan RLIKE

  • IN dan NOT IN

  • BETWEEN…AND

  Untuk informasi selengkapnya, lihat Operator relasional.

  Dalam klausa WHERE, Anda dapat menentukan rentang partisi untuk hanya memindai bagian tertentu dari tabel dan menghindari full table scan. Perintah berikut memberikan contohnya.

  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 menjalankan pernyataan EXPLAIN untuk memeriksa apakah pemangkasan partisi berlaku. Fungsi user-defined (UDF) atau pengaturan kondisi partisi dalam operasi JOIN dapat menyebabkan pemangkasan partisi gagal. Untuk informasi selengkapnya, lihat Memeriksa apakah pemangkasan partisi efektif.

• Anda dapat menggunakan UDF untuk mengimplementasikan pemangkasan partisi. UDF tersebut pertama-tama dijalankan sebagai job kecil. Kemudian, hasil job tersebut digunakan untuk menggantikan UDF dalam pernyataan aslinya.

  • Metode implementasi

    • Saat menulis UDF, Anda dapat menambahkan anotasi ke kelas UDF.

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

      Catatan

      com.aliyun.odps.udf.annotation.UdfProperty didefinisikan dalam file odps-sdk-udf.jar. Anda harus meningkatkan versi odps-sdk-udf yang direferensikan ke versi 0.30.x atau lebih baru.

    • Anda dapat menambahkan SET odps.sql.udf.ppr.deterministic = true; sebelum pernyataan SQL yang ingin Anda eksekusi. Dengan demikian, semua UDF dalam pernyataan SQL tersebut dianggap sebagai UDF deterministic. Perintah `SET` di atas melakukan backfill partisi dengan hasil job. Maksimal 1.000 partisi dapat di-backfill dengan hasil job. Jika Anda menambahkan anotasi ke kelas UDF, error yang menunjukkan lebih dari 1.000 partisi di-backfill mungkin dikembalikan. Untuk mengabaikan error ini, Anda dapat menjalankan perintah SET odps.sql.udf.ppr.to.subquery = false;. Setelah menjalankan perintah ini, pemangkasan partisi berbasis UDF tidak lagi berlaku.

  • Catatan

    Agar pemangkasan partisi berbasis UDF berlaku, UDF harus berada dalam klausa WHERE.

    • Contoh berikut menunjukkan cara yang benar menggunakan UDF untuk pemangkasan partisi.

      --UDF harus ditempatkan dalam klausa WHERE tabel sumber:
      SELECT key, value FROM srcp WHERE udf(ds) = 'xx';

    • Contoh berikut menunjukkan cara yang salah menggunakan UDF untuk pemangkasan partisi.

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

• Jika kolom dalam Ekspresi kolom (SELECT_expr) menggunakan fungsi dan diubah namanya menggunakan alias, alias tersebut tidak dapat direferensikan dalam klausa WHERE. Contoh 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 kebanyakan kasus, GROUP BY digunakan bersama fungsi agregat untuk mengelompokkan baris berdasarkan kolom tertentu, kolom kunci partisi, atau ekspresi reguler. Aturan berikut berlaku untuk GROUP BY:

• Klausa GROUP BY dieksekusi sebelum klausa SELECT. Oleh karena itu, kolom dalam klausa GROUP BY dapat ditentukan berdasarkan nama kolom tabel input untuk pernyataan SELECT, atau berdasarkan ekspresi yang dibentuk dari kolom-kolom tabel tersebut. Saat menggunakan klausa GROUP BY, perhatikan poin-poin berikut:

  • Jika Anda menggunakan ekspresi reguler dalam klausa GROUP BY, Anda harus menggunakan ekspresi lengkap untuk kolom-kolom tersebut.

  • Operasi GROUP BY ALL didukung. Untuk menggunakan operasi ini, atur odps.sql.bigquery.compatible=true; untuk mengaktifkan mode kompatibilitas BigQuery.

  • Semua kolom dalam daftar SELECT yang tidak dibungkus dalam fungsi agregat harus disertakan dalam klausa GROUP BY.

  Contoh penggunaan:

  • Anda dapat langsung menggunakan kolom `region` dalam klausa GROUP BY untuk mengelompokkan data berdasarkan nilai dalam kolom ini. Pernyataan berikut memberikan contohnya.

    SELECT region FROM sale_detail GROUP BY region;

    Hasil berikut dikembalikan.

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

  • Perintah berikut mengelompokkan data berdasarkan `region` dan mengembalikan total penjualan untuk setiap kelompok. Contoh berikut menunjukkan perintah contoh.

    SELECT SUM(total_price) FROM sale_detail GROUP BY region;

    Hasil berikut dikembalikan.

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

  • Perintah berikut mengelompokkan data berdasarkan `region` dan mengembalikan nilai `region` unik serta total penjualan untuk setiap kelompok. Contoh berikut menunjukkan perintah contoh.

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

    Hasil berikut dikembalikan.

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

  • Perintah berikut memberikan contoh pengelompokan berdasarkan alias kolom dalam pernyataan SELECT.

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

  • Anda dapat mengelompokkan berdasarkan ekspresi kolom. Contoh berikut menunjukkan perintah contoh.

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

    Hasil berikut dikembalikan.

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

  • Semua kolom dalam daftar SELECT yang tidak digunakan dalam fungsi agregat harus disertakan dalam klausa GROUP BY. Jika tidak, error akan dikembalikan. Contoh perintah yang salah:

    -- Error: 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;

    Contoh berikut menunjukkan perintah 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       |
    +------------+-------------+

  • Saat Anda mengatur odps.sql.bigquery.compatible=true; untuk mengaktifkan mode kompatibilitas BigQuery, Anda dapat menggunakan sintaks GROUP BY ALL untuk secara otomatis mengelompokkan berdasarkan semua field kueri.

    -- Secara eksplisit mencantumkan semua field untuk pengelompokan.
    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;
    
    -- Gunakan GROUP BY ALL untuk mengelompokkan berdasarkan semua field, yang setara dengan mencantumkan semua field secara eksplisit untuk pengelompokan.
    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;

    Hasil berikut dikembalikan.

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

• Jika Anda menjalankan perintah SET odps.sql.groupby.position.alias=true;, konstanta integer dalam klausa GROUP BY dianggap sebagai nomor ordinal kolom dalam daftar SELECT. Pernyataan contoh:

  -- Jalankan perintah ini bersama 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 kelompok. 
  SELECT region, SUM(total_price) FROM sale_detail GROUP BY 1;

  Hasil berikut dikembalikan.

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

Klausa `HAVING` bersifat opsional. Klausa HAVING biasanya digunakan bersama fungsi agregat untuk memfilter data yang telah dikelompokkan. Pernyataan contoh:

-- Masukkan data ke 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 bersama fungsi agregat untuk memfilter 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 mengurutkan semua catatan data berdasarkan kolom tertentu, kolom kunci partisi, atau konstanta. Aturan berikut berlaku untuk ORDER BY:

• Secara default, data diurutkan secara ascending. Untuk mengurutkan data secara descending, gunakan kata kunci DESC.

• Secara default, klausa ORDER BY harus digunakan bersama klausa LIMIT untuk membatasi jumlah baris output. Jika Anda tidak menyertakan klausa LIMIT, error akan dikembalikan. Untuk menghapus batasan bahwa klausa ORDER BY harus digunakan bersama klausa LIMIT, lihat Membatasi baris output dengan LIMIT > Menghapus persyaratan LIMIT untuk ORDER BY.

  • Perintah berikut mengkueri data dari tabel `sale_detail` dan mengurutkan hasil berdasarkan `total_price` secara ascending, mengembalikan dua baris teratas. Contoh berikut menunjukkan perintah contoh.

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

  • Perintah berikut mengkueri data dari tabel `sale_detail` dan mengurutkan hasil berdasarkan `total_price` secara descending, mengembalikan dua baris teratas. Contoh berikut menunjukkan perintah contoh.

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

• Saat Anda menggunakan ORDER BY untuk mengurutkan data, nilai `NULL` dianggap sebagai nilai terkecil. Perilaku ini konsisten dengan MySQL tetapi berbeda dari Oracle.

  Perintah berikut mengkueri data dari tabel `sale_detail` dan mengurutkan hasil berdasarkan `total_price` secara ascending, mengembalikan dua baris teratas. Contoh berikut menunjukkan perintah contoh.

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

• Klausa ORDER BY dapat mereferensikan alias kolom dari daftar SELECT. Jika Anda tidak menentukan alias untuk kolom dalam daftar SELECT, nama kolom digunakan sebagai aliasnya.

  Klausa ORDER BY dapat diikuti oleh alias kolom. Pernyataan berikut memberikan contohnya:

  SELECT total_price AS t FROM sale_detail ORDER BY total_price LIMIT 3;
  
  -- Pernyataan di atas 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 menjalankan perintah SET odps.sql.orderby.position.alias=true;, konstanta integer dalam klausa ORDER BY dianggap sebagai nomor ordinal kolom dalam daftar SELECT. Pernyataan berikut memberikan contohnya:

  -- Jalankan perintah ini bersama 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 klausa ORDER BY...LIMIT untuk menentukan jumlah baris yang dilewati. Sintaksnya adalah ORDER BY...LIMIT m OFFSET n, yang dapat disingkat menjadi ORDER BY...LIMIT n, m. Dalam sintaks ini, LIMIT m menentukan bahwa `m` baris dikembalikan, dan OFFSET n menentukan bahwa `n` baris pertama dari hasil yang diurutkan dilewati. OFFSET 0 memiliki efek yang sama dengan menghilangkan klausa `OFFSET`.

  Perintah berikut mengurutkan tabel `sale_detail` berdasarkan `total_price` secara ascending, lalu mengeluarkan tiga baris, dimulai dari baris ketiga. Contoh berikut menunjukkan perintah contoh.

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

  Karena hanya tersisa satu baris setelah melewati dua baris pertama, hasilnya hanya berisi satu baris, meskipun batasnya adalah 3.

• Anda dapat menggunakan Range Clustering untuk mempercepat pengurutan global. Dalam skenario `ORDER BY` tipikal, semua data harus digabungkan dan diproses pada satu instans untuk memastikan urutan global. Pendekatan ini mencegah Anda memanfaatkan pemrosesan paralel. Anda dapat menggunakan langkah `PARTITION` dari Range Clustering untuk melakukan pengurutan global secara konkuren. Proses ini pertama-tama melakukan sampling data dan membaginya menjadi rentang, lalu mengurutkan setiap rentang secara konkuren untuk menghasilkan hasil yang diurutkan secara global. Untuk informasi selengkapnya, lihat Percepatan pengurutan global.

Anda harus menentukan alias kolom output dari pernyataan SELECT. Jika Anda mengeksekusi pernyataan SELECT untuk mengkueri data kolom dan alias kolom tersebut tidak ditentukan, nama kolom digunakan sebagai alias. Pernyataan contoh:

-- Pernyataan berikut mengkueri nilai kolom region dari tabel sale_detail dan melakukan sharding 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;

Hasil berikut dikembalikan.

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

Klausa opsional ini biasanya digunakan bersama DISTRIBUTE BY. Aturan berikut berlaku untuk SORT BY:

• Secara default, SORT BY mengurutkan data secara ascending. Jika Anda ingin mengurutkan data secara descending, kata kunci DESC wajib digunakan.

• Jika SORT BY digunakan bersama DISTRIBUTE BY, SORT BY mengurutkan data dalam setiap partisi yang dibuat oleh DISTRIBUTE BY.

  • Perintah berikut mengkueri nilai kolom `region` dan `total_price` dari tabel `sale_detail`, melakukan sharding hash pada hasil berdasarkan nilai `region`, lalu mengurutkan secara lokal hasil yang telah di-sharding berdasarkan `total_price` secara ascending. Contoh berikut menunjukkan perintah contoh.

    -- Masukkan data ke 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);
    
    ---- Ubah jumlah Worker untuk setiap tahap Reduce.
    SET odps.stage.reducer.num=2;
    SELECT region,total_price FROM sale_detail DISTRIBUTE BY region SORT BY total_price;

    Hasil berikut dikembalikan.

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

  • Perintah berikut mengkueri nilai kolom `region` dan `total_price` dari tabel `sale_detail`, melakukan sharding hash pada hasil berdasarkan nilai `region`, lalu mengurutkan secara lokal hasil yang telah di-sharding berdasarkan `total_price` secara descending. Contoh berikut menunjukkan perintah contoh.

    -- Ubah jumlah Worker untuk setiap tahap Reduce.
    SET odps.stage.reducer.num=2;
    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       | 
    | shanghai   | NULL        | 
    | china      | 100.3       | 
    | china      | 100.2       | 
    | china      | 100.1       | 
    +------------+-------------+

• Jika SORT BY digunakan tanpa DISTRIBUTE BY, SORT BY mengurutkan data dalam setiap reducer.

  Hal ini memastikan bahwa data output setiap reducer diurutkan, yang dapat meningkatkan rasio kompresi penyimpanan, mengurangi jumlah data yang dibaca dari disk selama pemfilteran, serta meningkatkan efisiensi operasi pengurutan global berikutnya. Contoh berikut menunjukkan perintah contoh.

  -- Atur jumlah Worker untuk setiap tahap Reduce.
  SET odps.stage.reducer.num=2;
  SELECT region,total_price FROM sale_detail 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 Anda menghadapi skenario berikut, Anda dapat merujuk ke solusi yang sesuai:

• Klausa ORDER BY kini dapat digunakan tanpa klausa LIMIT.

  Karena ORDER BY melakukan pengurutan global pada satu node eksekusi, batasan LIMIT diberlakukan secara default. Hal ini mencegah penyalahgunaan yang dapat menyebabkan satu node memproses data dalam jumlah besar. Jika skenario Anda mengharuskan Anda menggunakan ORDER BY tanpa batasan LIMIT, Anda dapat menggunakan salah satu metode berikut:

  • Tingkat proyek: Jalankan perintah SETPROJECT odps.sql.validate.orderby.limit=false; untuk menonaktifkan batasan bahwa ORDER BY harus digunakan bersama LIMIT.

  • Tingkat sesi: Atur SET odps.sql.validate.orderby.limit=false; untuk menonaktifkan persyaratan bahwa klausa ORDER BY harus digunakan bersama klausa LIMIT. Kirimkan perintah ini bersama pernyataan SQL.

    Catatan

    Jika Anda menonaktifkan persyaratan untuk menggunakan ORDER BY dengan LIMIT, pengurutan data dalam jumlah besar pada satu node eksekusi akan mengonsumsi lebih banyak sumber daya dan memakan waktu lebih lama.

• Menghapus batas tampilan layar

  Jika Anda menjalankan pernyataan SELECT tanpa klausa LIMIT, atau jika NUMBER yang ditentukan dalam klausa LIMIT melebihi batas tampilan yang dikonfigurasi (n), jendela hasil menampilkan maksimal n baris.

  Batas tampilan layar dapat berbeda untuk setiap proyek. Anda dapat mengontrol batas ini menggunakan metode berikut:

  • Jika perlindungan data dinonaktifkan untuk proyek, Anda harus memodifikasi file `odpscmd_config.ini`.

    Atur use_instance_tunnel=true dalam file `odpscmd_config.ini`. Jika parameter instance_tunnel_max_record tidak dikonfigurasi, jumlah baris yang dapat ditampilkan tidak terbatas. Jika tidak, jumlah baris yang dapat ditampilkan dibatasi oleh nilai parameter instance_tunnel_max_record. Nilai maksimum parameter instance_tunnel_max_record adalah 10.000 baris. Untuk informasi selengkapnya tentang Instance Tunnel, lihat Catatan penggunaan.

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

  Catatan

  Anda dapat menjalankan perintah SHOW SecurityConfiguration; untuk melihat konfigurasi properti ProjectProtection. Jika ProjectProtection=true, Anda harus menentukan apakah akan menonaktifkan mekanisme perlindungan data berdasarkan kebutuhan perlindungan data proyek Anda. Untuk menonaktifkan mekanisme tersebut, jalankan perintah SET ProjectProtection=false;. Secara default, properti ProjectProtection tidak diaktifkan. Untuk informasi selengkapnya tentang mekanisme perlindungan data proyek, lihat Mekanisme perlindungan data.