8 必知必会第8课：使用函数处理数据

作者

黄天元

8.1 数据库的连接

library(pacman)
p_load(tidyverse,DBI,RSQLite)

mydb = dbConnect(RSQLite::SQLite(), "data/TYSQL.sqlite")

8.2 基于SQL的数据操作

# 8.2.1
dbGetQuery(mydb,"SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;")

        vend_name vend_name_upcase
1   Bear Emporium    BEAR EMPORIUM
2      Bears R Us       BEARS R US
3 Doll House Inc.  DOLL HOUSE INC.
4   Fun and Games    FUN AND GAMES
5    Furball Inc.     FURBALL INC.
6  Jouets et ours   JOUETS ET OURS

dbGetQuery(mydb,"SELECT cust_name, cust_contact
FROM Customers
WHERE cust_contact = 'Michael Green';")

[1] cust_name    cust_contact
<0 rows> (or 0-length row.names)

dbGetQuery(mydb,"SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');")

   cust_name   cust_contact
1 Kids Place Michelle Green

# 8.2.2
dbGetQuery(mydb,"SELECT order_num
FROM Orders
WHERE strftime('%Y', order_date) = '2020';")

  order_num
1     20005
2     20006
3     20007
4     20008
5     20009

8.3 基于tidyverse的数据操作

# 8.2.1
tbl(mydb,"Vendors") %>% 
  transmute(vend_name,vend_name_upcase = str_to_upper(vend_name)) %>% 
  arrange(vend_name)

# Source:     SQL [6 x 2]
# Database:   sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
# Ordered by: vend_name
  vend_name       vend_name_upcase
  <chr>           <chr>           
1 Bear Emporium   BEAR EMPORIUM   
2 Bears R Us      BEARS R US      
3 Doll House Inc. DOLL HOUSE INC. 
4 Fun and Games   FUN AND GAMES   
5 Furball Inc.    FURBALL INC.    
6 Jouets et ours  JOUETS ET OURS

# 8.2.2
tbl(mydb,"Orders") %>% 
  filter(year(order_date) == 2020) %>% 
  select(order_num)

# Source:   SQL [5 x 1]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
  order_num
      <int>
1     20005
2     20006
3     20007
4     20008
5     20009

8.4 练习

请使用SQL和R两种方式，解决课后的挑战题。