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

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

מטרות

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

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

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

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

2. Verify that billing is enabled for your Google Cloud project.

3. ‫BigQuery מופעל באופן אוטומטי בפרויקטים חדשים. כדי להפעיל את BigQuery בפרויקט קיים:

   מפעילים את BigQuery API, אם הוא עדיין לא מופעל.

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

   כדי להפעיל ממשקי API, צריך את ההרשאה serviceusage.services.enable. אם יצרתם את הפרויקט, סביר להניח שכבר יש לכם את ההרשאה הזו דרך התפקיד "בעלים" (roles/owner). אחרת, תוכלו לקבל את ההרשאה הזו דרך התפקיד "אדמין בממשק 'שימוש בשירות'" (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 שנתמכות בתחביר Pipe. בתחביר רגיל זה לא אפשרי, וצריך להשתמש בסעיף 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 שמשתמש בפונקציה אנליטית עם משפט OVER כדי לחשב את התאריך המקסימלי בטבלה.
2. משתמשים באופרטור הצינור WHERE כדי לסנן את השורות שנוצרו אחרי התאריך המקסימלי.

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

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

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

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