Halaman ini diterjemahkan oleh Cloud Translation API.

Menganalisis data menggunakan sintaksis pipa

Tutorial ini menunjukkan cara menulis kueri menggunakan sintaksis pipa untuk menganalisis data.

Sintaksis pipa adalah ekstensi untuk GoogleSQL yang mendukung struktur kueri linear yang dirancang untuk membuat kueri Anda lebih mudah dibaca, ditulis, dan dikelola. Sintaksis pipa terdiri dari simbol pipa |>, nama operator pipa, dan argumen apa pun. Untuk informasi selengkapnya, lihat referensi berikut:

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

Dalam tutorial ini, Anda akan membuat kueri kompleks dalam sintaksis pipa menggunakan tabel bigquery-public-data.austin_bikeshare.bikeshare_trips yang tersedia secara publik, yang berisi data tentang perjalanan sepeda.

Tujuan

Lihat data tabel dengan memulai kueri dengan klausa FROM.
Tambahkan kolom menggunakan operator pipa EXTEND.
Gabungkan data berdasarkan hari dan minggu menggunakan operator pipa AGGREGATE.
Menggabungkan data melalui jendela geser menggunakan operator pipa CROSS JOIN.
Filter data menggunakan operator pipa WHERE.
Bandingkan struktur kueri linear dari sintaksis pipa dengan struktur kueri bertingkat dari sintaksis standar saat melakukan agregasi multi-level.

Sebelum memulai

Untuk mulai menggunakan set data publik BigQuery, Anda harus membuat atau memilih sebuah project. Satu terabyte data pertama yang diproses per bulan bersifat gratis, sehingga Anda dapat mulai membuat kueri set data publik tanpa mengaktifkan penagihan. Jika ingin melampaui paket gratis, Anda juga harus mengaktifkan penagihan.

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Go to project selector

Make sure that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Go to project selector

Make sure that billing is enabled for your Google Cloud project.

BigQuery secara otomatis diaktifkan dalam project baru. Untuk mengaktifkan BigQuery di project yang sudah ada,
Enable the BigQuery API.
Enable the API

Untuk informasi selengkapnya tentang berbagai cara menjalankan kueri, lihat artikel Menjalankan kueri.

Melihat data tabel

Untuk mengambil semua data dari tabel bikeshare_trips, jalankan kueri berikut:

Sintaksis pipa

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

Sintaksis standar

SELECT *
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

Dalam sintaksis pipa, kueri dapat dimulai dengan klausa FROM tanpa klausa SELECT untuk menampilkan hasil tabel.

Hasilnya mirip dengan berikut ini:

+----------+-----------------+---------+-----------+-------------------------+-----+
| trip_id  | subscriber_type | bike_id | bike_type | start_time              | ... |
+----------+-----------------+---------+-----------+-------------------------+-----+
| 28875008 | Pay-as-you-ride | 18181   | electric  | 2023-02-12 12:46:32 UTC | ... |
| 28735401 | Explorer        | 214     | classic   | 2023-01-13 12:01:45 UTC | ... |
| 29381980 | Local365        | 21803   | electric  | 2023-04-20 08:43:46 UTC | ... |
| ...      | ...             | ...     | ...       | ...                     | ... |
+----------+-----------------+---------+-----------+-------------------------+-----+

Tambah kolom

Dalam tabel bikeshare_trips, kolom start_time adalah stempel waktu, tetapi Anda mungkin ingin menambahkan kolom yang hanya menampilkan tanggal perjalanan. Untuk menambahkan kolom dalam sintaksis pipa, gunakan operator pipa EXTEND:

Sintaksis pipa

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date;

Sintaksis standar

SELECT *, CAST(start_time AS DATE) AS date
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

Hasilnya mirip dengan berikut ini:

+----------+-----------------+---------+-----------+-------------------------+------------+-----+
| trip_id  | subscriber_type | bike_id | bike_type | start_time              | date       | ... |
+----------+-----------------+---------+-----------+-------------------------+------------+-----+
| 28875008 | Pay-as-you-ride | 18181   | electric  | 2023-02-12 12:46:32 UTC | 2023-02-12 | ... |
| 28735401 | Explorer        | 214     | classic   | 2023-01-13 12:01:45 UTC | 2023-01-13 | ... |
| 29381980 | Local365        | 21803   | electric  | 2023-04-20 08:43:46 UTC | 2023-04-20 | ... |
| ...      | ...             | ...     | ...       | ...                     | ...        | ... |
+----------+-----------------+---------+-----------+-------------------------+------------+-----+

Data gabungan harian

Anda dapat mengelompokkan menurut tanggal untuk menemukan jumlah total perjalanan yang dilakukan dan sepeda yang digunakan per hari.

Gunakan operator pipa AGGREGATE dengan fungsi COUNT untuk menemukan jumlah total perjalanan yang dilakukan dan sepeda yang digunakan.
Gunakan klausa GROUP BY untuk mengelompokkan hasil menurut tanggal.

Sintaksis pipa

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> AGGREGATE
     COUNT(*) AS trips,
     COUNT(DISTINCT bike_id) AS distinct_bikes
   GROUP BY date;

Sintaksis standar

SELECT
  CAST(start_time AS DATE) AS date,
  COUNT(*) AS trips,
  COUNT(DISTINCT bike_id) AS distinct_bikes
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY date;

Hasilnya mirip dengan berikut ini:

+------------+-------+----------------+
| date       | trips | distinct_bikes |
+------------+-------+----------------+
| 2023-04-20 | 841   | 197            |
| 2023-01-27 | 763   | 148            |
| 2023-06-12 | 562   | 202            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Mengurutkan hasil

Untuk mengurutkan hasil dalam urutan menurun menurut kolom date, tambahkan akhiran DESC ke klausa GROUP BY:

Sintaksis pipa

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> AGGREGATE
     COUNT(*) AS trips,
     COUNT(DISTINCT bike_id) AS distinct_bikes
   GROUP BY date DESC;

Sintaksis standar

SELECT
  CAST(start_time AS DATE) AS date,
  COUNT(*) AS trips,
  COUNT(DISTINCT bike_id) AS distinct_bikes
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY date
ORDER BY date DESC;

Hasilnya mirip dengan berikut ini:

+------------+-------+----------------+
| date       | trips | distinct_bikes |
+------------+-------+----------------+
| 2024-06-30 | 331   | 90             |
| 2024-06-29 | 395   | 123            |
| 2024-06-28 | 437   | 137            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Dalam sintaksis pipa, Anda dapat menambahkan akhiran pengurutan langsung ke klausa GROUP BY tanpa menggunakan operator pipa ORDER BY. Menambahkan akhiran ke klausa GROUP BY adalah salah satu dari beberapa fitur pengurutan singkatan opsional dengan AGGREGATE yang didukung sintaksis pipa. Dalam sintaksis standar, hal ini tidak dapat dilakukan dan Anda harus menggunakan klausa ORDER BY untuk pengurutan.

Data gabungan mingguan

Setelah memiliki data tentang jumlah sepeda yang digunakan setiap hari, Anda dapat membuat kueri untuk menemukan jumlah sepeda unik yang digunakan selama setiap periode tujuh hari.

Untuk memperbarui baris dalam tabel agar menampilkan minggu, bukan hari, gunakan fungsi DATE_TRUNC dalam klausa GROUP BY dan tetapkan perincian ke WEEK:

Sintaksis pipa

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> AGGREGATE
    COUNT(*) AS trips,
    COUNT(DISTINCT bike_id) AS distinct_bikes,
GROUP BY DATE_TRUNC(date, WEEK) AS date DESC;

Sintaksis standar

SELECT
  DATE_TRUNC(CAST(start_time AS DATE), WEEK) AS date,
  COUNT(*) AS trips,
  COUNT(DISTINCT bike_id) AS distinct_bikes,
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY date
ORDER BY date DESC;

Hasilnya mirip dengan berikut ini:

+------------+-------+----------------+
| date       | trips | distinct_bikes |
+------------+-------+----------------+
| 2024-06-30 | 331   | 90             |
| 2024-06-23 | 3206  | 213            |
| 2024-06-16 | 3441  | 212            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Menggabungkan data dalam periode geser

Hasil di bagian sebelumnya menunjukkan perjalanan dalam periode tetap antara tanggal mulai dan akhir, seperti 2024-06-23 hingga 2024-06-29. Sebagai gantinya, Anda mungkin ingin melihat perjalanan dalam periode geser, selama periode tujuh hari yang bergerak maju seiring waktu dengan setiap hari baru. Dengan kata lain, untuk tanggal tertentu, Anda mungkin ingin mengetahui jumlah perjalanan yang dilakukan dan sepeda yang digunakan selama minggu berikutnya.

Untuk menerapkan periode geser ke data Anda, pertama-tama salin setiap perjalanan ke depan enam hari aktif tambahan dari tanggal mulainya. Kemudian, hitung tanggal hari aktif menggunakan fungsi DATE_ADD. Terakhir, gabungkan ID perjalanan dan sepeda untuk setiap hari aktif.

Untuk menyalin data ke depan, gunakan fungsi GENERATE_ARRAY dan join silang:
Sintaksis pipa
```
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days;
```
Sintaksis standar
```
SELECT *, CAST(start_time AS DATE) AS date
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days;
```
Fungsi GENERATE_ARRAY membuat array dengan tujuh elemen, 0 hingga 6. Operasi CROSS JOIN UNNEST membuat tujuh salinan setiap baris, dengan kolom diff_days baru yang berisi salah satu nilai elemen array dari 0 ke 6 untuk setiap baris. Anda dapat menggunakan nilai diff_days sebagai penyesuaian pada tanggal asli untuk menggeser periode ke depan sebanyak hari tersebut, hingga tujuh hari setelah tanggal asli.

Untuk melihat tanggal aktif yang dihitung untuk perjalanan, gunakan operator pipa EXTEND dengan fungsi DATE_ADD untuk membuat kolom bernama active_date yang berisi tanggal mulai ditambah nilai di kolom diff_days:

Sintaksis pipa

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days
|> EXTEND DATE_ADD(date, INTERVAL diff_days DAY) AS active_date;

Sintaksis standar

SELECT *, DATE_ADD(date, INTERVAL diff_days DAY) AS active_date
FROM (
  SELECT *, CAST(start_time AS DATE) AS date
  FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
  CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days)

Misalnya, perjalanan yang dimulai pada 2024-05-20 juga dianggap aktif setiap hari hingga 2024-05-26.

Terakhir, gabungkan ID perjalanan dan ID sepeda, lalu kelompokkan menurut active_date:

Sintaksis pipa

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days
|> EXTEND DATE_ADD(date, INTERVAL diff_days DAY) AS active_date
|> AGGREGATE COUNT(DISTINCT bike_id) AS active_7d_bikes,
            COUNT(trip_id) AS active_7d_trips
GROUP BY active_date DESC;

Sintaksis standar

SELECT
  DATE_ADD(date, INTERVAL diff_days DAY) AS active_date,
  COUNT(DISTINCT bike_id) AS active_7d_bikes,
  COUNT(trip_id) AS active_7d_trips
FROM (
  SELECT *, CAST(start_time AS DATE) AS date
  FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
  CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days)
GROUP BY active_date
ORDER BY active_date DESC;

Hasilnya mirip dengan berikut ini:

+-------------+-----------------+-----------------+
| active_date | active_7d_bikes | active_7d_trips |
+-------------+-----------------+-----------------+
| 2024-07-06  | 90              | 331             |
| 2024-07-05  | 142             | 726             |
| 2024-07-04  | 186             | 1163            |
| ...         | ...             | ...             |
+-------------+-----------------+-----------------+

Memfilter tanggal mendatang

Dalam kueri sebelumnya, tanggal diperluas ke masa mendatang hingga enam hari setelah tanggal terakhir dalam data Anda. Untuk memfilter tanggal yang melampaui akhir data, tetapkan tanggal maksimum dalam kueri:

Tambahkan operator pipa EXTEND lain yang menggunakan fungsi jendela dengan klausa OVER untuk menghitung tanggal maksimum dalam tabel.
Gunakan operator pipa WHERE untuk memfilter baris yang dihasilkan yang melewati tanggal maksimum.

Sintaksis pipa

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> EXTEND MAX(date) OVER () AS max_date
|> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days
|> EXTEND DATE_ADD(date, INTERVAL diff_days DAY) AS active_date
|> WHERE active_date <= max_date
|> AGGREGATE COUNT(DISTINCT bike_id) AS active_7d_bikes,
             COUNT(trip_id) AS active_7d_trips
   GROUP BY active_date DESC;

Sintaksis standar

SELECT
  DATE_ADD(date, INTERVAL diff_days DAY) AS active_date,
  COUNT(DISTINCT bike_id) AS active_7d_bikes,
  COUNT(trip_id) AS active_7d_trips
FROM(
  SELECT *
  FROM (
    SELECT *,
      DATE_ADD(date, INTERVAL diff_days DAY) AS active_date,
      MAX(date) OVER () AS max_date
    FROM(
      SELECT *, CAST(start_time AS DATE) AS date,
      FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
      CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days))
  WHERE active_date <= max_date)
GROUP BY active_date
ORDER BY active_date DESC;

Hasilnya mirip dengan berikut ini:

+-------------+-----------------+-----------------+
| active_date | active_7d_bikes | active_7d_trips |
+-------------+-----------------+-----------------+
| 2024-06-30  | 212             | 3031            |
| 2024-06-29  | 213             | 3206            |
| 2024-06-28  | 219             | 3476            |
| ...         | ...             | ...             |
+-------------+-----------------+-----------------+

Langkah berikutnya

Untuk mengetahui informasi selengkapnya tentang cara kerja sintaksis pipa, lihat Menggunakan sintaksis kueri pipa.
Untuk informasi teknis selengkapnya, lihat dokumentasi referensi Sintaksis kueri pipa.