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

תחביר השאילתה Pipe הוא תוסף ל-GoogleSQL שתומך במבנה שאילתה לינארי, שנועד להקל על הקריאה, הכתיבה והתחזוקה של השאילתות. אפשר להשתמש בתחביר Pipe בכל מקום שבו כותבים GoogleSQL.

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

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

בתחביר הרגיל יש בעיות שיכולות להקשות על הקריאה, הכתיבה והתחזוקה. בטבלה הבאה מוסבר איך תחביר הצינור פותר את הבעיות האלה:

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

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

תחביר בסיסי

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

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

אלה המאפיינים העיקריים של תחביר Pipe:

  • כל אופרטור צינור עיבוד נתונים בתחביר של צינור עיבוד נתונים מורכב מסמל צינור עיבוד הנתונים, |>, שם האופרטור וכל הארגומנטים:
    |> operator_name argument_list
  • אפשר להוסיף אופרטורים של צינור עיבוד נתונים בסוף כל שאילתה חוקית.
  • אפשר להחיל אופרטורים של צינור עיבוד נתונים בכל סדר, כמה פעמים שרוצים.
  • תחביר Pipe פועל בכל מקום שבו יש תמיכה בתחביר רגיל: בשאילתות, בתצוגות, בפונקציות עם ערכי טבלה ובהקשרים אחרים.
  • אפשר לשלב תחביר Pipe עם תחביר רגיל באותה שאילתה. לדוגמה, שאילתות משנה יכולות להשתמש בתחביר שונה מהשאילתה הראשית.
  • אופרטור צינור עיבוד נתונים יכול לראות כל כינוי שקיים בטבלה שלפני הצינור.
  • שאילתה יכולה להתחיל בסעיף FROM, ואפשר להוסיף אופרטורים של קו אנכי אחרי הסעיף FROM.

לצורך המחשה, נבחן את הטבלה הבאה:

CREATE OR REPLACE TABLE mydataset.Produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

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

השאילתות יכולות להתחיל בסעיף FROM, ולא חייבות להכיל את הסמל "|":

-- View the table.
FROM mydataset.Produce;

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | carrots | 0     | vegetable |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

אפשר לסנן באמצעות WHERE אופרטור קו אנכי:

-- Filter items with no sales.
FROM mydataset.Produce
|> WHERE sales > 0;

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

כדי לבצע צבירה, משתמשים באופרטור הצינור AGGREGATE, ואחריו כל מספר של פונקציות צבירה, ואחריהן משפט GROUP BY. הסעיף GROUP BY הוא חלק מהאופרטור AGGREGATE, והוא לא מופרד באמצעות סמל קו אנכי (|>).

-- Compute total sales by item.
FROM mydataset.Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item;

/*---------+-------------+-----------+
 | item    | total_sales | num_sales |
 +---------+-------------+-----------+
 | apples  | 9           | 2         |
 | bananas | 15          | 1         |
 +---------+-------------+-----------*/

עכשיו נניח שיש לכם את הטבלה הבאה, שמכילה מזהה לכל פריט:

CREATE OR REPLACE TABLE mydataset.ItemData AS (
  SELECT 'apples' AS item, '123' AS id
  UNION ALL
  SELECT 'bananas' AS item, '456' AS id
  UNION ALL
  SELECT 'carrots' AS item, '789' AS id
);

אפשר להשתמש באופרטור הצינור JOIN כדי לאחד את התוצאות של השאילתה הקודמת עם הטבלה הזו, כך שכל מזהה פריט ייכלל:

FROM mydataset.Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item
|> JOIN mydataset.ItemData USING(item);

/*---------+-------------+-----------+-----+
 | item    | total_sales | num_sales | id  |
 +---------+-------------+-----------+-----+
 | apples  | 9           | 2         | 123 |
 | bananas | 15          | 1         | 456 |
 +---------+-------------+-----------+-----*/

ההבדלים העיקריים מהתחביר הרגיל

ההבדלים בין תחביר Pipe לתחביר רגיל:

  • שאילתות יכולות להתחיל בסעיף FROM.
  • האופרטור SELECT לא מבצע צבירה. במקום זאת, צריך להשתמש באופרטור הצינור (pipe) AGGREGATE.
  • הסינון תמיד מתבצע באמצעות האופרטור WHERE pipe, שאפשר להחיל בכל מקום. אופרטור הצינור WHERE, שמחליף את HAVING ו-QUALIFY, יכול לסנן את התוצאות של פונקציות צבירה או פונקציות אנליטיות (window function).

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

תרחישים לדוגמה

תרחישי שימוש נפוצים לתחביר צינור עיבוד נתונים כוללים:

  • ניתוח אד-הוק ויצירת שאילתות מצטברות: סדר הפעולות הלוגי מקל על כתיבת שאילתות וניפוי באגים. התחילית של כל שאילתה עד לסמל הצינור |> היא שאילתה חוקית, שעוזרת לכם לראות תוצאות ביניים בשאילתה ארוכה. השיפור בפרודוקטיביות יכול להאיץ את תהליך הפיתוח בכל הארגון.
  • ניתוח נתוני יומן: יש סוגים אחרים של תחביר דמוי צינור שפופולריים בקרב משתמשים של ניתוח נתוני יומן. תחביר Pipe מספק מבנה מוכר שמפשט את תהליך ההצטרפות של המשתמשים האלה ל-Observability Analytics ול-BigQuery.

תכונות נוספות בתחביר של תו הקו האנכי

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

אופרטור הצינור EXTEND

אופרטור הצינור EXTEND מאפשר להוסיף עמודות מחושבות לטבלה הנוכחית. האופרטור EXTEND דומה להצהרה SELECT *, new_column, אבל הוא נותן יותר גמישות בהפניה לכינויים של עמודות.

נניח שיש לנו טבלה עם שני ציוני מבחנים לכל אדם:

CREATE OR REPLACE TABLE mydataset.Scores AS (
  SELECT 'Alex' AS student, 9 AS score1, 10 AS score2, 10 AS points_possible
  UNION ALL
  SELECT 'Dana' AS student, 5 AS score1, 7 AS score2, 10 AS points_possible);

/*---------+--------+--------+-----------------+
 | student | score1 | score2 | points_possible |
 +---------+--------+--------+-----------------+
 | Alex    | 9      | 10     | 10              |
 | Dana    | 5      | 7      | 10              |
 +---------+--------+--------+-----------------*/

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

SELECT student,
  (score1 + score2) / 2 AS average_score,
  (score1 + score2) / 2 / points_possible AS average_percent
FROM mydataset.Scores;

אופרטור הצינור EXTEND יכול להפנות לכינויים שנעשה בהם שימוש בעבר, וכך קל יותר לקרוא את השאילתה והיא פחות נוטה לשגיאות:

FROM mydataset.Scores
|> EXTEND (score1 + score2) / 2 AS average_score
|> EXTEND average_score / points_possible AS average_percent
|> SELECT student, average_score, average_percent;

/*---------+---------------+-----------------+
 | student | average_score | average_percent |
 +---------+---------------+-----------------+
 | Alex    | 9.5           | .95             |
 | Dana    | 6.0           | 0.6             |
 +---------+---------------+-----------------*/

אופרטור הצינור SET

אופרטור הצינור SET מאפשר להחליף את הערך של עמודות בטבלה הנוכחית. האופרטור SET דומה להצהרה SELECT * REPLACE (expression AS column). כדי להפנות לערך המקורי, צריך להוסיף לשם העמודה כינוי של טבלה.

FROM (SELECT 3 AS x, 5 AS y)
|> SET x = 2 * x;

/*---+---+
 | x | y |
 +---+---+
 | 6 | 5 |
 +---+---*/

אופרטור הצינור DROP

אופרטור הצינור DROP מאפשר להסיר עמודות מהטבלה הנוכחית. האופרטור DROP דומה להצהרה SELECT * EXCEPT(column). אחרי שמשמיטים עמודה, עדיין אפשר להפנות לערך המקורי על ידי הוספת כינוי של טבלה לשם העמודה.

FROM (SELECT 1 AS x, 2 AS y) AS t
|> DROP x;

/*---+
 | y |
 +---+
 | 2 |
 +---*/

אופרטור הצינור RENAME

אופרטור הצינור RENAME מאפשר לשנות את שמות העמודות בטבלה הנוכחית. האופרטור RENAME דומה להצהרה SELECT * EXCEPT(old_column), old_column AS new_column.

FROM (SELECT 1 AS x, 2 AS y, 3 AS z) AS t
|> RENAME y AS w;

/*---+---+---+
 | x | w | z |
 +---+---+---+
 | 1 | 2 | 3 |
 +---+---+---*/

אופרטור הצינור AGGREGATE

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

בדוגמאות שבקטע הזה נעשה שימוש בטבלה Produce:

CREATE OR REPLACE TABLE mydataset.Produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | carrots | 0     | vegetable |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY item, category;

/*---------+-----------+-------+-------------+
 | item    | category  | total | num_records |
 +---------+-----------+-------+-------------+
 | apples  | fruit     | 9     | 2           |
 | carrots | vegetable | 0     | 1           |
 | bananas | fruit     | 15    | 1           |
 +---------+-----------+-------+-------------*/

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

אם רוצים לסדר את כל העמודות, אפשר להחליף את הסעיף GROUP BY בסעיף GROUP AND ORDER BY, שמסדר כל עמודה בסדר עולה כברירת מחדל. אפשר לציין DESC אחרי העמודות שרוצים למיין בסדר יורד. לדוגמה, שלוש השאילתות הבאות זהות:

-- Use a separate ORDER BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY category, item
|> ORDER BY category DESC, item;
-- Explicitly mark how to order columns in the GROUP BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY category DESC, item ASC;
-- Only mark descending columns in the GROUP AND ORDER BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP AND ORDER BY category DESC, item;

היתרון בשימוש בסעיף GROUP AND ORDER BY הוא שלא צריך לחזור על שמות העמודות בשני מקומות.

כדי לבצע צבירה של טבלה מלאה, משתמשים ב-GROUP BY() או משמיטים את הסעיף GROUP BY לגמרי:

FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records;

/*-------+-------------+
 | total | num_records |
 +-------+-------------+
 | 24    | 4           |
 +-------+-------------*/

אופרטור הצינור JOIN

אופרטור הצינור JOIN מאפשר לצרף את הטבלה הנוכחית לטבלה אחרת, ותומך בפעולות הצירוף הרגילות, כולל CROSS,‏ INNER,‏ LEFT,‏ RIGHT ו-FULL.

הדוגמאות הבאות מתייחסות לטבלאות Produce ו-ItemData:

CREATE OR REPLACE TABLE mydataset.Produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);
CREATE OR REPLACE TABLE mydataset.ItemData AS (
  SELECT 'apples' AS item, '123' AS id
  UNION ALL
  SELECT 'bananas' AS item, '456' AS id
  UNION ALL
  SELECT 'carrots' AS item, '789' AS id
);

בדוגמה הבאה נעשה שימוש בסעיף USING כדי למנוע חוסר בהירות בעמודה:

FROM mydataset.Produce
|> JOIN mydataset.ItemData USING(item)
|> WHERE item = 'apples';

/*--------+-------+----------+-----+
 | item   | sales | category | id  |
 +--------+-------+----------+-----+
 | apples | 2     | fruit    | 123 |
 | apples | 7     | fruit    | 123 |
 +--------+-------+----------+-----*/

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

FROM mydataset.Produce
|> AS produce_table
|> JOIN mydataset.ItemData AS item_table
   ON produce_table.item = item_table.item
|> WHERE produce_table.item = 'bananas'
|> SELECT item_table.item, sales, id;

/*---------+-------+-----+
 | item    | sales | id  |
 +---------+-------+-----+
 | bananas | 15    | 123 |
 +---------+-------+-----*/

הצד הימני של הצירוף לא יכול לראות את הצד השמאלי של הצירוף, כלומר אי אפשר לצרף את הטבלה הנוכחית לעצמה. לדוגמה, השאילתה הבאה תיכשל:

-- This query doesn't work.
FROM mydataset.Produce
|> AS produce_table
|> JOIN produce_table AS produce_table_2 USING(item);

כדי לבצע self-join עם טבלה ששונתה, אפשר להשתמש בביטוי טבלה נפוץ (CTE) בתוך משפט WITH.

WITH cte_table AS (
  FROM mydataset.Produce
  |> WHERE item = 'carrots'
)
FROM cte_table
|> JOIN cte_table AS cte_table_2 USING(item);

דוגמה

נניח שיש לנו את הטבלה הבאה עם מידע על הזמנות של לקוחות:

CREATE OR REPLACE TABLE mydataset.CustomerOrders AS (
  SELECT 1 AS customer_id, 100 AS order_id, 'WA' AS state, 5 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 1 AS customer_id, 101 AS order_id, 'WA' AS state, 20 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 1 AS customer_id, 102 AS order_id, 'WA' AS state, 3 AS cost, 'food' AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 103 AS order_id, 'NY' AS state, 16 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 104 AS order_id, 'NY' AS state, 22 AS cost, 'housewares' AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 104 AS order_id, 'WA' AS state, 45 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 3 AS customer_id, 105 AS order_id, 'MI' AS state, 29 AS cost, 'clothing' AS item_type);

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

SELECT state, item_type, AVG(total_cost) AS average
FROM
  (
    SELECT
      SUM(cost) AS total_cost,
      customer_id,
      state,
      item_type,
      COUNT(*) OVER (PARTITION BY customer_id) AS num_orders
    FROM mydataset.CustomerOrders
    GROUP BY customer_id, state, item_type
    QUALIFY num_orders > 1
  )
GROUP BY state, item_type
ORDER BY state DESC, item_type ASC;

אם קוראים את השאילתה מלמעלה למטה, נתקלים בעמודה total_cost לפני שהיא הוגדרה. גם בתוך שאילתת המשנה, אתם קוראים את שמות העמודות לפני שאתם רואים מאיזו טבלה הן מגיעות.

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

השאילתה המקבילה הבאה כתובה באמצעות תחביר צינור עיבוד נתונים:

FROM mydataset.CustomerOrders
|> AGGREGATE SUM(cost) AS total_cost, GROUP BY customer_id, state, item_type
|> EXTEND COUNT(*) OVER (PARTITION BY customer_id) AS num_orders
|> WHERE num_orders > 1
|> AGGREGATE AVG(total_cost) AS average GROUP BY state DESC, item_type ASC;

/*-------+------------+---------+
 | state | item_type  | average |
 +-------+------------+---------+
 | WA    | clothing   | 35.0    |
 | WA    | food       | 3.0     |
 | NY    | clothing   | 16.0    |
 | NY    | housewares | 22.0    |
 +-------+------------+---------*/

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

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

מגבלות

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

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