在 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

¡Vamos a practicar!

R 数据导入进阶