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

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

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

• למבוא לתחביר של צינורות, ראו עבודה עם תחביר של שאילתות צינורות.
• פרטים מלאים על התחביר זמינים במאמרי העזרה בנושא תחביר שאילתות עם צינורות.

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

מטרות

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

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

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

נכנסים לחשבון Google Cloud . אם אתם משתמשים חדשים ב- Google Cloud, צרו חשבון כדי שתוכלו להעריך את הביצועים של המוצרים שלנו בתרחישים מהעולם האמיתי. לקוחות חדשים מקבלים בחינם גם קרדיט בשווי 300$ להרצה, לבדיקה ולפריסה של עומסי העבודה.

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 מופעל באופן אוטומטי בפרויקטים חדשים. כדי להפעיל את BigQuery בפרויקט קיים,

   מפעילים את BigQuery API.

   תפקידים שנדרשים להפעלת ממשקי API

   כדי להפעיל ממשקי API, צריך את תפקיד ה-IAM 'אדמין של Service Usage' (roles/serviceusage.serviceUsageAdmin), שכולל את ההרשאה serviceusage.services.enable. איך מקצים תפקידים

   להפעלת ה-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. לבסוף, מצטברים הנתונים של הנסיעות ומזהי האופניים לכל יום פעיל.

1. כדי להעתיק את הנתונים קדימה, משתמשים בפונקציה 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 כדי לשנות את התאריך המקורי ולהקדים את חלון הזמן במספר הימים הזה, עד שבעה ימים לפני התאריך המקורי.

2. כדי לראות את התאריכים הפעילים המחושבים של הנסיעות, משתמשים באופרטור הצינור 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.

3. לבסוף, מצטברים מזהי נסיעות ומזהי אופניים ומקובצים לפי 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            |
   | ...         | ...             | ...             |
   +-------------+-----------------+-----------------+
   

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

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

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

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

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