שפת השאילתות של Google Visualization API מאפשרת לבצע מניפולציות שונות של נתונים עם השאילתה למקור הנתונים.
תוכן עניינים
הקדמה
בדרך כלל, הרכיבים החזותיים מצפים לנתונים בצורה מסוימת. לדוגמה, תרשים עוגה יכול לצפות לנתונים כמו שתי עמודות: תווית טקסט וערך מספרי. ייתכן שהנתונים במקור הנתונים לא יתאימו בדיוק למבנה הזה. לדוגמה, ייתכן שלמקור הנתונים יהיו יותר משתי עמודות, או שסדר העמודות לא יהיה תואם לסדר הצפוי בתרשים העוגה.
בעזרת שפת השאילתות אפשר לשלוח בקשות לשינוי עיצוב ולעיצוב נתונים למקור הנתונים, ולוודא שהמבנה והנתונים שמוחזרים תואמים למבנה הצפוי.
התחביר של שפת השאילתה דומה לתחביר של SQL. מפתחים שמכירים את SQL אמורים להיות מסוגלים ללמוד במהירות את שפת השאילתות הזו ולהשתמש בה. יש הרבה מדריכים ל-SQL שזמינים באינטרנט. יש כמה הבדלים בין שפת השאילתה הזו לבין SQL, והן מתוארות בקטע תחביר.
חשוב לשים לב שמקורות הנתונים לא נדרשים כדי להטמיע את שפת השאילתות, או אם הם עושים זאת, כדי להטמיע את כל התכונות של השפה. אם אין לך סיבה להאמין אחרת, לא כדאי להסתמך על מקור נתונים כדי להטמיע את כל התכונות של השפה הזו.
שימוש בשפת השאילתות
ניתן לצרף מחרוזת שאילתה לבקשת מקור נתונים בשתי דרכים: על ידי הגדרת מחרוזת השאילתה מתוך קוד JavaScript, או הגדרת מחרוזת השאילתה כפרמטר בכתובת האתר של מקור הנתונים. אם הבקשה לא כוללת מחרוזת שאילתה, התנהגות ברירת המחדל עבור מקור הנתונים היא להחזיר את כל השורות והעמודות בשימוש בסדר ברירת המחדל של השורה/העמודה שלה. כדי לשנות את ההגדרה, צריך לכלול מחרוזת שאילתה בבקשה למקור הנתונים.
הגדרת השאילתה מתוך JavaScript
כדי להגדיר את מחרוזת השאילתה מתוך קוד JavaScript, קראו לשיטה 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 באמצעות הפונקציה encodeURIComponent
ב-JavaScript, או לקודד אותה ידנית באמצעות כלי הקידוד שבסוף הקטע הזה.
דוגמה:
מומלץ להשתמש במחרוזת השאילתה הבאה עבור גיליון אלקטרוני של Google. (לתשומת ליבך, מזהי העמודות בגיליונות אלקטרוניים הם תמיד אותיות, והטקסט של כותרת העמודה שמוצג בגיליון האלקטרוני שפורסם הוא תוויות ולא מזהים. עליך להשתמש במזהה, ולא בתווית, במחרוזת השאילתה).
select A, sum(B) group by A
בעת הקידוד, השאילתה הופכת ל:
select%20A%2C%20sum(B)%20group%20by%20A
נניח שזוהי כתובת האתר של הגיליון האלקטרוני שלכם:
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: הרשאה.
תחביר שפה
סקירה כללית
התחביר של שפת השאילתות של Google Visualization API נועד להיות דומה לתחביר של SQL. עם זאת, זוהי קבוצת משנה של SQL, עם כמה תכונות משלה שצריך ללמוד. אם אתם מכירים את SQL, זה לא אמור להיות קשה מדי ללמוד.
טבלאות נתונים
במסמך הזה אנחנו משתמשים במונח טבלת נתונים כדי להפנות לקבוצת התוצאות של שאילתה. טבלת נתונים מורכבת משורות ועמודות. לכל עמודה בטבלת נתונים יש את המאפיינים הבאים:
- מזהה (או מזהה עמודה). משמשים להפניה לעמודות בתוך השאילתה. חשוב לשים לב שאין לנסות להתייחס לעמודה לפי תווית בשאילתה, רק לפי מזהה. טיפ: מומלץ להימנע משימוש במזהים שכוללים רווחים. קשה לנהל רווחים, וזה עלול לגרום לשגיאות קטנות, אבל קשה למצוא אותן. כמו כן, אם יש רווחים, צריך להקיף אותם במירכאות לאחור.
- תווית.
string
שמוצגים בדרך כלל למשתמשי קצה. לדוגמה, מקרא בתרשים עוגה או כותרת עמודה בטבלה. - סוג הנתונים. סוגי הנתונים הנתמכים הם
string
,number
,boolean
,date
,datetime
וגםtimeofday
. לכל הערכים בעמודה יהיה סוג נתונים שתואם לסוג העמודה, או ערךnull
. הסוגים האלה דומים לסוגי ה-JavaScript אבל הם לא זהים. הם מתוארים בקטע ליטרים של הדף הזה. - דפוס עיצוב. מקור הנתונים יכול להגדיר תבניות עיצוב לחלק מהעמודות או לכולן. כדי לשנות את התבנית הזו, יש לכלול ביטוי בפורמט.
לאורך הקטע הזה, כל הדוגמאות לשאילתות מתייחסות לטבלה הבאה. כותרות העמודות הן מזהי העמודות.
שםstring |
יציאהstring |
ארוחת צהרייםtimeofday |
שכר number |
hireDatedate |
גילnumber |
בכירboolean |
הזדהות להתחלת ההפעלהdatetime |
---|---|---|---|---|---|---|---|
ג'ון | Eng | 12:00:00 | 1000 | 2005-03-19 | 35 | נכון | 2007-12-02 15:56:00 |
דייב | Eng | 12:00:00 | 500 | 2006-04-19 | 27 | לא נכון | null |
סאלי | Eng | 13:00:00 | 600 | 2005-10-10 | 30 | לא נכון | null |
בן | מכירות | 12:00:00 | 400 | 2002-10-10 | 32 | נכון | 2005-03-09 12:30:00 |
דנה | מכירות | 12:00:00 | 350 | 2004-09-08 | 25 | לא נכון | null |
מייק | שיווק | 13:00:00 | 800 | 2005-01-10 | 24 | נכון | 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
מחזירה את התגובה הבאה:
ארוחת צהריים | name |
---|---|
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
- התאמת מחרוזת משנה. כל החלק נכון אם חלק נמצא במקום כלשהו בתוך כולו. דוגמה:where name contains 'John'
תואם ל'ג'ון', 'ג'ון אדאמס', 'לונג ג'ון סילבר' אבל לא ל'ג'ון אדאמס'.starts with
- התאמת קידומת. valuestarts with
prefix נכון אם prefix מופיע בתחילת value. דוגמאות:where dept starts with 'engineering'
תואם ל-'הנדסה' ול'מנהלי הנדסה'.where dept starts with 'e'
תואם ל-'הנדסה', ל-'eng' ול-'e'.ends with
– התאמה לסיומת. valueends with
suffix נכון אם suffix מופיע בסוף value. דוגמה:where role ends with 'y'
תואם ל-'קאובוי', 'בנים' ו-'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
מחזירה את התגובה הבאה:
name |
---|
ג'ון |
מייק |
קיבוץ לפי
התנאי 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
מחזירה את התגובה הבאה:
ארוחת צהריים | שכר ממוצע | גיל |
---|---|---|
12:00:00 | 425 | 2 |
13:00:00 | 600 | 1 |
12:00:00 | 700 | 2 |
13:00:00 | 800 | 1 |
ציר
הסעיף pivot
משמש להמרת ערכים נפרדים בעמודות
לעמודות חדשות. לדוגמה, הציר של עמודה 'שנה' יפיק טבלה עם עמודה לכל שנה נפרדת שתופיע בטבלה המקורית.
האפשרות הזו יכולה להיות שימושית, למשל, אם הצגה של תרשים קו מייצגת כל עמודה כקו נפרד. אם רוצים לצייר קו נפרד לכל שנה, ו-'year' היא אחת העמודות של הטבלה המקורית, כדאי להשתמש בפעולת ציר כדי לבצע את השינוי הדרוש בנתונים.
הערה: אם משתמשים במשפט pivot
, כל עמודה שמצוינת בסעיף select
חייבת להיות רשומה בסעיף group by
. צריכה להיות מוקפת בפונקציית צבירה
מאחר ששורות רבות עשויות להכיל את אותם ערכים בעמודות הצירים,
המשמעות של טבלת הציר היא של הצבירה. חשוב לזכור שכשמשתמשים בפונקציה pivot
בלי
להשתמש ב-group by
, טבלת התוצאות תכיל בדיוק שורה אחת.
לדוגמה, אם מריצים את השאילתה הבאה בטבלת הדוגמאות:
select sum(salary) pivot dept
מחזירה את התגובה הבאה:
שכר אנגלי | שכר שכר שיווקי | שכר מכירות כולל |
---|---|---|
2100 | 800 | 750 |
הסיבה לכך היא ש-2100 הוא סכום המשכורות למחלקת Eng, 800 למחלקת השיווק וכו'.
השימוש ב-pivot
יחד עם group by
יכול להיות עוד יותר שימושי, כי הוא יוצר טבלה שבה כל תא מכיל את התוצאה של הצבירה בשורה הרלוונטית והעמודה הרלוונטית. לדוגמה,
מריצים את השאילתה הבאה בטבלת הדוגמאות:
select dept, sum(salary) group by dept pivot lunchTime
מחזירה את התגובה הבאה:
Dept | משכורת 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
מחזירה את התגובה הבאה:
ארוחת צהריים | שכר אנגלי | שכר שכר שיווקי | שכר מכירות כולל |
---|---|---|---|
12:00:00 | 1500 | null | 750 |
13:00:00 | 600 | 800 | null |
אפשר גם להשתמש ביותר מעמודה אחת בקטע pivot
. במקרה כזה, העמודות של טבלת התגובות מורכבות מכל השילובים הייחודיים של ערכים בעמודות הקיימות בטבלה המקורית. לדוגמה, מריצים את השאילתה הבאה בטבלת הדוגמאות:
select sum(salary) pivot dept, lunchTime
מחזירה את התגובה הבאה:
eng,12:00:00 pay-salary | שכר דירה,13:00:00 | שיווק,13:00:00 שכר | שכר מכירות,12:00:00 שכר |
---|---|---|---|
1500 | 600 | 800 | 750 |
לתשומת ליבך, רק השילובים שמופיעים בטבלה המקורית מקבלים עמודות בטבלת התגובות. לכן אין עמודה עבור Marketing,12:00:00 או עבור Sales,13:00:00.
אפשר להשתמש ביותר מצבירה אחת. לדוגמה, אם מריצים את השאילתה הבאה בטבלת הדוגמאות:
select sum(salary), max(lunchTime) pivot dept
מחזירה את התגובה הבאה:
שכר אנגלי | שכר שכר שיווקי | שכר מכירות כולל | Eng max-lunchTime | שיווק זמן מקסימלי | מכירות מקסימליות בצהריים |
---|---|---|---|---|---|
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
. כתוצאה מכך, הפורמט של כל העמודות החדשות הרלוונטיות להצטברות ספציפיות, שנוצר על ידי פעולת הצירים, נקבע לפי התבנית שצוינה. בדוגמה שלמעלה, הוספת format sum(salary) "some_format_string"
תשפיע על העמודות הבאות: Eng-salor-salary, sal-salary-salary ו-sal-salre sales Sales.
אפשר להוסיף תוויות לצבירה. אם לא מצוינת תווית
במשפט label
, התווית של העמודה שנוצרת בזכות טבלת הצירים מורכבת מרשימת הערכים בעמודות הצירים, מסוג
הצבירה (min, max, amount, ...) והתווית של הנתונים הנצברים.
לדוגמה, "Eng,12:00:00 total pay". אם תצוין רק צבירה אחת
במשפט select
, החלק של הצבירה יוסר
מהתווית, ורק רשימת הערכים בעמודות הצירים תישמר. לדוגמה,
"Eng,12:00:00". כשביטוי של label
מציין תווית של
עמודת צבירה, התווית המבוקשת מצורפת לרשימת הערכים, גם כאשר יש צבירה אחת בלבד בselect
, וגם כאשר יש יותר מאחת. לדוגמה,
label sum(salary) "sumsal"
יוביל לתוויות העמודה
"Eng,12:00:00 totals", "Eng,13:00:00 totals" וכו'.
סידור לפי
הסעיף 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
- התווית שצריך להקצות לעמודה הזו. תצוגות חזותיות רבות משתמשות בתווית העמודה כטקסט להצגה למשתמש הקצה, כמו תווית מקרא בתרשים עוגה. תוויות הן ליטרים של מחרוזת, ופועלות לפי כללי התחביר האלה.
דוגמה:
בדוגמה הבאה, התווית של עמודת המחלקה היא "מחלקה", התווית של עמודת השם היא "שם העובד" והתווית של עמודת המיקום ל"מיקום העובד":
label dept 'Department', name "Employee Name", location 'Employee Location'
פורמט
הסעיף format
משמש לציון ערך מעוצב לתאים בעמודה אחת או יותר. הנתונים שמוחזרים צריכים להכיל גם ערך בפועל וגם ערך מעוצב לכל תא בעמודה מעוצבת. חלק גדול מהרכיבים החזותיים משתמשים בערך לא מעוצב לחישובים, אבל הערך שפורמט התצוגה מייצג. הדפוסים שתציינו בקטע הזה מוחזרים בדרך כלל במאפיין pattern [דוגמת] של העמודות התואמות.
תחביר קו ביטול נעילה:
number
,date
,timeofday
,datetime
- הדפוסים date ו-number שהוגדרו על ידי 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
מסירה את הערכים הבסיסיים מהתוצאה ומשאירה רק את הערכים המפורמטים. אפשר להשתמש בתנאי שהתצוגה החזותית הספציפית משתמשת רק בערכים המעוצבים כדי לצמצם את גודל התגובה.
פונקציות מניפולציה על נתונים
יש כמה סוגים של אופרטורים ופונקציות שמאפשרים לבצע שינויים או צבירה של נתונים בעמודה אחת, ולהשוות בין נתונים או לשלב אותם בעמודות. הדוגמאות כוללות את סכום הסכום (כדי להוסיף את כל הערכים בעמודה), את מקסימום (כדי למצוא את הערך הגבוה ביותר בעמודה) ואת הסימן + (כדי להוסיף את הערכים של שתי עמודות באותה שורה).
חלק מהפונקציות יכולות להופיע בכל קטע, חלקן יכולות להופיע בקבוצת משנה של סעיפים. בהמשך מתוארים האפשרויות הבאות:
דוגמה:
בטבלה הזו... | אם נחיל את השאילתה הזו... | אנחנו מקבלים את התוצאה הזו. | ||||||||||||||||||||||||
|
select upper(name), year(startDate) |
|
הפונקציות הבאות לביצוע שינויים בנתונים מוגדרות בשפת השאילתות של Google Visualization 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() |
מחזירה את מספר הרכיבים בעמודה שצוינה בקבוצה. תאים מיותרים לא נספרים. | כל סוג | 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.
פונקציות סקלריות
פונקציות סקאלה פועלות מעל אפס פרמטרים או יותר כדי ליצור ערך נוסף. אפשר להעביר כל פונקציית סקאלה לכל ביטוי שנבדק בפרמטר של הסוג המתאים. חשוב לזכור שהסוגים האלה הם הסוגים שמוגדרים בקטע ליטרים של המסמך, והם עשויים להיות שונים מעט מאובייקטים של JavaScript ששמותיהם דומים.
הערה: כדי לשנות את שם העמודה, צריך להקיף אותה בפונקציה סקלרית.
פונקציות סקאלה יכולות לקבל כל פרמטר שמעריך לערך יחיד:
year(max(startDate)) datediff(now(), todate(1234567890000))
ניתן להשתמש בפונקציות סקאלה בכל אחד מהסעיפים הבאים: select
,
where
, group by
, pivot
,
order by
, label,
ו-
format
.
שם | |
---|---|
year() |
הפונקציה מחזירה את הערך של השנה לפי תאריך או לפי תאריך ושעה. לדוגמה:
פרמטרים: פרמטר אחד מסוג
date או datetime סוג החזרה:
number |
month() |
מחזירה את הערך של החודש מבוסס האפס מערך של תאריך או של תאריך ושעה. לדוגמה:
פרמטרים: פרמטר אחד מסוג
date או datetime סוג החזרה:
number |
day() |
מחזירה את היום בחודש מהערך פרמטרים: פרמטר אחד מסוג
date או datetime סוג החזרה:
number |
hour() |
מחזירה את הערך של שעה מ-date או מ- פרמטרים: פרמטר אחד מסוג
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 s |
number |
- |
מחזירה את ההפרש בין שני ערכי number . |
שני number s |
number |
* |
מחזירה את המכפלה של שני number s. |
שני number s |
number |
/ |
מחזירה את המנה של number שניות. החילוק באפס מחזיר את הערך null. |
שני number s |
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_]), או
- מתחיל בספרה
הוא חייב להיות מוקף במירכאות כפולות (לא במירכאות בודדות).
אחרת, אין צורך לצטט את המזהה. (שימו לב שלא כל מילות המפתח שהוגדרו בתחביר הן מילים שמורות; לדוגמה, אפשר להשתמש במילה "מקסימום" כמזהה, ללא ציטוט חוזר).
דוגמאות: col1 employee_table `start
date` `7 days traffic` `select`
מומלץ לא לבחור מזהה שמצריך מירכאות לאחור, כי לפעמים קל לשכוח להשתמש במירכאות האחוריות, או להשתמש בטעות ב'מירכאות בודדות' במקום ב'מירכאות אחוריות'. אלו הן טעויות נפוצות ולעיתים קרובות קשה לניפוי באגים.
תלות באותיות רישיות
מזהים ומחרוזות של מחרוזת הן תלויות אותיות רישיות. כל שאר רכיבי השפה אינם תלויי-רישיות.
מילים שמורות
יש להשתמש בציטוט חוזר של המילים השמורות הבאות אם משתמשים בהן כמזהה:
and asc by date datetime desc false format group label limit not offset options or order pivot select timeofday timestamp true where