Google uses AI technology to translate content into your preferred language. AI translations can contain errors.

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 untuk membuat kueri Anda lebih mudah dibaca, ditulis, dan dikelola. Sintaksis pipe terdiri dari simbol pipe |>, a operator pipe nama, dan argumen apa pun. Untuk mengetahui informasi selengkapnya, lihat referensi berikut:

Untuk mengetahui pengantar sintaksis pipe, lihat Menggunakan sintaksis kueri pipe.
Untuk mengetahui detail sintaksis lengkap, lihat Dokumentasi referensi sintaksis kueri pipe.

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

Tujuan

Melihat data tabel dengan memulai kueri menggunakan klausa FROM.
Menambahkan kolom menggunakan operator pipe EXTEND.
Mengagregasi data menurut hari dan minggu menggunakan operator pipe AGGREGATE.
Mengagregasi data dalam rentang waktu geser menggunakan operator pipe CROSS JOIN.
Memfilter data menggunakan operator pipe WHERE.
Membandingkan 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 keakun Anda. Google Cloud 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.
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 Admin Penggunaan Layanan (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 | ... |
| ...      | ...             | ...     | ...       | ...                     | ... |
+----------+-----------------+---------+-----------+-------------------------+-----+

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

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

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

Mengurutkan hasil

Untuk mengurutkan hasil dalam urutan menurun menurut kolom date, tambahkan DESC akhiran 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 GROUP BY klausa tanpa menggunakan ORDER BY operator pipe. Menambahkan akhiran 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.

Mengagregasi 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 rentang waktu tujuh hari.

Untuk memperbarui baris dalam tabel agar menampilkan minggu, bukan hari, gunakan fungsi DATE_TRUNC dalam klausa GROUP BY dan tetapkan detail 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            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Mengagregasi dalam rentang waktu geser

Hasil di bagian sebelumnya menunjukkan perjalanan dalam rentang waktu tetap antara tanggal mulai dan tanggal berakhir, seperti 2024-06-23 hingga 2024-06-29. Sebagai gantinya, Anda mungkin ingin melihat perjalanan dalam rentang waktu geser, selama periode tujuh hari yang bergerak maju dalam 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 rentang waktu 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 ID 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 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 ke tanggal asli untuk menggeser rentang waktu ke depan sebanyak 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 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 mendatang

Dalam kueri sebelumnya, tanggal diperpanjang hingga enam hari setelah tanggal terakhir dalam data Anda. Untuk memfilter tanggal yang diperpanjang di luar 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 Menggunakan sintaksis kueri pipe.
Untuk mengetahui informasi teknis selengkapnya, lihat Dokumentasi referensi sintaksis kueri pipe.