R SQL

Write SQL Query in RStudio

เขียน SQL query ง่ายๆใน RStudio ด้วย package sqldf พร้อมวิธีการสร้าง connection กับ database ต่างๆ

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 เลยก็ได้

WHERE

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

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

ตัวอย่างถัดไปมาลองเขียน 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 กันไม่จำเป็นต้องเหมือนกันก็ได้

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://datarockie.com

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.