Menganalisis data menggunakan sintaksis pipe

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

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

• Untuk pengenalan sintaksis pipe, lihat Bekerja dengan sintaksis kueri pipe.
• Untuk mengetahui detail sintaksis lengkap, lihat dokumentasi referensi Sintaksis kueri teruskan.

Dalam tutorial ini, Anda akan membuat kueri kompleks dalam sintaksis pipe 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 menggunakan klausa FROM.
• Tambahkan kolom menggunakan operator pipa EXTEND.
• Gabungkan data menurut hari dan minggu menggunakan operator pipa AGGREGATE.
• Gabungkan data di jendela geser menggunakan operator pipa CROSS JOIN.
• Memfilter data menggunakan operator pipa WHERE.
• Bandingkan struktur kueri linear sintaksis pipe dengan struktur kueri bertingkat 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.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify 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.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

1. BigQuery secara otomatis diaktifkan dalam project baru. Untuk mengaktifkan BigQuery di project yang sudah ada,

   Enable the BigQuery API.

   Roles required to enable APIs

   To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

   Enable the API

Untuk mengetahui 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 pipe, 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

Di 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 pipe, gunakan operator pipe 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 | ... |
| ...      | ...             | ...     | ...       | ...                     | ...        | ... |
+----------+-----------------+---------+-----------+-------------------------+------------+-----+

Menggabungkan data harian

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

• Gunakan operator saluran 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            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Urutkan hasil

Untuk mengurutkan hasil dalam urutan menurun menurut kolom date, tambahkan sufiks DESC ke klausul 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 pipe, Anda dapat menambahkan sufiks pengurutan langsung ke klausa GROUP BY tanpa menggunakan operator pipe ORDER BY. Menambahkan sufiks ke klausa GROUP BY adalah salah satu dari beberapa fitur pengurutan singkat opsional dengan AGGREGATE yang didukung sintaksis pipe. Dalam sintaksis standar, hal ini tidak mungkin dilakukan dan Anda harus menggunakan klausa ORDER BY untuk pengurutan.

Menggabungkan data mingguan

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

Untuk memperbarui baris dalam tabel Anda 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 pada jendela geser

Hasil di bagian sebelumnya menunjukkan perjalanan dalam rentang 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 berjalannya waktu 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 jendela geser ke data Anda, salin terlebih dahulu setiap perjalanan enam hari aktif tambahan dari tanggal mulainya. Kemudian, hitung tanggal hari aktif menggunakan fungsi DATE_ADD. Terakhir, gabungkan perjalanan dan ID sepeda untuk setiap hari aktif.

1. Untuk menyalin data ke depan, gunakan fungsi GENERATE_ARRAY dan gabungan 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 hingga 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.

2. Untuk melihat tanggal aktif yang dihitung untuk perjalanan, gunakan operator pipe 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.

3. Terakhir, gabungkan ID perjalanan dan ID sepeda, lalu kelompokkan berdasarkan 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 diperpanjang hingga enam hari ke depan setelah tanggal terakhir dalam data Anda. Untuk mengecualikan tanggal yang melampaui akhir data, tetapkan tanggal maksimum dalam kueri Anda:

1. Tambahkan operator saluran EXTEND lain yang menggunakan fungsi jendela dengan klausa OVER untuk menghitung tanggal maksimum dalam tabel.
2. Gunakan operator pipe WHERE untuk memfilter baris yang dibuat 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 pipe, lihat Bekerja dengan sintaksis kueri pipe.
• Untuk informasi teknis selengkapnya, lihat dokumentasi referensi Sintaksis kueri saluran.