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

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

שאילתת משנה היא משפט 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;

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

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

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

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

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

תחביר

פונקציות לביצוע פעולות ברמת הביטים

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

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

תחביר

פונקציות Cast

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

תחביר

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

‫

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.

לדוגמה:

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.

תחביר

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_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 אם שני הביטויים נכונים.
• הפונקציה מחזירה 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(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('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('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')

‫

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

'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(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

תחביר

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