Google menggunakan teknologi AI untuk menerjemahkan konten ke dalam bahasa pilihan Anda. Terjemahan AI mungkin mengandung kesalahan.

Menganalisis data menggunakan sintaksis pipe

Sintaksis pipe di GoogleSQL memungkinkan Anda menyusun kueri secara linear, sehingga analisis data yang kompleks lebih mudah dibaca, ditulis, dan dikelola. Dalam tutorial ini, Anda akan membuat kueri menggunakan tabel bigquery-public-data.austin_bikeshare.bikeshare_trips yang tersedia secara publik untuk memfilter, menggabungkan, dan menganalisis data perjalanan sepeda. Untuk mengetahui latar belakang konseptual dan spesifikasi operator, lihat Bekerja dengan sintaksis kueri pipe dan Sintaksis kueri pipe.

Tujuan

Lihat data tabel dengan memulai kueri menggunakan klausa FROM.
Tambahkan kolom menggunakan operator pipa EXTEND.
Gabungkan data menurut hari dan minggu menggunakan operator pipe AGGREGATE.
Gabungkan data di jendela geser menggunakan operator pipa CROSS JOIN.
Filter 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.

Login ke akun Google Cloud Anda. Jika Anda baru menggunakan Google Cloud, buat akun untuk mengevaluasi performa produk kami dalam skenario dunia nyata. Pelanggan baru juga mendapatkan kredit gratis senilai $300 untuk menjalankan, menguji, dan men-deploy workload.

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.

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.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

BigQuery secara otomatis diaktifkan dalam project baru. Untuk mengaktifkan BigQuery dalam project yang sudah ada,
Aktifkan BigQuery API, jika belum diaktifkan.
Peran yang diperlukan untuk mengaktifkan API
Untuk mengaktifkan API, Anda memerlukan izin serviceusage.services.enable. Jika Anda membuat project, kemungkinan Anda sudah memiliki izin ini melalui peran Pemilik (roles/owner). Jika tidak, Anda bisa mendapatkan izin ini melalui peran Service Usage Admin (roles/serviceusage.serviceUsageAdmin). Pelajari cara memberikan peran.
Mengaktifkan API

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

Melihat data tabel

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

Sintaksis pipe

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

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 pipe, gunakan operator pipe EXTEND:

Sintaksis pipe

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 pipe AGGREGATE dengan fungsi COUNT untuk menemukan jumlah total perjalanan yang dilakukan dan sepeda yang digunakan.
Gunakan klausa GROUP BY untuk mengelompokkan hasil berdasarkan tanggal.

Sintaksis pipe

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

Hasil pesanan

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

Sintaksis pipe

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 akhiran pengurutan langsung ke klausa GROUP BY tanpa menggunakan operator pipe ORDER BY. Menambahkan akhiran ke klausa GROUP BY adalah salah satu dari beberapa fitur pengurutan singkat opsional dengan AGGREGATE yang didukung oleh sintaksis pipe. Dalam sintaksis standar, hal ini tidak mungkin dilakukan dan Anda harus menggunakan klausa ORDER BY untuk pengurutan.

Menggabungkan data mingguan

Setelah memiliki data tentang jumlah sepeda yang digunakan setiap hari, Anda dapat membuat kueri untuk menemukan jumlah sepeda berbeda 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 pipe

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

Agregasi selama jendela 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 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 periode geser ke data Anda, salin setiap perjalanan ke depan 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.

Untuk menyalin data ke depan, gunakan fungsi GENERATE_ARRAY dan cross join:
Sintaksis pipe
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 dari 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 jumlah hari tersebut, hingga tujuh hari setelah tanggal asli.

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 pipe

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 pada setiap hari hingga 2024-05-26.

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

Sintaksis pipe

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 yang akan datang

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

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

Sintaksis pipe

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