תחביר, פונקציות ואופרטורים של 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;

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

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

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

#legacySQL
SELECT
  *
FROM
  dataset.table$30;

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

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.

אי אפשר להשתמש בסעיפים ON בפעולות CROSS JOIN. 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-גרמות שבהן המילה הראשונה ב-n-גרמה היא גם המילה השנייה ב-n-גרמה אחרת, שבה המילה השלישית היא 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 לא תומך בצירופים חצי-פנימיים או חצי-חיצוניים מתואמים. שאילתת המשנה לא יכולה להפנות לשדות משאילתת החוץ.
• שאילתת המשנה שמשמשת בצירוף חצי או בצירוף חצי הפוך חייבת לבחור שדה אחד בדיוק.
• הסוגים של השדה שנבחר והשדה שנעשה בו שימוש מהשאילתה החיצונית בסעיף WHERE חייבים להיות זהים לחלוטין. מערכת BigQuery לא תבצע המרה של טיפוסי נתונים עבור הצטרפויות חלקיות או הצטרפויות חלקיות הפוכות.

סעיף WHERE

הפסקה WHERE, שנקראת לפעמים predicate, מסננת רשומות שנוצרו על ידי הפסקה 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');

השמטת רשומת 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 בפונקציה TOPבמקום ב-GROUP BY יפתור בעיות מסוימות שקשורות לשינוי גודל.

סעיף HAVING

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

דוגמה

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

הערה:

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

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

רוב סעיפי ההצהרה של SELECT תומכים בפונקציות. אין צורך לפרט שדות שמופיעים בהפניה לפונקציה באף SELECTclause. לכן, השאילתה הבאה תקינה, למרות שהשדה 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 ברמת הביטים
|	ערך OR ברמת הביטים
^	ערך XOR ברמת הביטים
<<	הזזה שמאלה ברמת הביטים
>>	הזזה בינארית ימינה
~	NOT ברמת הביטים
BIT_COUNT()	הפונקציה מחזירה את מספר הביטים ...

פונקציות Cast
BOOLEAN()	המרת הערך לערך בוליאני.
BYTES()	המרת הערך לבייטים.
CAST(expr AS type)	הפונקציה ממירה את expr למשתנה מסוג type.
FLOAT()	הפעלת Cast למסך מפוצל.
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()	הפונקציה מחזירה את השעה של חותמת זמן כמספר שלם בין 0 ל-23.
MINUTE()	הפונקציה מחזירה את הדקות של חותמת זמן כמספר שלם בין 0 ל-59.
MONTH()	הפונקציה מחזירה את החודש של חותמת זמן כמספר שלם בין 1 ל-12.
MSEC_TO_TIMESTAMP()	הפונקציה ממירה חותמת זמן של מערכת UNIX באלפיות השנייה לחותמת זמן.
NOW()	הפונקציה מחזירה את חותמת הזמן הנוכחית של מערכת UNIX במיקרו-שניות.
PARSE_UTC_USEC()	ממירה מחרוזת תאריך לחותמת זמן במיקרו-שניות לפי ראשית זמן יוניקס (Unix epoch).
QUARTER()	הפונקציה מחזירה את הרבעון בשנה של חותמת זמן כמספר שלם בין 1 ל-4.
SEC_TO_TIMESTAMP()	ממירה חותמת זמן של מערכת UNIX בשניות לחותמת זמן.
SECOND()	הפונקציה מחזירה את השניות של חותמת זמן כמספר שלם בין 0 ל-59.
STRFTIME_UTC_USEC()	מחזירה מחרוזת תאריך בפורמט date_format_str.
TIME()	מחזירה חותמת זמן בפורמט %H:%M:%S.
TIMESTAMP()	הפונקציה ממירה מחרוזת תאריך לערך מסוג TIMESTAMP.
TIMESTAMP_TO_MSEC()	הפונקציה ממירה חותמת זמן לחותמת זמן של מערכת Unix באלפיות השנייה.
TIMESTAMP_TO_SEC()	ממירה חותמת זמן לחותמת זמן של מערכת UNIX בשניות.
TIMESTAMP_TO_USEC()	הפונקציה ממירה חותמת זמן לחותמת זמן של מערכת UNIX במיקרו-שניות.
USEC_TO_TIMESTAMP()	הפונקציה ממירה חותמת זמן של מערכת UNIX במיקרו-שניות לחותמת זמן.
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()	הפונקציה מחזירה את השבוע של חותמת זמן כמספר שלם בין 1 ל-53.
YEAR()	הפונקציה מחזירה את השנה של חותמת זמן.

פונקציות 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()	מחזירה ערך אקראי של מספר ממשי בטווח 0.0 <= value < 1.0.
ROUND()	מעגלת את הארגומנט כלפי מעלה או מטה למספר השלם הקרוב ביותר.
SIN()	הפונקציה מחזירה את הסינוס של הארגומנט.
SINH()	הפונקציה מחזירה את הסינוס ההיפרבולי של הארגומנט.
SQRT()	הפונקציה מחזירה את השורש הריבועי של הביטוי.
TAN()	מחזירה את הטנגנס של הארגומנט.
TANH()	הפונקציה מחזירה את הטנגנס ההיפרבולי של הארגומנט.

פונקציות של ביטויים רגולריים
REGEXP_MATCH()	הפונקציה מחזירה את הערך True אם הארגומנט תואם לביטוי הרגולרי.
REGEXP_EXTRACT()	הפונקציה מחזירה את החלק של הארגומנט שתואם לקבוצת הלכידה בביטוי הרגולרי.
REGEXP_REPLACE()	מחליפה תת-מחרוזת שתואמת לביטוי רגולרי.

פונקציות מחרוזת
CONCAT()	הפונקציה מחזירה את השרשור של שתי מחרוזות או יותר, או NULL אם אחד מהערכים הוא NULL.
expr CONTAINS 'str'	הפונקציה מחזירה true אם expr מכיל את ארגומנט המחרוזת שצוין.
INSTR()	הפונקציה מחזירה את האינדקס מבוסס-1 של המופע הראשון של מחרוזת.
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.

פונקציות חלון
AVG() COUNT(*) COUNT([DISTINCT]) MAX() MIN() STDDEV() SUM()	אותה פעולה כמו פונקציות הצבירה המתאימות, אבל החישוב מתבצע על חלון שמוגדר על ידי פסוקית OVER.
CUME_DIST()	מחזירה ערך כפול שמציין את ההתפלגות המצטברת של ערך בקבוצת ערכים ...
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()	ממירה את מחרוזת הקלט שמקודדת ב-Base64 לפורמט BYTES.
HASH()	הפונקציה מחשבת ומחזירה ערך גיבוב (hash) עם סימן בן 64 ביט ...
FARM_FINGERPRINT()	הפונקציה מחשבת ומחזירה ערך טביעת אצבע עם סימן בן 64 ביט ...
IF()	אם הארגומנט הראשון הוא true, מחזירה את הארגומנט השני. אחרת, מחזירה את הארגומנט השלישי.
POSITION()	הפונקציה מחזירה את המיקום הרציף של הארגומנט, החל מ-1.
SHA1()	מחזירה גיבוב 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, יכולת ההתאמה של האופרטור הזה תצטמצם, וזמן הביצוע של השאילתה עלול להתארך משמעותית או שהשאילתה עלולה להיכשל.

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

‫

Returns the minimum value in the scope of the function.

NEST(expr)

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

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

NTH(n, field)

‫

מחזירה את הערך הרציף ה-n בתחום הפונקציה, כאשר n הוא קבוע. הפונקציה NTH מתחילה את הספירה ב-1, ולכן אין איבר אפס. אם בהיקף הפונקציה יש פחות מ-n ערכים, הפונקציה מחזירה NULL.

QUANTILES(expr[, buckets])

הפונקציה מחשבת את המינימום, המקסימום והכמויות המשוערים של ביטוי הקלט. המערכת מתעלמת מערכי הקלט של NULL. קלט ריק או קלט שמורכב רק מ-NULL יניב פלט של NULL. מספר הכמויות שחושבו נקבע באמצעות הפרמטר האופציונלי buckets, שכולל את המינימום והמקסימום בספירה. כדי לחשב אחוזונים משוערים, משתמשים ב-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)) מעריכה את ה-19 של expr (האחוזון ה-95). שני האומדנים כוללים שולי שגיאה של 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 הרשומות העליונות לפי תדירות. פרטים נוספים מופיעים בתיאור שלמעלה.

UNIQUE(expr)

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

VARIANCE(numeric_expr)

‫

Computes the variance of the values computed by numeric_expr. שורות עם ערך NULL לא נכללות בחישוב. הפונקציה VARIANCE היא כינוי של הפונקציה VAR_SAMP.

VAR_POP(numeric_expr)

‫

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

VAR_SAMP(numeric_expr)

‫

Computes the sample variance of the values computed by 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
|	ערך 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

פונקציות Cast

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

תחביר

פונקציות Cast
BOOLEAN()	המרת הערך לערך בוליאני.
BYTES()	המרת הערך לבייטים.
CAST(expr AS type)	הפונקציה ממירה את expr למשתנה מסוג type.
FLOAT()	הפעלת Cast למסך מפוצל.
HEX_STRING()	המרת הערך למחרוזת הקסדצימלית.
INTEGER()	המרת הערך למספר שלם.
STRING()	המרת הערך למחרוזת.

BOOLEAN(<numeric_expr>)

• הפונקציה מחזירה את הערך true אם <numeric_expr> לא שווה ל-0 ולא ל-NULL.
• הפונקציה מחזירה false אם <numeric_expr> הוא 0.
• הפונקציה מחזירה NULL אם <numeric_expr> הוא NULL.

BYTES(string_expr)

‫

Returns string_expr as a value of type bytes.

CAST(expr AS type)

‫

Converts expr into a variable of type type.

FLOAT(expr)

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

HEX_STRING(numeric_expr)

מחזירה את הערך numeric_expr כמחרוזת הקסדצימלית.

INTEGER(expr)

מבצע המרה של expr למספר שלם ב-64 ביט.

• הפונקציה מחזירה NULL אם expr היא מחרוזת שלא תואמת לערך של מספר שלם.
• הפונקציה מחזירה את מספר המיקרו-שניות מאז תקופת ה-Unix אם 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

‫

Returns true if expr1 is greater than 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)

‫

If expr is not null, returns expr, otherwise returns null_default.

IS_INF(numeric_expr)

‫

Returns true if numeric_expr is positive or negative infinity.

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)

‫

If expr is not null, returns expr, otherwise returns null_default. הפונקציה NVL היא כינוי של הפונקציה IFNULL.

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

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

פונקציות של תאריך ושעה שפועלות עם חותמות זמן של UNIX פועלות על זמן 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()	הפונקציה מחזירה את השעה של חותמת זמן כמספר שלם בין 0 ל-23.
MINUTE()	הפונקציה מחזירה את הדקות של חותמת זמן כמספר שלם בין 0 ל-59.
MONTH()	הפונקציה מחזירה את החודש של חותמת זמן כמספר שלם בין 1 ל-12.
MSEC_TO_TIMESTAMP()	הפונקציה ממירה חותמת זמן של מערכת UNIX באלפיות השנייה לחותמת זמן.
NOW()	הפונקציה מחזירה את חותמת הזמן הנוכחית של מערכת UNIX במיקרו-שניות.
PARSE_UTC_USEC()	ממירה מחרוזת תאריך לחותמת זמן במיקרו-שניות לפי ראשית זמן יוניקס (Unix epoch).
QUARTER()	הפונקציה מחזירה את הרבעון בשנה של חותמת זמן כמספר שלם בין 1 ל-4.
SEC_TO_TIMESTAMP()	ממירה חותמת זמן של מערכת UNIX בשניות לחותמת זמן.
SECOND()	הפונקציה מחזירה את השניות של חותמת זמן כמספר שלם בין 0 ל-59.
STRFTIME_UTC_USEC()	מחזירה מחרוזת תאריך בפורמט date_format_str.
TIME()	מחזירה חותמת זמן בפורמט %H:%M:%S.
TIMESTAMP()	הפונקציה ממירה מחרוזת תאריך לערך מסוג TIMESTAMP.
TIMESTAMP_TO_MSEC()	הפונקציה ממירה חותמת זמן לחותמת זמן של מערכת Unix באלפיות השנייה.
TIMESTAMP_TO_SEC()	ממירה חותמת זמן לחותמת זמן של מערכת UNIX בשניות.
TIMESTAMP_TO_USEC()	הפונקציה ממירה חותמת זמן לחותמת זמן של מערכת UNIX במיקרו-שניות.
USEC_TO_TIMESTAMP()	הפונקציה ממירה חותמת זמן של מערכת UNIX במיקרו-שניות לחותמת זמן.
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()	הפונקציה מחזירה את השבוע של חותמת זמן כמספר שלם בין 1 ל-53.
YEAR()	הפונקציה מחזירה את השנה של חותמת זמן.

CURRENT_DATE()

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

לדוגמה:

SELECT CURRENT_DATE();

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

CURRENT_TIME()

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

לדוגמה:

SELECT CURRENT_TIME();

החזרה: 01:32:56

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

לדוגמה:

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 באלפיות השנייה לסוג נתונים של חותמת זמן.

לדוגמה:

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 epoch). הערך של 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 בשניות לסוג נתונים של חותמת זמן.

לדוגמה:

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>)

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

לדוגמה:

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

הערך שמוחזר: 1349053323000

TIMESTAMP_TO_SEC(<timestamp>)

‫

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

לדוגמה:

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

הערך שמוחזר: 1349053323

TIMESTAMP_TO_USEC(<timestamp>)

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

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

לדוגמה:

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 מתרחש ביום שישי, 2008-04-11, ומגדירים את day_of_week ל-2 (יום שלישי), הפונקציה מחזירה חותמת זמן של ראשית זמן יוניקס (Unix epoch) ליום שלישי, 2008-04-08.

לדוגמה:

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.

תחביר

פונקציות 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)

‫

Converts 32 least significant bits of integer_value to human-readable IPv4 address string. לדוגמה, הפונקציה 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 בהתאם לביטוי JSONPath‏ json_path. הערך json_path חייב להיות קבוע מחרוזת. הפונקציה מחזירה את הערך בפורמט מחרוזת JSON.

JSON_EXTRACT_SCALAR(json, json_path)

בוחר ערך ב-json בהתאם לביטוי JSONPath‏ json_path. הערך 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 אם אחד מהביטויים או שניהם הם 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()	מחזירה ערך אקראי של מספר ממשי בטווח 0.0 <= value < 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)

‫

Returns numeric_expr, converted from radians to degrees.

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)

‫

Returns the result of raising numeric_expr1 to the power of numeric_expr2.

RADIANS(numeric_expr)

‫

Returns numeric_expr, converted from degrees to radians. (הערה: π רדיאנים שווים ל-180 מעלות).

RAND([int32_seed])

‫

מחזירה ערך אקראי של מספר עשרוני בטווח 0.0 <= value < 1.0. כל ערך של int32_seed תמיד יוצר את אותו רצף של מספרים אקראיים בתוך שאילתה נתונה, כל עוד לא משתמשים בסעיף LIMIT. אם לא מציינים את int32_seed, ‏ BigQuery משתמש בחותמת הזמן הנוכחית כערך ה-seed.

ROUND(numeric_expr [, digits])

‫

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

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

‫

מחזירה את האינדקס מבוסס-1 של המופע הראשון של 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')

‫

Pads str1 בצד שמאל עם str2, חוזר על str2 עד שמחרוזת התוצאה היא בדיוק numeric_expr תווים. לדוגמה: הפונקציה LPAD('1', 7, '?') מחזירה ??????1.

LTRIM('str1' [, str2])

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

לדוגמה:

‫SELECT LTRIM("Say hello", "yaS") returns " hello".

‫SELECT LTRIM("Say hello", " ySa") returns "hello".

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

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

RIGHT('str', numeric_expr)

הפונקציה מחזירה את numeric_expr התווים הימניים ביותר של str. אם המספר ארוך יותר מהמחרוזת, הפונקציה תחזיר את המחרוזת כולה. לדוגמה: הפונקציה RIGHT('kirkland', 4) מחזירה land.

RPAD('str1', numeric_expr, 'str2')

‫

Pads str1 on the right with str2, repeating str2 until the result string is exactly numeric_expr characters. לדוגמה: הפונקציה RPAD('1', 7, '?') מחזירה 1??????.

RTRIM('str1' [, str2])

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

לדוגמה:

‫SELECT RTRIM("Say hello", "leo") returns "Say h".

‫SELECT RTRIM("Say hello ", " hloe") returns "Say".

SPLIT('str' [, 'delimiter'])

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

SUBSTR('str', index [, max_len])

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

UPPER('str')

‫

מחזירה את המחרוזת המקורית עם כל התווים באותיות רישיות.

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

כדי להשתמש בתו בריחה (escape) עם תווים מיוחדים, אפשר להשתמש באחת מהשיטות הבאות:

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

דוגמאות לשימוש בתווי Escape:

'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

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

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

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

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

תחביר

פונקציות של תווים כלליים לחיפוש בטבלאות
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

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

#legacySQL
SELECT <window_function>
  OVER (
      [PARTITION BY <expr>]
      [ORDER BY <expr> [ASC | DESC]]
      [<window-frame-clause>]
     )

PARTITION BY

מגדיר את המחיצה הבסיסית שהפונקציה פועלת עליה. מציינים שם של עמודה אחת או יותר שמופרדים בפסיקים. תיווצר מחיצה אחת לכל קבוצה נפרדת של ערכים בעמודות האלה, בדומה לסעיף GROUP BY. אם לא מציינים את PARTITION BY, מחיצת הבסיס היא כל השורות בקלט של פונקציה אנליטית (window function).

הסעיף PARTITION BY מאפשר גם לפונקציות אנליטיות (window function) לחלק את הנתונים ולבצע את הפעולות במקביל. אם רוצים להשתמש בפונקציה אנליטית (window function) עם 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 |
+------+-------+---------------+

תחביר

פונקציות חלון
AVG() COUNT(*) COUNT([DISTINCT]) MAX() MIN() STDDEV() SUM()	אותה פעולה כמו פונקציות הצבירה המתאימות, אבל החישוב מתבצע על חלון שמוגדר על ידי פסוקית OVER.
CUME_DIST()	מחזירה ערך כפול שמציין את ההתפלגות המצטברת של ערך בקבוצת ערכים ...
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) מחזירה תוצאות מדויקות כשמשתמשים בה כפונקציה אנליטית (window function), וההתנהגות שלה דומה לזו של פונקציית הצבירה 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>. ערכים שווים מחזירים את אותו ערך של התפלגות מצטברת.

פונקציה אנליטית (window function) זו דורשת 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().

פונקציה אנליטית (window function) זו דורשת 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> הוא אינדקס מבוסס-1.

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.

פונקציה אנליטית (window function) זו דורשת 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.

פונקציה אנליטית (window function) זו דורשת 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.

פונקציה אנליטית (window function) זו דורשת 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().

פונקציה אנליטית (window function) זו דורשת 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>)

הפונקציה מחזירה את היחס של כל ערך לסכום הערכים, כמספר עשרוני בין 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()	ממירה את מחרוזת הקלט שמקודדת ב-Base64 לפורמט BYTES.
HASH()	הפונקציה מחשבת ומחזירה ערך גיבוב (hash) עם סימן בן 64 ביט ...
FARM_FINGERPRINT()	הפונקציה מחשבת ומחזירה ערך טביעת אצבע עם סימן בן 64 ביט ...
IF()	אם הארגומנט הראשון הוא true, מחזירה את הארגומנט השני. אחרת, מחזירה את הארגומנט השלישי.
POSITION()	הפונקציה מחזירה את המיקום הרציף של הארגומנט, החל מ-1.
SHA1()	מחזירה גיבוב 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>)

‫

Converts the base64-encoded input string str into BYTES format. כדי להמיר 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 (true או false). הערכים המוחזרים יכולים להיות ערכים מילוליים או ערכים שנגזרים משדה, אבל הם צריכים להיות מאותו סוג נתונים. אין צורך לכלול את הערכים שנגזרים משדה מסוים בסעיף 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>)

‫

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

  לדוגמה:

  #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;