באמצעות שפת השאילתות של GoogleVisual API אפשר לבצע מניפולציות שונות על הנתונים בשאילתה למקור הנתונים.
תוכן העניינים
מבוא
בדרך כלל, הרכיבים החזותיים מצפים לנתונים בצורה ספציפית. לדוגמה, בתרשים עוגה עשוי להיות נתונים כשתי עמודות: תווית טקסט וערך מספרי. יכול להיות שהנתונים במקור הנתונים לא יהיו זהים למבנה הזה. לדוגמה, במקור הנתונים יכול להיות יותר משתי עמודות, או שסדר העמודות לא יהיה תואם לסדר הצפוי לפי תרשים העוגה.
שפת השאילתה מאפשרת לשלוח בקשות לשינוי ולעיצוב של הנתונים למקור הנתונים, ולהבטיח שמבנה הנתונים והתוכן המוחזרים תואמים למבנה הצפוי.
התחביר של שפת השאילתה דומה ל-SQL. מפתחים שמכירים את SQL צריכים להיות מסוגלים ללמוד במהירות את שפת השאילתות הזו ולהשתמש בה. יש הרבה מדריכים ל-SQL שזמינים באינטרנט. יש כמה הבדלים בין שפת השאילתה הזו לבין ה-SQL שמתוארים בקטע syntax.
חשוב לשים לב שמקורות הנתונים לא נדרשים כדי להטמיע את שפת השאילתה, ואם כן, כדי להטמיע את כל התכונות של השפה. אלא אם יש לך סיבה להאמין אחרת, אין להסתמך על מקור נתונים כדי להטמיע את כל התכונות של השפה הזו.
שימוש בשפת השאילתה
אפשר לצרף מחרוזת שאילתה לבקשה של מקור נתונים בשתי דרכים: על ידי הגדרת מחרוזת השאילתה מתוך קוד JavaScript, או על ידי הגדרת מחרוזת השאילתה כפרמטר בכתובת ה-URL של מקור הנתונים. אם הבקשה לא כוללת מחרוזת שאילתה, ברירת המחדל של מקור נתונים היא להחזיר את כל השורות והעמודות לפי סדר ברירת המחדל של השורות/העמודות. אפשר לשנות את זה על ידי הכללת מחרוזת שאילתה בבקשה למקור נתונים.
הגדרת השאילתה מ-JavaScript
כדי להגדיר את מחרוזת השאילתה מתוך קוד JavaScript, קוראים ל-method setQuery
מהמחלקה google.visualization.Query
.
var query = new google.visualization.Query(DATA_SOURCE_URL); query.setQuery('select dept, sum(salary) group by dept'); query.send(handleQueryResponse);
הגדרת השאילתה בכתובת ה-URL של מקור הנתונים
אפשר להוסיף את מחרוזת השאילתה לכתובת ה-URL של מקור הנתונים באמצעות הפרמטר tq
.
אם מגדירים את השאילתה בפרמטר של כתובת האתר במקום ב-JavaScript, אפשר להשתמש בקלות ברכיבים חזותיים שנכתבו על ידי מפתחים אחרים, ועדיין להתאים אישית את השאילתה.
מחרוזת השאילתה צריכה להיות מקודדת כראוי כפרמטר של כתובת URL.
אפשר לקודד כתובת URL באמצעות פונקציית encodeURIComponent
של JavaScript או לקודד ידנית באמצעות כלי הקידוד בסוף הקטע הזה.
דוגמה:
נבחן את מחרוזת השאילתה הבאה בגיליון אלקטרוני של Google. (חשוב לשים לב שמזהי עמודות בגיליונות אלקטרוניים הם תמיד אותיות. הטקסט בכותרת העמודה שמוצג בגיליון האלקטרוני שפורסם הוא תוויות, ולא מזהים. צריך להשתמש במזהה, ולא בתווית במחרוזת השאילתה.)
select A, sum(B) group by A
כשהשאילתה הזו מקודדת, היא הופכת ל:
select%20A%2C%20sum(B)%20group%20by%20A
נניח שזו כתובת ה-URL של הגיליון האלקטרוני שלכם:
https://docs.google.com/a/google.com/spreadsheets/d/1r8_mfnZAvTFmT02JHi1XgOwn_-sLCR9XgmR8wEQ4uW4
מוסיפים את /gviz/tq?tq=
YOUR_QUERY_STRING לכתובת ה-URL של הגיליון האלקטרוני כדי לקבל את מחרוזת השאילתה הסופית:
https://docs.google.com/a/google.com/spreadsheets/d/1r8_mfnZAvTFmT02JHi1XgOwn_-sLCR9XgmR8wEQ4uW4/gviz/tq?tq=select%A%2C%20sum(B)%20group%20by%20A
משתמשים בכלי הבא כדי לקודד או לפענח מחרוזת שאילתה:
הערה: כדי לגשת לנתונים בגיליון אלקטרוני פרטי, צריך להעביר פרטי כניסה מפורשים להרשאה באמצעות OAuth. לפרטים נוספים, אפשר לעיין בקטע Google Sheets: Authorization.
תחביר השפה
סקירה כללית
התחביר של 'שפת השאילתות של GoogleVisual API' תוכנן להיות דומה לתחביר של SQL. עם זאת, זו רק קבוצת משנה של SQL, וצריך ללמוד כמה תכונות משלה. אם אתם מכירים SQL, זה לא אמור להיות קשה מדי ללמוד.
טבלאות נתונים
במסמך הזה משתמשים במונח טבלת נתונים כדי להתייחס לקבוצת התוצאות של שאילתה. טבלת נתונים מורכבת משורות ומעמודות. כל עמודה בטבלת נתונים כוללת את המאפיינים הבאים:
- Identifier (או מזהה העמודה). משמש להפניה לעמודות בתוך השאילתה. שימו לב שאסור אף פעם לנסות להפנות לעמודה לפי תווית בשאילתה, אלא רק לפי מזהה. טיפ: מומלץ לא להשתמש במזהים שכוללים רווחים. קשה לנהל רווחים. זה עלול לגרום לטעויות קטנות, אבל קשה למצוא אותן, בתכנות. כמו כן, מזהה שכולל רווחים צריך להיות מוקף במירכאות כפולות.
- תווית.
string
שמוצג בדרך כלל למשתמשי קצה. לדוגמה, כמקרא בתוך תרשים עוגה, או ככותרת עמודה בטבלה. - סוג הנתונים. סוגי הנתונים הנתמכים הם
string
,number
,boolean
,date
,datetime
ו-timeofday
. לכל הערכים בעמודה יהיה סוג נתונים שתואם לסוג העמודה, או ערךnull
. הסוגים האלה דומים, אבל לא זהים, לסוגים של JavaScript, שמתוארים בקטע ליטרים בדף הזה. - דפוס עיצוב. מקור הנתונים יכול להגדיר תבניות עיצוב לחלק מהעמודות או לכולן. כדי לשנות את הדפוס הזה אפשר לכלול תנאי פורמט.
הטבלה שבה נעשה שימוש בכל הדוגמאות:
בקטע הזה, כל הדוגמאות לשאילתות מתייחסות לטבלה הבאה. הכותרות של העמודות הן מזהי העמודות.
שםstring |
מחנהstring |
lunchTimetimeofday |
שכר number |
hireDatedate |
גילnumber |
isSeniorboolean |
seniorityStartTimedatetime |
---|---|---|---|---|---|---|---|
ג'ון | Eng | 12:00:00 | 1,000 | 2005-03-19 | 35 | true | 2007-12-02 15:56:00 |
דייב | Eng | 12:00:00 | 500 | 2006-04-19 | 27 | false | null |
סאלי | Eng | 13:00:00 | 600 | 2005-10-10 | 30 | false | null |
בן | מכירות | 12:00:00 | 400 | 2002-10-10 | 32 | true | 2005-03-09 12:30:00 |
דנה | מכירות | 12:00:00 | 350 | 2004-09-08 | 25 | false | null |
מיקי | שיווק | 13:00:00 | 800 | 2005-01-10 | 24 | true | 2007-12-30 14:40:00 |
סעיפי שפה
התחביר של שפת השאילתה מורכב מהסעיפים הבאים. כל תנאי מתחיל במילת מפתח אחת או שתיים. כל הסעיפים הם אופציונליים. הסעיפים מופרדים ברווחים. סדר הסעיפים חייב להיות:
סעיף | Usage |
---|---|
select |
בוחר אילו עמודות להחזיר ובאיזה סדר. אם לא צוין מספר, יוחזרו כל העמודות בטבלה לפי סדר ברירת המחדל שלהן. |
where |
מחזירה רק שורות שתואמות לתנאי. אם לא צוין, יוחזרו כל השורות. |
group by |
צבירת ערכים משורות שונות. |
pivot |
מבצעת טרנספורמציה של ערכים נפרדים בעמודות לעמודות חדשות. |
order by |
מיון שורות לפי ערכים בעמודות. |
limit |
הגבלת מספר השורות שהוחזרו. |
offset |
מדלג על מספר נתון של שורות ראשונות. |
label |
מגדיר את תוויות העמודה. |
format |
עיצוב הערכים בעמודות מסוימות באמצעות תבניות עיצוב נתונות. |
options |
מגדירה אפשרויות נוספות. |
from |
הסעיף from הוסר מהשפה. |
בחירה
התנאי select
משמש לציון העמודות שצריך להחזיר ואת הסדר שלהן.
אם התנאי הזה לא צוין, או אם משתמשים ב-select *
, כל העמודות של טבלת מקור הנתונים יוחזרו לפי הסדר המקורי שלהן.
המזהים מפנים לעמודות באמצעות המזהים (לא לפי תוויות). לדוגמה, בגיליון אלקטרוני של Google, מזהי עמודות הם אות אחת או שתיים בעמודה (A, B, C, ...).
הפריטים בתנאי select
יכולים להיות מזהי עמודות, או פלט של פונקציות צבירה, פונקציות סקלריות או אופרטורים.
לדוגמה:
select *
select dept, salary
select max(salary)
בדוגמה הבאה, המירכאות הכפולות משמשות להפניה למזהי עמודות שמכילים רווחים (כתובת אימייל) או למילים שמורות (תאריך):
select `email address`, name, `date`
מריצים את השאילתה הבאה בטבלת הדוגמאות:
select lunchTime, name
הפונקציה מחזירה את התגובה הבאה:
lunchTime | שם |
---|---|
12:00:00 | ג'ון |
12:00:00 | דייב |
13:00:00 | סאלי |
12:00:00 | בן |
12:00:00 | דנה |
13:00:00 | מיקי |
איפה
התנאי where
משמש להחזרת רק שורות שתואמות לתנאי שצוין.
האופרטורים הפשוטים להשוואות הם <=, <, >, >=, =,
!=, <>
. המשמעות של שני האופרטורים של ההשוואה != <>
היא
לא שווה. משווים בין מחרוזות לפי ערך לקסיקוגרפי. חשוב לשים לב שהשוויון
מצוין על ידי =
, ולא על ידי ==
כמו ברוב שפות המחשב.
ההשוואה אל null
מתבצעת
באמצעות is null
או is not null
.
אפשר להצטרף לכמה תנאים באמצעות האופרטורים הלוגיים and
, or
ו-not
. אפשר להשתמש בסוגריים כדי להגדיר קדימות מפורשת.
הסעיף שבו יש תמיכה גם בכמה אופרטורים מורכבים יותר להשוואת מחרוזות.
האופרטורים האלה משתמשים בשתי מחרוזות כארגומנטים. כל ארגומנטים שאינם מחרוזות (למשל תאריכים או מספרים) יומרו למחרוזות לפני ההשוואה. התאמת מחרוזות היא תלוית אותיות רישיות (אפשר להשתמש בפונקציות סקלריות upper()
או lower()
כדי לעקוף זאת).
contains
– התאמה למחרוזת משנה. כלcontains
חלק היא TRUE אם חלק נמצא במקום כלשהו בתוך כול. דוגמה:where name contains 'John'
תואם ל-'John', 'John Adams', 'לונג ג'ון סילבר' אבל לא ל'ג'ון אדמס'.starts with
– התאמת קידומת. valuestarts with
prefix מחזירה True אם prefix מופיע בתחילת value. דוגמאות:where dept starts with 'engineering'
תואם ל'הנדסה' ול 'מנהלי הנדסה'.where dept starts with 'e'
תואם ל-'engineering', 'eng' ו-'e'.ends with
– התאמת סיומת. valueends with
suffix מחזירה True אם suffix נמצא בסוף value. דוגמה: הערךwhere role ends with 'y'
תואם ל-'cowboy', ל-'boy' ול-'y'.matches
– התאמה של ביטוי רגולרי (preg). haystackmatches
needle הוא True אם הביטוי הרגולרי ב-needle תואם ל-haystack. דוגמאות: הערךwhere country matches '.*ia'
תואם להודו ולניגריה, אבל לא לאינדיאנה. לתשומת ליבכם: זהו לא חיפוש גלובלי, ולכןwhere country matches 'an'
לא יתאים ל 'קנדה'.like
- חיפוש טקסט שתומך בשני תווים כלליים לחיפוש: %, שתואם לאפס תווים או יותר מכל סוג שהוא, ו-_ (קו תחתון), שתואם לכל תו אחד. הפקודה הזו דומה לאופרטור SQL LIKE. דוגמה:where name like fre%
תואם ל-'fre', ל-'fred' ול-'freddy'.
לדוגמה:
where salary >= 600 where dept != 'Eng' and date '2005-01-21' < hireDate where (dept<>'Eng' and isSenior=true) or (dept='Sales') or seniorityStartTime is null
מריצים את השאילתה הבאה בטבלת הדוגמאות:
select name where salary > 700
הפונקציה מחזירה את התגובה הבאה:
שם |
---|
ג'ון |
מיקי |
קיבוץ לפי
התנאי group by
משמש לצבירה של ערכים בשורות שונות.
נוצרת שורה אחת לכל שילוב נפרד של ערכים בתנאי 'קיבוץ לפי'.
הנתונים ממוינים באופן אוטומטי לפי עמודות הקיבוץ, אלא אם צוין אחרת באמצעות תנאי order by
.
הערה: אם משתמשים בתנאי group by
, כל עמודה שמופיעה בתנאי select
צריכה להופיע בתנאי group by
או מוקפת בפונקציית צבירה.
לדוגמה:
select dept, max(salary) group by dept
מריצים את השאילתה הבאה בטבלת הדוגמאות:
select lunchTime, avg(salary), count(age) group by isSenior,lunchTime
הפונקציה מחזירה את התגובה הבאה:
lunchTime | שכר ממוצע | גיל הספירה |
---|---|---|
12:00:00 | 425 | 2 |
13:00:00 | 600 | 1 |
12:00:00 | 700 | 2 |
13:00:00 | 800 | 1 |
ציר
התנאי pivot
משמש לטרנספורמציה של ערכים נפרדים בעמודות
לעמודות חדשות. לדוגמה, אם מסובבים את הנתונים בעמודה 'year', נוצרת טבלה עם עמודה לכל שנה בנפרד שמופיעה בטבלה המקורית.
האפשרות הזו יכולה להיות שימושית אם, למשל, תרשים קו ויזואלי משרטט כל עמודה כקו נפרד. אם רוצים לשרטט קו נפרד לכל שנה, ו 'year' היא אחת מהעמודות של הטבלה המקורית, מומלץ להשתמש בפעולת ציר כדי לבצע את טרנספורמציה של הנתונים הדרושה.
הערה: אם משתמשים בתנאי pivot
, כל עמודה שמופיעה בתנאי select
צריכה להיות רשומה בתנאי group by
או מוקפת בפונקציית צבירה.
בגלל שכמה שורות מכילות את אותם הערכים לעמודות הצירים,
השינוי מעיד על צבירה. חשוב לשים לב שכאשר משתמשים בפונקציה pivot
בלי
להשתמש בפונקציה group by
, טבלת התוצאות תכיל רק שורה אחת.
לדוגמה, הרצת השאילתה הבאה על הטבלה לדוגמה:
select sum(salary) pivot dept
הפונקציה מחזירה את התגובה הבאה:
שכר סכום Eng | שכר כולל של שיווק | סכום מכירות |
---|---|---|
2100 | 800 | 750 |
הסיבה לכך היא ש-2100 הוא סכום המשכורות של מחלקת האנג, 800 עבור מחלקת השיווק וכו'.
שימוש בפונקציה pivot
יחד עם group by
יכול להיות שימושי עוד יותר, כי הוא יוצר טבלה שבה כל תא מכיל את תוצאת הצבירה של השורה הרלוונטית והעמודה הרלוונטית. לדוגמה, הרצת השאילתה הבאה על הטבלה לדוגמה:
select dept, sum(salary) group by dept pivot lunchTime
הפונקציה מחזירה את התגובה הבאה:
מח' | 12:00:00 סכום-שכר | סכום-שכר 13:00:00 |
---|---|---|
Eng | 1500 | 600 |
שיווק | null | 800 |
מכירות | 750 | null |
אפשר גם 'להפוך' את הטבלה הזו על ידי מעבר בין עמודות ושורות, על ידי מעבר בין העמודות pivot
לבין העמודות group by
. מריצים את השאילתה הבאה בטבלת הדוגמאות:
select lunchTime, sum(salary) group by lunchTime pivot dept
הפונקציה מחזירה את התגובה הבאה:
lunchTime | שכר סכום Eng | שכר כולל של שיווק | סכום מכירות |
---|---|---|---|
12:00:00 | 1500 | null | 750 |
13:00:00 | 600 | 800 | null |
אפשר גם להשתמש ביותר מעמודה אחת בתנאי pivot
. במקרה כזה, העמודות של טבלת התשובות מורכבות מכל השילובים הייחודיים של הערכים בעמודות שקיימות בטבלה המקורית. לדוגמה, הרצת השאילתה הבאה על הטבלה לדוגמה:
select sum(salary) pivot dept, lunchTime
הפונקציה מחזירה את התגובה הבאה:
Eng,12:00:00 amount-salary | Eng,13:00:00 amount-salary | Marketing,13:00:00 amount-salary | מכירות,סכום-שכר 12:00:00 |
---|---|---|---|
1500 | 600 | 800 | 750 |
שימו לב שרק השילובים שמופיעים בטבלה המקורית מקבלים עמודות בטבלת התגובות. לכן אין עמודה של Marketing,12:00:00 או של Sales,13:00:00.
ניתן גם להשתמש ביותר מצבירה אחת. לדוגמה, הרצת השאילתה הבאה על הטבלה לדוגמה:
select sum(salary), max(lunchTime) pivot dept
הפונקציה מחזירה את התגובה הבאה:
שכר סכום Eng | שכר כולל של שיווק | סכום מכירות | זמן ארוחת צהריים מקסימלי | זמן ארוחת צהריים מקסימלי לשיווק | מכירות מקסימליות לארוחת צהריים |
---|---|---|---|---|---|
2100 | 800 | 750 | 13:00:00 | 13:00:00 | 12:00:00 |
אפשר לשלב כמה צבירת נתונים בתנאי select
, כמה עמודות בתנאי group by
וכמה עמודות בסעיף pivot
. באופן פנימי, הצבירה מתבצעת על ידי
שרשור העמודות בקבוצה לפי סעיפי ציר.
יכול להיות שהעמודות שצוינו בתנאי pivot
לא יופיעו בתנאים select
, group by
או order by
.
כשמשתמשים ב-pivot
, התנאי order by
לא יכול להכיל עמודות צבירה. הסיבה לכך היא שלכל צבירה שצוינה בסעיף select
נוצרות עמודות רבות בטבלת התוצאות. אבל אפשר לעצב עמודות צבירה כשמשתמשים ב-pivot
. התוצאה של פורמט כזה היא שכל העמודות החדשות שרלוונטיות לצבירה הספציפית, שנוצרות על ידי פעולת ה-pivot, נוצרות לפי הדפוס שצוין. בדוגמה שלמעלה, ההוספה של format sum(salary) "some_format_string"
תשפיע על העמודות הבאות: Eng amount-salary , Marketing amount-salary , ו-Sales amount-salary (שכר סכום המכירות).
אפשר להוסיף תוויות לעמודות צבירה. אם לא צוינה תווית בתנאי label
, התווית של העמודה שנוצרת כתוצאה מהעברת הצירים מורכבת מרשימת הערכים בעמודות הצירים, מסוג הצבירה (min, max, amount, ...) והתווית של העמודה המצטברת.
לדוגמה, 'Eng,12:00:00 amount Salary'. אם ציינת רק צבירה אחת
בסעיף select
, החלק של הצבירה יוסר
מהתווית ונשמר רק רשימת הערכים בעמודות הצירים. לדוגמה "Eng,12:00:00". כשסעיף label
מציין תווית של עמודת צבירה, התווית המבוקשת מצורפת לרשימת
הערכים, גם כשיש רק צבירה אחת בסעיף select
וגם כשיש יותר מאירוע אחד. לדוגמה, הפונקציה label sum(salary) "sumsal"
תוביל לתוויות של עמודות
"Eng,12:00:00 Sumsal", "Eng,13:00:00 amountsal" וכו'.
הזמנה לפי
התנאי order by
משמש למיון השורות לפי הערכים בעמודות שצוינו.
הפריטים בתנאי order by
יכולים להיות מזהי עמודות, או פלט של פונקציות צבירה, פונקציות סקלריות או אופרטורים.
לדוגמה:
order by dept, salary desc
select dept, max(salary) group by dept order by max(salary)
הגבלה
הסעיף limit
משמש להגבלת מספר השורות שהוחזרו.
דוגמה:
limit 100
קיזוז
התנאי offset
משמש לדילוג על מספר נתון של שורות ראשונות.
אם משתמשים בתנאי limit
, מחילים קודם את offset
: לדוגמה, limit 15 offset
30
מחזירה את שורות 31 עד 45.
לדוגמה:
offset 10 limit 30 offset 210
תווית
התנאי label
משמש להגדרת התווית בעמודה אחת או יותר.
שימו לב שלא ניתן להשתמש בערך של תווית במקום במזהה בשאילתה.
הפריטים בתנאי label
יכולים להיות מזהי עמודות, או פלט של פונקציות צבירה, פונקציות סקלריות או אופרטורים.
תחביר:
label column_id label_string [,column_id label_string]
column_id
- מזהה העמודה שהוקצה לה התווית.
label_string
- התווית שצריך להקצות לעמודה הזו. בתרשימים רבים נעשה שימוש בתווית העמודה כטקסט להצגה למשתמש הקצה, למשל תווית מקרא בתרשים עוגה. התוויות הן מחרוזות מילוליות, והן בנויות לפי כללי התחביר האלה.
דוגמה:
בדוגמה הבאה התווית של העמודה dept מוגדרת כ-'מחלקה', התווית של עמודת השם כ'שם העובד' והתווית של עמודת המיקום כ'מיקום עובד':
label dept 'Department', name "Employee Name", location 'Employee Location'
פורמט
התנאי format
משמש לציון ערך בפורמט בשביל תאים בעמודה אחת או יותר. הנתונים שמוחזרים צריכים לכלול גם ערך בפועל וגם ערך מעוצב לכל תא בעמודה מעוצבת. בהרבה תצוגות חזותיות נעשה שימוש בערך לא בפורמט לצורך חישובים, אבל בערך בפורמט של התצוגה. הדפוסים שמציינים בסעיף הזה מוחזרים בדרך כלל במאפיין pattern בעמודות המתאימות.
תחביר של דפוס:
number
,date
,timeofday
,datetime
- הדפוסים תאריך ומספר שהוגדרו על ידי ICU.
-
boolean
- הדפוס הוא
string
בפורמט 'value-if-true:value-if-false'.
דוגמה:
format salary '#,##0.00', hireDate 'dd-MMM-yyyy', isSenior 'Yes!:Not yet'
אפשרויות
התנאי options
משמש לשליטה באפשרויות נוספות להפעלת השאילתה. מילות מפתח אפשריות שיכולות לעמוד בתנאי options
:
-
no_format
מסיר ערכים בפורמט מהתוצאה ומשאירים רק את הערכים הבסיסיים. אפשר להשתמש באפשרות הזו אם התצוגה החזותית הספציפית לא כוללת ערכים בפורמט כדי להקטין את גודל התשובה. -
no_values
מסירה את הערכים הבסיסיים מהתוצאה ומשאירה רק את הערכים בפורמט המתאים. אפשר להשתמש באפשרות הזו אם התצוגה החזותית הספציפית משתמשת רק בערכים בפורמט הנכון כדי להקטין את גודל התשובה.
פונקציות מניפולציה של נתונים
יש כמה סוגים של אופרטורים ופונקציות שמאפשרים להשפיע על הנתונים או לצבור אותם בעמודה אחת, או להשוות או לשלב בין נתונים בעמודות. לדוגמה: Sum() (כדי להוסיף את כל הערכים בעמודה), max (כדי למצוא את הערך הגדול ביותר בעמודה) ו-+ (כדי לחבר את הערכים של שתי עמודות יחד באותה שורה).
פונקציות מסוימות יכולות להופיע בכל תנאי, וחלקן יכולות להופיע בקבוצת משנה של תנאים. המידע הזה מתועד בהמשך.
דוגמה:
על סמך הטבלה הזו... | אם נחיל את השאילתה הזו... | אנחנו מקבלים את התוצאה הזו. | ||||||||||||||||||||||||
|
select upper(name), year(startDate) |
|
הפונקציות הבאות לטיפול בנתונים מוגדרות על ידי שפת השאילתות של GoogleVisual API:
פונקציות צבירה
פונקציות הצבירה מועברות באמצעות מזהה עמודה אחד
ומבצעים פעולה בכל הערכים בכל קבוצה (קבוצות מוגדרות באמצעות
תנאים group by
או pivot
, או כל השורות אם לא נעשה שימוש בתנאים האלה).
לדוגמה:
select max(salary) // Returns a table with one column and one row. select max(salary) group by dept // Returns a table with the max salary for each dept column value. select max(salary) pivot dept // Returns a one-row table with a column for each dept, // and the max salary for that department.
אפשר להשתמש בפונקציות צבירה בקטעים select
, order by
, label
ו-format
.
הם לא יכולים להופיע בסעיפים where
, group by
, pivot
,
limit
, offset
או options
.
פונקציות הצבירה הנתמכות:
שם | תיאור | סוגי עמודות נתמכים | סוג הערך המוחזר |
---|---|---|---|
avg() |
מחזירה את הערך הממוצע של כל הערכים בעמודה עבור קבוצה. | number |
number |
count() |
מחזירה את מספר הרכיבים בעמודה שצוינה עבור קבוצה. תאים עם ערכי null לא נספרים. | כל סוג | number |
max() |
מחזירה את הערך המקסימלי בעמודה עבור קבוצה. מתבצעת השוואה בין התאריכים
עם תקופות מוקדמות יותר קטנות יותר. השוואה בין התאריכים string לפי סדר אלפביתי עם תלות באותיות רישיות. |
כל סוג | אותו סוג כמו העמודה |
min() |
מחזירה את הערך המינימלי בעמודה של קבוצה. מתבצעת השוואה בין התאריכים
עם תקופות מוקדמות יותר קטנות יותר. השוואה בין התאריכים string לפי סדר אלפביתי עם התלות באותיות רישיות |
כל סוג | אותו סוג כמו העמודה |
sum() |
מחזירה את הסכום של כל הערכים בעמודה עבור קבוצה. | number |
number |
הערה: פונקציות צבירה יכולות להשתמש רק במזהה עמודה כארגומנט:
max(startDate) // OK min(firstScore) + min(secondScore) // OK max(year(startDate)) // INVALID. max requires column ID sum(salary + perks) // INVALID. sum requires column ID.
פונקציות סקלריות
פונקציות Scalar פועלות מעל אפס פרמטרים או יותר כדי לייצר ערך נוסף. אפשר להעביר פונקציות סקלריות כל ביטוי ששווה לפרמטר מהסוג המתאים. שימו לב שהסוגים האלה הם הסוגים שמוגדרים בקטע Literals במסמך הזה. הם עשויים להיות שונים מעט מהאובייקטים של JavaScript בעלי שמות דומים.
חשוב לשים לב ששם העמודה ישתנה על ידי גלישתה באמצעות פונקציה סקלרית.
פונקציות סקלריות יכולות לקבל כפרמטר כל דבר ששווה לערך בודד:
year(max(startDate)) datediff(now(), todate(1234567890000))
אפשר להשתמש בפונקציות Scalar בכל אחד מהתנאים הבאים: select
, where
, group by
, pivot
, order by
, label,
ו-
format
.
שם | |
---|---|
year() |
הפונקציה מחזירה את ערך השנה מתוך ערך של תאריך או של תאריך ושעה. לדוגמה: הפקודה פרמטרים: פרמטר אחד מסוג
date או datetime סוג החזרה:
number |
month() |
הפונקציה מחזירה את הערך של חודש על בסיס אפס מתוך ערך של תאריך או של תאריך ושעה. לדוגמה:
פרמטרים: פרמטר אחד מסוג
date או datetime סוג החזרה:
number |
day() |
הפונקציה מחזירה את היום בחודש מתוך ערך של פרמטרים: פרמטר אחד מסוג
date או datetime סוג החזרה:
number |
hour() |
הפונקציה מחזירה את ערך השעה מערך של תאריך ושעה או פרמטרים: פרמטר אחד מסוג
datetime או timeofday סוג החזרה:
number |
minute() |
הפונקציה מחזירה את ערך הדקות מערך פרמטרים: פרמטר אחד מסוג
datetime או timeofday סוג החזרה:
number |
second() |
הפונקציה מחזירה את הערך השני מערך של פרמטרים: פרמטר אחד מסוג
datetime או timeofday סוג החזרה:
number |
millisecond() |
מחזירה את החלק של אלפית השנייה של ערך של פרמטרים: פרמטר אחד מסוג
datetime או timeofday סוג החזרה:
number |
quarter() |
הפונקציה מחזירה את הרבעון מערך של פרמטרים: פרמטר אחד מסוג
date או datetime סוג החזרה:
number |
dayOfWeek() |
הפונקציה מחזירה את היום בשבוע מערך של פרמטרים: פרמטר אחד מסוג
date או datetime סוג החזרה:
number |
now() |
הפונקציה מחזירה ערך של תאריך ושעה שמייצג את פרמטרים: ללא
סוג החזרה:
datetime |
dateDiff() |
מחזירה את ההפרש בימים בין שני ערכי פרמטרים: שני פרמטרים מסוג
date או datetime (יכולים להיות אחד מכל אחד מהם)סוג החזרה:
number |
toDate() |
הפונקציה ממירה את הערך הנתון לערך
פרמטרים: פרמטר אחד מסוג
date , datetime או number סוג החזרה:
date |
upper() |
הפונקציה מחזירה את הערך פרמטרים: פרמטר אחד מסוג
string סוג החזרה:
string |
lower() |
הפונקציה מחזירה את הערך פרמטרים: פרמטר אחד מסוג
string סוג החזרה:
string |
אופרטורים אריתמטיים
אפשר להשתמש באופרטורים אריתמטיים כדי לבצע פעולות מתמטיות על כל דבר ששווה למספר יחיד (כלומר, פלט של פונקציות אגרגטיביות, אופרטורים או קבועים מתאימים).
לדוגמה:
select empSalary - empTax select 2 * (max(empSalary) / max(empTax))
האופרטורים הבאים מוגדרים:
שם | תיאור | פרמטרים | סוג הערך המוחזר |
---|---|---|---|
+ |
מחזירה את הסכום של שני ערכי number . |
שתי number |
number |
- |
מחזירה את ההפרש בין שני ערכי number . |
שתי number |
number |
* |
מחזירה את המכפלה של שתי מחרוזות number . |
שתי number |
number |
/ |
מחזירה את המנה של שתי פונקציות number . החילוק באפס מחזירה null. |
שתי number |
number |
רכיבי שפה
ליטרים
מילוליים הם ערכים שמשמשים להשוואות או למטלות. ליטרים יכולים להיות string
, מספרים, ערכים בוליאניים או סוגים שונים של תאריך ושעה. ריכזנו כאן כמה דוגמאות לייצוגים מילוליים בתחביר של השאילתה:
where startDate < date "2008-03-18" // date "2008-03-18" is a date literal
limit 30 // 30 is a numeric literal
format salary '#,##0.00', isSenior 'not yet:of course!' // '#,##0.00' and 'not yet:of course!' are both string
literals
הנה הפורמטים לכל סוג של מילים:
string
את הליטרל
string
צריך להיות מוקף במירכאות יחידות או כפולות. לדוגמה:"fourteen" 'hello world' "It's raining"
.-
number
- ליטרלים מספריים מצוינים בסימון עשרוני.
דוגמאות:
3 3.0 3.14 -71 -7.2 .6
-
boolean
- ליטרל בוליאני הוא
true
אוfalse
. -
date
- כדאי להשתמש במילת המפתח
date
ולאחר מכן בליטרלstring
בפורמטyyyy-MM-dd
. לדוגמה:date "2008-03-18"
. -
timeofday
- שימוש במילת המפתח
timeofday
ולאחר מכן בליטרלstring
בפורמטHH:mm:ss[.SSS]
דוגמה:timeofday "12:30:45"
. -
datetime
- תאריך ושעה באמצעות מילת המפתח
datetime
או מילת המפתחtimestamp
, ואחריה יש מילה במילהstring
בפורמטyyyy-MM-dd HH:mm:ss[.sss]
. לדוגמה:datetime '2008-03-18 12:30:34.123'
מזהים
מזהים (או מזהים) הם מזהי טקסט מסוג string
שמזהים עמודות.
חשוב: אם המזהה שלכם
- כולל רווחים,
- היא מילה שמורה,
- מכיל כל דבר מלבד תווים אלפאנומריים או קווים תחתונים ([a-zA-Z0-9_]), או
- מתחיל בספרה
הוא חייב להיות מוקף במירכאות כפולות (לא במירכאות).
אחרת, אין צורך להקיף את המזהה במירכאות. (שימו לב שלא כל מילות המפתח שמוגדרות בתחביר הן מילים שמורות. לדוגמה, אפשר להשתמש ב-"max" כמזהה, בלי להוסיף מירכאות אחורה).
דוגמאות: col1 employee_table `start
date` `7 days traffic` `select`
מומלץ לא לבחור מזהה שדורש מירכאות כפולות, כי לפעמים קל לשכוח להשתמש במירכאות הקודמות או להשתמש בטעות במירכאות יחידות במקום במירכאות. אלו שגיאות נפוצות, ולרוב קשה לנפות בהן באגים.
תלות באותיות רישיות
מזהים וליטרל מחרוזת הם תלויי אותיות רישיות (case-sensitive). כל שאר רכיבי השפה הם לא תלויי-רישיות.
מילים שמורות
אם משתמשים במזהה, צריך להקיף את המילים השמורות הבאות במירכאות:
and asc by date datetime desc false format group label limit not offset options or order pivot select timeofday timestamp true where