DataRockie

บทความนี้แอดอธิบายการเขียน SQL query พื้นฐานด้วยฟังชั่น QUERY() ใน Google Sheets – SELECT, WHERE, Aggregate Functions, GROUP BY และ ORDER BY ฟังชั่นนี้ exclusive สำหรับ Sheets เท่านั้น 😛

ส่วนตัวแอดคิดว่า QUERY() เป็นฟังชั่นที่มีประโยชน์มาก เพราะเราสามารถเขียน SQL-Like statement เพื่อ transform ข้อมูลที่อยู่บน Sheets ได้อย่างรวดเร็ว ทำไมมันง่ายอย่างนี้

What is Query?

(SQL) Query คือการเขียน statement ภาษาอังกฤษเพื่อดึงข้อมูลจาก table/ database คิดค้นโดย IBM และใช้กันอย่างแพร่หลายมานานเกือบ 50 ปี โดย Google ได้นำ SQL มาพัฒนาต่อยอดและสร้าง API Query Language สำหรับใช้กับ Google Products ต่างๆ เช่น Sheets และ Charts

ด้านล่างคือรายชื่อ commands ที่สามารถนำไปใช้กับฟังชั่น QUERY() ได้ในเวลานี้

Import CSV File

เพื่อนๆสามารถดาวน์โหลดไฟล์ movie_data.csv สำหรับบทความนี้ได้ที่นี่ (เลือก File > Download > Comma-separated values) save file ไว้บนหน้า desktop

สร้าง Google Sheets ใหม่ คลิก File > Import > Upload เพื่ออัพโหลดข้อมูล เลือก “Replace current sheet” ตามรูปด้านล่าง เสร็จแล้วคลิก “Import Data”

Set Named Ranges

หลังจาก import ข้อมูลเสร็จแล้ว เราสามารถตั้งชื่อ named ranges โดยกดปุ่ม CTRL+A เพื่อเลือกข้อมูลที่เราต้องการ เสร็จแล้วไปที่ Data > Named Ranges ตั้งชื่อข้อมูลชุดนี้ว่า “movie_data” คลิก Done

โอเค! ตอนนี้เราพร้อมเขียน basic query ด้วยกันแล้ว ทำตามใน step ต่อไปได้เลย

Getting Started with QUERY

ฟังชั่น QUERY() มี 3 arguments ประกอบด้วย data, query และ [header]

• data คือ ชื่อของข้อมูลที่เรา import เข้ามาใน Google Sheets เช่น movie_data
• query คือ SQL query ที่ใช้จัดการข้อมูล เช่น “SELECT A, B, C LIMIT 5”
• [header] คือ optional argument ให้เราใส่เลข 1 ถ้าแถวที่หนึ่งของ movie_data คือชื่อคอลัมน์ เช่น movie title, release date, wikipedia url, genre เป็นต้น

คลิกปุ่ม + ด้านล่างของหน้าจอเพื่อสร้างชี้ตใหม่ แล้วพิมพ์สูตร =QUERY() ด้านล่างใน cell A1

=QUERY(movie_data, "SELECT A, B, C LIMIT 5", 1)

QUERY: SELECT

SQL clause ที่เราเขียนบ่อยที่สุดคือ SELECT เพื่อดึงคอลัมน์ที่เราต้องการจากตารางข้อมูล โดย Sheets จะใช้ column index เช่น A, B, C, … เพื่อดึงข้อมูลจาก movie_data

=QUERY(movie_data, "SELECT A, B, D, F, M, N LIMIT 10", 1)

Query นี้จะดึงข้อมูลจากคอลัมน์ movie title, release date, genre (1), director (1), budget ($) และ box office revenue ($) ออกมาแค่ 10 แถวแรก i.e. LIMIT 10

QUERY: WHERE

เราใช้ WHERE command (หรือ clause) เพื่อฟิลเตอร์ rows ที่เราต้องการ ในตัวอย่างนี้เราเลือกเฉพาะภาพยนตร์ที่ใช้ column M: budget น้อยกว่า $2,000,000

=QUERY(movie_data, "SELECT A, B, M WHERE M < 2000000", 1)

เราสามารถใช้ AND หรือ OR เพื่อสร้างเงื่อนไขที่ซับซ้อนขึ้น ตัวอย่างนี้เราเลือกกรองเฉพาะภาพยนตร์ที่ใช้ column M: budget น้อยกว่า $2,000,000 และ column D: genre เท่ากับ Action

Tip – SQL ใช้ single quote เวลาเขียนเงื่อนไขของคอลัมน์แบบ character เช่น D = ‘Action’

=QUERY(movie_data, "SELECT A, B, M WHERE M < 2000000 AND D = 'Action'", 1)

QUERY: Aggregate Functions

Aggregate functions คือฟังชั่นที่ใช้หาค่าสถิติเบื้องต้นของคอลัมน์ที่เราต้องการ ประกอบด้วย AVG(), SUM(), MIN(), MAX() และ COUNT()

ตัวอย่างด้านล่าง เราเขียน query เพื่อคำนวณค่าสถิติของ column N: Box Office Revenue ($)

=QUERY(movie_data, "SELECT AVG(N), SUM(N), MIN(N), MAX(N), COUNT(N)", 1)

QUERY: GROUP BY

เรานิยมใช้ aggregate functions กับ GROUP BY เพื่อคำนวณค่าสถิติแบ่งตามกลุ่ม ตัวอย่างนี้เรานับจำนวนภาพยนตร์ COUNT(D) จับกลุ่มตามประเภท genre

=QUERY(movie_data, "SELECT D, COUNT(D) GROUP BY D", 1)

QUERY: ORDER BY

SQL command ตัวสุดท้ายที่เราจะสอนวันนี้คือ ORDER BY ใช้สำหรับเรียงลำดับข้อมูลจากน้อยไปมาก (default – ascending order) หรือจากมากไปหน่อย (desceding order) ตัวอย่างนี้เราเขียน ORDER BY COUNT(D) DESC ต่อท้าย query ที่แล้ว เพื่อเรียงข้อมูล genre ตามจำนวนภาพยนตร์จากสูงไปต่ำ

=QUERY(movie_data, "SELECT D, COUNT(D) GROUP BY D ORDER BY COUNT(D) DESC", 1)

Action คือ genre ที่ได้รับความนิยมสูงสุด 120 เรื่อง ตามมาด้วย comedy 98 เรื่อง และ drama 89 เรื่องตามลำดับ ส่วน genre ที่ได้รับความนิยมน้อยที่สุดสามอันดับล่างสุดคือ documentary, animation และ musical

Summary

แอดคิดว่าฟังชั่น QUERY() สามารถใช้แทน Pivot Table ได้เลยด้วยการเขียน SQL-Like statement เพื่อ transform ข้อมูลอย่างที่เราต้องการ ส่วนข้อจำกัดสำคัญคือ Google API ตอนนี้ยังรองรับ SQL แค่ไม่กี่ commands เอง i.e. ตอนนี้เรายังเขียน JOIN และ HAVING ในฟังชั่น QUERY() ไม่ได้ ต้องรอดูว่าในอนาคต Google จะอัพเดทฟีเจอร์ใหม่ๆหรือเปล่า

🚀 Always Explore!