บันทึกการใช้งาน Google Sheet 103 สำหรับนักวิทยาศาสตร์ข้อมูล Data Science - sprint 01

Image placeholder
แวะมาทักทายกันได้


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

แวะมาทักทายกันได้
donate

Categories: Tutorial Tags: #Data Science , 1880