MaxCompute:Urutan pernyataan SELECT

Urutan eksekusi

Sintaks SELECT mendukung klausa berikut: SELECT, FROM, WHERE, GROUP BY, HAVING, WINDOW, QUALIFY, ORDER BY, DISTRIBUTE BY, SORT BY, dan LIMIT.

MaxCompute mengeksekusi klausa-klausa tersebut dalam salah satu dari dua urutan, tergantung pada klausa yang digunakan.

ORDER BY dan GROUP BY tidak dapat digunakan bersamaan dengan DISTRIBUTE BY atau SORT BY.

Urutan 1 — digunakan ketika kueri mencakup GROUP BY, ORDER BY, atau LIMIT:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. ORDER BY

7. LIMIT

Urutan 2 — digunakan ketika kueri mencakup DISTRIBUTE BY atau SORT BY:

1. FROM

2. WHERE

3. SELECT

4. DISTRIBUTE BY

5. SORT BY

Meskipun SELECT muncul pertama dalam sintaks tertulis, klausa ini hanya dieksekusi setelah FROM, WHERE, GROUP BY, dan HAVING diproses.

Tulis pernyataan dalam urutan eksekusi

Untuk mempermudah pembacaan logika kueri, MaxCompute memungkinkan Anda menulis pernyataan SELECT dalam urutan eksekusi—dengan FROM di awal, bukan SELECT. Kedua bentuk ini setara:

-- Bentuk standar (SELECT pertama)
SELECT region, max(total_price)
FROM sale_detail
WHERE total_price > 100
GROUP BY region
HAVING sum(total_price) > 300.5
ORDER BY region
LIMIT 5;

-- Bentuk urutan eksekusi (FROM pertama) — setara dengan pernyataan di atas
FROM sale_detail
WHERE total_price > 100
GROUP BY region
HAVING sum(total_price) > 300.5
SELECT region, max(total_price)
ORDER BY region
LIMIT 5;

Sintaks lengkap dalam urutan eksekusi adalah:

FROM <table_reference>
[WHERE <where_condition>]
[GROUP BY <col_list>]
[HAVING <having_condition>]
[WINDOW <window_name> AS (<window_definition>)]
[QUALIFY <expression>]
SELECT [ALL | DISTINCT] <select_expr>, <select_expr>, ...
[ORDER BY <order_condition>]
[DISTRIBUTE BY <distribute_condition> [SORT BY <sort_condition>]]
[LIMIT <number>]

Data contoh

Contoh dalam topik ini menggunakan tabel partisi bernama sale_detail. Jalankan pernyataan berikut untuk membuat dan mengisi tabel tersebut:

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

-- Menambahkan partisi
ALTER TABLE sale_detail ADD
  PARTITION (sale_date='2013', region='china')
  PARTITION (sale_date='2014', region='shanghai');

-- Memasukkan data
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china')
  VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);

INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai')
  VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);

Untuk melihat seluruh isi tabel:

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail;

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

Contoh

-- Bentuk standar
SET odps.sql.allow.fullscan=true;
SELECT region, max(total_price)
FROM sale_detail
WHERE total_price > 100
GROUP BY region
HAVING sum(total_price) > 300.5
ORDER BY region
LIMIT 5;

-- Bentuk urutan eksekusi
SET odps.sql.allow.fullscan=true;
FROM sale_detail
WHERE total_price > 100
GROUP BY region
HAVING sum(total_price) > 300.5
SELECT region, max(total_price)
ORDER BY region
LIMIT 5;

+------------+------------+
| region     | _c1        |
+------------+------------+
| china      | 100.3      |
+------------+------------+

-- Bentuk standar
SET odps.sql.allow.fullscan=true;
SELECT shop_name,
       total_price,
       region
FROM   sale_detail
WHERE  total_price > 100.2
DISTRIBUTE BY region
SORT BY total_price;

-- Bentuk urutan eksekusi
SET odps.sql.allow.fullscan=true;
FROM   sale_detail
WHERE  total_price > 100.2
SELECT shop_name,
       total_price,
       region
DISTRIBUTE BY region
SORT BY total_price;

+------------+-------------+------------+
| shop_name  | total_price | region     |
+------------+-------------+------------+
| s3         | 100.3       | china      |
| s6         | 100.4       | shanghai   |
| s7         | 100.5       | shanghai   |
+------------+-------------+------------+