Menggunakan sintaksis kueri pipa

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

Sintaksis kueri pipa 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 pipa di mana pun Anda menulis GoogleSQL.

Sintaksis pipa mendukung operasi yang sama dengan sintaksis kueri GoogleSQL yang ada, atau sintaksis standar—misalnya, pemilihan, agregasi dan pengelompokan, penggabungan, dan pemfilteran—tetapi operasi tersebut dapat diterapkan dalam urutan apa pun, berapa pun frekuensinya. 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.

Untuk mengetahui detail sintaksis selengkapnya, lihat dokumentasi referensi Sintaksis kueri pipa.

Sintaksis dasar

Dalam sintaksis pipa, kueri dimulai dengan kueri SQL standar atau klausa FROM. Misalnya, klausa FROM mandiri, seperti FROM 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 membuat struktur kueri. Saat logika yang dinyatakan oleh kueri Anda menjadi lebih kompleks, kueri masih dapat dinyatakan sebagai urutan linear operator pipa, tanpa menggunakan subkueri bertingkat dalam, sehingga lebih mudah dibaca dan dipahami.

Sintaksis pipa memiliki karakteristik utama berikut:

  • Setiap operator pipa dalam sintaksis pipa terdiri dari simbol pipa, |>, 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 pipa berfungsi di mana pun sintaksis standar didukung: dalam 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.
  • Kueri dapat dimulai dengan klausa FROM, dan operator pipa dapat ditambahkan secara opsional setelah klausa FROM.

Pertimbangkan tabel berikut:

CREATE OR REPLACE TABLE 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 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 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 simbol pipa (|>).

-- Compute total sales by item.
FROM 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 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 pipa JOIN untuk menggabungkan hasil kueri sebelumnya dengan tabel ini untuk menyertakan ID setiap item:

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

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

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 pipa 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 mengetahui detail selengkapnya, lihat daftar lengkap operator pipa.

Kasus penggunaan

Kasus penggunaan umum untuk sintaksis pipa mencakup hal berikut:

  • 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 untuk dan .

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 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 OR REPLACE TABLE 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 Scores;

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

FROM 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 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 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 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 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 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 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 setelah kolom yang ingin diurutkan dalam urutan menurun. Misalnya, tiga kueri berikut setara:

-- Use a separate ORDER BY clause.
FROM 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 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 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 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 ItemData:

CREATE OR REPLACE TABLE 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 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 Produce
|> JOIN `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 perlu membuat alias tabel saat ini menggunakan operator pipa AS. Anda dapat memberi alias pada tabel yang digabungkan secara opsional. Anda dapat mereferensikan kedua alias setelah operator pipe berikutnya:

FROM Produce
|> AS produce_table
|> JOIN `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 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 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 (CTE) di dalam klausa WITH.

WITH cte_table AS (
  FROM 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 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 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 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 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 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 klausa privasi diferensial dalam sintaksis standar dan terapkan operator pipa setelah kueri.
  • Anda tidak dapat menggunakan jendela bernama dalam sintaksis pipa.