תחביר, פונקציות ואופרטורים של SQL מדור קודם

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

תחביר של שאילתות

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

סעיף SELECT

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

אפשר לתת לכל ביטוי כינוי על ידי הוספת רווח ואחריו מזהה אחרי הביטוי. כדי לשפר את הקריאוּת, אפשר להוסיף את מילת המפתח האופציונלית AS בין הביטוי לבין הכינוי. אפשר להפנות לכינויים שמוגדרים בסעיף SELECT בסעיפים GROUP BY,‏ HAVING ו-ORDER BY של השאילתה, אבל לא בסעיפים FROM,‏ WHERE או OMIT RECORD IF, וגם לא בביטויים אחרים באותו סעיף SELECT.

הערות:

  • אם משתמשים בפונקציית צבירה בסעיף SELECT, צריך להשתמש בפונקציית צבירה בכל הביטויים או שהשאילתה צריכה לכלול סעיף GROUP BY שכולל את כל השדות הלא נצברים בסעיף SELECT כמפתחות קיבוץ. לדוגמה: 
    #legacySQL
SELECT
  word,
  corpus,
  COUNT(word)
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS "th"
GROUP BY
  word,
  corpus; /* Succeeds because all non-aggregated fields are group keys. */
     
    #legacySQL
SELECT
  word,
  corpus,
  COUNT(word)
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS "th"
GROUP BY
  word; /* Fails because corpus is not aggregated nor is it a group key. */
  • אפשר להשתמש בסוגריים מרובעים כדי להחליף מילים שמורות, וכך להשתמש בהן כשמות של שדות וכינויים. לדוגמה, אם יש לכם עמודה בשם "partition", שהיא מילה שמורה בתחביר של BigQuery, השאילתות שמפנות לשדה הזה ייכשלו עם הודעות שגיאה לא ברורות, אלא אם תשתמשו בתו בריחה (escape) עם סוגריים מרובעים: 
    SELECT [partition] FROM ...
דוגמה

בדוגמה הזו מוגדרים כינויים בסעיף SELECT, ואז יש הפניה לאחד מהם בסעיף ORDER BY. שימו לב שלא ניתן להפנות לעמודה word באמצעות word_alias בסעיף WHERE, אלא צריך להפנות אליה לפי השם. גם שם העצם הנרדף len לא גלוי במשפט WHERE. הוא יהיה גלוי לסעיף HAVING. 

#legacySQL
SELECT
  word AS word_alias,
  LENGTH(word) AS len
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS 'th'
ORDER BY
  len;

המשתנה WITHIN לפונקציות צבירה

aggregate_function WITHIN RECORD [ [ AS ] alias ]

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

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

דוגמה

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

#legacySQL
SELECT
  repository.url,
  COUNT(payload.pages.page_name) WITHIN RECORD AS page_count
FROM
  [bigquery-public-data:samples.github_nested]
HAVING
  page_count > 80
ORDER BY
  page_count DESC;

פסוקית FROM

FROM
  [project_name:]datasetId.tableId [ [ AS ] alias ] |
  (subquery) [ [ AS ] alias ] |
  JOIN clause |
  FLATTEN clause |
  table wildcard function

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

טבלאות עם הפניות

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

דוגמה
[my-dashed-project:dataset1.tableName]

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

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

דוגמאות

הדוגמה הזו מפנה לעמודה בלי קידומת של טבלה. 

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare];

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

#legacySQL
SELECT
  samples.shakespeare.word
FROM
  samples.shakespeare;

בדוגמה הזו, שם העמודה מתחיל בשם חלופי של טבלה. 

#legacySQL
SELECT
  t.word
FROM
  [bigquery-public-data:samples.shakespeare] AS t;

טבלאות מחולקות למחיצות לפי טווח של מספרים שלמים

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

בדוגמה הבאה, השאילתה מתבצעת על מחיצת הטווח שמתחילה ב-30: 

#legacySQL
SELECT
  *
FROM
  dataset.table$30;

שימו לב שאי אפשר להשתמש ב-SQL מדור קודם כדי לשלוח שאילתות על טבלה מחולקת למחיצות (Partitions) שלמה שמחולקת לפי טווח של מספרים שלמים. במקום זאת, השאילתה מחזירה שגיאה כמו זו:

Querying tables partitioned on a field is not supported in Legacy SQL

שימוש בשאילתות משנה

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

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

פסיק בתור UNION ALL

בניגוד ל-GoogleSQL, ב-SQL מדור קודם הפסיק משמש כאופרטור UNION ALL ולא כאופרטור CROSS JOIN. זוהי התנהגות מדור קודם שהתפתחה כי בעבר BigQuery לא תמך ב-CROSS JOIN, ומשתמשי BigQuery היו צריכים לכתוב שאילתות UNION ALL באופן קבוע. ב-GoogleSQL, שאילתות שמבצעות איחודים הן מילוליות במיוחד. שימוש בפסיק כאופרטור האיחוד מאפשר לכתוב שאילתות כאלה בצורה הרבה יותר יעילה. לדוגמה, אפשר להשתמש בשאילתה הזו כדי להריץ שאילתה אחת על יומנים מכמה ימים. 

#legacySQL
SELECT
  FORMAT_UTC_USEC(event.timestamp_in_usec) AS time,
  request_url
FROM
  [applogs.events_20120501],
  [applogs.events_20120502],
  [applogs.events_20120503]
WHERE
  event.username = 'root' AND
  NOT event.source_ip.is_internal;

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

פונקציות של תווים כלליים לחיפוש בטבלאות

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

האופרטור FLATTEN

(FLATTEN([project_name:]datasetId.tableId, field_to_be_flattened))
(FLATTEN((subquery), field_to_be_flattened))

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

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

מידע נוסף ודוגמאות זמינים במאמר טיפול בנתונים.

האופרטור JOIN

‫BigQuery תומך בכמה אופרטורים של JOIN בכל משפט FROM. בפעולות JOIN הבאות, התוצאות של פעולת JOIN הקודמת משמשות כקלט JOIN השמאלי. אפשר להשתמש בשדות מכל קלט JOIN קודם כמפתחות בסעיפי ON של אופרטורים JOIN הבאים.

סוגי JOIN

ב-BigQuery אפשר לבצע פעולות של INNER, ‏ [FULL|RIGHT|LEFT] OUTER ו-CROSS JOIN. אם לא מציינים ערך, ברירת המחדל היא INNER.

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

התאמה לכל פריט (EACH)

המשתנה EACH הוא רמז שאומר ל-BigQuery להריץ את JOIN באמצעות כמה מחיצות. זה שימושי במיוחד כשאתם יודעים ששני הצדדים של JOIN גדולים. אי אפשר להשתמש במשתנה EACH בסעיפים של CROSS JOIN.

בעבר, במקרים רבים עודדנו את השימוש ב-EACH, אבל זה כבר לא המצב. כשאפשר, כדאי להשתמש ב-JOIN בלי המשתנה EACH כדי לשפר את הביצועים. משתמשים ב-JOIN EACH כשהשאילתה נכשלת ומוצגת הודעת השגיאה "חרגת מהמגבלה של המשאבים".

Semi-join ו-Anti-join

בנוסף לתמיכה ב-JOIN בסעיף FROM, ב-BigQuery יש גם תמיכה בשני סוגים של צירופים בסעיף WHERE: צירוף חצי-פנימי וצירוף חצי-חיצוני. כדי לציין semi-join, משתמשים במילת המפתח IN עם שאילתת משנה. כדי לציין anti-join, משתמשים במילות המפתח NOT IN.

דוגמאות

השאילתה הבאה משתמשת ב-semi-join כדי למצוא n-grams שבהם המילה הראשונה ב-n-gram היא גם המילה השנייה ב-n-gram אחר, שבו המילה השלישית ב-n-gram היא "AND". 

#legacySQL
SELECT
  ngram
FROM
  [bigquery-public-data:samples.trigrams]
WHERE
  first IN (SELECT
              second
            FROM
              [bigquery-public-data:samples.trigrams]
            WHERE
              third = "AND")
LIMIT 10;

השאילתה הבאה משתמשת ב-semi-join כדי להחזיר את מספר הנשים מעל גיל 50 שילדו ב-10 המדינות עם הכי הרבה לידות. 

#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state IN (SELECT
              state
            FROM
              (SELECT
                 state,
                 COUNT(state) total
               FROM
                 [bigquery-public-data:samples.natality]
               GROUP BY
                 state
               ORDER BY
                 total DESC
               LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC

כדי לראות את המספרים של 40 המדינות האחרות, אפשר להשתמש ב-anti-join. השאילתה הבאה כמעט זהה לדוגמה הקודמת, אבל משתמשת ב-NOT IN במקום ב-IN כדי להחזיר את מספר הנשים מעל גיל 50 שילדו ב-40 המדינות עם הכי מעט לידות. 

#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state NOT IN (SELECT
                  state
                FROM
                  (SELECT
                     state,
                     COUNT(state) total
                   FROM
                     [bigquery-public-data:samples.natality]
                   GROUP BY
                     state
                   ORDER BY
                     total DESC
                   LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC

הערות:

  • ‫BigQuery לא תומך ב-semi-join או ב-anti-semi-join מקושרים. שאילתת המשנה לא יכולה להפנות לשדות כלשהם מהשאילתה החיצונית.
  • בשאילתת המשנה שמשמשת ב-semi-join או ב-anti-semi-join צריך לבחור שדה אחד בדיוק.
  • סוגי השדה שנבחר והשדה שנעשה בו שימוש מהשאילתה החיצונית בסעיף WHERE חייבים להיות זהים. ‫BigQuery לא יבצע המרת סוגים (type coercion) עבור semi-join או anti-semi-join.

סעיף WHERE

הסעיף WHERE, שלפעמים נקרא פרדיקט, מסנן רשומות שנוצרו על ידי הסעיף FROM באמצעות ביטוי בוליאני. אפשר לחבר כמה תנאים באמצעות פסוקיות בוליאניות של AND ו-OR, ואפשר להקיף אותם בסוגריים – () – כדי לקבץ אותם. לא צריך לבחור את השדות שמופיעים בסעיף WHERE בסעיף SELECT התואם, והביטוי של סעיף WHERE לא יכול להפנות לביטויים שמחושבים בסעיף SELECT של השאילתה שאליה שייך סעיף WHERE.

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

דוגמה

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

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  (word CONTAINS 'prais' AND word CONTAINS 'ing') OR
  (word CONTAINS 'laugh' AND word CONTAINS 'ed');

סעיף OMIT RECORD IF

הסעיף OMIT RECORD IF הוא מבנה שייחודי ל-BigQuery. הוא שימושי במיוחד כשעובדים עם סכימות חוזרות בתוך סכימות. הוא דומה לסעיף WHERE , אבל שונה ממנו בשני אופנים חשובים. קודם כל, הוא משתמש בתנאי החרגה, כלומר הרשומות מושמטות אם הביטוי מחזיר true, אבל נשמרות אם הביטוי מחזיר false או null. שנית, התנאי OMIT RECORD IF יכול להשתמש (ובדרך כלל משתמש) בפונקציות צבירה מוגדרות היקף.

בנוסף לסינון של רשומות מלאות, אפשר לציין ב-OMIT...IF היקף צר יותר כדי לסנן רק חלקים של רשומה. כדי לעשות את זה, משתמשים בשם של צומת שאינו עלה בסכימה במקום ב-RECORD בסעיף OMIT...IF. משתמשי BigQuery משתמשים בפונקציונליות הזו לעיתים רחוקות. בWITHIN שצוין למעלה יש קישורים למסמכים נוספים על ההתנהגות המתקדמת הזו.

אם משתמשים ב-OMIT...IF כדי להחריג חלק מרשומה בשדה חוזר, והשאילתה בוחרת גם שדות חוזרים אחרים באופן עצמאי, BigQuery משמיט חלק מהרשומות החוזרות האחרות בשאילתה. אם מוצגת השגיאה Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>, מומלץ לעבור ל-GoogleSQL. למידע על העברת הצהרות OMIT...IF ל-GoogleSQL, קראו את המאמר העברה ל-GoogleSQL.

דוגמה

אם נחזור לדוגמה שבה השתמשנו לתיאור המגביל WITHIN, אפשר להשתמש ב-OMIT RECORD IF כדי להשיג את אותה מטרה שהשגנו באמצעות WITHIN ו-HAVING בדוגמה הזו. 

#legacySQL
SELECT
  repository.url
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  COUNT(payload.pages.page_name) <= 80;

סעיף GROUP BY

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

דוגמה

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

#legacySQL
SELECT
  first,
  COUNT(ngram)
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 10;

צבירת נתונים שמתבצעת באמצעות משפט GROUP BY נקראת צבירת נתונים מקובצת . בניגוד לצבירת נתונים ברמת ההיקף, צבירת נתונים מקובצים נפוצה ברוב מערכות העיבוד של SQL.

המשתנה EACH

המשתנה EACH הוא רמז שאומר ל-BigQuery להריץ את GROUP BY באמצעות כמה מחיצות. האפשרות הזו שימושית במיוחד אם אתם יודעים שקבוצת הנתונים מכילה מספר גדול של ערכים נפרדים למפתחות הקבוצה.

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

הפונקציה ROLLUP

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

דוגמה

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

#legacySQL
SELECT
  year,
  is_male,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

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

+------+---------+----------+
| year | is_male |  count   |
+------+---------+----------+
| NULL |    NULL | 12122730 |
| 2000 |    NULL |  4063823 |
| 2000 |   false |  1984255 |
| 2000 |    true |  2079568 |
| 2001 |    NULL |  4031531 |
| 2001 |   false |  1970770 |
| 2001 |    true |  2060761 |
| 2002 |    NULL |  4027376 |
| 2002 |   false |  1966519 |
| 2002 |    true |  2060857 |
+------+---------+----------+

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

דוגמה

השאילתה הזו מוסיפה את הפונקציה GROUPING לדוגמה הקודמת כדי לזהות טוב יותר את השורות שנוספו בגלל הפונקציה ROLLUP. 

#legacySQL
SELECT
  year,
  GROUPING(year) as rollup_year,
  is_male,
  GROUPING(is_male) as rollup_gender,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

אלה התוצאות שהשאילתה החדשה מחזירה. 

+------+-------------+---------+---------------+----------+
| year | rollup_year | is_male | rollup_gender |  count   |
+------+-------------+---------+---------------+----------+
| NULL |           1 |    NULL |             1 | 12122730 |
| 2000 |           0 |    NULL |             1 |  4063823 |
| 2000 |           0 |   false |             0 |  1984255 |
| 2000 |           0 |    true |             0 |  2079568 |
| 2001 |           0 |    NULL |             1 |  4031531 |
| 2001 |           0 |   false |             0 |  1970770 |
| 2001 |           0 |    true |             0 |  2060761 |
| 2002 |           0 |    NULL |             1 |  4027376 |
| 2002 |           0 |   false |             0 |  1966519 |
| 2002 |           0 |    true |             0 |  2060857 |
+------+-------------+---------+---------------+----------+

הערות:

  • שדות לא נצברים בסעיף SELECT חייבים להופיע בסעיף GROUP BY. 
    #legacySQL
SELECT
  word,
  corpus,
  COUNT(word)
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS "th"
GROUP BY
  word,
  corpus; /* Succeeds because all non-aggregated fields are group keys. */
     
    #legacySQL
SELECT
  word,
  corpus,
  COUNT(word)
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS "th"
GROUP BY
  word;  /* Fails because corpus is not aggregated nor is it a group key. */
  • אי אפשר להשתמש בביטויים שמחושבים בסעיף SELECT בסעיף GROUP BY התואם. 
    #legacySQL
SELECT
  word,
  corpus,
  COUNT(word) word_count
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS "th"
GROUP BY
  word,
  corpus,
  word_count;  /* Fails because word_count is not visible to this GROUP BY clause. */
  • אי אפשר לקבץ לפי ערכים מסוג float ו-double, כי פונקציית השוויון של הסוגים האלה לא מוגדרת היטב.
  • המערכת היא אינטראקטיבית, ולכן יכול להיות ששאילתות שמניבות מספר גדול של קבוצות ייכשלו. שימוש ב פונקציה TOP במקום GROUP BY עשוי לפתור חלק מהבעיות שקשורות לשינוי קנה המידה.

סעיף HAVING

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

דוגמה

השאילתה הזו מחשבת את המילים הראשונות הנפוצות ביותר בקבוצת הנתונים לדוגמה של ngram, שמכילות את האות a ומופיעות לכל היותר 10,000 פעמים. 

#legacySQL
SELECT
  first,
  COUNT(ngram) ngram_count
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
HAVING
  first contains "a"
  AND ngram_count < 10000
ORDER BY
  2 DESC
LIMIT 10;

סעיף ORDER BY

הסעיף ORDER BY ממיין את תוצאות השאילתה בסדר עולה או יורד באמצעות שדה מפתח אחד או יותר. כדי למיין לפי כמה שדות או כינויים, מזינים אותם כרשימה מופרדת בפסיקים. התוצאות ממוינות לפי השדות בסדר שבו הם מופיעים. כדי לציין את כיוון המיון, משתמשים ב-DESC (סדר יורד) או ב-ASC (סדר עולה). ‫ASC היא ברירת המחדל. אפשר לציין כיוון מיון שונה לכל מפתח מיון.

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

סעיף LIMIT

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

הערות:

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

דקדוק של שאילתות

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

query:
    SELECT { * | field_path.* | expression } [ [ AS ] alias ] [ , ... ]
    [ FROM from_body
      [ WHERE bool_expression ]
      [ OMIT RECORD IF bool_expression]
      [ GROUP [ EACH ] BY [ ROLLUP ] { field_name_or_alias } [ , ... ] ]
      [ HAVING bool_expression ]
      [ ORDER BY field_name_or_alias [ { DESC | ASC } ] [, ... ] ]
      [ LIMIT n ]
    ];

from_body:
    {
      from_item [, ...] |  # Warning: Comma means UNION ALL here
      from_item [ join_type ] JOIN [ EACH ] from_item [ ON join_predicate ] |
      (FLATTEN({ table_name | (query) }, field_name_or_alias)) |
      table_wildcard_function
    }

from_item:
    { table_name | (query) } [ [ AS ] alias ]

join_type:
    { INNER | [ FULL ] [ OUTER ] | RIGHT [ OUTER ] | LEFT [ OUTER ] | CROSS }

join_predicate:
    field_from_one_side_of_the_join = field_from_the_other_side_of_the_join [ AND ...]

expression:
    {
      literal_value |
      field_name_or_alias |
      function_call
    }

bool_expression:
    {
      expression_which_results_in_a_boolean_value |
      bool_expression AND bool_expression |
      bool_expression OR bool_expression |
      NOT bool_expression
    }

thought סימון:

  • סוגריים מרובעים "[ ]" מציינים סעיפים אופציונליים.
  • סוגריים מסולסלים "{ }" תוחמים קבוצה של אפשרויות.
  • הקו האנכי "|" מציין את האופרטור הלוגי OR.
  • פסיק או מילת מפתח שאחריהם מופיעות שלוש נקודות בתוך סוגריים מרובעים "[, ... ]" מציינים שהפריט הקודם יכול לחזור על עצמו ברשימה עם התו המפריד שצוין.
  • סוגריים "( )" מציינים סוגריים מילוליים.

הפונקציות והאופרטורים שנתמכים

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

#legacySQL
SELECT country, SUM(clicks) FROM table GROUP BY country;
פונקציות צבירה
AVG() הפונקציה מחזירה את הממוצע של הערכים בקבוצה של שורות ...
BIT_AND() מחזירה את התוצאה של פעולת AND ברמת הביט ...
BIT_OR() מחזירה את התוצאה של פעולת OR ...
BIT_XOR() הפונקציה מחזירה את התוצאה של פעולת XOR ...
CORR() הפונקציה מחזירה את מקדם המתאם של פירסון של קבוצת זוגות מספרים.
COUNT() הפונקציה מחזירה את המספר הכולל של הערכים ...
COUNT([DISTINCT]) הפונקציה מחזירה את המספר הכולל של ערכים שאינם NULL ...
COVAR_POP() מחשבת את השונות המשותפת של האוכלוסייה של הערכים ...
COVAR_SAMP() מחשבת את השונות המשותפת של המדגם של הערכים ...
EXACT_COUNT_DISTINCT() הפונקציה מחזירה את המספר המדויק של ערכים ייחודיים שאינם NULL בשדה שצוין.
FIRST() הפונקציה מחזירה את הערך הרציף הראשון בהיקף הפונקציה.
GROUP_CONCAT() משרשרת כמה מחרוזות למחרוזת אחת ...
GROUP_CONCAT_UNQUOTED() משרשרת כמה מחרוזות למחרוזת אחת… לא תוסיף מירכאות כפולות…
LAST() הפונקציה מחזירה את הערך העוקב האחרון ...
MAX() מחזירה את הערך המקסימלי ...
MIN() מחזירה את הערך המינימלי ...
NEST() צובר את כל הערכים בהיקף הצבירה הנוכחי לשדה חוזר.
NTH() מחזירה את הערך ה-n ברצף ...
QUANTILES() מחשב ערכי מינימום, מקסימום וקוונטילים משוערים ...
STDDEV() מחזירה את סטיית התקן ...
STDDEV_POP() מחשבת את סטיית התקן של האוכלוסייה ...
STDDEV_SAMP() מחשבת את סטיית התקן של המדגם ...
SUM() הפונקציה מחזירה את הסכום הכולל של הערכים ...
TOP() ... COUNT(*) הפונקציה מחזירה את הרשומות המובילות לפי תדירות, עד למספר המקסימלי שצוין ב-max_records.
UNIQUE() מחזירה את קבוצת הערכים הייחודיים שאינם NULL…
VARIANCE() מחשבת את השונות של הערכים ...
VAR_POP() מחשבת את שונות האוכלוסייה של הערכים ...
VAR_SAMP() מחשבת את השונות של המדגם לפי הערכים ...
אופרטורים אריתמטיים
+ הוספה
- חיסור
* כפל
/ חטיבה
% מודולו
פונקציות סיביות
& AND ברמת הביט
| Bitwise OR
^ XOR ברמת הביט
<< הזזה שמאלה ברמת הביט
>> הזזה ימינה ברמת הביט
~ NOT ברמת הביט
BIT_COUNT() הפונקציה מחזירה את מספר הביטים ...
פונקציות המרה
BOOLEAN() המרה לערך בוליאני.
BYTES() המרה לבייטים.
CAST(expr AS type) הפונקציה ממירה את expr למשתנה מסוג type.
FLOAT() המרה ל-double.
HEX_STRING() המרה למחרוזת הקסדצימלית.
INTEGER() המרה למספר שלם.
STRING() המרה למחרוזת.
פונקציות השוואה
expr1 = expr2 הפונקציה מחזירה את הערך true אם הביטויים שווים.
expr1 != expr2
expr1 <> expr2 		הפונקציה מחזירה את הערך true אם הביטויים לא שווים.
expr1 > expr2 הפונקציה מחזירה את הערך true אם expr1 גדול מ-expr2.
expr1 < expr2 הפונקציה מחזירה את הערך true אם הערך של expr1 קטן מ-expr2.
expr1 >= expr2 הפונקציה מחזירה את הערך true אם הערך של expr1 גדול מ-expr2 או שווה לו.
expr1 <= expr2 הפונקציה מחזירה את הערך true אם הערך של expr1 קטן מ-expr2 או שווה לו.
expr1 BETWEEN expr2 AND expr3 הפונקציה מחזירה את הערך true אם הערך של expr1 הוא בין expr2 ל-expr3, כולל.
expr IS NULL הפונקציה מחזירה את הערך true אם הערך של expr הוא NULL.
expr IN() הפונקציה מחזירה את הערך true אם expr תואם לערך expr1, expr2 או לכל ערך בסוגריים.
COALESCE() הפונקציה מחזירה את הארגומנט הראשון שהוא לא NULL.
GREATEST() הפונקציה מחזירה את הפרמטר numeric_expr הגדול ביותר.
IFNULL() אם הארגומנט הוא לא null, הפונקציה מחזירה את הארגומנט.
IS_INF() הפונקציה מחזירה את הערך true אם הערך הוא אינסוף חיובי או שלילי.
IS_NAN() הפונקציה מחזירה את הערך true אם הארגומנט הוא NaN.
IS_EXPLICITLY_DEFINED() הוצא משימוש: צריך להשתמש ב-expr IS NOT NULL במקום זאת.
LEAST() הפונקציה מחזירה את הפרמטר numeric_expr של הארגומנט הקטן ביותר.
NVL() אם הערך של expr הוא לא null, הפונקציה מחזירה את expr. אחרת, היא מחזירה את null_default.
פונקציות של תאריך ושעה
CURRENT_DATE() הפונקציה מחזירה את התאריך הנוכחי בפורמט %Y-%m-%d.
CURRENT_TIME() הפונקציה מחזירה את השעה הנוכחית בשרת בפורמט %H:%M:%S.
CURRENT_TIMESTAMP() הפונקציה מחזירה את השעה הנוכחית בשרת בפורמט %Y-%m-%d %H:%M:%S.
DATE() הפונקציה מחזירה את התאריך בפורמט %Y-%m-%d.
DATE_ADD() מוסיפה את המרווח שצוין לסוג נתונים TIMESTAMP.
DATEDIFF() הפונקציה מחזירה את מספר הימים בין שני סוגי נתונים של TIMESTAMP.
DAY() הפונקציה מחזירה את היום בחודש כמספר שלם בין 1 ל-31.
DAYOFWEEK() הפונקציה מחזירה את היום בשבוע כמספר שלם בין 1 (יום ראשון) ל-7 (שבת).
DAYOFYEAR() הפונקציה מחזירה את היום בשנה כמספר שלם בין 1 ל-366.
FORMAT_UTC_USEC() הפונקציה מחזירה חותמת זמן של מערכת UNIX בפורמט YYYY-MM-DD HH:MM:SS.uuuuuu.
HOUR() מחזירה את השעה של TIMESTAMP כמספר שלם בין 0 ל-23.
MINUTE() הפונקציה מחזירה את הדקות של TIMESTAMP כמספר שלם בין 0 ל-59.
MONTH() הפונקציה מחזירה את החודש של TIMESTAMP כמספר שלם בין 1 ל-12.
MSEC_TO_TIMESTAMP() ממירה חותמת זמן של UNIX באלפיות השנייה ל-TIMESTAMP.
NOW() הפונקציה מחזירה את חותמת הזמן הנוכחית של מערכת UNIX במיקרו-שניות.
PARSE_UTC_USEC() הפונקציה ממירה מחרוזת תאריך לחותמת זמן של UNIX במיקרו-שניות.
QUARTER() הפונקציה מחזירה את הרבעון בשנה של TIMESTAMP כמספר שלם בין 1 ל-4.
SEC_TO_TIMESTAMP() ממירה חותמת זמן של UNIX בשניות ל-TIMESTAMP.
SECOND() הפונקציה מחזירה את השניות של TIMESTAMP כמספר שלם בין 0 ל-59.
STRFTIME_UTC_USEC() הפונקציה מחזירה מחרוזת תאריך בפורמט date_format_str.
TIME() מחזירה TIMESTAMP בפורמט %H:%M:%S.
TIMESTAMP() המרת מחרוזת תאריך ל-TIMESTAMP.
TIMESTAMP_TO_MSEC() הפונקציה ממירה TIMESTAMP לחותמת זמן של UNIX באלפיות השנייה.
TIMESTAMP_TO_SEC() ממירה TIMESTAMP לחותמת זמן של UNIX בשניות.
TIMESTAMP_TO_USEC() הפונקציה ממירה TIMESTAMP לחותמת זמן של UNIX במיקרו-שניות.
USEC_TO_TIMESTAMP() ממירה חותמת זמן של UNIX במיקרו-שניות ל-TIMESTAMP.
UTC_USEC_TO_DAY() הפונקציה מעבירה חותמת זמן של מערכת UNIX במיקרו-שניות לתחילת היום שבו היא מתרחשת.
UTC_USEC_TO_HOUR() הפונקציה מעבירה את חותמת הזמן של מערכת UNIX במיקרו-שניות לתחילת השעה שבה היא מתרחשת.
UTC_USEC_TO_MONTH() הפונקציה מעבירה את חותמת הזמן של מערכת UNIX במיקרו-שניות לתחילת החודש שבו היא מתרחשת.
UTC_USEC_TO_WEEK() הפונקציה מחזירה חותמת זמן של UNIX במיקרו-שניות שמייצגת יום בשבוע.
UTC_USEC_TO_YEAR() הפונקציה מחזירה חותמת זמן של UNIX במיקרו-שניות שמייצגת את השנה.
WEEK() הפונקציה מחזירה את השבוע של TIMESTAMP כמספר שלם בין 1 ל-53.
YEAR() מחזירה את השנה של TIMESTAMP.
פונקציות IP
FORMAT_IP() ממירה 32 ביטים פחות משמעותיים של integer_value למחרוזת של כתובת IPv4 שקריאה לאנשים.
PARSE_IP() הפונקציה ממירה מחרוזת שמייצגת כתובת IPv4 לערך של מספר שלם ללא סימן.
FORMAT_PACKED_IP() מחזירה כתובת IP קריאה לאנשים בפורמט 10.1.5.23 או 2620:0:1009:1:216:36ff:feef:3f.
PARSE_PACKED_IP() הפונקציה מחזירה כתובת IP ב-BYTES.
פונקציות JSON
JSON_EXTRACT() בוחרת ערך לפי הביטוי של JSONPath ומחזירה מחרוזת JSON.
JSON_EXTRACT_SCALAR() בוחרת ערך לפי הביטוי JSONPath ומחזירה סקלר JSON.
אופרטורים לוגיים
expr AND expr הפונקציה מחזירה את הערך true אם שני הביטויים נכונים.
expr OR expr הפונקציה מחזירה את הערך true אם אחד מהביטויים או שניהם נכונים.
NOT expr הפונקציה מחזירה את הערך true אם הביטוי לא נכון.
פונקציות מתמטיות
ABS() הפונקציה מחזירה את הערך המוחלט של הארגומנט.
ACOS() מחזירה את הארק-קוסינוס של הארגומנט.
ACOSH() הפונקציה מחזירה את הקוסינוס ההיפרבולי ההפוך של הארגומנט.
ASIN() מחזירה את הארקסינוס של הארגומנט.
ASINH() מחזירה את הסינוס ההיפרבולי ההפוך של הארגומנט.
ATAN() מחזירה את הארק-טנגנס של הארגומנט.
ATANH() מחזירה את הטנגנס ההיפרבולי ההפוך של הארגומנט.
ATAN2() מחזירה את הארק-טנגנס של שני הארגומנטים.
CEIL() הפונקציה מעגלת את הארגומנט כלפי מעלה למספר השלם הקרוב ביותר ומחזירה את הערך המעוגל.
COS() מחזירה את הקוסינוס של הארגומנט.
COSH() מחזירה את הקוסינוס ההיפרבולי של הארגומנט.
DEGREES() המרת רדיאנים למעלות.
EXP() הפונקציה מחזירה את e בחזקת הארגומנט.
FLOOR() מעגלת את הארגומנט כלפי מטה למספר השלם הקרוב ביותר.
LN()
LOG() 		מחזירה את הלוגריתם הטבעי של הארגומנט.
LOG2() מחזירה את הלוגריתם של הארגומנט לפי בסיס 2.
LOG10() מחזירה את הלוגריתם של הארגומנט לפי בסיס 10.
PI() מחזירה את הקבוע π.
POW() הפונקציה מחזירה את הארגומנט הראשון בחזקת הארגומנט השני.
RADIANS() המרת מעלות לרדיאנים.
RAND() מחזירה ערך אקראי מסוג מספר ממשי (float) בטווח 0.0 <= ערך < 1.0.
ROUND() מעגלת את הארגומנט למעלה או למטה למספר השלם הקרוב ביותר.
SIN() מחזירה את הסינוס של הארגומנט.
SINH() מחזירה את הסינוס ההיפרבולי של הארגומנט.
SQRT() מחזירה את השורש הריבועי של הביטוי.
TAN() מחזירה את הטנגנס של הארגומנט.
TANH() מחזירה את הטנגנס ההיפרבולי של הארגומנט.
פונקציות של ביטויים רגולריים
REGEXP_MATCH() הפונקציה מחזירה את הערך True אם הארגומנט תואם לביטוי הרגולרי.
REGEXP_EXTRACT() הפונקציה מחזירה את החלק של הארגומנט שתואם לקבוצה לחילוץ בתוך הביטוי הרגולרי.
REGEXP_REPLACE() החלפת מחרוזת משנה שתואמת לביטוי רגולרי.
פונקציות של מחרוזות
CONCAT() הפונקציה מחזירה את השרשור של שתי מחרוזות או יותר, או NULL אם אחד מהערכים הוא NULL.
expr CONTAINS 'str' הפונקציה מחזירה את הערך true אם expr מכיל את ארגומנט המחרוזת שצוין.
INSTR() הפונקציה מחזירה את האינדקס מבוסס-האחד של המופע הראשון של מחרוזת.
LEFT() הפונקציה מחזירה את התווים השמאליים ביותר במחרוזת.
LENGTH() הפונקציה מחזירה את אורך המחרוזת.
LOWER() הפונקציה מחזירה את המחרוזת המקורית עם כל התווים באותיות קטנות.
LPAD() הוספת תווים משמאל למחרוזת.
LTRIM() מסירה תווים מהצד השמאלי של מחרוזת.
REPLACE() החלפת כל המופעים של מחרוזת משנה.
RIGHT() הפונקציה מחזירה את התווים הכי שמאליים במחרוזת.
RPAD() הוספת תווים לצד שמאל של מחרוזת.
RTRIM() הפונקציה מסירה תווים נגררים מהצד השמאלי של מחרוזת.
SPLIT() פיצול מחרוזת למחרוזות משנה חוזרות.
SUBSTR() הפונקציה מחזירה מחרוזת משנה ...
UPPER() הפונקציה מחזירה את המחרוזת המקורית כשכל התווים בה הם אותיות רישיות.
פונקציות של תווים כלליים לחיפוש בטבלאות
TABLE_DATE_RANGE() הרצת שאילתות על כמה טבלאות יומיות שכוללות טווח תאריכים.
TABLE_DATE_RANGE_STRICT() מריץ שאילתות על כמה טבלאות יומיות שכוללות טווח תאריכים, בלי תאריכים חסרים.
TABLE_QUERY() שליחת שאילתות לטבלאות שהשמות שלהן תואמים לפרדיקט שצוין.
פונקציות של כתובות URL
HOST() הפונקציה מקבלת כתובת URL ומחזירה את שם המארח כמחרוזת.
DOMAIN() הפונקציה מקבלת כתובת URL ומחזירה את הדומיין כמחרוזת.
TLD() מקבלת כתובת URL ומחזירה את הדומיין ברמה העליונה ואת הדומיין של המדינה בכתובת ה-URL.
פונקציות אנליטיות (window functions)
AVG()
COUNT(*)
COUNT([DISTINCT])
MAX()
MIN()
STDDEV()
SUM()
 		אותה פעולה כמו פונקציות הצבירה התואמות, אבל החישוב מתבצע בחלון שמוגדר על ידי משפט OVER.
CUME_DIST() הפונקציה מחזירה ערך מסוג double שמציין את ההתפלגות המצטברת של ערך בקבוצת ערכים ...
DENSE_RANK() הפונקציה מחזירה את הדירוג של ערך מסוים בקבוצת ערכים, כמספר שלם.
FIRST_VALUE() הפונקציה מחזירה את הערך הראשון של השדה שצוין בחלון.
LAG() מאפשרת לקרוא נתונים משורה קודמת בתוך חלון.
LAST_VALUE() הפונקציה מחזירה את הערך האחרון של השדה שצוין בחלון.
LEAD() מאפשרת לקרוא נתונים משורה עוקבת בתוך חלון.
NTH_VALUE() הפונקציה מחזירה את הערך של <expr> במיקום <n> של מסגרת החלון ...
NTILE() מחלקת את החלון למספר הקטגוריות שצוין.
PERCENT_RANK() הפונקציה מחזירה את הדירוג של השורה הנוכחית, ביחס לשורות האחרות במחיצה.
PERCENTILE_CONT() הפונקציה מחזירה ערך שעבר אינטרפולציה, שימופה לארגומנט האחוזון ביחס לחלון ...
PERCENTILE_DISC() הפונקציה מחזירה את הערך הקרוב ביותר לאחוזון של הארגומנט בחלון.
RANK() הפונקציה מחזירה את הדירוג של ערך מסוים בקבוצת ערכים, כמספר שלם.
RATIO_TO_REPORT() הפונקציה מחזירה את היחס בין כל ערך לסכום הערכים.
ROW_NUMBER() הפונקציה מחזירה את מספר השורה הנוכחית של תוצאת השאילתה בחלון.
פונקציות אחרות
CASE WHEN ... THEN אפשר להשתמש ב-CASE כדי לבחור בין שני ביטויים חלופיים או יותר בשאילתה.
CURRENT_USER() מחזירה את כתובת האימייל של המשתמש שמריץ את השאילתה.
EVERY() הפונקציה מחזירה את הערך True אם הארגומנט נכון לכל ערכי הקלט שלו.
FROM_BASE64() ממירה את מחרוזת הקלט בקידוד base-64 לפורמט BYTES.
HASH() מחשבת ומחזירה ערך גיבוב (hash) חתום של 64 ביט ...
FARM_FINGERPRINT() מחשבת ומחזירה ערך טביעת אצבע חתום של 64 ביט ...
IF() אם הארגומנט הראשון נכון, הפונקציה מחזירה את הארגומנט השני. אחרת, היא מחזירה את הארגומנט השלישי.
POSITION() הפונקציה מחזירה את המיקום הסידורי של הארגומנט, החל מ-1.
SHA1() הפונקציה מחזירה גיבוב (hash) של SHA1 בפורמט BYTES.
SOME() הפונקציה מחזירה את הערך True אם הארגומנט נכון לפחות לאחד מהקלטים שלו.
TO_BASE64() הפונקציה ממירה את הארגומנט BYTES למחרוזת בקידוד Base64.

פונקציות צבירה

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

  • צבירת נתונים בטבלה

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

    SELECT COUNT(f1) FROM ds.Table;

  • צבירת נתונים קבוצתית

    משתמשת בפונקציית צבירה ובסעיף GROUP BY שמציין שדה לא נצבר כדי לסכם שורות לפי קבוצה. לדוגמה:

    SELECT COUNT(f1) FROM ds.Table GROUP BY b1;

    הפונקציה TOP מייצגת מקרה מיוחד של צבירת נתונים קבוצתית.

  • צבירת נתונים לפי היקף

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

    SELECT COUNT(m1.f2) WITHIN RECORD FROM Table;

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

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

  • כינוי בשאילתת משנה. ההגבלה מצוינת בסעיף WHERE החיצוני.

    #legacySQL
SELECT corpus, count_corpus_words
FROM
  (SELECT corpus, count(word) AS count_corpus_words
  FROM [bigquery-public-data:samples.shakespeare]
  GROUP BY corpus) AS sub_shakespeare
WHERE count_corpus_words > 4000

  • כינוי במשפט HAVING.

    #legacySQL
SELECT corpus, count(word) AS count_corpus_words
FROM [bigquery-public-data:samples.shakespeare]
GROUP BY corpus
HAVING count_corpus_words > 4000;

אפשר גם להפנות לכינוי בסעיפים GROUP BY או ORDER BY.

תחביר

פונקציות צבירה
AVG() הפונקציה מחזירה את הממוצע של הערכים בקבוצה של שורות ...
BIT_AND() מחזירה את התוצאה של פעולת AND ברמת הביט ...
BIT_OR() מחזירה את התוצאה של פעולת OR ...
BIT_XOR() הפונקציה מחזירה את התוצאה של פעולת XOR ...
CORR() הפונקציה מחזירה את מקדם המתאם של פירסון של קבוצת זוגות מספרים.
COUNT() הפונקציה מחזירה את המספר הכולל של הערכים ...
COUNT([DISTINCT]) הפונקציה מחזירה את המספר הכולל של ערכים שאינם NULL ...
COVAR_POP() מחשבת את השונות המשותפת של האוכלוסייה של הערכים ...
COVAR_SAMP() מחשבת את השונות המשותפת של המדגם של הערכים ...
EXACT_COUNT_DISTINCT() הפונקציה מחזירה את המספר המדויק של ערכים ייחודיים שאינם NULL בשדה שצוין.
FIRST() הפונקציה מחזירה את הערך הרציף הראשון בהיקף הפונקציה.
GROUP_CONCAT() משרשרת כמה מחרוזות למחרוזת אחת ...
GROUP_CONCAT_UNQUOTED() משרשרת כמה מחרוזות למחרוזת אחת… לא תוסיף מירכאות כפולות…
LAST() הפונקציה מחזירה את הערך העוקב האחרון ...
MAX() מחזירה את הערך המקסימלי ...
MIN() מחזירה את הערך המינימלי ...
NEST() צובר את כל הערכים בהיקף הצבירה הנוכחי לשדה חוזר.
NTH() מחזירה את הערך ה-n ברצף ...
QUANTILES() מחשב ערכי מינימום, מקסימום וקוונטילים משוערים ...
STDDEV() מחזירה את סטיית התקן ...
STDDEV_POP() מחשבת את סטיית התקן של האוכלוסייה ...
STDDEV_SAMP() מחשבת את סטיית התקן של המדגם ...
SUM() הפונקציה מחזירה את הסכום הכולל של הערכים ...
TOP() ... COUNT(*) הפונקציה מחזירה את הרשומות המובילות לפי תדירות, עד למספר המקסימלי שצוין ב-max_records.
UNIQUE() מחזירה את קבוצת הערכים הייחודיים שאינם NULL…
VARIANCE() מחשבת את השונות של הערכים ...
VAR_POP() מחשבת את שונות האוכלוסייה של הערכים ...
VAR_SAMP() מחשבת את השונות של המדגם לפי הערכים ...
AVG(numeric_expr)
הפונקציה מחזירה את הממוצע של הערכים בקבוצת שורות שחושבו על ידי numeric_expr. שורות עם ערך NULL לא נכללות בחישוב.
BIT_AND(numeric_expr)
הפונקציה מחזירה את התוצאה של פעולת AND ברמת הביט בין כל מופע של numeric_expr בכל השורות. המערכת מתעלמת מהערכים של NULL. הפונקציה הזו מחזירה את הערך NULL אם כל המופעים של numeric_expr שווים ל-NULL.
BIT_OR(numeric_expr)
הפונקציה מחזירה את התוצאה של פעולת OR ברמת הביט בין כל מופע של numeric_expr בכל השורות. המערכת מתעלמת מהערכים של NULL. הפונקציה הזו מחזירה את הערך NULL אם כל המופעים של numeric_expr שווים ל-NULL.
BIT_XOR(numeric_expr)
הפונקציה מחזירה את התוצאה של פעולת XOR ברמת הביט בין כל מופע של numeric_expr בכל השורות. המערכת מתעלמת מהערכים של NULL. הפונקציה הזו מחזירה את הערך NULL אם כל המופעים של numeric_expr שווים ל-NULL.
CORR(numeric_expr, numeric_expr)
הפונקציה מחזירה את מקדם המתאם של פירסון של קבוצת זוגות מספרים.
COUNT(*)
הפונקציה מחזירה את המספר הכולל של הערכים (NULL ולא NULL) בהיקף של הפונקציה. אלא אם משתמשים ב-COUNT(*) עם הפונקציה TOP, עדיף לציין במפורש את השדה שרוצים לספור.
COUNT([DISTINCT] field [, n])
הפונקציה מחזירה את המספר הכולל של ערכים שאינם NULL בהיקף הפונקציה.

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

כדי לקבל תשובה מדויקת, צריך להשתמש ב-EXACT_COUNT_DISTINCT().

אם אתם צריכים דיוק גבוה יותר מ-COUNT(DISTINCT), אתם יכולים לציין פרמטר שני, n, שמספק את הסף שמתחתיו מובטחות תוצאות מדויקות. כברירת מחדל, הערך של n הוא 1,000, אבל אם תזינו ערך גדול יותר n, תקבלו תוצאות מדויקות של COUNT(DISTINCT) עד לערך הזה של n. עם זאת, ערכים גדולים יותר של n יפחיתו את יכולת ההתאמה לעומס (scalability) של האופרטור הזה, ועלולים להאריך באופן משמעותי את זמן הביצוע של השאילתה או לגרום לה להיכשל.

כדי לחשב את המספר המדויק של ערכים נפרדים, משתמשים בפונקציה EXACT_COUNT_DISTINCT. לחלופין, כדי להשתמש בגישה שניתנת להרחבה, אפשר להשתמש ב-GROUP EACH BY בשדות הרלוונטיים ואז להחיל את COUNT(*). הגישה של GROUP EACH BY ניתנת יותר להתאמה לעומס, אבל יכול להיות שהיא תגרום לפגיעה קלה בביצועים בהתחלה.

COVAR_POP(numeric_expr1, numeric_expr2)
מחשבת את השונות המשותפת של האוכלוסייה של הערכים שמחושבים על ידי numeric_expr1 ו-numeric_expr2.
COVAR_SAMP(numeric_expr1, numeric_expr2)
מחשבת את השונות המשותפת של המדגם של הערכים שמחושבים על ידי numeric_expr1 ו-numeric_expr2.
EXACT_COUNT_DISTINCT(field)
הפונקציה מחזירה את המספר המדויק של ערכים ייחודיים שאינם NULL בשדה שצוין. כדי לשפר את יכולת ההתאמה לעומס (scalability) ואת הביצועים, השתמשו ב-COUNT(DISTINCT field).
FIRST(expr)
‫
מחזירה את הערך הרציף הראשון בהיקף הפונקציה.
GROUP_CONCAT('str' [, separator])

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

אם מחרוזת בנתוני המקור מכילה תו של מירכאות כפולות, הפונקציה GROUP_CONCAT מחזירה את המחרוזת עם מירכאות כפולות נוספות. לדוגמה, המחרוזת a"b תוחזר בתור "a""b". אם אתם מעדיפים שהמחרוזות האלה לא יוחזרו עם מירכאות כפולות, השתמשו ב-GROUP_CONCAT_UNQUOTED.

לדוגמה:

#legacySQL
SELECT
  GROUP_CONCAT(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);
GROUP_CONCAT_UNQUOTED('str' [, separator])

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

בניגוד לפונקציה GROUP_CONCAT, הפונקציה הזו לא תוסיף מירכאות כפולות לערכים שמוחזרים וכוללים תו של מירכאות כפולות. לדוגמה, המחרוזת a"b תוחזר בתור a"b.

לדוגמה:

#legacySQL
SELECT
  GROUP_CONCAT_UNQUOTED(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);
LAST(field)
הפונקציה מחזירה את הערך הרציף האחרון בהיקף הפונקציה.
MAX(field)
הפונקציה מחזירה את הערך המקסימלי בהיקף שלה.
MIN(field)
הפונקציה מחזירה את הערך המינימלי בהיקף שלה.
NEST(expr)

צובר את כל הערכים בהיקף הצבירה הנוכחי לשדה חוזר. לדוגמה, השאילתה "SELECT x, NEST(y) FROM ... GROUP BY x" מחזירה רשומת פלט אחת לכל ערך x נפרד, ומכילה שדה חוזר לכל ערכי y שמשויכים ל-x בקלט של השאילתה. הפונקציה NEST דורשת סעיף GROUP BY.

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

NTH(n, field)
‫
מחזירה את הערך העוקב ה-n בהיקף הפונקציה, כאשר n הוא קבוע. הפונקציה NTH מתחילה לספור מ-1, כך שאין איבר אפס. אם בהיקף הפונקציה יש פחות מ-n ערכים, הפונקציה מחזירה את הערך NULL.
QUANTILES(expr[, buckets])

מחשב את הערכים המשוערים של המינימום, המקסימום והאחוזונים של ביטוי הקלט. המערכת מתעלמת מערכי קלט של NULL. קלט ריק או קלט שמכיל רק NULL יגרום לפלט של NULL. מספר האחוזונים המחושבים נשלט על ידי הפרמטר האופציונלי buckets, שכולל את המינימום והמקסימום בספירה. כדי לחשב N-tiles משוערים, משתמשים ב-N+1 buckets. ערך ברירת המחדל של buckets הוא 100. (הערה: ערך ברירת המחדל 100 לא מעריך אחוזונים. כדי להעריך אחוזונים, צריך להשתמש ב-101 buckets לפחות.) אם מציינים את buckets באופן מפורש, הערך חייב להיות לפחות 2.

השגיאה החלקית לכל אחוזון היא אפסילון = 1 חלקי buckets, כלומר השגיאה קטנה ככל שמספר הקטגוריות גדל. לדוגמה:

QUANTILES(<expr>, 2) # computes min and max with 50% error.
QUANTILES(<expr>, 3) # computes min, median, and max with 33% error.
QUANTILES(<expr>, 5) # computes quartiles with 25% error.
QUANTILES(<expr>, 11) # computes deciles with 10% error.
QUANTILES(<expr>, 21) # computes vigintiles with 5% error.
QUANTILES(<expr>, 101) # computes percentiles with 1% error.

אפשר להשתמש בפונקציה NTH כדי לבחור קוונטיל מסוים, אבל חשוב לזכור שהפונקציה NTH מבוססת על 1, ושהפונקציה QUANTILES מחזירה את המינימום (הקוונטיל ה-0) במיקום הראשון, ואת המקסימום (האחוזון ה-100 או ה-N-tile ה-N) במיקום האחרון. לדוגמה, NTH(11, QUANTILES(expr, 21)) מעריך את החציון של expr, ואילו NTH(20, QUANTILES(expr, 21)) מעריך את האחוזון ה-95 של expr. בשני האומדנים יש מרווח טעות של 5%.

כדי לשפר את הדיוק, צריך להשתמש ביותר קטגוריות. לדוגמה, כדי לצמצם את מרווח הטעות של החישובים הקודמים מ-5% ל-0.1%, משתמשים ב-1,001 קטגוריות במקום ב-21, ומשנים את הארגומנט לפונקציה NTH בהתאם. כדי לחשב את החציון עם שגיאה של 0.1%, משתמשים ב-NTH(501, QUANTILES(expr, 1001)). כדי לחשב את האחוזון ה-95 עם שגיאה של 0.1%, משתמשים ב-NTH(951, QUANTILES(expr, 1001)).

STDDEV(numeric_expr)
הפונקציה מחזירה את סטיית התקן של הערכים שחושבו על ידי numeric_expr. שורות עם ערך NULL לא נכללות בחישוב. הפונקציה STDDEV היא כינוי של STDDEV_SAMP.
STDDEV_POP(numeric_expr)
מחשבת את סטיית התקן של האוכלוסייה של הערך שמחושב על ידי numeric_expr. אפשר להשתמש בפונקציה STDDEV_POP() כדי לחשב את סטיית התקן של קבוצת נתונים שכוללת את כל האוכלוסייה הרלוונטית. אם מערך הנתונים כולל רק מדגם מייצג של האוכלוסייה, צריך להשתמש במקום זאת ב-STDDEV_SAMP(). מידע נוסף על סטיית תקן של אוכלוסייה לעומת סטיית תקן של מדגם זמין במאמר על סטיית תקן בוויקיפדיה.
STDDEV_SAMP(numeric_expr)
מחשבת את סטיית התקן של המדגם של הערך שחושב על ידי numeric_expr. אפשר להשתמש בפונקציה STDDEV_SAMP() כדי לחשב את סטיית התקן של אוכלוסייה שלמה על סמך מדגם מייצג של האוכלוסייה. אם מערך הנתונים כולל את כל האוכלוסייה, צריך להשתמש בפונקציה STDDEV_POP(). מידע נוסף על סטיית תקן של אוכלוסייה לעומת סטיית תקן של מדגם זמין במאמר סטיית תקן בוויקיפדיה.
SUM(field)
‫
מחזירה את הסכום הכולל של הערכים בהיקף הפונקציה. לשימוש עם סוגי נתונים מספריים בלבד.
TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)
‫
מחזירה את הרשומות המובילות לפי תדירות, עד למספר המקסימלי שצוין ב-max_records. פרטים נוספים מופיעים בתיאור של TOP בהמשך.
UNIQUE(expr)
הפונקציה מחזירה את קבוצת הערכים הייחודיים שאינם NULL בהיקף הפונקציה, בסדר לא מוגדר. בדומה לסעיף GROUP BY גדול בלי מילת המפתח EACH, השאילתה תיכשל עם השגיאה "Resources Exceeded" אם יש יותר מדי ערכים נפרדים. עם זאת, בניגוד ל-GROUP BY, אפשר להחיל את הפונקציה UNIQUE עם צבירה ברמת ההיקף, וכך לבצע פעולה יעילה בשדות בתצוגת עץ עם מספר מוגבל של ערכים.
VARIANCE(numeric_expr)
מחשבת את השונות של הערכים שחושבו על ידי numeric_expr. שורות עם ערך NULL לא נכללות בחישוב. הפונקציה VARIANCE היא כינוי של VAR_SAMP.
VAR_POP(numeric_expr)
מחשבת את השונות של האוכלוסייה של הערכים שמחושבים על ידי numeric_expr. מידע נוסף על סטיית תקן של אוכלוסייה לעומת סטיית תקן של מדגם זמין במאמר סטיית תקן בוויקיפדיה.
VAR_SAMP(numeric_expr)
מחשבת את השונות של המדגם של הערכים שחושבו על ידי numeric_expr. מידע נוסף על סטיית תקן של אוכלוסייה לעומת סטיית תקן של מדגם זמין במאמר סטיית תקן בוויקיפדיה.

הפונקציה()TOP

‫TOP היא פונקציה שמהווה חלופה לסעיף GROUP BY. הוא משמש כתחביר פשוט של GROUP BY ... ORDER BY ... LIMIT .... באופן כללי, הפונקציה TOP פועלת מהר יותר מהשאילתה המלאה של ... GROUP BY ... ORDER BY ... LIMIT ..., אבל יכול להיות שהיא תחזיר רק תוצאות משוערות. זה התחביר של הפונקציה TOP:

TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)

כשמשתמשים ב-TOP בסעיף SELECT, צריך לכלול את COUNT(*) כאחד מהשדות.

שאילתה שמשתמשת בפונקציה TOP()‎ יכולה להחזיר רק שני שדות: השדה TOP והערך COUNT(*)‎.

field|alias
‫
השדה או הכינוי שרוצים להחזיר.
max_values
‫
[אופציונלי] המספר המקסימלי של תוצאות שיוחזרו. ברירת המחדל היא 20.
multiplier
מספר שלם חיובי שמגדיל את הערכים שמוחזרים על ידי COUNT(*) בכפולה שצוינה.

דוגמאות לשימוש בפונקציה()TOP

  • שאילתות בסיסיות לדוגמה שמשתמשות ב-TOP()

    השאילתות הבאות משתמשות ב-TOP() כדי להחזיר 10 שורות.

    דוגמה 1:

    #legacySQL
SELECT
  TOP(word, 10) as word, COUNT(*) as cnt
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS "th";

    דוגמה 2:

    #legacySQL
SELECT
  word, left(word, 3)
FROM
  (SELECT TOP(word, 10) AS word, COUNT(*)
     FROM [bigquery-public-data:samples.shakespeare]
     WHERE word CONTAINS "th");

  • השוואה בין TOP() לבין GROUP BY...ORDER BY...LIMIT

    השאילתה מחזירה, לפי הסדר, את 10 המילים הנפוצות ביותר שמכילות את האותיות "th", ואת מספר המסמכים שבהם המילים האלה מופיעות. השאילתה TOP תפעל הרבה יותר מהר:

    דוגמה בלי TOP():

    #legacySQL
SELECT
  word, COUNT(*) AS cnt
FROM
  ds.Table
WHERE
  word CONTAINS 'th'
GROUP BY
  word
ORDER BY
  cnt DESC LIMIT 10;

    דוגמה עם TOP():

    #legacySQL
SELECT
  TOP(word, 10), COUNT(*)
FROM
  ds.Table
WHERE
  word contains 'th';

  • שימוש בפרמטר multiplier.

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

    דוגמה בלי הפרמטר multiplier:

    #legacySQL
SELECT
  TOP(month,3) as month, COUNT(*) as cnt
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state = "WY";

    החזרות:

    +-------+-------+
| month |  cnt  |
+-------+-------+
|   7   | 19594 |
|   5   | 19038 |
|   8   | 19030 |
+-------+-------+

    דוגמה עם הפרמטר multiplier:

    #legacySQL
SELECT
  TOP(month,3,100) as month, COUNT(*) as cnt
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state = "WY";

    החזרות:

    +-------+---------+
| month |   cnt   |
+-------+---------+
|   7   | 1959400 |
|   5   | 1903800 |
|   8   | 1903000 |
+-------+---------+

הערה: כדי להשתמש ב-TOP, צריך לכלול את COUNT(*) בסעיף SELECT.

דוגמאות מתקדמות

  • ממוצע וסטיית תקן מקובצים לפי תנאי

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

    לדוגמה:

    #legacySQL
SELECT
  cigarette_use,
  /* Finds average and standard deviation */
  AVG(weight_pounds) baby_weight,
  STDDEV(weight_pounds) baby_weight_stdev,
  AVG(mother_age) mother_age
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year=2003 AND state='OH'
/* Group the result values by those */
/* who smoked and those who didn't.  */
GROUP BY
  cigarette_use;

  • סינון תוצאות של שאילתות באמצעות ערך נצבר

    כדי לסנן תוצאות של שאילתות באמצעות ערך נצבר (לדוגמה, סינון לפי הערך של SUM), צריך להשתמש בפונקציה HAVING. הפונקציה HAVING משווה ערך לתוצאה שנקבעה על ידי פונקציית צבירה, בניגוד לפונקציה WHERE שפועלת על כל שורה לפני הצבירה.

    לדוגמה:

    #legacySQL
SELECT
  state,
  /* If 'is_male' is True, return 'Male', */
  /* otherwise return 'Female' */
  IF (is_male, 'Male', 'Female') AS sex,
  /* The count value is aliased as 'cnt' */
  /* and used in the HAVING clause below. */
  COUNT(*) AS cnt
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state != ''
GROUP BY
  state, sex
HAVING
  cnt > 3000000
ORDER BY
  cnt DESC

    החזרות:

    +-------+--------+---------+
| state |  sex   |   cnt   |
+-------+--------+---------+
| CA    | Male   | 7060826 |
| CA    | Female | 6733288 |
| TX    | Male   | 5107542 |
| TX    | Female | 4879247 |
| NY    | Male   | 4442246 |
| NY    | Female | 4227891 |
| IL    | Male   | 3089555 |
+-------+--------+---------+

אופרטורים אריתמטיים

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

תחביר

אופרטור תיאור דוגמה
+ הוספה

SELECT 6 + (5 - 1);

התוצאה: 10
- חיסור

SELECT 6 - (4 + 1);

הפונקציה מחזירה: 1
* כפל

SELECT 6 * (5 - 1);

החזרות: 24
/ חטיבה

SELECT 6 / (2 + 2);

התוצאה: 1.5
% מודולו

SELECT 6 % (2 + 2);

הפונקציה מחזירה: 2

פונקציות סיביות

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

שלוש פונקציות נוספות של פעולות על סיביות, BIT_AND, ‏ BIT_OR ו-BIT_XOR, מתועדות בפונקציות צבירה.

תחביר

אופרטור תיאור דוגמה
& AND ברמת הביט

SELECT (1 + 3) & 1

החזרות: 0
| Bitwise OR

SELECT 24 | 12

התוצאה: 28
^ XOR ברמת הביט

SELECT 1 ^ 0

הפונקציה מחזירה: 1
<< הזזה שמאלה ברמת הביט

SELECT 1 << (2 + 2)

הפונקציה מחזירה: 16
>> הזזה ימינה ברמת הביט

SELECT (6 + 2) >> 2

הפונקציה מחזירה: 2
~ NOT ברמת הביט

SELECT ~2

התוצאה: -3
BIT_COUNT(<numeric_expr>)

הפונקציה מחזירה את מספר הביטים שמוגדרים ב-<numeric_expr>.

SELECT BIT_COUNT(29);

החזרות: 4

פונקציות המרה

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

תחביר

פונקציות המרה
BOOLEAN() המרה לערך בוליאני.
BYTES() המרה לבייטים.
CAST(expr AS type) הפונקציה ממירה את expr למשתנה מסוג type.
FLOAT() המרה ל-double.
HEX_STRING() המרה למחרוזת הקסדצימלית.
INTEGER() המרה למספר שלם.
STRING() המרה למחרוזת.
BOOLEAN(<numeric_expr>)
  • הפונקציה מחזירה את הערך true אם הערך של <numeric_expr> הוא לא 0 ולא NULL.
  • הפונקציה מחזירה את הערך false אם הערך של <numeric_expr> הוא 0.
  • הפונקציה מחזירה את הערך NULL אם הערך של <numeric_expr> הוא NULL.
BYTES(string_expr)
הפונקציה מחזירה את string_expr כערך מסוג bytes.
CAST(expr AS type)
הפונקציה ממירה את expr למשתנה מסוג type.
FLOAT(expr)
הפונקציה מחזירה את expr בתור מספר מסוג double. הפרמטר expr יכול להיות מחרוזת כמו '45.78', אבל הפונקציה מחזירה NULL לערכים לא מספריים.
HEX_STRING(numeric_expr)
‫
הפונקציה מחזירה את numeric_expr כמחרוזת הקסדצימלית.
INTEGER(expr)
ממירה את expr למספר שלם של 64 ביט.
  • הפונקציה מחזירה NULL אם expr הוא מחרוזת שלא תואמת לערך של מספר שלם.
  • הפונקציה מחזירה את מספר המיקרו-שניות מאז ראשית זמן יוניקס (Unix epoch) אם expr הוא חותמת זמן.
STRING(numeric_expr)
‫
הפונקציה מחזירה את numeric_expr כמחרוזת.

פונקציות השוואה

פונקציות השוואה מחזירות את הערך true או false, בהתאם לסוגי ההשוואות הבאים:

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

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

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

תחביר

פונקציות השוואה
expr1 = expr2 הפונקציה מחזירה את הערך true אם הביטויים שווים.
expr1 != expr2
expr1 <> expr2 		הפונקציה מחזירה את הערך true אם הביטויים לא שווים.
expr1 > expr2 הפונקציה מחזירה את הערך true אם expr1 גדול מ-expr2.
expr1 < expr2 הפונקציה מחזירה את הערך true אם הערך של expr1 קטן מ-expr2.
expr1 >= expr2 הפונקציה מחזירה את הערך true אם הערך של expr1 גדול מ-expr2 או שווה לו.
expr1 <= expr2 הפונקציה מחזירה את הערך true אם הערך של expr1 קטן מ-expr2 או שווה לו.
expr1 BETWEEN expr2 AND expr3 הפונקציה מחזירה את הערך true אם הערך של expr1 הוא בין expr2 ל-expr3, כולל.
expr IS NULL הפונקציה מחזירה את הערך true אם הערך של expr הוא NULL.
expr IN() הפונקציה מחזירה את הערך true אם expr תואם לערך expr1, expr2 או לכל ערך בסוגריים.
COALESCE() הפונקציה מחזירה את הארגומנט הראשון שהוא לא NULL.
GREATEST() הפונקציה מחזירה את הפרמטר numeric_expr הגדול ביותר.
IFNULL() אם הארגומנט הוא לא null, הפונקציה מחזירה את הארגומנט.
IS_INF() הפונקציה מחזירה את הערך true אם הערך הוא אינסוף חיובי או שלילי.
IS_NAN() הפונקציה מחזירה את הערך true אם הארגומנט הוא NaN.
IS_EXPLICITLY_DEFINED() הוצא משימוש: צריך להשתמש ב-expr IS NOT NULL במקום זאת.
LEAST() הפונקציה מחזירה את הפרמטר numeric_expr של הארגומנט הקטן ביותר.
NVL() אם הערך של expr הוא לא null, הפונקציה מחזירה את expr. אחרת, היא מחזירה את null_default.
expr1 = expr2
הפונקציה מחזירה את הערך true אם הביטויים שווים.
expr1 != expr2
expr1 <> expr2
הפונקציה מחזירה את הערך true אם הביטויים לא שווים.
expr1 > expr2
‫
הפונקציה מחזירה את הערך true אם expr1 גדול מ-expr2.
expr1 < expr2
‫
מחזירה את הערך true אם expr1 קטן מ-expr2.
expr1 >= expr2
‫
הפונקציה מחזירה את הערך true אם expr1 גדול מ-expr2 או שווה לו.
expr1 <= expr2
‫
הפונקציה מחזירה את הערך true אם הערך של expr1 קטן מ-expr2 או שווה לו.
expr1 BETWEEN expr2 AND expr3

הפונקציה מחזירה את הערך true אם הערך של expr1 גדול מ-expr2 או שווה לו, וקטן מ-expr3 או שווה לו.

expr IS NULL
‫
מחזירה את הערך true אם הערך של expr הוא NULL.
expr IN(expr1, expr2, ...)
‫
הפונקציה מחזירה את הערך true אם expr תואם ל-expr1, ל-expr2 או לכל ערך בסוגריים. מילת המפתח IN היא קיצור יעיל ל-(expr = expr1 || expr = expr2 || ...). הביטויים שמשמשים עם מילת המפתח IN חייבים להיות קבועים ולהתאים לסוג הנתונים של expr. אפשר להשתמש בסעיף IN גם כדי ליצור semi-join ו-anti-join. מידע נוסף זמין במאמר חצי-איחוד ואיחוד-הפוך.
COALESCE(<expr1>, <expr2>, ...)
הפונקציה מחזירה את הארגומנט הראשון שהוא לא NULL.
GREATEST(numeric_expr1, numeric_expr2, ...)

הפונקציה מחזירה את הפרמטר numeric_expr הגדול ביותר. כל הפרמטרים צריכים להיות מספריים ומאותו סוג. אם אחד מהפרמטרים הוא NULL, הפונקציה מחזירה NULL.

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

SELECT GREATEST(IFNULL(a,-1), IFNULL(b,-1)) FROM (SELECT 1 as a, NULL as b);
IFNULL(expr, null_default)
אם הערך של expr הוא לא null, הפונקציה מחזירה את expr, אחרת היא מחזירה את null_default.
IS_INF(numeric_expr)
הפונקציה מחזירה את הערך true אם הערך של numeric_expr הוא אינסוף חיובי או שלילי.
IS_NAN(numeric_expr)
הפונקציה מחזירה את הערך true אם numeric_expr הוא הערך המספרי המיוחד NaN.
IS_EXPLICITLY_DEFINED(expr)

הפונקציה הזו הוצאה משימוש. במקום זאת, אתם צריכים להשתמש ב-expr IS NOT NULL.

LEAST(numeric_expr1, numeric_expr2, ...)

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

NVL(expr, null_default)
אם הערך של expr הוא לא null, הפונקציה מחזירה את expr, אחרת היא מחזירה את null_default. הפונקציה NVL היא כינוי של IFNULL.

פונקציות של תאריך ושעה

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

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

תחביר

פונקציות של תאריך ושעה
CURRENT_DATE() הפונקציה מחזירה את התאריך הנוכחי בפורמט %Y-%m-%d.
CURRENT_TIME() הפונקציה מחזירה את השעה הנוכחית בשרת בפורמט %H:%M:%S.
CURRENT_TIMESTAMP() הפונקציה מחזירה את השעה הנוכחית בשרת בפורמט %Y-%m-%d %H:%M:%S.
DATE() הפונקציה מחזירה את התאריך בפורמט %Y-%m-%d.
DATE_ADD() מוסיפה את המרווח שצוין לסוג נתונים TIMESTAMP.
DATEDIFF() הפונקציה מחזירה את מספר הימים בין שני סוגי נתונים של TIMESTAMP.
DAY() הפונקציה מחזירה את היום בחודש כמספר שלם בין 1 ל-31.
DAYOFWEEK() הפונקציה מחזירה את היום בשבוע כמספר שלם בין 1 (יום ראשון) ל-7 (שבת).
DAYOFYEAR() הפונקציה מחזירה את היום בשנה כמספר שלם בין 1 ל-366.
FORMAT_UTC_USEC() הפונקציה מחזירה חותמת זמן של מערכת UNIX בפורמט YYYY-MM-DD HH:MM:SS.uuuuuu.
HOUR() מחזירה את השעה של TIMESTAMP כמספר שלם בין 0 ל-23.
MINUTE() הפונקציה מחזירה את הדקות של TIMESTAMP כמספר שלם בין 0 ל-59.
MONTH() הפונקציה מחזירה את החודש של TIMESTAMP כמספר שלם בין 1 ל-12.
MSEC_TO_TIMESTAMP() ממירה חותמת זמן של UNIX באלפיות השנייה ל-TIMESTAMP.
NOW() הפונקציה מחזירה את חותמת הזמן הנוכחית של מערכת UNIX במיקרו-שניות.
PARSE_UTC_USEC() הפונקציה ממירה מחרוזת תאריך לחותמת זמן של UNIX במיקרו-שניות.
QUARTER() הפונקציה מחזירה את הרבעון בשנה של TIMESTAMP כמספר שלם בין 1 ל-4.
SEC_TO_TIMESTAMP() ממירה חותמת זמן של UNIX בשניות ל-TIMESTAMP.
SECOND() הפונקציה מחזירה את השניות של TIMESTAMP כמספר שלם בין 0 ל-59.
STRFTIME_UTC_USEC() הפונקציה מחזירה מחרוזת תאריך בפורמט date_format_str.
TIME() מחזירה TIMESTAMP בפורמט %H:%M:%S.
TIMESTAMP() המרת מחרוזת תאריך ל-TIMESTAMP.
TIMESTAMP_TO_MSEC() הפונקציה ממירה TIMESTAMP לחותמת זמן של UNIX באלפיות השנייה.
TIMESTAMP_TO_SEC() ממירה TIMESTAMP לחותמת זמן של UNIX בשניות.
TIMESTAMP_TO_USEC() הפונקציה ממירה TIMESTAMP לחותמת זמן של UNIX במיקרו-שניות.
USEC_TO_TIMESTAMP() ממירה חותמת זמן של UNIX במיקרו-שניות ל-TIMESTAMP.
UTC_USEC_TO_DAY() הפונקציה מעבירה חותמת זמן של מערכת UNIX במיקרו-שניות לתחילת היום שבו היא מתרחשת.
UTC_USEC_TO_HOUR() הפונקציה מעבירה את חותמת הזמן של מערכת UNIX במיקרו-שניות לתחילת השעה שבה היא מתרחשת.
UTC_USEC_TO_MONTH() הפונקציה מעבירה את חותמת הזמן של מערכת UNIX במיקרו-שניות לתחילת החודש שבו היא מתרחשת.
UTC_USEC_TO_WEEK() הפונקציה מחזירה חותמת זמן של UNIX במיקרו-שניות שמייצגת יום בשבוע.
UTC_USEC_TO_YEAR() הפונקציה מחזירה חותמת זמן של UNIX במיקרו-שניות שמייצגת את השנה.
WEEK() הפונקציה מחזירה את השבוע של TIMESTAMP כמספר שלם בין 1 ל-53.
YEAR() מחזירה את השנה של TIMESTAMP.

CURRENT_DATE()

הפונקציה מחזירה מחרוזת קריאה לאנשים של התאריך הנוכחי בפורמט %Y-%m-%d.

לדוגמה:

SELECT CURRENT_DATE();

הפונקציה מחזירה: 2013-02-01

CURRENT_TIME()

הפונקציה מחזירה מחרוזת קריאה לאנשים של השעה הנוכחית בשרת בפורמט %H:%M:%S.

לדוגמה:

SELECT CURRENT_TIME();

הפונקציה מחזירה: 01:32:56

CURRENT_TIMESTAMP()

מחזירה סוג נתונים של TIMESTAMP של השעה הנוכחית בשרת בפורמט %Y-%m-%d %H:%M:%S.

לדוגמה:

SELECT CURRENT_TIMESTAMP();

הפונקציה מחזירה: 2013-02-01 01:33:35 UTC

DATE(<timestamp>)

מחזירה מחרוזת קריאה לאנשים של סוג הנתונים TIMESTAMP בפורמט %Y-%m-%d.

לדוגמה:

SELECT DATE(TIMESTAMP('2012-10-01 02:03:04'));

התוצאה: 2012-10-01

DATE_ADD(<timestamp>,<interval>,
                 <interval_units>)

מוסיפה את המרווח שצוין לסוג נתונים TIMESTAMP. הערכים האפשריים של interval_units כוללים את YEAR,‏ MONTH,‏ DAY,‏ HOUR,‏ MINUTE ו-SECOND. אם interval הוא מספר שלילי, המרווח מופחת מטיפוס הנתונים TIMESTAMP.

לדוגמה:

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), 5, "YEAR");

התוצאה: 2017-10-01 02:03:04 UTC

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");

התוצאה: 2007-10-01 02:03:04 UTC

DATEDIFF(<timestamp1>,<timestamp2>)

הפונקציה מחזירה את מספר הימים בין שני סוגי נתונים של TIMESTAMP. התוצאה חיובית אם סוג הנתונים הראשון של TIMESTAMP מגיע אחרי סוג הנתונים השני של TIMESTAMP, אחרת התוצאה שלילית.

לדוגמה:

SELECT DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'));

התוצאה: 466

לדוגמה:

SELECT DATEDIFF(TIMESTAMP('2011-06-24 12:18:35'), TIMESTAMP('2012-10-02 05:23:48'));

הפונקציה מחזירה: -466

DAY(<timestamp>)

הפונקציה מחזירה את היום בחודש של סוג הנתונים TIMESTAMP כמספר שלם בין 1 ל-31, כולל.

לדוגמה:

SELECT DAY(TIMESTAMP('2012-10-02 05:23:48'));

הפונקציה מחזירה: 2

DAYOFWEEK(<timestamp>)

הפונקציה מחזירה את היום בשבוע של סוג הנתונים TIMESTAMP כמספר שלם בין 1 (יום ראשון) ל-7 (שבת), כולל.

לדוגמה:

SELECT DAYOFWEEK(TIMESTAMP("2012-10-01 02:03:04"));

הפונקציה מחזירה: 2

DAYOFYEAR(<timestamp>)

הפונקציה מחזירה את היום בשנה של סוג הנתונים TIMESTAMP כמספר שלם בין 1 ל-366, כולל. המספר השלם 1 מתייחס ל-1 בינואר.

לדוגמה:

SELECT DAYOFYEAR(TIMESTAMP("2012-10-01 02:03:04"));

התוצאה: 275

FORMAT_UTC_USEC(<unix_timestamp>)

מחזירה ייצוג מחרוזת קריא לאנשים של חותמת זמן של UNIX בפורמט YYYY-MM-DD HH:MM:SS.uuuuuu.

לדוגמה:

SELECT FORMAT_UTC_USEC(1274259481071200);

התוצאה: 2010-05-19 08:58:01.071200

HOUR(<timestamp>)

הפונקציה מחזירה את השעה של סוג הנתונים TIMESTAMP כמספר שלם בין 0 ל-23, כולל.

לדוגמה:

SELECT HOUR(TIMESTAMP('2012-10-02 05:23:48'));

התוצאה: 5

MINUTE(<timestamp>)

הפונקציה מחזירה את הדקות של סוג הנתונים TIMESTAMP כמספר שלם בין 0 ל-59, כולל.

לדוגמה:

SELECT MINUTE(TIMESTAMP('2012-10-02 05:23:48'));

הפונקציה מחזירה: 23

MONTH(<timestamp>)

הפונקציה מחזירה את החודש של סוג הנתונים TIMESTAMP כמספר שלם בין 1 ל-12, כולל.

לדוגמה:

SELECT MONTH(TIMESTAMP('2012-10-02 05:23:48'));

התוצאה: 10

MSEC_TO_TIMESTAMP(<expr>)
הפונקציה ממירה חותמת זמן של UNIX באלפיות השנייה לסוג הנתונים TIMESTAMP.

לדוגמה:

SELECT MSEC_TO_TIMESTAMP(1349053323000);

התוצאה: 2012-10-01 01:02:03 UTC

SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)

התוצאה: 2012-10-01 01:02:04 UTC

NOW()

הפונקציה מחזירה את חותמת הזמן הנוכחית של מערכת UNIX במיקרו-שניות.

לדוגמה:

SELECT NOW();

התוצאה: 1359685811687920

PARSE_UTC_USEC(<date_string>)

הפונקציה ממירה מחרוזת תאריך לחותמת זמן של UNIX במיקרו-שניות. הפורמט של date_string צריך להיות YYYY-MM-DD HH:MM:SS[.uuuuuu]. החלק השברי של השנייה יכול להיות באורך של עד 6 ספרות, או שאפשר להשמיט אותו.

הפונקציה TIMESTAMP_TO_USEC היא פונקציה מקבילה שממירה ארגומנט מסוג נתונים TIMESTAMP במקום מחרוזת תאריך.

לדוגמה:

SELECT PARSE_UTC_USEC("2012-10-01 02:03:04");

התוצאה: 1349056984000000

QUARTER(<timestamp>)

הפונקציה מחזירה את הרבעון בשנה של סוג נתונים TIMESTAMP כמספר שלם בין 1 ל-4, כולל.

לדוגמה:

SELECT QUARTER(TIMESTAMP("2012-10-01 02:03:04"));

החזרות: 4

SEC_TO_TIMESTAMP(<expr>)

ממירה חותמת זמן של UNIX בשניות לסוג נתונים TIMESTAMP.

לדוגמה:

SELECT SEC_TO_TIMESTAMP(1355968987);

הערך שמוחזר: 2012-12-20 02:03:07 UTC

SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));

הערך שמוחזר: 2012-12-20 02:03:07 UTC

SECOND(<timestamp>)

הפונקציה מחזירה את השניות של סוג הנתונים TIMESTAMP כמספר שלם בין 0 ל-59, כולל.

במהלך דקה מעוברת, טווח המספרים השלמים הוא בין 0 ל-60, כולל.

לדוגמה:

SELECT SECOND(TIMESTAMP('2012-10-02 05:23:48'));

החזרות: 48

STRFTIME_UTC_USEC(<unix_timestamp>,
                  <date_format_str>)

מחזירה מחרוזת תאריך קריאה לאנשים בפורמט date_format_str. הפרמטר date_format_str יכול לכלול סימני פיסוק שקשורים לתאריך (כמו / ו--) ותווים מיוחדים שמתקבלים על ידי הפונקציה strftime ב-C++‎ (כמו %d לציון היום בחודש).

אם אתם מתכננים לקבץ את נתוני השאילתה לפי מרווחי זמן, למשל לקבל את כל הנתונים של חודש מסוים, כדאי להשתמש בפונקציות UTC_USEC_TO_<function_name> כי הן יעילות יותר.

לדוגמה:

SELECT STRFTIME_UTC_USEC(1274259481071200, "%Y-%m-%d");

התוצאה: 2010-05-19

TIME(<timestamp>)

מחזירה מחרוזת קריאה לאנשים של סוג הנתונים TIMESTAMP, בפורמט %H:%M:%S.

לדוגמה:

SELECT TIME(TIMESTAMP('2012-10-01 02:03:04'));

התוצאה: 02:03:04

TIMESTAMP(<date_string>)

המרת מחרוזת תאריך לסוג נתונים TIMESTAMP.

לדוגמה:

SELECT TIMESTAMP("2012-10-01 01:02:03");

התוצאה: 2012-10-01 01:02:03 UTC

TIMESTAMP_TO_MSEC(<timestamp>)

הפונקציה ממירה סוג נתונים של TIMESTAMP לחותמת זמן של UNIX באלפיות השנייה.

לדוגמה:

SELECT TIMESTAMP_TO_MSEC(TIMESTAMP("2012-10-01 01:02:03"));

הפונקציה מחזירה: 1349053323000

TIMESTAMP_TO_SEC(<timestamp>)
הפונקציה ממירה את סוג הנתונים TIMESTAMP לחותמת זמן של UNIX בשניות.

לדוגמה:

SELECT TIMESTAMP_TO_SEC(TIMESTAMP("2012-10-01 01:02:03"));

התוצאה: 1349053323

TIMESTAMP_TO_USEC(<timestamp>)

הפונקציה ממירה סוג נתונים של TIMESTAMP לחותמת זמן של UNIX במיקרו-שניות.

‫PARSE_UTC_USEC היא פונקציה מקבילה שממירה ארגומנט של מחרוזת נתונים במקום סוג נתונים של TIMESTAMP.

לדוגמה:

SELECT TIMESTAMP_TO_USEC(TIMESTAMP("2012-10-01 01:02:03"));

הפונקציה מחזירה: 1349053323000000

USEC_TO_TIMESTAMP(<expr>)

הפונקציה ממירה חותמת זמן של מערכת UNIX במיקרו-שניות לסוג הנתונים TIMESTAMP.

לדוגמה:

SELECT USEC_TO_TIMESTAMP(1349053323000000);

התוצאה: 2012-10-01 01:02:03 UTC

SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)

התוצאה: 2012-10-01 01:02:04 UTC

UTC_USEC_TO_DAY(<unix_timestamp>)

הפונקציה מעבירה חותמת זמן של מערכת UNIX במיקרו-שניות לתחילת היום שבו היא מתרחשת.

לדוגמה, אם unix_timestamp מתרחש ב-19 במאי בשעה 08:58, הפונקציה הזו מחזירה חותמת זמן של UNIX ל-19 במאי בשעה 00:00 (חצות).

לדוגמה:

SELECT UTC_USEC_TO_DAY(1274259481071200);

התוצאה: 1274227200000000

UTC_USEC_TO_HOUR(<unix_timestamp>)

הפונקציה מעבירה את חותמת הזמן של מערכת UNIX במיקרו-שניות לתחילת השעה שבה היא מתרחשת.

לדוגמה, אם unix_timestamp מתרחש בשעה 08:58, הפונקציה הזו מחזירה חותמת זמן של UNIX לשעה 08:00 באותו יום.

לדוגמה:

SELECT UTC_USEC_TO_HOUR(1274259481071200);

התוצאה: 1274256000000000

UTC_USEC_TO_MONTH(<unix_timestamp>)

הפונקציה מעבירה את חותמת הזמן של מערכת UNIX במיקרו-שניות לתחילת החודש שבו היא מתרחשת.

לדוגמה, אם unix_timestamp מתרחש ב-19 במרץ, הפונקציה הזו מחזירה חותמת זמן של UNIX ל-1 במרץ של אותה שנה.

לדוגמה:

SELECT UTC_USEC_TO_MONTH(1274259481071200);

הפונקציה מחזירה: 1272672000000000

UTC_USEC_TO_WEEK(<unix_timestamp>,
                 <day_of_week>)

הפונקציה מחזירה חותמת זמן של UNIX במיקרו-שניות שמייצגת יום בשבוע של הארגומנט unix_timestamp. הפונקציה הזו מקבלת שני ארגומנטים: חותמת זמן של מערכת UNIX במיקרו-שניות, ויום בשבוע מ-0 (ראשון) עד 6 (שבת).

לדוגמה, אם unix_timestamp חל ביום שישי, 11.4.2008, והגדרתם את day_of_week ל-2 (יום שלישי), הפונקציה מחזירה חותמת זמן של UNIX ליום שלישי, 8.4.2008.

לדוגמה:

SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;

התוצאה: 1207612800000000

UTC_USEC_TO_YEAR(<unix_timestamp>)

הפונקציה מחזירה חותמת זמן של UNIX במיקרו-שניות שמייצגת את השנה של הארגומנט unix_timestamp.

לדוגמה, אם unix_timestamp מתרחש בשנת 2010, הפונקציה מחזירה את הערך 1274259481071200, שהוא הייצוג של 2010-01-01 00:00 במיקרו-שניות.

לדוגמה:

SELECT UTC_USEC_TO_YEAR(1274259481071200);

הפונקציה מחזירה: 1262304000000000

WEEK(<timestamp>)

הפונקציה מחזירה את השבוע של סוג הנתונים TIMESTAMP כמספר שלם בין 1 ל-53, כולל.

השבועות מתחילים ביום ראשון, כך שאם 1 בינואר חל ביום אחר, בשבוע 1 יש פחות מ-7 ימים ויום ראשון הראשון בשנה הוא היום הראשון של שבוע 2.

לדוגמה:

SELECT WEEK(TIMESTAMP('2014-12-31'));

התוצאה: 53

YEAR(<timestamp>)
הפונקציה מחזירה את השנה של סוג הנתונים TIMESTAMP.

לדוגמה:

SELECT YEAR(TIMESTAMP('2012-10-02 05:23:48'));

התוצאה: 2012

דוגמאות מתקדמות

  • המרת תוצאות של חותמות זמן מסוג מספר שלם לפורמט קריא לאנשים

    השאילתה הבאה מוצאת את 5 הרגעים שבהם בוצעו הכי הרבה גרסאות של ויקיפדיה. כדי להציג את התוצאות בפורמט קריא לאנשים, משתמשים בפונקציה FORMAT_UTC_USEC() של BigQuery, שמקבלת חותמת זמן במיקרו-שניות כקלט. השאילתה הזו מכפילה את חותמות הזמן בפורמט POSIX של ויקיפדיה (בשניות) ב-1,000,000 כדי להמיר את הערך למיקרו-שניות.

    לדוגמה:

    #legacySQL
SELECT
  /* Multiply timestamp by 1000000 and convert */
  /* into a more human-readable format. */
  TOP (FORMAT_UTC_USEC(timestamp * 1000000), 5)
    AS top_revision_time,
  COUNT (*) AS revision_count
FROM
  [bigquery-public-data:samples.wikipedia];

    החזרות:

    +----------------------------+----------------+
|     top_revision_time      | revision_count |
+----------------------------+----------------+
| 2002-02-25 15:51:15.000000 |          20976 |
| 2002-02-25 15:43:11.000000 |          15974 |
| 2010-02-02 03:34:51.000000 |              3 |
| 2010-02-02 01:04:59.000000 |              3 |
| 2010-02-01 23:55:05.000000 |              3 |
+----------------------------+----------------+

  • חלוקת התוצאות לקטגוריות לפי חותמת זמן

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

    לדוגמה:

    #legacySQL
SELECT
  contributor_username,
  /* Return the timestamp shifted to the
   * start of the month, formatted in
   * a human-readable format. Uses the
   * 'LEFT()' string function to return only
   * the first 7 characters of the formatted timestamp.
   */
  LEFT (FORMAT_UTC_USEC(
    UTC_USEC_TO_MONTH(timestamp * 1000000)),7)
    AS month,
  SUM(LENGTH(comment)) as total_chars_used
FROM
  [bigquery-public-data:samples.wikipedia]
WHERE
  (contributor_username != '' AND
   contributor_username IS NOT NULL)
  AND timestamp > 1133395200
  AND timestamp < 1157068800
GROUP BY
  contributor_username, month
ORDER BY
  total_chars_used DESC;

    החזרות (מקוצר):

    +--------------------------------+---------+-----------------------+
|      contributor_username      |  month  | total_chars_used      |
+--------------------------------+---------+-----------------------+
| Kingbotk                       | 2006-08 |              18015066 |
| SmackBot                       | 2006-03 |               7838365 |
| SmackBot                       | 2006-05 |               5148863 |
| Tawkerbot2                     | 2006-05 |               4434348 |
| Cydebot                        | 2006-06 |               3380577 |
etc ...

פונקציות IP

פונקציות IP ממירות כתובות IP לפורמט קריא לאנשים ולהפך.

תחביר

פונקציות IP
FORMAT_IP() ממירה 32 ביטים פחות משמעותיים של integer_value למחרוזת של כתובת IPv4 שקריאה לאנשים.
PARSE_IP() הפונקציה ממירה מחרוזת שמייצגת כתובת IPv4 לערך של מספר שלם ללא סימן.
FORMAT_PACKED_IP() מחזירה כתובת IP קריאה לאנשים בפורמט 10.1.5.23 או 2620:0:1009:1:216:36ff:feef:3f.
PARSE_PACKED_IP() הפונקציה מחזירה כתובת IP ב-BYTES.
FORMAT_IP(integer_value)
‫
ממירה 32 ביטים פחות משמעותיים של integer_value למחרוזת של כתובת IPv4 שקריאה לאנשים. לדוגמה, FORMAT_IP(1) יחזיר את המחרוזת '0.0.0.1'.
PARSE_IP(readable_ip)
‫
ממירה מחרוזת שמייצגת כתובת IPv4 לערך של מספר שלם לא חתום. לדוגמה, הפונקציה PARSE_IP('0.0.0.1') תחזיר את הערך 1. אם המחרוזת היא לא כתובת IPv4 חוקית, הפונקציה PARSE_IP תחזיר את הערך NULL.

ב-BigQuery אפשר לכתוב כתובות IPv4 ו-IPv6 במחרוזות דחוסות, כנתונים בינאריים של 4 או 16 בייטים בסדר בייטים ברשת. הפונקציות שמתוארות בהמשך תומכות בניתוח הכתובות אל פורמט קריא לאנשים וממנו. הפונקציות האלה פועלות רק בשדות מחרוזת עם כתובות IP.

תחביר

FORMAT_PACKED_IP(packed_ip)

מחזירה כתובת IP קריאה לאנשים, בפורמט 10.1.5.23 או 2620:0:1009:1:216:36ff:feef:3f. דוגמאות:

  • FORMAT_PACKED_IP('0123456789@ABCDE') מחזיר את '3031:3233:3435:3637:3839:4041:4243:4445'
  • FORMAT_PACKED_IP('0123') מחזיר את '48.49.50.51'
PARSE_PACKED_IP(readable_ip)

הפונקציה מחזירה כתובת IP ב-BYTES. אם מחרוזת הקלט היא לא כתובת IPv4 או IPv6 חוקית, הפונקציה PARSE_PACKED_IP תחזיר את הערך NULL. דוגמאות:

  • PARSE_PACKED_IP('48.49.50.51') מחזיר את 'MDEyMw=='
  • PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445') מחזיר את 'MDEyMzQ1Njc4OUBBQkNERQ=='

פונקציות JSON

פונקציות ה-JSON של BigQuery מאפשרות לכם למצוא ערכים בנתוני ה-JSON ששמרתם, באמצעות ביטויים שדומים ל-JSONPath.

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

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

תחביר

פונקציות JSON
JSON_EXTRACT() בוחרת ערך לפי הביטוי של JSONPath ומחזירה מחרוזת JSON.
JSON_EXTRACT_SCALAR() בוחרת ערך לפי הביטוי JSONPath ומחזירה סקלר JSON.
JSON_EXTRACT(json, json_path)

בוחר ערך ב-json לפי הביטוי json_path של JSONPath. ‫json_path חייב להיות קבוע של מחרוזת. הפונקציה מחזירה את הערך בפורמט מחרוזת JSON.

JSON_EXTRACT_SCALAR(json, json_path)

בוחר ערך ב-json לפי הביטוי json_path של JSONPath. ‫json_path חייב להיות קבוע של מחרוזת. מחזירה ערך JSON סקלרי.

אופרטורים לוגיים

אופרטורים לוגיים מבצעים לוגיקה בינארית או טרנארית על ביטויים. לוגיקה בינארית מחזירה true או false. לוגיקה טרנרית מתאימה לערכי NULL ומחזירה את הערכים true,‏ false או NULL.

תחביר

אופרטורים לוגיים
expr AND expr הפונקציה מחזירה את הערך true אם שני הביטויים נכונים.
expr OR expr הפונקציה מחזירה את הערך true אם אחד מהביטויים או שניהם נכונים.
NOT expr הפונקציה מחזירה את הערך true אם הביטוי לא נכון.
expr AND expr
  • הפונקציה מחזירה את הערך true אם שני הביטויים נכונים.
  • הפונקציה מחזירה את הערך false אם אחד מהביטויים או שניהם לא נכונים.
  • הפונקציה מחזירה את הערך NULL אם שני הביטויים הם NULL או אם ביטוי אחד הוא TRUE והשני הוא NULL.
expr OR expr
  • הפונקציה מחזירה את הערך true אם אחד מהביטויים או שניהם נכונים.
  • הפונקציה מחזירה את הערך false אם שני הביטויים לא נכונים.
  • הפונקציה מחזירה NULL אם שני הביטויים הם NULL או אם ביטוי אחד הוא FALSE והשני הוא NULL.
NOT expr
  • הפונקציה מחזירה את הערך true אם הביטוי לא נכון.
  • הפונקציה מחזירה את הערך false אם הביטוי נכון.
  • הפונקציה מחזירה את הערך NULL אם הביטוי הוא NULL.

אפשר להשתמש ב-NOT עם פונקציות אחרות כאופרטור שלילה. לדוגמה, NOT IN(expr1, expr2) או IS NOT NULL.

פונקציות מתמטיות

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

תחביר

פונקציות מתמטיות
ABS() הפונקציה מחזירה את הערך המוחלט של הארגומנט.
ACOS() מחזירה את הארק-קוסינוס של הארגומנט.
ACOSH() הפונקציה מחזירה את הקוסינוס ההיפרבולי ההפוך של הארגומנט.
ASIN() מחזירה את הארקסינוס של הארגומנט.
ASINH() מחזירה את הסינוס ההיפרבולי ההפוך של הארגומנט.
ATAN() מחזירה את הארק-טנגנס של הארגומנט.
ATANH() מחזירה את הטנגנס ההיפרבולי ההפוך של הארגומנט.
ATAN2() מחזירה את הארק-טנגנס של שני הארגומנטים.
CEIL() הפונקציה מעגלת את הארגומנט כלפי מעלה למספר השלם הקרוב ביותר ומחזירה את הערך המעוגל.
COS() מחזירה את הקוסינוס של הארגומנט.
COSH() מחזירה את הקוסינוס ההיפרבולי של הארגומנט.
DEGREES() המרת רדיאנים למעלות.
EXP() הפונקציה מחזירה את e בחזקת הארגומנט.
FLOOR() מעגלת את הארגומנט כלפי מטה למספר השלם הקרוב ביותר.
LN()
LOG() 		מחזירה את הלוגריתם הטבעי של הארגומנט.
LOG2() מחזירה את הלוגריתם של הארגומנט לפי בסיס 2.
LOG10() מחזירה את הלוגריתם של הארגומנט לפי בסיס 10.
PI() מחזירה את הקבוע π.
POW() הפונקציה מחזירה את הארגומנט הראשון בחזקת הארגומנט השני.
RADIANS() המרת מעלות לרדיאנים.
RAND() מחזירה ערך אקראי מסוג מספר ממשי (float) בטווח 0.0 <= ערך < 1.0.
ROUND() מעגלת את הארגומנט למעלה או למטה למספר השלם הקרוב ביותר.
SIN() מחזירה את הסינוס של הארגומנט.
SINH() מחזירה את הסינוס ההיפרבולי של הארגומנט.
SQRT() מחזירה את השורש הריבועי של הביטוי.
TAN() מחזירה את הטנגנס של הארגומנט.
TANH() מחזירה את הטנגנס ההיפרבולי של הארגומנט.
ABS(numeric_expr)
מחזירה את הערך המוחלט של הארגומנט.
ACOS(numeric_expr)
מחזירה את הארק-קוסינוס של הארגומנט.
ACOSH(numeric_expr)
החזרת הקוסינוס ההיפרבולי ההפוך של הארגומנט.
ASIN(numeric_expr)
מחזירה את הארקסינוס של הארגומנט.
ASINH(numeric_expr)
מחזירה את הסינוס ההיפרבולי ההפוך של הארגומנט.
ATAN(numeric_expr)
מחזירה את הארק-טנגנס של הארגומנט.
ATANH(numeric_expr)
מחזירה את הטנגנס ההיפרבולי ההפוך של הארגומנט.
ATAN2(numeric_expr1, numeric_expr2)
מחזירה את הארק-טנגנס של שני הארגומנטים.
CEIL(numeric_expr)
הפונקציה מעגלת את הארגומנט כלפי מעלה למספר השלם הקרוב ביותר ומחזירה את הערך המעוגל.
COS(numeric_expr)
מחזירה את הקוסינוס של הארגומנט.
COSH(numeric_expr)
החזרת הקוסינוס ההיפרבולי של הארגומנט.
DEGREES(numeric_expr)
הפונקציה מחזירה את הערך numeric_expr, אחרי המרה מרדיאנים למעלות.
EXP(numeric_expr)
הפונקציה מחזירה את התוצאה של העלאת הקבוע "e" – הבסיס של הלוגריתם הטבעי – בחזקת numeric_expr.
FLOOR(numeric_expr)
הפונקציה מעגלת את הארגומנט כלפי מטה למספר השלם הקרוב ביותר ומחזירה את הערך המעוגל.
LN(numeric_expr)
LOG(numeric_expr)
‫
מחזירה את הלוגריתם הטבעי של הארגומנט.
LOG2(numeric_expr)
מחזירה את הלוגריתם של הארגומנט לפי בסיס 2.
LOG10(numeric_expr)
מחזירה את הלוגריתם של הארגומנט לפי בסיס 10.
PI()
מחזירה את הקבוע π. הפונקציה PI() דורשת סוגריים כדי לציין שהיא פונקציה, אבל לא מקבלת ארגומנטים בסוגריים האלה. אפשר להשתמש ב-PI() כמו בקבוע עם פונקציות מתמטיות וחשבוניות.
POW(numeric_expr1, numeric_expr2)
הפונקציה מחזירה את התוצאה של העלאת numeric_expr1 בחזקת numeric_expr2.
RADIANS(numeric_expr)
‫
הפונקציה מחזירה את הערך numeric_expr, אחרי המרה ממעלות לרדיאנים. (שימו לב ש-π רדיאנים שווה ל-180 מעלות).
RAND([int32_seed])
‫
הפונקציה מחזירה ערך אקראי מסוג float בטווח ‎0.0 <= value < 1.0. כל ערך של int32_seed תמיד יוצר את אותו רצף של מספרים אקראיים בשאילתה נתונה, כל עוד לא משתמשים בסעיף LIMIT. אם לא מציינים את int32_seed, ‏BigQuery משתמש בחותמת הזמן הנוכחית כערך הבסיס.
ROUND(numeric_expr [, digits])
הפונקציה מעגלת את הארגומנט כלפי מעלה או כלפי מטה למספר השלם הקרוב ביותר (או למספר הספרות שצוין, אם צוין) ומחזירה את הערך המעוגל.
SIN(numeric_expr)
מחזירה את הסינוס של הארגומנט.
SINH(numeric_expr)
מחזירה את הסינוס ההיפרבולי של הארגומנט.
SQRT(numeric_expr)
הפונקציה מחזירה את השורש הריבועי של הביטוי.
TAN(numeric_expr)
מחזירה את הטנגנס של הארגומנט.
TANH(numeric_expr)
מחזירה את הטנגנס ההיפרבולי של הארגומנט.

דוגמאות מתקדמות

  • שאילתה של תיבה תוחמת

    השאילתה הבאה מחזירה אוסף של נקודות בתוך תיבה תוחמת מלבנית שמרכזה בסן פרנסיסקו (‎37.46, -122.50).

    לדוגמה:

    #legacySQL
SELECT
  year, month,
  AVG(mean_temp) avg_temp,
  MIN(min_temperature) min_temp,
  MAX(max_temperature) max_temp
FROM
  [weather_geo.table]
WHERE
  /* Return values between a pair of */
  /* latitude and longitude coordinates */
  lat / 1000 > 37.46 AND
  lat / 1000 < 37.65 AND
  long / 1000 > -122.50 AND
  long / 1000 < -122.30
GROUP BY
  year, month
ORDER BY
  year, month ASC;

  • שאילתה של מעגל תוחם משוער

    החזרת אוסף של עד 100 נקודות בתוך מעגל משוער שנקבע באמצעות חוק הקוסינוסים הכדורי, שמרכזו בדנבר, קולורדו (‎39.73, -104.98). השאילתה הזו משתמשת בפונקציות המתמטיות והטריגונומטריות של BigQuery, כמו PI(),‏ SIN() ו-COS().

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

    לדוגמה:

    #legacySQL
SELECT
  distance, lat, long, temp
FROM
  (SELECT
    ((ACOS(SIN(39.73756700 * PI() / 180) *
           SIN((lat/1000) * PI() / 180) +
           COS(39.73756700 * PI() / 180) *
           COS((lat/1000) * PI() / 180) *
           COS((-104.98471790 -
           (long/1000)) * PI() / 180)) *
           180 / PI()) * 60 * 1.1515)
      AS distance,
     AVG(mean_temp) AS temp,
     AVG(lat/1000) lat, AVG(long/1000) long
FROM
  [weather_geo.table]
WHERE
  month=1 GROUP BY distance)
WHERE
  distance < 100
ORDER BY
  distance ASC
LIMIT 100;

פונקציות של ביטויים רגולריים

ב-BigQuery יש תמיכה בביטויים רגולריים באמצעות הספרייה re2. בתיעוד של הספרייה מפורט התחביר של ביטויים רגולריים.

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

תחביר

פונקציות של ביטויים רגולריים
REGEXP_MATCH() הפונקציה מחזירה את הערך True אם הארגומנט תואם לביטוי הרגולרי.
REGEXP_EXTRACT() הפונקציה מחזירה את החלק של הארגומנט שתואם לקבוצה לחילוץ בתוך הביטוי הרגולרי.
REGEXP_REPLACE() החלפת מחרוזת משנה שתואמת לביטוי רגולרי.
REGEXP_MATCH('str', 'reg_exp')

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

לדוגמה:

#legacySQL
SELECT
   word,
   COUNT(word) AS count
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   (REGEXP_MATCH(word,r'\w\w\'\w\w'))
GROUP BY word
ORDER BY count DESC
LIMIT 3;

החזרות:

+-------+-------+
| word  | count |
+-------+-------+
| ne'er |    42 |
| we'll |    35 |
| We'll |    33 |
+-------+-------+
REGEXP_EXTRACT('str', 'reg_exp')

הפונקציה מחזירה את החלק של str שתואם לקבוצה לחילוץ בתוך הביטוי הרגולרי.

לדוגמה:

#legacySQL
SELECT
   REGEXP_EXTRACT(word,r'(\w\w\'\w\w)') AS fragment
FROM
   [bigquery-public-data:samples.shakespeare]
GROUP BY fragment
ORDER BY fragment
LIMIT 3;

החזרות:

+----------+
| fragment |
+----------+
| NULL     |
| Al'ce    |
| As'es    |
+----------+
REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')

הפונקציה מחזירה מחרוזת שבה כל מחרוזת משנה של orig_str שתואמת ל-reg_exp מוחלפת ב-replace_str. לדוגמה, הפונקציה REGEXP_REPLACE ('Hello', 'lo', 'p')‎ מחזירה את הערך Help.

לדוגמה:

#legacySQL
SELECT
  REGEXP_REPLACE(word, r'ne\'er', 'never') AS expanded_word
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  REGEXP_MATCH(word, r'ne\'er')
GROUP BY expanded_word
ORDER BY expanded_word
LIMIT 5;

החזרות:

+---------------+
| expanded_word |
+---------------+
| Whenever      |
| never         |
| nevertheless  |
| whenever      |
+---------------+

דוגמאות מתקדמות

  • סינון קבוצת תוצאות לפי התאמה של ביטוי רגולרי

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

    לדוגמה:

    #legacySQL
SELECT
  /* Replace white spaces in the title with underscores. */
  REGEXP_REPLACE(title, r'\s+', '_') AS regexp_title, revisions
FROM
  (SELECT title, COUNT(revision_id) as revisions
  FROM
    [bigquery-public-data:samples.wikipedia]
  WHERE
    wp_namespace=0
    /* Match titles that start with 'G', end with
     * 'e', and contain at least two 'o's.
     */
    AND REGEXP_MATCH(title, r'^G.*o.*o.*e$')
  GROUP BY
    title
  ORDER BY
    revisions DESC
  LIMIT 100);

  • שימוש בביטויים רגולריים בנתונים מסוג מספר שלם או מספר ממשי

    הפונקציות של ביטויים רגולריים ב-BigQuery פועלות רק על נתוני מחרוזות, אבל אפשר להשתמש בפונקציה STRING() כדי להמיר נתונים של מספרים שלמים או מספרים בנקודה צפה (float) לפורמט של מחרוזת. בדוגמה הזו, הפונקציה STRING() משמשת להמרת הערך של המספר השלם corpus_date למחרוזת, שמשתנה לאחר מכן על ידי REGEXP_REPLACE.

    לדוגמה:

    #legacySQL
SELECT
  corpus_date,
  /* Cast the corpus_date to a string value  */
  REGEXP_REPLACE(STRING(corpus_date),
    '^16',
    'Written in the sixteen hundreds, in the year \''
    ) AS date_string
FROM [bigquery-public-data:samples.shakespeare]
/* Cast the corpus_date to string, */
/* match values that begin with '16' */
WHERE
  REGEXP_MATCH(STRING(corpus_date), '^16')
GROUP BY
  corpus_date, date_string
ORDER BY
  date_string DESC
LIMIT 5;

פונקציות של מחרוזות

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

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

תחביר

פונקציות של מחרוזות
CONCAT() הפונקציה מחזירה את השרשור של שתי מחרוזות או יותר, או NULL אם אחד מהערכים הוא NULL.
expr CONTAINS 'str' הפונקציה מחזירה את הערך true אם expr מכיל את ארגומנט המחרוזת שצוין.
INSTR() הפונקציה מחזירה את האינדקס מבוסס-האחד של המופע הראשון של מחרוזת.
LEFT() הפונקציה מחזירה את התווים השמאליים ביותר במחרוזת.
LENGTH() הפונקציה מחזירה את אורך המחרוזת.
LOWER() הפונקציה מחזירה את המחרוזת המקורית עם כל התווים באותיות קטנות.
LPAD() הוספת תווים משמאל למחרוזת.
LTRIM() מסירה תווים מהצד השמאלי של מחרוזת.
REPLACE() החלפת כל המופעים של מחרוזת משנה.
RIGHT() הפונקציה מחזירה את התווים הכי שמאליים במחרוזת.
RPAD() הוספת תווים לצד שמאל של מחרוזת.
RTRIM() הפונקציה מסירה תווים נגררים מהצד השמאלי של מחרוזת.
SPLIT() פיצול מחרוזת למחרוזות משנה חוזרות.
SUBSTR() הפונקציה מחזירה מחרוזת משנה ...
UPPER() הפונקציה מחזירה את המחרוזת המקורית כשכל התווים בה הם אותיות רישיות.
CONCAT('str1', 'str2', '...')
str1 + str2 + ...
‫
מחזירה את השרשור של שתי מחרוזות או יותר, או NULL אם אחד מהערכים הוא NULL. דוגמה: אם str1 הוא Java ו-str2 הוא Script, הפונקציה CONCAT מחזירה את הערך JavaScript.
expr CONTAINS 'str'
הפונקציה מחזירה את הערך true אם expr מכיל את ארגומנט המחרוזת שצוין. ההשוואה הזו תלוית אותיות רישיות.
INSTR('str1', 'str2')
הפונקציה מחזירה את האינדקס מבוסס-האחד של המופע הראשון של str2 ב-str1, או מחזירה 0 אם str2 לא מופיע ב-str1.
LEFT('str', numeric_expr)
‫
מחזירה את התווים השמאליים ביותר של numeric_expr ב-str. אם המספר ארוך יותר מ-str, תוחזר המחרוזת המלאה. דוגמה: LEFT('seattle', 3) מחזיר את הערך sea.
LENGTH('str')
‫
הפונקציה מחזירה ערך מספרי שמייצג את אורך המחרוזת. דוגמה: אם הערך של str הוא '123456', הפונקציה LENGTH מחזירה את הערך 6.
LOWER('str')
‫
הפונקציה מחזירה את המחרוזת המקורית עם כל התווים באותיות קטנות.
LPAD('str1', numeric_expr, 'str2')
מוסיף ריפוד ל-str1 משמאל באמצעות str2, וחוזר על str2 עד שמחרוזת התוצאה היא באורך של numeric_expr תווים בדיוק. דוגמה: LPAD('1', 7, '?') מחזיר את הערך ??????1.
LTRIM('str1' [, str2])

מסירה תווים מהצד השמאלי של str1. אם משמיטים את str2, הפונקציה LTRIM מסירה רווחים מהצד השמאלי של str1. אחרת, הפונקציה LTRIM מסירה את כל התווים ב-str2 מהצד השמאלי של str1 (תלוי אותיות רישיות).

לדוגמה:

הפונקציה SELECT LTRIM("Say hello", "yaS") מחזירה את הערך " hello".

הפונקציה SELECT LTRIM("Say hello", " ySa") מחזירה את הערך "hello".

REPLACE('str1', 'str2', 'str3')

הפונקציה מחליפה את כל המופעים של str2 בתוך str1 ב-str3.

‫
מחזירה את התווים הימניים ביותר של numeric_expr ב-str. אם המספר ארוך יותר מהמחרוזת, הפונקציה תחזיר את כל המחרוזת. דוגמה: RIGHT('kirkland', 4) מחזיר את הערך land.
RPAD('str1', numeric_expr, 'str2')
מוסיף ריפוד ל-str1 מימין באמצעות str2, וחוזר על str2 עד שמחרוזת התוצאה היא באורך של numeric_expr תווים בדיוק. דוגמה: RPAD('1', 7, '?') מחזיר את הערך 1??????.
RTRIM('str1' [, str2])

מסירה תווים נגררים מהצד הימני של str1. אם משמיטים את str2, הפונקציה RTRIM מסירה רווחים בסוף של str1. אחרת, הפונקציה RTRIM מסירה את כל התווים ב-str2 מהצד הימני של str1 (תלוי אותיות רישיות).

לדוגמה:

הפונקציה SELECT RTRIM("Say hello", "leo") מחזירה את הערך "Say h".

הפונקציה SELECT RTRIM("Say hello ", " hloe") מחזירה את הערך "Say".

SPLIT('str' [, 'delimiter'])
פיצול מחרוזת למחרוזות משנה חוזרות. אם מציינים את delimiter, הפונקציה SPLIT מפצלת את str למחרוזות משנה, ומשתמשת ב-delimiter בתור התו המפריד.
SUBSTR('str', index [, max_len])
הפונקציה מחזירה מחרוזת משנה של str, החל מ-index. אם משתמשים בפרמטר האופציונלי max_len, המחרוזת שמוחזרת היא באורך של max_len תווים לכל היותר. הספירה מתחילה מ-1, כך שהתו הראשון במחרוזת נמצא במיקום 1 (ולא אפס). אם index הוא 5, מחרוזת המשנה מתחילה בתו החמישי משמאל ב-str. אם הערך של index הוא -4, מחרוזת המשנה מתחילה בתו הרביעי מימין ב-str. לדוגמה: הפונקציה SUBSTR('awesome', -4, 4) מחזירה את המחרוזת המשנית some.
UPPER('str')
‫
הפונקציה מחזירה את המחרוזת המקורית כשכל התווים הם אותיות רישיות.

סימון בתווי בריחה (escape) של תווים מיוחדים במחרוזות

כדי להוסיף תווי בריחה לתווים מיוחדים, אפשר להשתמש באחת מהשיטות הבאות:

  • משתמשים בסימון '\xDD', שבו אחרי '\x' מופיע הייצוג ההקסדצימלי הדו-ספרתי של התו.
  • צריך להשתמש בלוכסן מילוט לפני לוכסנים, מירכאות בודדות ומירכאות כפולות.
  • לתווים אחרים, צריך להשתמש ברצפים בסגנון C‏ ('\a', '\b', '\f', '\n', '\r', '\t', ו-'\v').

דוגמאות לתווי בריחה: 

'this is a space: \x20'
'this string has \'single quote\' inside it'
'first line \n second line'
"double quotes are also ok"
'\070' -> ERROR: octal escaping is not supported

פונקציות של תווים כלליים לחיפוש בטבלאות

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

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

לא ניתן להשתמש בתו כללי לחיפוש כדי להריץ שאילתות על מספר טבלאות לגבי תוצאות במטמון (גם אם סימנתם את האפשרות Use Cached Results). אם תפעילו כמה פעמים את אותה שאילתה עם תו כללי לחיפוש, תחויבו על כל שאילתה.

תחביר

פונקציות של תווים כלליים לחיפוש בטבלאות
TABLE_DATE_RANGE() הרצת שאילתות על כמה טבלאות יומיות שכוללות טווח תאריכים.
TABLE_DATE_RANGE_STRICT() מריץ שאילתות על כמה טבלאות יומיות שכוללות טווח תאריכים, בלי תאריכים חסרים.
TABLE_QUERY() שליחת שאילתות לטבלאות שהשמות שלהן תואמים לפרדיקט שצוין.
TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)

שאילתות בטבלאות יומיות שחופפות לטווח התאריכים שבין <timestamp1> ל-<timestamp2>.

שמות הטבלאות צריכים להיות בפורמט הבא: <prefix><day>, כאשר <day> הוא בפורמט YYYYMMDD.

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

  • TIMESTAMP('2012-10-01 02:03:04')
  • DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')

דוגמה: קבלת טבלאות בין שני ימים

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

  • mydata.people20140325
  • mydata.people20140326
  • mydata.people20140327
#legacySQL
SELECT
  name
FROM
  TABLE_DATE_RANGE([myproject-1234:mydata.people],
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27'))
WHERE
  age >= 35

תואם לטבלאות הבאות:

  • mydata.people20140325
  • mydata.people20140326
  • mydata.people20140327

דוגמה: קבלת טבלאות בטווח של יומיים עד "עכשיו"

בדוגמה הזו, אנחנו מניחים שהטבלאות הבאות קיימות בפרויקט בשם myproject-1234:

  • mydata.people20140323
  • mydata.people20140324
  • mydata.people20140325
#legacySQL
SELECT
  name
FROM
  (TABLE_DATE_RANGE([myproject-1234:mydata.people],
                    DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'),
                    CURRENT_TIMESTAMP()))
WHERE
  age >= 35

תואם לטבלאות הבאות:

  • mydata.people20140323
  • mydata.people20140324
  • mydata.people20140325
TABLE_DATE_RANGE_STRICT(prefix, timestamp1, timestamp2)

הפונקציה הזו מקבילה לפונקציה TABLE_DATE_RANGE. ההבדל היחיד הוא שאם חסרה טבלה יומית כלשהי ברצף, הפונקציה TABLE_DATE_RANGE_STRICT נכשלת ומחזירה שגיאת Not Found: Table <table_name>.

דוגמה: שגיאה בטבלה חסרה

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

  • people20140325
  • people20140327
#legacySQL
SELECT
  name
FROM
  (TABLE_DATE_RANGE_STRICT([myproject-1234:mydata.people],
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27')))
WHERE age >= 35

הדוגמה שלמעלה מחזירה את השגיאה "Not Found" לטבלה "people20140326".

TABLE_QUERY(dataset, expr)

מבצעת שאילתות בטבלאות שהשמות שלהן תואמים ל-expr שסופק. הפרמטר expr חייב להיות מיוצג כמחרוזת ולהכיל ביטוי להערכה. לדוגמה, 'length(table_id) < 3'.

דוגמה: התאמה לטבלאות שהשמות שלהן מכילים את האותיות "oo" והאורך שלהם גדול מ-4

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

  • mydata.boo
  • mydata.fork
  • mydata.ooze
  • mydata.spoon
#legacySQL
SELECT
  speed
FROM (TABLE_QUERY([myproject-1234:mydata],
                  'table_id CONTAINS "oo" AND length(table_id) >= 4'))

תואם לטבלאות הבאות:

  • mydata.ooze
  • mydata.spoon

דוגמה: התאמה לטבלאות שהשמות שלהן מתחילים ב-"boo", ואחריו 3-5 ספרות

בדוגמה הזו, אנחנו מניחים שהטבלאות הבאות קיימות בפרויקט בשם myproject-1234:

  • mydata.book4
  • mydata.book418
  • mydata.boom12345
  • mydata.boom123456789
  • mydata.taboo999
#legacySQL
SELECT
  speed
FROM
  TABLE_QUERY([myproject-1234:mydata],
               'REGEXP_MATCH(table_id, r"^boo[\d]{3,5}")')

תואם לטבלאות הבאות:

  • mydata.book418
  • mydata.boom12345

פונקציות של כתובות URL

תחביר

פונקציות של כתובות URL
HOST() הפונקציה מקבלת כתובת URL ומחזירה את שם המארח כמחרוזת.
DOMAIN() הפונקציה מקבלת כתובת URL ומחזירה את הדומיין כמחרוזת.
TLD() מקבלת כתובת URL ומחזירה את הדומיין ברמה העליונה ואת הדומיין של המדינה בכתובת ה-URL.
HOST('url_str')
הפונקציה מקבלת כתובת URL ומחזירה את שם המארח כמחרוזת. דוגמה: הפונקציה HOST('http://www.google.com:80/index.html')‎ מחזירה את הערך 'www.google.com'
DOMAIN('url_str')
הפונקציה מקבלת כתובת URL ומחזירה את הדומיין כמחרוזת. דוגמה: הפונקציה DOMAIN('http://www.google.com:80/index.html')‎ מחזירה את הערך 'google.com'.
TLD('url_str')
מקבלת כתובת URL ומחזירה את הדומיין ברמה העליונה ואת הדומיין של המדינה בכתובת ה-URL. דוגמה: הפונקציה TLD('http://www.google.com:80/index.html')‎ מחזירה את הערך '‎.com'. הפונקציה TLD('http://www.google.co.uk:80/index.html')‎ מחזירה את הערך '‎.co.uk'.

הערות:

  • הפונקציות האלה לא מבצעות שאילתת DNS הפוכה, כך שאם קוראים לפונקציות האלה באמצעות כתובת IP, הפונקציות יחזירו פלחים של כתובת ה-IP במקום פלחים של שם המארח.
  • כל הפונקציות לניתוח כתובות URL מצפות לתווים באותיות קטנות. אם תשתמשו באותיות רישיות בכתובת ה-URL, תקבלו תוצאה מסוג NULL או תוצאה שגויה אחרת. אם הנתונים שלכם כוללים אותיות רישיות וקטנות, כדאי להעביר את הקלט לפונקציה הזו דרך LOWER()‎.

דוגמה מתקדמת

ניתוח שמות דומיינים מנתוני כתובות URL

השאילתה הזו משתמשת בפונקציה DOMAIN() כדי להחזיר את הדומיינים הפופולריים ביותר שרשומים כדפי הבית של המאגר ב-GitHub. שימו לב לשימוש ב-HAVING כדי לסנן רשומות באמצעות התוצאה של הפונקציה DOMAIN(). זו פונקציה שימושית לקביעת מידע על מפנים מנתוני כתובות URL.

לדוגמה:

#legacySQL
SELECT
  DOMAIN(repository_homepage) AS user_domain,
  COUNT(*) AS activity_count
FROM
  [bigquery-public-data:samples.github_timeline]
GROUP BY
  user_domain
HAVING
  user_domain IS NOT NULL AND user_domain != ''
ORDER BY
  activity_count DESC
LIMIT 5;

החזרות:

+-----------------+----------------+
|   user_domain   | activity_count |
+-----------------+----------------+
| github.com      |         281879 |
| google.com      |          34769 |
| khanacademy.org |          17316 |
| sourceforge.net |          15103 |
| mozilla.org     |          14091 |
+-----------------+----------------+

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

#legacySQL
SELECT
  TLD(repository_homepage) AS user_tld,
  COUNT(*) AS activity_count
FROM
  [bigquery-public-data:samples.github_timeline]
GROUP BY
  user_tld
HAVING
  /* Only consider TLDs that are NOT NULL */
  /* or in our list of common TLDs */
  user_tld IS NOT NULL AND NOT user_tld
  IN ('','.com','.net','.org','.info','.edu')
ORDER BY
  activity_count DESC
LIMIT 5;

החזרות:

+----------+----------------+
| user_tld | activity_count |
+----------+----------------+
| .de      |          22934 |
| .io      |          17528 |
| .me      |          13652 |
| .fr      |          12895 |
| .co.uk   |           9135 |
+----------+----------------+

פונקציות אנליטיות (window functions)

פונקציות אנליטיות (window functions) מאפשרות לבצע חישובים בקבוצת משנה ספציפית, או "חלון", של קבוצת תוצאות. פונקציות אנליטיות מקלות על יצירת דוחות שכוללים ניתוחים מורכבים, כמו ממוצעים נעים וסכומים מצטברים.

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

#legacySQL
SELECT <window_function>
  OVER (
      [PARTITION BY <expr>]
      [ORDER BY <expr> [ASC | DESC]]
      [<window-frame-clause>]
     )
PARTITION BY
מגדיר את המחיצה הבסיסית שעליה הפונקציה הזו פועלת. מציינים שם אחד או יותר של עמודה, מופרדים בפסיקים. תיווצר מחיצה אחת לכל קבוצה נפרדת של ערכים בעמודות האלה, בדומה לסעיף GROUP BY. אם משמיטים את PARTITION BY, המחיצה הבסיסית היא כל השורות בקלט של הפונקציה האנליטית.
הסעיף PARTITION BY מאפשר גם לפונקציות אנליטיות (window functions) לחלק את הנתונים למחיצות ולהריץ את השאילתה במקביל. אם רוצים להשתמש בפונקציה אנליטית עם allowLargeResults, או אם מתכוונים להחיל עוד צירופים או צבירות על הפלט של הפונקציה האנליטית, צריך להשתמש ב-PARTITION BY כדי להפעיל את הביצוע במקביל.
אי אפשר להשתמש בסעיפים
,‏ JOIN EACH ו-GROUP EACH BY בפלט של פונקציות אנליטיות. כדי ליצור תוצאות שאילתה גדולות כשמשתמשים בפונקציות אנליטיות, צריך להשתמש ב-PARTITION BY.
ORDER BY
ממיין את המחיצה. אם ORDER BY לא מופיע, אין ערובה לסדר מיון כלשהו כברירת מחדל. המיון מתבצע ברמת המחיצה, לפני שמוחלת פסקה כלשהי של מסגרת חלון. אם מציינים חלון RANGE, צריך להוסיף משפט ORDER BY. סדר ברירת המחדל הוא ASC.
ORDER BY הוא אופציונלי במקרים מסוימים, אבל פונקציות אנליטיות מסוימות, כמו rank()‎ או dense_rank(), דורשות את הסעיף.
אם משתמשים ב-ORDER BY בלי לציין את ROWS או את RANGE, המשמעות של ORDER BY היא שהחלון מתחיל בתחילת המחיצה ומסתיים בשורה הנוכחית. אם אין משפט ORDER BY, החלון הוא המחיצה כולה.
<window-frame-clause>
{ROWS | RANGE} {BETWEEN <start> AND <end> | <start> | <end>}
קבוצת משנה של המחיצה שעליה תתבצע הפעולה. הגודל יכול להיות זהה לגודל המחיצה או קטן יותר. אם משתמשים ב-ORDER BY בלי window-frame-clause, מסגרת החלון שמוגדרת כברירת מחדל היא RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. אם משמיטים גם את ORDER BY וגם את window-frame-clause, מסגרת החלון שמוגדרת כברירת מחדל היא המחיצה כולה.
  • ‫ROWS – מגדיר חלון במונחים של מיקום השורה, ביחס לשורה הנוכחית. לדוגמה, כדי להוסיף עמודה שבה מוצג הסכום של 5 השורות הקודמות של ערכי השכר, צריך להריץ את השאילתה SUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW). קבוצת השורות בדרך כלל כוללת את השורה הנוכחית, אבל זה לא חובה.
  • ‫RANGE – מגדיר חלון במונחים של טווח ערכים בעמודה נתונה, ביחס לערך של אותה עמודה בשורה הנוכחית. הפונקציה פועלת רק על מספרים ותאריכים, כאשר ערכי התאריכים הם מספרים שלמים פשוטים (מיקרו-שניות מאז ראשית זמן יוניקס). שורות סמוכות עם אותו ערך נקראות שורות עמיתות. שורות מקבילות של CURRENT ROW נכללות במסגרת חלון שמציינת את CURRENT ROW. לדוגמה, אם מציינים שסוף החלון הוא CURRENT ROW, והשורה הבאה בחלון מכילה את אותו ערך, היא תיכלל בחישוב הפונקציה.
  • ‫BETWEEN <start> AND <end> – טווח, כולל שורות ההתחלה והסיום. הטווח לא חייב לכלול את השורה הנוכחית, אבל <start> חייב להיות קטן מ-<end> או שווה לו.
  • ‫<start> – מציין את היסט ההתחלה של החלון הזה, ביחס לשורה הנוכחית. אפשר להשתמש באפשרויות הבאות: 
    {UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
     כאשר <expr> הוא מספר שלם חיובי, PRECEDING מציין מספר שורה או ערך טווח קודמים, ו-FOLLOWING מציין מספר שורה או ערך טווח עוקבים. UNBOUNDED PRECEDING מייצג את השורה הראשונה במחיצה. אם ההתחלה קודמת לחלון, היא תוגדר לשורה הראשונה של המחיצה.
  • <end> – מציין את היסט הסיום של החלון הזה, ביחס לשורה הנוכחית. אלה האפשרויות שנתמכות: 
    {UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
     כאשר <expr> הוא מספר שלם חיובי, PRECEDING מציין מספר שורה קודמת או ערך טווח, ו-FOLLOWING מציין מספר שורה עוקבת או ערך טווח. ‫UNBOUNDED FOLLOWING מייצג את השורה האחרונה במחיצה. אם הערך של end חורג מסוף החלון, הוא יוגדר לשורה האחרונה במחיצה.

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

#legacySQL
SELECT name, value, SUM(value) OVER (ORDER BY value) AS RunningTotal
FROM
  (SELECT "a" AS name, 0 AS value),
  (SELECT "b" AS name, 1 AS value),
  (SELECT "c" AS name, 2 AS value),
  (SELECT "d" AS name, 3 AS value),
  (SELECT "e" AS name, 4 AS value);

הערך המוחזר:

+------+-------+--------------+
| name | value | RunningTotal |
+------+-------+--------------+
| a    |     0 |            0 |
| b    |     1 |            1 |
| c    |     2 |            3 |
| d    |     3 |            6 |
| e    |     4 |           10 |
+------+-------+--------------+

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

#legacySQL
SELECT
  name,
  value,
  AVG(value)
    OVER (ORDER BY value
          ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
    AS MovingAverage
FROM
  (SELECT "a" AS name, 0 AS value),
  (SELECT "b" AS name, 1 AS value),
  (SELECT "c" AS name, 2 AS value),
  (SELECT "d" AS name, 3 AS value),
  (SELECT "e" AS name, 4 AS value);

הערך המוחזר:

+------+-------+---------------+
| name | value | MovingAverage |
+------+-------+---------------+
| a    |     0 |           0.0 |
| b    |     1 |           0.5 |
| c    |     2 |           1.5 |
| d    |     3 |           2.5 |
| e    |     4 |           3.5 |
+------+-------+---------------+

תחביר

פונקציות אנליטיות (window functions)
AVG()
COUNT(*)
COUNT([DISTINCT])
MAX()
MIN()
STDDEV()
SUM()
 		אותה פעולה כמו פונקציות הצבירה התואמות, אבל החישוב מתבצע בחלון שמוגדר על ידי משפט OVER.
CUME_DIST() הפונקציה מחזירה ערך מסוג double שמציין את ההתפלגות המצטברת של ערך בקבוצת ערכים ...
DENSE_RANK() הפונקציה מחזירה את הדירוג של ערך מסוים בקבוצת ערכים, כמספר שלם.
FIRST_VALUE() הפונקציה מחזירה את הערך הראשון של השדה שצוין בחלון.
LAG() מאפשרת לקרוא נתונים משורה קודמת בתוך חלון.
LAST_VALUE() הפונקציה מחזירה את הערך האחרון של השדה שצוין בחלון.
LEAD() מאפשרת לקרוא נתונים משורה עוקבת בתוך חלון.
NTH_VALUE() הפונקציה מחזירה את הערך של <expr> במיקום <n> של מסגרת החלון ...
NTILE() מחלקת את החלון למספר הקטגוריות שצוין.
PERCENT_RANK() הפונקציה מחזירה את הדירוג של השורה הנוכחית, ביחס לשורות האחרות במחיצה.
PERCENTILE_CONT() הפונקציה מחזירה ערך שעבר אינטרפולציה, שימופה לארגומנט האחוזון ביחס לחלון ...
PERCENTILE_DISC() הפונקציה מחזירה את הערך הקרוב ביותר לאחוזון של הארגומנט בחלון.
RANK() הפונקציה מחזירה את הדירוג של ערך מסוים בקבוצת ערכים, כמספר שלם.
RATIO_TO_REPORT() הפונקציה מחזירה את היחס בין כל ערך לסכום הערכים.
ROW_NUMBER() הפונקציה מחזירה את מספר השורה הנוכחית של תוצאת השאילתה בחלון.
AVG(numeric_expr)
COUNT(*)
COUNT([DISTINCT] field)
MAX(field)
MIN(field)
STDDEV(numeric_expr)
SUM(field)
הפונקציות האנליטיות האלה מבצעות את אותה פעולה כמו הפונקציות המצטברות התואמות, אבל הן מחושבות בחלון שמוגדר על ידי משפט OVER.

הבדל משמעותי נוסף הוא שהפונקציה COUNT([DISTINCT] field) מפיקה תוצאות מדויקות כשמשתמשים בה כפונקציה אנליטית, עם התנהגות דומה לפונקציית הצבירה EXACT_COUNT_DISTINCT().

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

#legacySQL
SELECT
   corpus_date,
   corpus,
   word_count,
   SUM(word_count) OVER (
     PARTITION BY corpus_date
     ORDER BY word_count) annual_total
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   word='love'
ORDER BY
   corpus_date, word_count

החזרות:

corpus_date קורפוס word_count annual_total
0 שונים 37 37
0 סונטות 157 194
1590 2kinghenryvi 18 18
1590 1kinghenryvi 24 42
1590 3kinghenryvi 40 82
CUME_DIST()

הפונקציה מחזירה ערך מסוג double שמציין את ההתפלגות המצטברת של ערך בקבוצת ערכים, שמחושבת באמצעות הנוסחה <number of rows preceding or tied with the current row> / <total rows>. ערכים זהים מחזירים את אותו ערך של התפלגות מצטברת.

הפונקציה האנליטית הזו דורשת ORDER BY בסעיף OVER.

#legacySQL
SELECT
   word,
   word_count,
   CUME_DIST() OVER (PARTITION BY corpus ORDER BY word_count DESC) cume_dist,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

החזרות:

מילה word_count cume_dist
מטפחת 29 0.2
שביעות רצון 5 0.4
חוסר שביעות רצון 4 0.8
כלי נגינה 4 0.8
נסיבות 3 1.0
DENSE_RANK()

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

ערכים זהים מוצגים באותו דירוג. הדירוג של הערך הבא גדל ב-1. לדוגמה, אם שני ערכים חולקים את הדירוג 2, הערך הבא בדירוג הוא 3. אם אתם מעדיפים שיהיה פער ברשימת הדירוג, השתמשו ב-rank()‎.

הפונקציה האנליטית הזו דורשת ORDER BY בסעיף OVER.

#legacySQL
SELECT
   word,
   word_count,
   DENSE_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) dense_rank,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 החזרות:
מילה word_count dense_rank
מטפחת 29 1
שביעות רצון 5 2
חוסר שביעות רצון 4 3
כלי נגינה 4 3
נסיבות 3 4
FIRST_VALUE(<field_name>)

הפונקציה מחזירה את הערך הראשון של <field_name> בחלון.

#legacySQL
SELECT
   word,
   word_count,
   FIRST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) fv,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 1
 החזרות:
מילה word_count fv
לא מושלם 1 לא מושלם
LAG(<expr>[, <offset>[, <default_value>]])

מאפשרת לקרוא נתונים משורה קודמת בתוך חלון. באופן ספציפי, הפונקציה LAG() מחזירה את הערך של <expr> בשורה שנמצאת <offset> שורות לפני השורה הנוכחית. אם השורה לא קיימת, הפונקציה <default_value> מחזירה ערך.

#legacySQL
SELECT
   word,
   word_count,
   LAG(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lag,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

החזרות:

מילה word_count איטיות
מטפחת 29 null
שביעות רצון 5 מטפחת
חוסר שביעות רצון 4 שביעות רצון
כלי נגינה 4 חוסר שביעות רצון
נסיבות 3 כלי נגינה
LAST_VALUE(<field_name>)

הפונקציה מחזירה את הערך האחרון של <field_name> בחלון.

#legacySQL
SELECT
   word,
   word_count,
   LAST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) lv,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 1

החזרות:

מילה word_count lv
לא מושלם 1 לא מושלם

LEAD(<expr>[, <offset>[, <default_value>]])

מאפשרת לקרוא נתונים משורה עוקבת בתוך חלון. באופן ספציפי, LEAD() מחזירה את הערך של <expr> בשורה שנמצאת <offset> שורות אחרי השורה הנוכחית. אם השורה לא קיימת, הפונקציה מחזירה <default_value>.

#legacySQL
SELECT
   word,
   word_count,
   LEAD(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lead,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 החזרות:
מילה word_count ליד
מטפחת 29 שביעות רצון
שביעות רצון 5 חוסר שביעות רצון
חוסר שביעות רצון 4 כלי נגינה
כלי נגינה 4 נסיבות
נסיבות 3 null
NTH_VALUE(<expr>, <n>)

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

NTILE(<num_buckets>)

מחלקת רצף של שורות ל-<num_buckets> קטגוריות ומקצה לכל שורה מספר קטגוריה תואם, כמספר שלם. הפונקציה ntile() מקצה את מספרי הקטגוריות באופן שווה ככל האפשר ומחזירה ערך מ-1 עד <num_buckets> לכל שורה.

#legacySQL
SELECT
   word,
   word_count,
   NTILE(2) OVER (PARTITION BY corpus ORDER BY word_count DESC) ntile,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 החזרות:
מילה word_count ntile
מטפחת 29 1
שביעות רצון 5 1
חוסר שביעות רצון 4 1
כלי נגינה 4 2
נסיבות 3 2
PERCENT_RANK()

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

הפונקציה האנליטית הזו דורשת ORDER BY בסעיף OVER.

#legacySQL
SELECT
   word,
   word_count,
   PERCENT_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) p_rank,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 החזרות:
מילה word_count p_rank
מטפחת 29 0.0
שביעות רצון 5 0.25
חוסר שביעות רצון 4 0.5
כלי נגינה 4 0.5
נסיבות 3 1.0
PERCENTILE_CONT(<percentile>)

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

הערך של <percentile> חייב להיות בין 0 ל-1.

הפונקציה האנליטית הזו דורשת ORDER BY בסעיף OVER.

#legacySQL
SELECT
   word,
   word_count,
   PERCENTILE_CONT(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_cont,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 החזרות:
מילה word_count p_cont
מטפחת 29 4
שביעות רצון 5 4
חוסר שביעות רצון 4 4
כלי נגינה 4 4
נסיבות 3 4
PERCENTILE_DISC(<percentile>)

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

הערך של <percentile> חייב להיות בין 0 ל-1.

הפונקציה האנליטית הזו דורשת ORDER BY בסעיף OVER.

#legacySQL
SELECT
   word,
   word_count,
   PERCENTILE_DISC(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_disc,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 החזרות:
מילה word_count p_disc
מטפחת 29 4
שביעות רצון 5 4
חוסר שביעות רצון 4 4
כלי נגינה 4 4
נסיבות 3 4
RANK()

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

ערכים זהים מוצגים באותו דירוג. הדירוג של הערך הבא גדל בהתאם למספר הערכים הזהים שהופיעו לפניו. לדוגמה, אם שני ערכים חולקים את הדירוג 2, הערך הבא בדירוג יהיה 4, ולא 3. אם אתם מעדיפים שלא יהיו פערים ברשימת הדירוג, השתמשו ב-dense_rank().

הפונקציה האנליטית הזו דורשת ORDER BY בסעיף OVER.

#legacySQL
SELECT
   word,
   word_count,
   RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) rank,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 החזרות:
מילה word_count דירוג
מטפחת 29 1
שביעות רצון 5 2
חוסר שביעות רצון 4 3
כלי נגינה 4 3
נסיבות 3 5
RATIO_TO_REPORT(<column>)

הפונקציה מחזירה את היחס בין כל ערך לסכום הערכים, כערך מסוג double בין 0 ל-1.

#legacySQL
SELECT
   word,
   word_count,
   RATIO_TO_REPORT(word_count) OVER (PARTITION BY corpus ORDER BY word_count DESC) r_to_r,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 החזרות:
מילה word_count r_to_r
מטפחת 29 0.6444444444444445
שביעות רצון 5 0.1111111111111111
חוסר שביעות רצון 4 0.08888888888888889
כלי נגינה 4 0.08888888888888889
נסיבות 3 0.06666666666666667
ROW_NUMBER()

הפונקציה מחזירה את מספר השורה הנוכחי של תוצאת השאילתה בחלון, החל מ-1.

#legacySQL
SELECT
   word,
   word_count,
   ROW_NUMBER() OVER (PARTITION BY corpus ORDER BY word_count DESC) row_num,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5
 החזרות:
מילה word_count row_num
מטפחת 29 1
שביעות רצון 5 2
חוסר שביעות רצון 4 3
כלי נגינה 4 4
נסיבות 3 5

פונקציות אחרות

תחביר

פונקציות אחרות
CASE WHEN ... THEN אפשר להשתמש ב-CASE כדי לבחור בין שני ביטויים חלופיים או יותר בשאילתה.
CURRENT_USER() מחזירה את כתובת האימייל של המשתמש שמריץ את השאילתה.
EVERY() הפונקציה מחזירה את הערך True אם הארגומנט נכון לכל ערכי הקלט שלו.
FROM_BASE64() ממירה את מחרוזת הקלט בקידוד base-64 לפורמט BYTES.
HASH() מחשבת ומחזירה ערך גיבוב (hash) חתום של 64 ביט ...
FARM_FINGERPRINT() מחשבת ומחזירה ערך טביעת אצבע חתום של 64 ביט ...
IF() אם הארגומנט הראשון נכון, הפונקציה מחזירה את הארגומנט השני. אחרת, היא מחזירה את הארגומנט השלישי.
POSITION() הפונקציה מחזירה את המיקום הסידורי של הארגומנט, החל מ-1.
SHA1() הפונקציה מחזירה גיבוב (hash) של SHA1 בפורמט BYTES.
SOME() הפונקציה מחזירה את הערך True אם הארגומנט נכון לפחות לאחד מהקלטים שלו.
TO_BASE64() הפונקציה ממירה את הארגומנט BYTES למחרוזת בקידוד Base64.
CASE WHEN when_expr1 THEN then_expr1
  WHEN when_expr2 THEN then_expr2 ...
  ELSE else_expr END
אפשר להשתמש ב-CASE כדי לבחור בין שני ביטויים חלופיים או יותר בשאילתה. ביטויים של WHEN חייבים להיות בוליאניים, וכל הביטויים בסעיפי THEN ובסעיף ELSE חייבים להיות מסוגים תואמים.
CURRENT_USER()
החזרה של כתובת האימייל של המשתמש שמריץ את השאילתה.
EVERY(<condition>)
הפונקציה מחזירה את הערך true אם condition נכון לכל ערכי הקלט שלה. כשמשתמשים בפונקציה הזו עם הסעיף OMIT IF, היא שימושית לשאילתות שכוללות שדות חוזרים.
FROM_BASE64(<str>)
הפונקציה ממירה את מחרוזת הקלט str בקידוד base64 לפורמט BYTES. כדי להמיר BYTES למחרוזת בקידוד base64, משתמשים ב-TO_BASE64().
HASH(expr)
מחשבת ומחזירה ערך גיבוב (hash) חתום של 64 ביט של הבייטים של expr, כפי שמוגדר בספריית CityHash (גרסה 1.0.3). הפונקציה תומכת בכל ביטוי של מחרוזת או מספר שלם, והיא מכבדת את IGNORE CASE עבור מחרוזות, ומחזירה ערכים ללא תלות באותיות רישיות.
FARM_FINGERPRINT(expr)
מחשבת ומחזירה ערך טביעת אצבע חתום של 64 ביט של הקלט STRING או BYTES באמצעות הפונקציה Fingerprint64 מספריית הקוד הפתוח FarmHash. הפלט של הפונקציה הזו לקלט מסוים לא ישתנה אף פעם, והוא תואם לפלט של הפונקציה FARM_FINGERPRINT כשמשתמשים ב-GoogleSQL. הפונקציה מכבדת את IGNORE CASE עבור מחרוזות, ומחזירה ערכים ללא הבדל בין אותיות רישיות לקטנות.
IF(condition, true_return, false_return)
הפונקציה מחזירה את הערך true_return או false_return, בהתאם לערך של condition (נכון או לא נכון). ערכי ההחזרה יכולים להיות ערכים מילוליים או ערכים שנגזרים משדות, אבל הם חייבים להיות מאותו סוג נתונים. אין צורך לכלול ערכים שנגזרים משדות בסעיף SELECT.
POSITION(field)
הפונקציה מחזירה את המיקום הסידורי של field בתוך קבוצה של שדות חוזרים, החל מ-1.
SHA1(<str>)
הפונקציה מחזירה גיבוב (hash) של SHA1, בפורמט BYTES, של מחרוזת הקלט str. אפשר להמיר את התוצאה ל-base64 באמצעות הפונקציה TO_BASE64()‎. לדוגמה: 
#legacySQL
SELECT
  TO_BASE64(SHA1(corpus))
FROM
  [bigquery-public-data:samples.shakespeare]
LIMIT
  100;
SOME(<condition>)
הפונקציה מחזירה את הערך true אם condition נכון לפחות לאחד מהקלטים שלה. כשמשתמשים בפונקציה הזו עם הסעיף OMIT IF, היא שימושית לשאילתות שכוללות שדות חוזרים.
TO_BASE64(<bin_data>)
הפונקציה ממירה את הקלט bin_data מסוג BYTES למחרוזת בקידוד base64. לדוגמה: 
#legacySQL
SELECT
  TO_BASE64(SHA1(title))
FROM
  [bigquery-public-data:samples.wikipedia]
LIMIT
  100;
 כדי להמיר מחרוזת בקידוד base64 ל-BYTES, משתמשים ב-FROM_BASE64().

דוגמאות מתקדמות

  • חלוקת התוצאות לקטגוריות באמצעות תנאים

    השאילתה הבאה משתמשת בבלוק CASE/WHEN כדי לחלק את התוצאות לקטגוריות של "אזור" על סמך רשימה של מדינות. אם המצב לא מופיע כאפשרות באחת מהצהרות ה-WHEN, ערך ברירת המחדל של המצב יהיה "None".

    לדוגמה:

    #legacySQL
SELECT
  CASE
    WHEN state IN ('WA', 'OR', 'CA', 'AK', 'HI', 'ID',
                   'MT', 'WY', 'NV', 'UT', 'CO', 'AZ', 'NM')
      THEN 'West'
    WHEN state IN ('OK', 'TX', 'AR', 'LA', 'TN', 'MS', 'AL',
                   'KY', 'GA', 'FL', 'SC', 'NC', 'VA', 'WV',
                   'MD', 'DC', 'DE')
      THEN 'South'
    WHEN state IN ('ND', 'SD', 'NE', 'KS', 'MN', 'IA',
                   'MO', 'WI', 'IL', 'IN', 'MI', 'OH')
      THEN 'Midwest'
    WHEN state IN ('NY', 'PA', 'NJ', 'CT',
                   'RI', 'MA', 'VT', 'NH', 'ME')
      THEN 'Northeast'
    ELSE 'None'
  END as region,
  average_mother_age,
  average_father_age,
  state, year
FROM
  (SELECT
     year, state,
     SUM(mother_age)/COUNT(mother_age) as average_mother_age,
     SUM(father_age)/COUNT(father_age) as average_father_age
   FROM
     [bigquery-public-data:samples.natality]
   WHERE
     father_age < 99
   GROUP BY
     year, state)
ORDER BY
  year
LIMIT 5;

    החזרות:

    +--------+--------------------+--------------------+-------+------+
| region | average_mother_age | average_father_age | state | year |
+--------+--------------------+--------------------+-------+------+
| South  | 24.342600163532296 | 27.683769419460344 | AR    | 1969 |
| West   | 25.185041908446163 | 28.268214055448098 | AK    | 1969 |
| West   | 24.780776677578217 | 27.831181063905248 | CA    | 1969 |
| West   | 25.005834769924412 | 27.942978384829598 | AZ    | 1969 |
| South  | 24.541730952905738 | 27.686430093306885 | AL    | 1969 |
+--------+--------------------+--------------------+-------+------+

  • סימולציה של טבלת צירים

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

    לדוגמה:

    #legacySQL
SELECT
  page_title,
  /* Populate these columns as True or False, */
  /*  depending on the condition */
  IF (page_title CONTAINS 'search',
      INTEGER(total), 0) AS search,
  IF (page_title CONTAINS 'Earth' OR
      page_title CONTAINS 'Maps', INTEGER(total), 0) AS geo,
FROM
  /* Subselect to return top revised Wikipedia articles */
  /* containing 'Google', followed by additional text. */
  (SELECT
    TOP (title, 5) as page_title,
    COUNT (*) as total
   FROM
     [bigquery-public-data:samples.wikipedia]
   WHERE
     REGEXP_MATCH (title, r'^Google.+') AND wp_namespace = 0
  );

    החזרות:

    +---------------+--------+------+
|  page_title   | search | geo  |
+---------------+--------+------+
| Google search |   4261 |    0 |
| Google Earth  |      0 | 3874 |
| Google Chrome |      0 |    0 |
| Google Maps   |      0 | 2617 |
| Google bomb   |      0 |    0 |
+---------------+--------+------+

  • שימוש ב-HASH כדי לבחור מדגם אקראי של הנתונים

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

    לדוגמה, השאילתה הבאה תמצא את הערך HASH() של הערך "title", ואז תבדוק אם הערך הזה מודולו "2" הוא אפס. כתוצאה מכך, כ-50% מהערכים אמורים להיות מסומנים בתווית "נדגם". כדי לדגום פחות ערכים, מגדילים את הערך של פעולת המודולו מ-"2" למספר גדול יותר. השאילתה משתמשת בפונקציה ABS בשילוב עם HASH, כי HASH יכולה להחזיר ערכים שליליים, והאופרטור modulo בערך שלילי מניב ערך שלילי.

    לדוגמה:

    #legacySQL
SELECT
  title,
  HASH(title) AS hash_value,
  IF(ABS(HASH(title)) % 2 == 1, 'True', 'False')
    AS included_in_sample
FROM
  [bigquery-public-data:samples.wikipedia]
WHERE
  wp_namespace = 0
LIMIT 5;