ฝึกเขียน SQL Query ใน RStudio

R มี package sqldf ที่ช่วยให้เราสามารถเขียน SQL query เพื่อจัดการ data frame ง่ายๆ tutorial วันนี้เราจะมาสอนเขียน basic query เพื่อดึงข้อมูลจาก data frame รวมถึงการ join tables โดยเราสามารถ execute query ด้วยฟังชั่น sqldf()

SQL verbs ตัวหลักๆที่เราใช้กันในทุกๆ query มีสามตัวคือ

select – ใช้เลือกคอลั่มที่เราต้องการจาก data frame (หรือ table ใน database)
where – ใช้เลือก records ที่เราต้องการด้วยเงื่อนไขที่เราสร้าง
join – ใช้เชื่อมหลายๆ table เข้าด้วยกันด้วย primary และ foreign keys

Read SQL as English Language

เทคนิคการเขียน SQL คือให้มองมันเป็นประโยคภาษาอังกฤษธรรมดา ลองดูตัวอย่างด้านล่าง

SELECT * FROM customers; อ่านว่า “select all columns from table customers.”

SELECT * FROM customers WHERE age <= 30; อ่านว่า “select all columns from table customers filter only customers age less than or equal 30 years.”

SELECT * FROM customers JOIN location ON customers.id = location.id; อ่านว่า “select all columns from table customers (inner) joined with location using column id (to join).”

สิ่งที่ต้องรู้คือการเขียน SQL query เป็นแบบ case insensitive แต่เรานิยมเขียน SQL clause เป็นตัวพิมพ์ใหญ่ตามตัวอย่างด้านบนและปิดท้าย query ด้วย ;

SELECT

ให้เริ่มจากการติดตั้ง package sqldf แล้วลองเขียน SQL query เพื่อดึงทุกคอลั่มออกมาจาก mtcars ด้วย * หรือจะ specify ชื่อคอลั่มที่เราต้องการใน select clause เลยก็ได้

	# install sqldf
	install.packages("sqldf")
	library(sqldf)

	# select all columns from table mtcars
	sqldf("SELECT * FROM mtcars;")

	# select these 3 columns from table mtcars
	sqldf("SELECT mpg, wt, hp FROM mtcars;")

view raw sqldf1.r hosted with ❤ by GitHub

WHERE

เราใช้ where clause ในการฟิลเตอร์ records ที่เราต้องการ โดยเราสามารถใส่ได้มากกว่าหนึ่งเงื่อนไขด้วย AND หรือ OR

	# filter only cars with hp <= 100
	sqldf("SELECT * FROM mtcars WHERE hp <= 100;")

	# filter more than one conditions with AND
	sqldf("SELECT * FROM mtcars WHERE hp <= 100 AND am = 1;")

	# filter more than one condition with OR
	sqldf("SELECT * FROM mtcars WHERE hp <= 100 OR gear = 4;")

view raw sqldf2.r hosted with ❤ by GitHub

โค้ด R ด้านล่างใช้สร้าง customers data frame มีสามคอลั่ม {ID, cust_name, year} และมีข้อมูลอยู่ 10 records พอสร้าง df เสร็จแล้วเราใช้ฟังชั่น head() เพื่อปริ้น 6 แถวแรกของ df ออกมาดูใน console

	# add ID and models columns to mtcars
	mtcars$ID <- 1:32
	mtcars$models <- rownames(mtcars)

	# create another ID dataframe
	customers <- data.frame(ID = 1:10,
	cust_name = LETTERS[1:10],
	year = 2010:2019)

	# see first six rows of customers
	head(customers)

view raw sqldf3.r hosted with ❤ by GitHub

ตัวอย่างถัดไปมาลองเขียน join table mtcars และ customers ด้วยคอลั่ม ID

JOIN

line 2-4 ในตัวอย่างด้านล่างเราเขียน join tables ด้วยชื่อเต็มของ table นั้นๆ i.e. customers.ID = mtcars.ID เราใช้ . ในการ refer ชื่อคอลั่มในตารางนั้นๆ

line 7-9 เราใช้ alias ชื่อย่อของ tables ในการ join tables i.e. c.ID = m.ID ทั้งสองวิธีได้ผลเหมือนกัน แต่การใช้ alias จะช่วยให้การเขียน query สั้นลง ประหยัดเวลาเขียนชื่อตารางแบบเต็มๆ

JOIN clause ใน SQL คือ inner join ถ้าอยากได้ left join ให้พิมพ์ว่า LEFT JOIN แบบ explicit ได้เลย โดยชื่อคอลั่มในสองตารางที่เอามา join กันไม่จำเป็นต้องเหมือนกันก็ได้

	# join two tables and select all columns
	sqldf("SELECT * FROM mtcars
	JOIN customers
	ON mtcars.ID = customers.ID;")

	# join two tables and select some columns
	sqldf("SELECT c.ID, m.models, m.mpg, c.year FROM mtcars m
	JOIN customers c
	ON c.ID = m.ID;")

view raw sqldf4.r hosted with ❤ by GitHub

Tip – ถ้าเราต้องการฟิลเตอร์ records ที่เราต้องการ ให้เขียน WHERE clause หลังจากที่เรา JOIN tables เสร็จแล้ว เช่น select * from … join … on … where …;

Appendix – Connect to PostgreSQL

ด้านล่างเป็นตัวอย่างการเขียนสร้าง connection กับ PostgreSQL เพื่อนๆสามารถอ่านวิธีการ connect RStudio กับ database ต่างๆได้ที่ https://db.rstudio.com/databases/

# https://db.rstudio.com/databases/postgresql/
con <- DBI::dbConnect(odbc::odbc(),
    Driver   = "[your driver's name]",
    Server   = "[your server's path]",
    Database = "[your database's name]",
    UID      = rstudioapi::askForPassword("Database user"),
    PWD      = rstudioapi::askForPassword("Database password"),
    Port     = 5432)

จบแล้วกับ tutorial วันนี้ สำหรับเพื่อนๆที่อยากเรียน SQL แบบเต็มๆ ลองดูคอร์สเรียนออนไลน์ฟรีของเราได้ที่ https://datarockieblog.wordpress.com

DataRockie