R에서 실행하는 SQL 쿼리

R 데이터 가져오기 중급

Filip Schouwenaars

Instructor, DataCamp

ch_2_1_slides.003.png

ch_2_1_slides.004.png

ch_2_1_slides.005.png

ch_2_1_slides.006.png

ch_2_1_slides.007.png

선택적 가져오기

SQL 쿼리
DBI -> RMySQL, RPostgreSQL, ...
SQL 기본만 다룹니다

company

ch_2_1_slides.012.png

company

ch_2_1_slides.013.png

패키지 로드 및 연결

library(DBI)con <- dbConnect(RMySQL::MySQL(),
         dbname = "company",
         host = "courses.csrrinzqubik.us-
                          east-1.rds.amazonaws.com",
         port = 3306,
         user = "student",
         password = "datacamp")

예제 1

employees <- dbReadTable(con, "employees")
subset(employees, 
         subset = started_at > "2012-09-01", 
         select = name)

     name
3   Julie
4 Heather
5    John

dbGetQuery(con, "SELECT name FROM employees 
                      WHERE started_at > '2012-09-01'")

     name
1   Julie
2 Heather
3    John

예제 1

ch_2_1_slides.027.png

예제 1

ch_2_1_slides.028.png

예제 1

ch_2_1_slides.029.png

예제 1

ch_2_1_slides.030.png

예제 1

ch_2_1_slides.031.png

예제 1

ch_2_1_slides.032.png

company

ch_2_1_slides.034.png

company

ch_2_1_slides.035.png

예제 2

products <- dbReadTable(con, "products")

subset(products, subset = contract == 1)

  id          name contract
2  2     Call Plus        1
4  9 Biz Unlimited        1

dbGetQuery(con, "SELECT * FROM products 
                                   WHERE contract = 1")

  id          name contract
1  2     Call Plus        1
2  9 Biz Unlimited        1

연습해 봅시다!

R 데이터 가져오기 중급