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

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

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

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

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

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

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

תחביר בסיסי

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

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

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

• כל אופרטור צינור בתחביר צינור מורכב מסמל הצינור, |>, משם של אופרטור ומארגומנטים כלשהם:
  |> operator_name argument_list
• אפשר להוסיף אופרטורים של צינור בסוף של כל שאילתה תקינה.
• אפשר להשתמש באופרטורים של צינורות בכל סדר ובכל מספר פעמים.
• תחביר עם קו אנכי פועל בכל מקום שבו יש תמיכה בתחביר רגיל: בשאילתות, בתצוגות, בפונקציות שמחזירות טבלה ובהקשרים אחרים.
• אפשר לשלב תחביר של צינור עם תחביר רגיל באותה שאילתה. לדוגמה, שאילתות משנה יכולות להשתמש בתחביר שונה משל שאילתת האב.
• אופרטור צינור יכול לראות כל כינוי שקיים בטבלה שלפני הצינור.
• שאילתה יכולה להתחיל עם פסקה של 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 |
 +---------+-------------+-----------+-----*/

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

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

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

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

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

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

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

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

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

EXTEND אופרטור קו אנכי

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

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 pipe מאפשר להחליף את הערך של עמודות בטבלה הנוכחית. האופרטור SET צינור דומה להצהרה SELECT * REPLACE (expression AS column). כדי להפנות לערך המקורי, צריך להוסיף כינוי לטבלה לשם העמודה.

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

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

DROP אופרטור קו אנכי

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

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

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

RENAME אופרטור קו אנכי

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

כדי לבצע צבירה בתחביר של צינורות, משתמשים באופרטור 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 pipe מאפשר לכם לצרף את הטבלה הנוכחית לטבלה אחרת, והוא תומך בפעולות הצירוף הרגילות, כולל 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 |
 +---------+-------+-----*/

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

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

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

• ניתוח נתונים באמצעות תחביר של צינורות
• הסבר על התחביר של שאילתות עם צינורות
• הפניה לתחביר של שאילתות רגילות
• מאמר בכנס VLDB 2024 בנושא תחביר צינורות