Sintaksis pipa

Pratinjau

Fitur ini tunduk pada "Persyaratan Penawaran Pra-GA" di bagian Persyaratan Layanan Umum dalam Persyaratan Khusus Layanan. Fitur pra-GA tersedia "sebagaimana adanya" dan mungkin memiliki dukungan terbatas. Untuk mengetahui informasi selengkapnya, lihat deskripsi tahap peluncuran.

Catatan: Untuk meminta masukan atau dukungan terkait fitur ini, kirim email ke [e-mail].

Sintaksis pipa adalah ekstensi untuk GoogleSQL yang mendukung struktur kueri linear yang dirancang untuk membuat kueri Anda lebih mudah dibaca, ditulis, dan dikelola.

Untuk mendaftarkan project dalam pratinjau sintaksis pipa, isi formulir pendaftaran sintaksis pipa BigQuery.

Ringkasan

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

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

Sintaksis standar mengalami masalah yang dapat membuat sulit dibaca, ditulis, dan dikelola. Tabel berikut menunjukkan cara sintaksis pipa mengatasi masalah ini:

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

Sintaksis dasar

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

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

Pertimbangkan tabel berikut:

CREATE 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 dimulai dengan klausa FROM dan tidak perlu berisi simbol pipa:

-- 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 pipa 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 pipa AGGREGATE, diikuti dengan sejumlah fungsi agregat, diikuti dengan klausa GROUP BY. Klausa GROUP BY adalah bagian dari operator pipa AGGREGATE dan tidak dipisahkan oleh pipa (|>).

-- 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 TABLE mydataset.item_data 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 pipa JOIN untuk menggabungkan hasil kueri sebelumnya dengan tabel ini untuk menyertakan setiap ID item:

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

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

Sintaksis pipa memiliki karakteristik utama berikut:

• Operator pipa dapat diterapkan dalam urutan apa pun, berapa kali pun.
• Sintaksis pipa berfungsi di mana pun sintaksis standar didukung: kueri, tampilan, fungsi nilai tabel, dan konteks lainnya.
• Sintaksis pipa dapat dicampur dengan sintaksis standar dalam kueri yang sama. Misalnya, subkueri dapat menggunakan sintaksis yang berbeda dari kueri induk.
• Operator pipa dapat melihat setiap alias yang ada dalam tabel sebelum pipa.

Perbedaan utama dari sintaksis standar

Sintaksis pipa berbeda dengan sintaksis standar dalam hal berikut:

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

Untuk informasi selengkapnya dan daftar lengkap operator pipa, lihat Sintaksis kueri pipa.

Kasus penggunaan

Kasus penggunaan umum untuk sintaksis pipa meliputi:

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

Fitur tambahan dalam sintaksis pipa

Dengan beberapa pengecualian, sintaksis pipa mendukung semua operator yang dilakukan sintaksis standar dengan sintaksis yang sama. Selain itu, sintaksis pipa memperkenalkan operator pipa berikut.

Operator pipa EXTEND

Operator pipa EXTEND, yang hanya dapat digunakan segera setelah simbol pipa, memungkinkan Anda menambahkan kolom yang dihitung ke tabel saat ini. Operator pipa EXTEND mirip dengan pernyataan SELECT *, new_column, tetapi memberi Anda fleksibilitas yang lebih besar dalam mereferensikan alias kolom.

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

CREATE 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 diterima setiap siswa dalam ujian. Dalam sintaksis 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 pipa EXTEND dapat mereferensikan alias yang digunakan sebelumnya, sehingga kueri lebih mudah dibaca dan tidak terlalu 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 pipa SET, yang hanya dapat digunakan langsung setelah simbol pipa, memungkinkan Anda mengganti nilai kolom dalam tabel saat ini. Operator pipa SET mirip dengan pernyataan SELECT * REPLACE (expression AS column). Anda dapat mereferensikan nilai asli dengan memenuhi penentuan 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 pipa DROP, yang hanya dapat digunakan segera setelah simbol pipa, memungkinkan Anda menghapus kolom dari tabel saat ini. Operator pipa DROP mirip dengan pernyataan SELECT * EXCEPT(column). Setelah kolom dihapus, Anda masih dapat mereferensikan nilai asli dengan menentukan nama kolom dengan alias tabel.

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

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

Operator pipa RENAME

Operator pipa RENAME, yang hanya dapat digunakan segera setelah simbol pipa, memungkinkan Anda mengganti nama kolom dari tabel saat ini. Operator pipa 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 pipa, gunakan operator pipa AGGREGATE, diikuti dengan berapa pun fungsi agregat, diikuti dengan klausa GROUP BY. Anda tidak perlu mengulangi kolom dalam klausa SELECT.

Contoh di bagian ini menggunakan tabel produce:

CREATE 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 agregasi, 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 mengikuti kolom yang ingin diurutkan dalam urutan menurun. Misalnya, tiga kueri berikut adalah 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 lengkap, gunakan GROUP BY() atau hapus klausa GROUP BY sepenuhnya:

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

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

Operator pipa JOIN

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

Contoh berikut mereferensikan tabel produce dan item_data:

CREATE 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 TABLE mydataset.item_data 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.item_data` 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 perlu membuat alias tabel saat ini menggunakan operator pipa AS. Secara opsional, Anda dapat memberi alias pada tabel yang digabungkan. Anda dapat mereferensikan kedua alias setelah operator pipa berikutnya:

FROM `mydataset.produce`
|> AS produce_table
|> JOIN `mydataset.item_data` 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 join tidak memiliki visibilitas ke sisi kiri join, yang berarti Anda tidak dapat menggabungkan tabel saat ini dengan dirinya sendiri. Misalnya, kueri berikut gagal:

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

Untuk melakukan join mandiri dengan tabel yang diubah, Anda dapat menggunakan ekspresi tabel umum 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 TABLE mydataset.customer_orders 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 status 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.customer_orders
    GROUP BY customer_id, state, item_type
    QUALIFY num_orders > 1
  )
GROUP BY state, item_type
ORDER BY state DESC, item_type ASC;

Jika membaca kueri dari atas ke bawah, Anda akan menemukan kolom total_cost sebelum kolom tersebut 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 lagi dalam klausa ORDER BY.

Kueri yang setara berikut ditulis menggunakan sintaksis pipa:

FROM mydataset.customer_orders
|> 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 pipa, Anda dapat menulis kueri untuk mengikuti langkah-langkah logis yang mungkin Anda pikirkan untuk menyelesaikan masalah awal. Baris sintaksis dalam kueri sesuai dengan langkah logis berikut:

• Mulai dengan tabel pesanan pelanggan.
• Cari tahu jumlah yang dibelanjakan setiap pelanggan untuk setiap jenis item menurut status.
• Menghitung jumlah pesanan untuk setiap pelanggan.
• Batasi hasil untuk pelanggan berulang.
• Temukan jumlah rata-rata yang dibelanjakan pelanggan berulang untuk setiap negara bagian dan jenis item.

Batasan

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

Langkah selanjutnya

• Pelajari sintaksis kueri pipa lebih lanjut.
• Pelajari sintaksis kueri standar lebih lanjut.