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, agregasi dan pengelompokan, penggabungan, dan pemfilteran—tetapi operasi dapat diterapkan dalam urutan apa pun, berapa kali pun. 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 Anda menulis kueri dengan sintaksis pipe, ikuti panduan untuk memperkirakan biaya dan mengoptimalkan komputasi kueri.

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

Sintaksis standar Sintaksis pipe
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 pipe ke akhir kueri.
Selama agregasi, 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 pipe.

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 untuk 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 pipe cocok dengan nama operator sintaksis standarnya dan umumnya memiliki perilaku yang sama. Perbedaan utama antara sintaksis standar dan sintaksis pipe adalah cara Anda menyusun kueri. Seiring logika yang dinyatakan oleh kueri Anda menjadi lebih kompleks, kueri masih dapat dinyatakan sebagai urutan linear operator pipe, tanpa menggunakan subkueri bertingkat yang 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 kali pun.
  • 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 pipe 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 pipe 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 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 pipa AGGREGATE, diikuti dengan sejumlah fungsi agregat, diikuti dengan klausa GROUP BY. Klausa 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 sintaksis standar

Sintaksis pipe berbeda dengan sintaksis standar dalam hal berikut:

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

Untuk mengetahui detail selengkapnya, lihat daftar lengkap operator pipe.

Kasus penggunaan

Kasus penggunaan umum untuk sintaksis pipe mencakup:

  • 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 panjang. Peningkatan produktivitas dapat mempercepat proses pengembangan di seluruh organisasi Anda.
  • Analisis log: Ada jenis sintaksis mirip pipe lainnya yang populer di kalangan pengguna analisis log. Sintaksis pipe menyediakan struktur yang sudah dikenal, sehingga memudahkan orientasi pengguna tersebut ke Observability 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 dimodifikasi untuk agregasi dan join. Bagian berikut menjelaskan beberapa operator ini. Untuk semua operator yang didukung, lihat daftar lengkap operator pipe.

Operator pipa EXTEND

Operator pipe EXTEND memungkinkan Anda menambahkan kolom komputasi 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 tes 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 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 pipe EXTEND dapat mereferensikan alias yang digunakan sebelumnya, sehingga membuat kueri lebih mudah dibaca dan mengurangi kemungkinan 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 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 menentukan 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 pipe DROP 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 mengualifikasi nama kolom dengan alias tabel.

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

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

Operator pipa RENAME

Operator pipe RENAME 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 pipe, gunakan operator pipe AGGREGATE, diikuti dengan sejumlah fungsi agregat, 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 untuk 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 setelah 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 pipe JOIN memungkinkan Anda menggabungkan tabel saat ini dengan tabel lain dan mendukung operasi join standar, termasuk CROSS, INNER, LEFT, RIGHT, dan FULL.

Contoh berikut merujuk ke 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 memperjelas kolom dalam klausa ON, Anda perlu memberi alias pada tabel saat ini menggunakan operator pipe AS. Anda dapat memberikan alias pada tabel yang digabungkan 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 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 dimodifikasi, 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

Perhatikan tabel berikut yang berisi 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 jumlah rata-rata yang dibelanjakan oleh pelanggan berulang untuk setiap negara bagian dan jenis item. 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 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 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 logis berikut:

  • Mulai dengan tabel pesanan pelanggan.
  • Cari tahu jumlah yang dibelanjakan setiap pelanggan untuk setiap jenis item menurut negara bagian.
  • 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 pipe setelah kueri.

Langkah berikutnya