Halaman ini diterjemahkan oleh Cloud Translation API.

Menggunakan sintaksis kueri pipa

Sintaksis kueri pipe adalah ekstensi untuk GoogleSQL yang mendukung struktur kueri linear yang dirancang untuk membuat kueri Anda lebih mudah dibaca, ditulis, dan dikelola. Anda dapat menggunakan sintaksis pipe di mana pun Anda menulis GoogleSQL.

Sintaksis pipe mendukung operasi yang sama dengan sintaksis kueri GoogleSQL yang ada, atau sintaksis standar—misalnya, pemilihan, penggabungan dan pengelompokan, penggabungan, dan pemfilteran—tetapi operasi dapat diterapkan dalam urutan apa pun, berapa pun jumlahnya. Struktur linear sintaksis pipe memungkinkan Anda menulis kueri sehingga urutan sintaksis kueri cocok dengan urutan langkah logis yang diambil untuk membuat tabel hasil.

Kueri yang menggunakan sintaksis pipe diberi harga, dieksekusi, dan dioptimalkan dengan cara yang sama seperti kueri sintaksis standar yang setara. Saat menulis kueri dengan sintaksis pipe, ikuti panduan untuk memperkirakan biaya dan mengoptimalkan komputasi kueri.

Sintaksis standar memiliki masalah yang dapat membuatnya sulit dibaca, ditulis, dan dikelola. Tabel berikut menunjukkan cara sintaksis pipe mengatasi masalah ini:

Sintaksis standar	Sintaksis pipa
Klausul harus muncul dalam urutan tertentu.	Operator pipe dapat diterapkan dalam urutan apa pun.
Kueri yang lebih kompleks, seperti kueri dengan agregasi multi-tingkat, biasanya memerlukan CTE atau subkueri bertingkat.	Kueri yang lebih kompleks biasanya dinyatakan dengan menambahkan operator pipe di akhir kueri.
Selama penggabungan, kolom diulang dalam klausa `SELECT`, `GROUP BY`, dan `ORDER BY`.	Kolom hanya dapat dicantumkan sekali per agregasi.

Untuk membuat kueri kompleks langkah demi langkah dalam sintaksis pipe, lihat Menganalisis data menggunakan sintaksis pipe. Untuk mengetahui detail sintaksis lengkap, lihat dokumentasi referensi Sintaksis kueri berpipa.

Sintaksis dasar

Dalam sintaksis pipe, kueri dimulai dengan kueri SQL standar atau klausa FROM. Misalnya, klausa FROM mandiri, seperti FROM MyTable, adalah sintaksis pipe yang valid. Hasil kueri SQL standar atau tabel dari klausa FROM kemudian dapat diteruskan sebagai input ke simbol pipe, |>, diikuti dengan nama operator pipe dan argumen apa pun ke operator tersebut. Operator pipe mengubah tabel dengan cara tertentu, dan hasil transformasi tersebut dapat diteruskan ke operator pipe lain.

Anda dapat menggunakan sejumlah operator pipe dalam kueri untuk melakukan hal-hal seperti memilih, mengurutkan, memfilter, menggabungkan, atau mengagregasi kolom. Nama operator saluran cocok dengan rekan sintaksis standarnya dan umumnya memiliki perilaku yang sama. Perbedaan utama antara sintaksis standar dan sintaksis pipe adalah cara Anda menyusun kueri. Saat logika yang dinyatakan oleh kueri Anda menjadi lebih kompleks, kueri masih dapat dinyatakan sebagai urutan operator pipa linier, tanpa menggunakan subkueri bertingkat dalam, sehingga lebih mudah dibaca dan dipahami.

Sintaksis pipe memiliki karakteristik utama berikut:

Setiap operator pipe dalam sintaksis pipe terdiri dari simbol pipe, |>, nama operator, dan argumen apa pun:
|> operator_name argument_list
Operator pipe dapat ditambahkan ke akhir kueri yang valid.
Operator pipa dapat diterapkan dalam urutan apa pun, berapa pun kali.
Sintaksis pipe berfungsi di mana pun sintaksis standar didukung: dalam kueri, tampilan, fungsi bernilai tabel, dan konteks lainnya.
Sintaksis pipe dapat digabungkan dengan sintaksis standar dalam kueri yang sama. Misalnya, subkueri dapat menggunakan sintaksis yang berbeda dari kueri induk.
Operator pipe dapat melihat setiap alias yang ada dalam tabel sebelum pipe.
Kueri dapat dimulai dengan klausa FROM, dan operator saluran dapat ditambahkan secara opsional setelah klausa FROM.

Pertimbangkan tabel berikut:

CREATE OR REPLACE TABLE mydataset.Produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

Setiap kueri berikut berisi sintaksis pipa yang valid yang menunjukkan cara membuat kueri secara berurutan.

Kueri dapat diawali dengan klausa FROM dan tidak perlu berisi simbol pipe:

-- View the table.
FROM mydataset.Produce;

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | carrots | 0     | vegetable |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

Anda dapat memfilter dengan operator pipe WHERE:

-- Filter items with no sales.
FROM mydataset.Produce
|> WHERE sales > 0;

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

Untuk melakukan agregasi, gunakan operator AGGREGATE pipe, diikuti dengan sejumlah fungsi agregasi, lalu diikuti dengan klausa GROUP BY. Klausul GROUP BY adalah bagian dari operator pipe AGGREGATE dan tidak dipisahkan oleh simbol pipe (|>).

-- Compute total sales by item.
FROM mydataset.Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item;

/*---------+-------------+-----------+
 | item    | total_sales | num_sales |
 +---------+-------------+-----------+
 | apples  | 9           | 2         |
 | bananas | 15          | 1         |
 +---------+-------------+-----------*/

Sekarang, misalkan Anda memiliki tabel berikut yang berisi ID untuk setiap item:

CREATE OR REPLACE TABLE mydataset.ItemData AS (
  SELECT 'apples' AS item, '123' AS id
  UNION ALL
  SELECT 'bananas' AS item, '456' AS id
  UNION ALL
  SELECT 'carrots' AS item, '789' AS id
);

Anda dapat menggunakan operator pipe JOIN untuk menggabungkan hasil kueri sebelumnya dengan tabel ini untuk menyertakan ID setiap item:

FROM mydataset.Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item
|> JOIN mydataset.ItemData USING(item);

/*---------+-------------+-----------+-----+
 | item    | total_sales | num_sales | id  |
 +---------+-------------+-----------+-----+
 | apples  | 9           | 2         | 123 |
 | bananas | 15          | 1         | 456 |
 +---------+-------------+-----------+-----*/

Perbedaan utama dari sintaks standar

Sintaksis pipa berbeda dari sintaksis standar dalam hal berikut:

Kueri dapat dimulai dengan klausa FROM.
Operator saluran SELECT tidak melakukan agregasi. Anda harus menggunakan operator saluran AGGREGATE.
Pemfilteran selalu dilakukan dengan operator WHERE pipe, yang dapat diterapkan di mana saja. Operator saluran WHERE, yang menggantikan HAVING dan QUALIFY, dapat memfilter hasil agregasi atau fungsi jendela.

Untuk mengetahui detail selengkapnya, lihat daftar lengkap operator pipa.

Kasus penggunaan

Kasus penggunaan umum untuk sintaksis pipe mencakup hal berikut:

Analisis ad-hoc dan pembuatan kueri inkremental: Urutan operasi logis mempermudah penulisan dan proses debug kueri. Awalan kueri apa pun hingga simbol pipa |> adalah kueri yang valid, yang membantu Anda melihat hasil sementara dalam kueri yang panjang. Peningkatan produktivitas dapat mempercepat proses pengembangan di seluruh organisasi Anda.
Analisis log: Ada jenis sintaksis seperti pipa lainnya yang populer di kalangan pengguna analisis log. Sintaksis pipe memberikan struktur yang sudah dikenal yang menyederhanakan proses aktivasi bagi pengguna tersebut ke Log Analytics dan BigQuery.

Fitur tambahan dalam sintaksis pipe

Dengan beberapa pengecualian, sintaksis pipe mendukung semua operator yang didukung sintaksis standar dengan sintaksis yang sama. Selain itu, sintaksis pipe memperkenalkan operator pipe tambahan dan menggunakan sintaksis yang diubah untuk agregasi dan penggabungan. Bagian berikut menjelaskan operator ini. Untuk semua operator yang didukung, lihat daftar lengkap operator saluran.

Operator pipa `EXTEND`

Operator pipe EXTEND memungkinkan Anda menambahkan kolom terkomputasi ke tabel saat ini. Operator pipe EXTEND mirip dengan pernyataan SELECT *, new_column, tetapi memberi Anda lebih banyak fleksibilitas dalam mereferensikan alias kolom.

Pertimbangkan tabel berikut yang berisi dua skor pengujian untuk setiap orang:

CREATE OR REPLACE TABLE mydataset.Scores AS (
  SELECT 'Alex' AS student, 9 AS score1, 10 AS score2, 10 AS points_possible
  UNION ALL
  SELECT 'Dana' AS student, 5 AS score1, 7 AS score2, 10 AS points_possible);

/*---------+--------+--------+-----------------+
 | student | score1 | score2 | points_possible |
 +---------+--------+--------+-----------------+
 | Alex    | 9      | 10     | 10              |
 | Dana    | 5      | 7      | 10              |
 +---------+--------+--------+-----------------*/

Misalnya, Anda ingin menghitung skor mentah rata-rata dan skor persentase rata-rata yang diperoleh setiap siswa dalam tes. Dalam sintaks standar, kolom berikutnya dalam pernyataan SELECT tidak memiliki visibilitas ke alias sebelumnya. Untuk menghindari subkueri, Anda harus mengulangi ekspresi untuk rata-rata:

SELECT student,
  (score1 + score2) / 2 AS average_score,
  (score1 + score2) / 2 / points_possible AS average_percent
FROM mydataset.Scores;

Operator saluran EXTEND dapat mereferensikan alias yang digunakan sebelumnya, sehingga kueri lebih mudah dibaca dan tidak rentan terhadap error:

FROM mydataset.Scores
|> EXTEND (score1 + score2) / 2 AS average_score
|> EXTEND average_score / points_possible AS average_percent
|> SELECT student, average_score, average_percent;

/*---------+---------------+-----------------+
 | student | average_score | average_percent |
 +---------+---------------+-----------------+
 | Alex    | 9.5           | .95             |
 | Dana    | 6.0           | 0.6             |
 +---------+---------------+-----------------*/

Operator pipa `SET`

Operator pipe SET memungkinkan Anda mengganti nilai kolom dalam tabel saat ini. Operator pipe SET mirip dengan pernyataan SELECT * REPLACE (expression AS column). Anda dapat mereferensikan nilai asli dengan memenuhi syarat nama kolom menggunakan alias tabel.

FROM (SELECT 3 AS x, 5 AS y)
|> SET x = 2 * x;

/*---+---+
 | x | y |
 +---+---+
 | 6 | 5 |
 +---+---*/

Operator pipa `DROP`

Operator saluran DROP memungkinkan Anda menghapus kolom dari tabel saat ini. Operator pipe DROP mirip dengan pernyataan SELECT * EXCEPT(column). Setelah kolom dilepas, Anda masih dapat mereferensikan nilai asli dengan memenuhi syarat nama kolom dengan alias tabel.

FROM (SELECT 1 AS x, 2 AS y) AS t
|> DROP x;

/*---+
 | y |
 +---+
 | 2 |
 +---*/

Operator pipa `RENAME`

Operator saluran RENAME memungkinkan Anda mengganti nama kolom dari tabel saat ini. Operator pipe RENAME mirip dengan pernyataan SELECT * EXCEPT(old_column), old_column AS new_column.

FROM (SELECT 1 AS x, 2 AS y, 3 AS z) AS t
|> RENAME y AS w;

/*---+---+---+
 | x | w | z |
 +---+---+---+
 | 1 | 2 | 3 |
 +---+---+---*/

Operator pipa `AGGREGATE`

Untuk melakukan agregasi dalam sintaksis pipe, gunakan operator pipe AGGREGATE, diikuti dengan sejumlah fungsi agregat, lalu diikuti dengan klausa GROUP BY. Anda tidak perlu mengulangi kolom dalam klausa SELECT.

Contoh di bagian ini menggunakan tabel Produce:

CREATE OR REPLACE TABLE mydataset.Produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | carrots | 0     | vegetable |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY item, category;

/*---------+-----------+-------+-------------+
 | item    | category  | total | num_records |
 +---------+-----------+-------+-------------+
 | apples  | fruit     | 9     | 2           |
 | carrots | vegetable | 0     | 1           |
 | bananas | fruit     | 15    | 1           |
 +---------+-----------+-------+-------------*/

Jika Anda siap mengurutkan hasil segera setelah penggabungan, Anda dapat menandai kolom dalam klausa GROUP BY yang ingin diurutkan dengan ASC atau DESC. Kolom yang tidak ditandai tidak diurutkan.

Jika ingin mengurutkan semua kolom, Anda dapat mengganti klausa GROUP BY dengan klausa GROUP AND ORDER BY, yang mengurutkan setiap kolom dalam urutan menaik secara default. Anda dapat menentukan DESC dengan mengikuti kolom yang ingin Anda urutkan dalam urutan menurun. Misalnya, tiga kueri berikut setara:

-- Use a separate ORDER BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY category, item
|> ORDER BY category DESC, item;

-- Explicitly mark how to order columns in the GROUP BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY category DESC, item ASC;

-- Only mark descending columns in the GROUP AND ORDER BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP AND ORDER BY category DESC, item;

Keuntungan menggunakan klausa GROUP AND ORDER BY adalah Anda tidak perlu mengulangi nama kolom di dua tempat.

Untuk melakukan agregasi tabel penuh, gunakan GROUP BY() atau hilangkan klausa GROUP BY seluruhnya:

FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records;

/*-------+-------------+
 | total | num_records |
 +-------+-------------+
 | 24    | 4           |
 +-------+-------------*/

Operator pipa `JOIN`

Operator pipe JOIN memungkinkan Anda menggabungkan tabel saat ini dengan tabel lain dan mendukung operasi gabungan standar, termasuk CROSS, INNER, LEFT, RIGHT, dan FULL.

Contoh berikut mereferensikan tabel Produce dan ItemData:

CREATE OR REPLACE TABLE mydataset.Produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

CREATE OR REPLACE TABLE mydataset.ItemData AS (
  SELECT 'apples' AS item, '123' AS id
  UNION ALL
  SELECT 'bananas' AS item, '456' AS id
  UNION ALL
  SELECT 'carrots' AS item, '789' AS id
);

Contoh berikut menggunakan klausa USING dan menghindari ambiguitas kolom:

FROM mydataset.Produce
|> JOIN mydataset.ItemData USING(item)
|> WHERE item = 'apples';

/*--------+-------+----------+-----+
 | item   | sales | category | id  |
 +--------+-------+----------+-----+
 | apples | 2     | fruit    | 123 |
 | apples | 7     | fruit    | 123 |
 +--------+-------+----------+-----*/

Untuk mereferensikan kolom dalam tabel saat ini, seperti untuk membedakan kolom dalam klausa ON, Anda harus membuat alias tabel saat ini dengan menggunakan operator AS pipe. Anda dapat membuat alias untuk tabel gabungan secara opsional. Anda dapat mereferensikan kedua alias setelah operator pipe berikutnya:

FROM mydataset.Produce
|> AS produce_table
|> JOIN mydataset.ItemData AS item_table
   ON produce_table.item = item_table.item
|> WHERE produce_table.item = 'bananas'
|> SELECT item_table.item, sales, id;

/*---------+-------+-----+
 | item    | sales | id  |
 +---------+-------+-----+
 | bananas | 15    | 123 |
 +---------+-------+-----*/

Sisi kanan gabungan tidak memiliki visibilitas ke sisi kiri gabungan, yang berarti Anda tidak dapat menggabungkan tabel saat ini dengan tabel itu sendiri. Misalnya, kueri berikut akan gagal:

-- This query doesn't work.
FROM mydataset.Produce
|> AS produce_table
|> JOIN produce_table AS produce_table_2 USING(item);

Untuk melakukan self-join dengan tabel yang diubah, Anda dapat menggunakan ekspresi tabel umum (CTE) di dalam klausa WITH.

WITH cte_table AS (
  FROM mydataset.Produce
  |> WHERE item = 'carrots'
)
FROM cte_table
|> JOIN cte_table AS cte_table_2 USING(item);

Contoh

Pertimbangkan tabel berikut dengan informasi tentang pesanan pelanggan:

CREATE OR REPLACE TABLE mydataset.CustomerOrders AS (
  SELECT 1 AS customer_id, 100 AS order_id, 'WA' AS state, 5 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 1 AS customer_id, 101 AS order_id, 'WA' AS state, 20 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 1 AS customer_id, 102 AS order_id, 'WA' AS state, 3 AS cost, 'food' AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 103 AS order_id, 'NY' AS state, 16 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 104 AS order_id, 'NY' AS state, 22 AS cost, 'housewares' AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 104 AS order_id, 'WA' AS state, 45 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 3 AS customer_id, 105 AS order_id, 'MI' AS state, 29 AS cost, 'clothing' AS item_type);

Misalnya, Anda ingin mengetahui, untuk setiap negara bagian dan jenis item, jumlah rata-rata yang dibelanjakan oleh pelanggan berulang. Anda dapat menulis kueri dengan cara berikut:

SELECT state, item_type, AVG(total_cost) AS average
FROM
  (
    SELECT
      SUM(cost) AS total_cost,
      customer_id,
      state,
      item_type,
      COUNT(*) OVER (PARTITION BY customer_id) AS num_orders
    FROM mydataset.CustomerOrders
    GROUP BY customer_id, state, item_type
    QUALIFY num_orders > 1
  )
GROUP BY state, item_type
ORDER BY state DESC, item_type ASC;

Jika Anda membaca kueri dari atas ke bawah, Anda akan menemukan kolom total_cost sebelum ditentukan. Bahkan dalam subkueri, Anda membaca nama kolom sebelum melihat tabel asalnya.

Untuk memahami kueri ini, kueri harus dibaca dari dalam ke luar. Kolom state dan item_type diulang beberapa kali dalam klausa SELECT dan GROUP BY, lalu diulang lagi dalam klausa ORDER BY.

Kueri setara berikut ditulis menggunakan sintaksis pipe:

FROM mydataset.CustomerOrders
|> AGGREGATE SUM(cost) AS total_cost, GROUP BY customer_id, state, item_type
|> EXTEND COUNT(*) OVER (PARTITION BY customer_id) AS num_orders
|> WHERE num_orders > 1
|> AGGREGATE AVG(total_cost) AS average GROUP BY state DESC, item_type ASC;

/*-------+------------+---------+
 | state | item_type  | average |
 +-------+------------+---------+
 | WA    | clothing   | 35.0    |
 | WA    | food       | 3.0     |
 | NY    | clothing   | 16.0    |
 | NY    | housewares | 22.0    |
 +-------+------------+---------*/

Dengan sintaksis pipe, Anda dapat menulis kueri untuk mengikuti langkah-langkah logis yang mungkin Anda pikirkan untuk menyelesaikan masalah awal. Baris sintaksis dalam kueri sesuai dengan langkah-langkah logis berikut:

Mulai dengan tabel pesanan pelanggan.
Cari tahu berapa banyak yang dibelanjakan setiap pelanggan untuk setiap jenis item menurut negara bagian.
Hitung jumlah pesanan untuk setiap pelanggan.
Membatasi hasil untuk pelanggan berulang.
Temukan jumlah rata-rata yang dibelanjakan pelanggan yang kembali untuk setiap negara bagian dan jenis item.

Batasan

Anda tidak dapat menyertakan klausa privasi diferensial dalam pernyataan SELECT yang mengikuti operator pipa. Sebagai gantinya, gunakan klausa privasi diferensial dalam sintaksis standar dan terapkan operator pipe setelah kueri.
Anda tidak dapat menggunakan jendela bernama dalam sintaksis pipe.

Langkah berikutnya

Menganalisis data menggunakan sintaksis pipe
Referensi sintaksis kueri pipe
Referensi sintaksis kueri standar
Makalah konferensi VLDB 2024 tentang sintaksis pipe

Menggunakan sintaksis kueri pipa Tetap teratur dengan koleksi Simpan dan kategorikan konten berdasarkan preferensi Anda.