บันทึกการใช้งาน Google Sheet 103 สำหรับนักวิทยาศาสตร์ข้อมูล Data Science - sprint 01
01 - How Query Function Work?
Google sheet มีฟังก์ชัน Query ที่ทำ SQL บนตารางเหมือนเป็นฐานข้อมูลได้นะ
sql-like query กับ manipulate date เช่น select, where, order by, group by, aggregate function เช่น avg, sum, min, max, count และ limit
ใช้ IMDb Dataset เป็นฐานข้อมูลการเก็บหนังทั่วโลก
วิธีการใช้ Query function
=QUERY(data, query string)
เช่น
=QUERY( IMDB, “select A, B, C”)
เป็นต้น
02 - Select Data
เราจะสร้าง Name Range ของ Dataset ของ IMDb เอาไว้ล่วงหน้า เก็บเอาไว้ใน sheet ชื่อ ว่า Dataset
สร้าง sheet ใหม่ขึ้นมาชื่อว่า select แล้วใช้ คำสั่ง =QUERY(IMDb, “select *”) มันก็จะดึงข้อมูลจาก Dataset ที่อยู่ใน Sheet ชื่อ Dataset มาวางไว้ให้
ฟังก์ชัน QUERY จะมี 2 args ที่ require ส่วน header จะเป็น optional
มาลองกัน 1 ตัวอย่าง ด้วยคำสั่ง =QUERY(IMDB, “select A, B, C”)
มันจะดึงข้อมูลมาเฉพาะตาม COLUMN ที่เราระบุ
???? tip : A, B, C คือ ตำแหน่งของ Column ตาราง ห้ามเป็นตัวเล็ก
สมมติว่า เราไม่ได้อยากเห็ยข้อมูลทั้งหมด อยากจะเห็นแค่ 5 แถวแรก ให้ใช้คำสั่ง limit เพิ่มเข้าไป
=QUERY(IMDB, “select B, C, D, G limit 5”)
03 - Filter Data
เราใช้ WHERE
เพื่อกรองข้อมูลที่เราต้องการ จะคล้ายกับ =FILTER()
สมมติว่าเราใช้คำสั่ง =QUERY(IMDB, “SELECT * WHERE D = ‘R’ “)
อ่านได้ว่า
“select all columns from IMDB table, filter only films with R rating”
เราใช้ LIKE
ใน WHERE
เพื่อค้นหาคำที่เราต้องการได้
wildcard ใน LIKE
คือ %
และ _
- โดยที่ % จะใช้ match any character
- โดยที่ _ จะใช้ match single character
ลองดูตัวอย่างง่ายๆ ของ % เช่น
=QUERY(customers, "SELECT * WHERE E LIKE '%@gmail.com' "
เป็นการค้นหา email ของลูกค้าที่ใช้ gmail.com
ลองดูตัวอย่างง่ายๆ ของ _ เช่น
=QUERY(customers, "SELECT * WHERE N LIKE 'J_hn' ")
เป็นการค้นหาชื่อ ขึ้นต้นด้วย J และ ปิดท้ายด้วย hn
เรายังสามารถใส่เงื่อนไขต่างเช่น AND หรือ OR ลงไปหลัง where อีกด้วย เช่น
=QUERY(IMDB, "select * where G >= 9 and D = 'PG-13'")
04 - Filter NULL
Null หรือ ค่า blank หรือ missing value ใน table
ในtable จะมีช่องว่างๆ อยู่ซึ่งปกติเราจะไม่เอา เราจะไม่ดึงออกมาโดยทำการ filter
ในการ query ค่าที่ไม่มีค่า null หรือ Filterโดยใช้คำสั่ง =QUERY(IMDB, "select A,B,C,D where D is not null")
เราก็จะได้ค่าที่ไม่มีค่า null แล้ว
05 - Aggregate Functions
วิธีหาค่าสถิติเบื้องต้น ในภาษา SQL จะเรียกว่า Aggresgate Function จะมีพวกค่าเฉลี่ย ค่าผลรวม ค่าต่ำสุด สูงสุด จำนวนนับ เป็นต้น
คำสั่งต่างๆ สามารถเขียนรวมกันลงไปใน query function ได้เลยทันที ยกตัวอย่างแรกก่อน
ค่าเฉลี่ย
=QUERY(IMDB, "select AVG(E)")
และอื่นๆ
06 - GROUP BY
เราสามารถจัดกลุ่มข้อมูลได้ด้วย โดยใช้คำสั่ง
=QUERY(IMDB, "select D AVG(E) group by D")
จากช่องว่างในรูปด้านบน จะเห็นว่าเป็นช่องว่างอยู่ ประเด็นคือ มันเกิดจากค่า null ในข้อมูลมีอยู่ 3 แถว เราสามารถที่ตัดข้อมูลออกไปได้ด้วยคำสั่ง
=QUERY(IMDB, "select D, AVG(E) where D is not null group by D")
คราวนี้เราก็สามารถที่จะหาค่าทางสถิติได้เลย จากการจัดกลุ่มข้อมูล
โดยการเพิ่ม คำสั่งจากหัวข้อที่แล้ว เช่น
=QUERY(IMDB, "select D, AVG(E), SUM(E),MIN(E), MAX(E) where D is not null group by D")
07 - LABEL การเปลี่ยนชื่อ column
เราสามารถที่จะเปลี่ยนชื่อของคอลัมน์ด้วยคำสั่ง query ได้ด้วยเช่น
=QUERY**(**IMDB, "select A, B, C limit 5 label A 'MOVIE_ID', B 'MOVIE_TITLE', C 'RELEASE YEAR' "**)**
label จะเป็นคำสั่งในการ rename โดย A คือ อ้างอิงไปยัง column นั้นๆ
คราวนี้จะมีหาค่า AGGREGAET Function กัน โดยใช้คำสั่ง
=QUERY(IMDB, "select D, AVG(G) where D is not null group by D")
เมื่อเราต้องการเปลี่ยนชื่อก็ใส่ label เติมหลังเข้าไป เช่น
=QUERY(IMDB, "select D, AVG(G) where D is not null group by D label AVG(G) 'Average Score'")
08 - PIVOT
ใน SQL จะเทียบเท่า Aggregation + Group By
เมื่อลองเทียบกับ group by แบบเก่า
=QUERY(IMDB, "select D, AVG(G) where D is not null group by D"
ส่วนการใช้ pivot
=QUERY(IMDB, "select AVG(G) pivot D ")
จะเห็นว่ายังมีค่า null ให้ลบค่า null ออก
=QUERY(IMDB, "select AVG(G) where D is not null pivot D ")
คราวนี้เราจะมาทำ column ที่ยาวไปทางด้านขวา ให้จากแนวนอนไปเป็น แนวตั้งโดยใช้ =TRANSPOSE()
09 - Order by
ในการจัดเรียงกลุ่มคำเราจะใช้ keyword ว่า order by ในการจัดเรียง
=QUERY(IMDB, "select B, D, G, E")
เราสามารถที่จะใส่ Order by ไปต่อท้ายได้เลย
=QUERY(IMDB, "select B, D, G ,E order by E desc")
เรียงจากมากไปน้อย โดยจัดเรียงด้วย E
=QUERY(IMDB, "select B, D, G, Eorder by E asc")
เรียงจากน้อยไปมาก
=QUERY(IMDB, "select B, D, G, E where D is not null order by E asc")
เราสามารถกำจัด null ได้ด้วย
10 - Dynamic Query
การเขียน dynamic query คืออะไร มันคือการที่เราเปลี่ยนค่า แล้ว query มันจะเปลี่ยนให้อัตโนมัติ
=QUERY(IMDB, "select * where F like '%Action%'")
คำสั่งนี้ เพื่อดึงข้อมูลหนัง action ออกมาทั้งหมด โดยเราใช้ like
เป็นตัวดึง
คราวนี้เราจะมาสร้าง drop down list เพื่อจะทดสอบการเปลี่ยนของ query string โดยเราจะสร้าง drop down list จาก manu data validation
จากนั้นก็เขียน query dynamic
=QUERY(IMDB, "select * where F like '%" & I11 & "%'"
โดยที่เราเอาช่อง มาแทน คำว่า Action ในตัวอย่างด้านบนนั้นเอง
เราก็จะสามารถที่จะ filter จาก query โดยการกดเลือก drop down list ได้แล้ว
คอร์สนี้ดีมากกกก (ไก่ ล้านตัว) ใครอ่านจบ แนะนำว่าให้ไปสมัครเรียน ติดตามได้ที่ link ด้านล่างนี้เลย
Course Online DATA ROCKIE Bootcamp