‫Google משתמשת בטכנולוגיית AI כדי לתרגם תוכן לשפה המועדפת עליך. בתרגומים כאלו עשויות להיות שגיאות.

ניתוח נתונים באמצעות תחביר של צינורות

תחביר הצינור ב-GoogleSQL מאפשר לכם לבנות שאילתות בצורה לינארית, וכך להקל על קריאה, כתיבה ותחזוקה של ניתוח נתונים מורכב. במדריך הזה, תיצרו שאילתה באמצעות הטבלה bigquery-public-data.austin_bikeshare.bikeshare_trips שזמינה לציבור, כדי לסנן, לצבור ולנתח נתוני נסיעה באופניים. למידע על הרקע התיאורטי ומפרטי האופרטורים, אפשר לעיין במאמרים עבודה עם תחביר של שאילתות עם צינורות ותחביר של שאילתות עם צינורות.

מטרות

כדי לראות את נתוני הטבלה, מתחילים שאילתה עם פסוקית FROM.
כדי להוסיף עמודות, משתמשים באופרטור הצינור EXTEND.
כדי לצבור נתונים לפי יום ושבוע, משתמשים בAGGREGATE אופרטור הצינור.
אפשר לצבור נתונים על פני חלון הזזה באמצעות אופרטור הצינור CROSS JOIN.
מסננים את הנתונים באמצעות WHERE האופרטור pipe.
כשמבצעים צבירות בכמה רמות, אפשר להשוות בין מבנה השאילתה הלינארי של תחביר הצינור לבין מבנה השאילתה המקונן של התחביר הרגיל.

לפני שמתחילים

כדי להתחיל להשתמש במערך נתונים ציבורי ב-BigQuery, צריך ליצור פרויקט או לבחור פרויקט קיים. הטרה-בייט הראשון של נתונים שעוברים עיבוד בכל חודש הוא בחינם, כך שאפשר להתחיל להריץ שאילתות על מערכי נתונים ציבוריים בלי להפעיל חיוב. אם אתם מתכוונים לחרוג מהתוכנית בחינם, אתם צריכים גם להפעיל את החיוב.

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.
‫BigQuery מופעל באופן אוטומטי בפרויקטים חדשים. כדי להפעיל את BigQuery בפרויקט קיים,
מפעילים את BigQuery API.
תפקידים שנדרשים להפעלת ממשקי API
כדי להפעיל ממשקי API, נדרשת ההרשאה serviceusage.services.enable. אם יצרתם את הפרויקט, סביר להניח שכבר יש לכם את ההרשאה הזו דרך התפקיד 'בעלים' (roles/owner). אחרת, תוכלו לקבל את ההרשאה הזו דרך התפקיד 'אדמין בממשק Service Usage' (roles/serviceusage.serviceUsageAdmin). איך מקצים תפקידים
להפעלת ה-API

מידע נוסף על הדרכים השונות להרצת שאילתות זמין במאמר בנושא הרצת שאילתה.

הצגת נתונים בטבלה

כדי לאחזר את כל הנתונים מהטבלה bikeshare_trips, מריצים את השאילתה הבאה:

תחביר של צינורות

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

תחביר רגיל

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

בתחביר של צינורות, השאילתה יכולה להתחיל עם פסקה FROM בלי פסקה SELECT כדי להחזיר תוצאות של טבלה.

התוצאה אמורה להיראות כך:

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

הוספת עמודות

בטבלה bikeshare_trips, העמודה start_time היא חותמת זמן, אבל יכול להיות שתרצו להוסיף עמודה שתציג רק את תאריך הנסיעה. כדי להוסיף עמודה בתחביר של קו אנכי, משתמשים באופרטור הקו האנכי EXTEND:

תחביר של צינורות

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

תחביר רגיל

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

התוצאה אמורה להיראות כך:

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

נתונים יומיים מצטברים

אפשר לקבץ לפי תאריך כדי לראות את המספר הכולל של הנסיעות שבוצעו ואת האופניים שהיו בשימוש בכל יום.

משתמשים בAGGREGATE אופרטור הצינור עם הפונקציה COUNT כדי למצוא את המספר הכולל של הנסיעות שבוצעו והאופניים שהיו בשימוש.
משתמשים בסעיף GROUP BY כדי לקבץ את התוצאות לפי תאריך.

תחביר של צינורות

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;

תחביר רגיל

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;

התוצאה אמורה להיראות כך:

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

סדר התוצאות

כדי למיין את התוצאות בסדר יורד לפי העמודה date, מוסיפים את הסיומת DESC למשפט GROUP BY:

תחביר של צינורות

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;

תחביר רגיל

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;

התוצאה אמורה להיראות כך:

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

בתחביר של צינורות, אפשר להוסיף את הסיומת של המיון ישירות לסעיף GROUP BY בלי להשתמש באופרטור ORDER BY של צינורות. הוספת הסיומת לסעיף GROUP BY היא אחת מכמה תכונות אופציונליות של קיצור דרך לסידור עם AGGREGATE שנתמכות בתחביר של צינורות. בתחביר רגיל, זה לא אפשרי וחובה להשתמש בסעיף ORDER BY למיון.

צבירת נתונים שבועיים

עכשיו שיש לכם נתונים על מספר האופניים שהיו בשימוש בכל יום, אתם יכולים להוסיף לשאילתה כדי למצוא את מספר האופניים הייחודיים שהיו בשימוש בכל חלון של שבעה ימים.

כדי לעדכן את השורות בטבלה כך שיוצגו שבועות במקום ימים, משתמשים בפונקציה DATE_TRUNC במשפט GROUP BY ומגדירים את רמת הפירוט ל-WEEK:

תחביר של צינורות

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;

תחביר רגיל

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;

התוצאה אמורה להיראות כך:

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

צבירה על חלון הזזה

התוצאות בקטע הקודם מציגות נסיעות בחלון קבוע בין תאריכי התחלה וסיום, כמו 2024-06-23 עד 2024-06-29. במקום זאת, יכול להיות שתרצו לראות נסיעות בחלון נע, במשך תקופה של שבעה ימים שמתקדמת בזמן עם כל יום חדש. במילים אחרות, יכול להיות שתרצו לדעת לגבי תאריך מסוים מהו מספר הנסיעות שבוצעו ומספר האופניים שהיו בשימוש בשבוע שלאחר התאריך הזה.

כדי להחיל חלון נע על הנתונים, קודם מעתיקים כל נסיעה קדימה לשישה ימים נוספים פעילים מתאריך ההתחלה שלה. לאחר מכן, מחשבים את התאריכים של הימים הפעילים באמצעות הפונקציה DATE_ADD. לבסוף, מצטברים הנתונים של הנסיעות ומזהי האופניים לכל יום פעיל.

כדי להעתיק את הנתונים קדימה, משתמשים בפונקציה GENERATE_ARRAY וב-cross join:
תחביר של צינורות
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;
תחביר רגיל
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;
הפונקציה GENERATE_ARRAY יוצרת מערך עם שבעה רכיבים, 0 עד 6. הפעולה CROSS JOIN UNNEST יוצרת שבעה עותקים של כל שורה, עם עמודה חדשה diff_days שמכילה אחד מערכי האלמנטים של המערך מ-0 עד 6 לכל שורה. אפשר להשתמש בערכים של diff_days כדי לשנות את התאריך המקורי ולהקדים את חלון הזמן במספר הימים הזה, עד שבעה ימים לפני התאריך המקורי.

כדי לראות את התאריכים הפעילים המחושבים של הנסיעות, משתמשים באופרטור הצינור EXTEND עם הפונקציה DATE_ADD כדי ליצור עמודה בשם active_date שמכילה את תאריך ההתחלה בתוספת הערך בעמודה diff_days:

תחביר של צינורות

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;

תחביר רגיל

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)

לדוגמה, נסיעה שמתחילה ב-2024-05-20 נחשבת גם כפעילה בכל יום עד 2024-05-26.

לבסוף, מצטברים מזהי נסיעות ומזהי אופניים ומקובצים לפי active_date:

תחביר של צינורות

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;

תחביר רגיל

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;

התוצאה אמורה להיראות כך:

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

סינון תאריכים עתידיים

בשאילתה הקודמת, התאריכים נמשכים אל העתיד עד שישה ימים אחרי התאריך האחרון בנתונים. כדי לסנן תאריכים שחורגים מתאריך הסיום של הנתונים, צריך להגדיר תאריך מקסימלי בשאילתה:

מוסיפים עוד אופרטור צינור EXTEND שמשתמש בפונקציה אנליטית (window function) עם פסוקית OVER כדי לחשב את התאריך המקסימלי בטבלה.
משתמשים באופרטור WHERE 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;

תחביר רגיל

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;

התוצאה אמורה להיראות כך:

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

המאמרים הבאים

מידע נוסף על התחביר של שאילתות עם צינורות זמין במאמר איך עובדים עם תחביר של שאילתות עם צינורות.
מידע טכני נוסף מופיע במאמרי העזרה בנושא תחביר של שאילתות עם צינורות.