Analytics test using Bigquery (2)
מרץ 2023
דודי לרך

7 פונקציות SQL BIGQUERY שאתם חייבים להכיר

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

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

  • INFORMATION_SCHEMA
  • QUALIFY
  • GRAETEST
  • ROLLUP
  • GREATEST
  • COALESCE
  • HARD CODED TABLE

INFORMATION_SCHEMA

לאיזה שימוש

כשאתם רוצים למצוא את כל שמות העמודות שהם אותו דבר בכל הדאטה סט שלכם ובאיזה טבלאות הם נמצאים


מוסיפים את ה information_schema ובוחרים סוג אפשר על דאטה סטס/טבלאות/ עמודות

בפירוט רב :

מטא דאטה על הטבלאות שלכם על הדאטה סטס שלכם,

Use case

  1. מאפשר לכם לקבל את כל שמות העמודות לפי סוג ערך
  2. מאפשר לכם להוציא רשימה של כל הטבלאות שקיימות בפרויקט מסויים
  3. מאפשר לכם לכתוב סקריפטים בצורה דינמית ולכתוב שאילתות מורכבות רק על בסיס המטא דאטה שלכם!
איך משתמשים?
				
					SELECT table_name,column_name,data_type
FROM 
`bigquery-public-data.census_utility.INFORMATION_SCHEMA.COLUMNS`
  WHERE
    table_name in('fips_class_code','fips_codes_all')

QUALIFY

לאיזה שימוש

פשוט לחסוך לכם זמן וקוד מלוכלך כשאתם משתמשים ב WINDOWS FUNCTION

 

מוסיפים את ה QUALIFY  ומפלטרים את ה WINDOWS FUNCTION

אם עד לא הכרתם אותה עכשיו זו  שערוריה , 

מכירים את זה שמבחינת סדר הפעולות ב windows function קורה אחרי ה WHERE? מכיוון שפקודת ה  ה where כבר בוצעה ואז צריך לעשות עוד מניפולציה קלה על הנתונים כדי לפלטר לפי ה windows functoin אז תגידו מעכשיו לא עוד!

 

 

				
					/*-----לפני----*/
Select *
	From
	(
Select *,
row_number() over (partitonin by user_id created_at) as rn
From t1
    )
 where rn = 1

				
			
				
					/*-----אחרי----*/

Select *,
row_number() over (partitonin by user_id created_at) as rn
From t1
Qualify rn = 1

ROLLUP

לאיזה שימוש

רוצים סיכום סוכם של ה GROUP BY שלכם

 

				
					/*-----לפני----*/
Select
	Country,
	sum(amount) as revenue
	From t1
Group by country

				
			
				
					/*-----אחרי----*/
Select
	Country,
	sum(amount) as revenue
	From t1
Group by rollup(country)

GREATEST

לאיזה שימוש

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

 

 

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

col1 col2 col3 col4
10 10 110 110
10 20 15 20 
				
					
SELECT col1,
       col2,
       col3,
       GREATEST(col1,col2,col3) as col4
    FROM table1

COALESCE

null אולי הערך שמופיע הכי הרבה ואנליסטים מתעסקים איתו לא מעט,

הרבה פעמים נרצה להציג את הערך הראשון שאיננו null מתוך סט של אפשרויות,
בשביל זה יש לנו את הפונקציה COALESCE

במקרה שלנו עמודה col4 מייצגת את הפונקציה  COALESCE

col1 col2 col3 col4
10 10 110 10
null 20 null 20

תשובה

				
					select
        col1,
        col2,
        col3,
        COALESCE(col1,col2,col3) as col4
    from t1

יצירת טבלה hard coded

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

				
					SELECT * 
    FROM UNNEST([ STRUCT('John' as name,10 as age), 
    ('tom', 20),
    ('jon', 30)
  ])

דרך נוספת זה פשוט להשתמש ב WITH  כך שתוכלו ממש בצורה נוחה להשתמש בזה

				
					WITH t1 as 
(
SELECT * 
    FROM UNNEST([ STRUCT('John' as name,10 as age), 
    ('tom', 20),
    ('jon', 30)
  ])   
)

SELECT * 
    FROM t1

אופצייה נוספת שניתן להשתמש

				
					SELECT id, name
FROM (
  SELECT 1 AS id, 'John' AS name UNION ALL
  SELECT 2, 'Jane' UNION ALL
  SELECT 3, 'Bob'
) AS t;
ID Name
1 John
2 Jane
3 Bob

GENERATE_DATE_ARRAY

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

				
					SELECT *
FROM
UNNEST(GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE(),
INTERVAL 7 DAY), CURRENT_DATE())) AS date
Date
2022-03-30
2022-03-31
2022-04-01
2022-04-02
2022-04-03
2022-04-04
2022-04-05

תשובה

רוצים להמשיך להתאמן מוזמנים להמשיך להתאמן עם הקורסים שלי

מבחנים לראיונות עבודה ב SQL

מבחני מיון ב SQL לראיונות עבודה,
להתקבל למקומות העבודה
הכי נחשקים במשק,
לתפקיד Data analyst/Product analyst/Business analyst

עכשיו במבצע