7 必知必会第7课：创建计算字段

作者

黄天元

7.1 数据库的连接

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

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

7.2 基于SQL的数据操作

# 7.2
dbGetQuery(mydb,"SELECT vend_name || '(' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;")

  vend_name || '(' || vend_country || ')'
1                      Bear Emporium(USA)
2                         Bears R Us(USA)
3                    Doll House Inc.(USA)
4                  Fun and Games(England)
5                       Furball Inc.(USA)
6                  Jouets et ours(France)

dbGetQuery(mydb,"SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
FROM Vendors
ORDER BY vend_name;")

  RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
1                                    Bear Emporium (USA)
2                                       Bears R Us (USA)
3                                  Doll House Inc. (USA)
4                                Fun and Games (England)
5                                     Furball Inc. (USA)
6                                Jouets et ours (France)

dbGetQuery(mydb,"SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;")

               vend_title
1     Bear Emporium (USA)
2        Bears R Us (USA)
3   Doll House Inc. (USA)
4 Fun and Games (England)
5      Furball Inc. (USA)
6 Jouets et ours (France)

# 7.3
dbGetQuery(mydb,"SELECT prod_id, quantity, item_price
FROM OrderItems
WHERE order_num = 20008;")

  prod_id quantity item_price
1  RGAN01        5       4.99
2    BR03        5      11.99
3  BNBG01       10       3.49
4  BNBG02       10       3.49
5  BNBG03       10       3.49

dbGetQuery(mydb,"SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;")

  prod_id quantity item_price expanded_price
1  RGAN01        5       4.99          24.95
2    BR03        5      11.99          59.95
3  BNBG01       10       3.49          34.90
4  BNBG02       10       3.49          34.90
5  BNBG03       10       3.49          34.90

7.3 基于tidyverse的数据操作

# 7.2
tbl(mydb,"Vendors") %>% 
  arrange(vend_name) %>% 
  transmute(vend_title = str_c(str_trim(vend_name,"right")," (",
                            str_trim(vend_country,"right"),")"))

# Source:   SQL [6 x 1]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
  vend_title             
  <chr>                  
1 Bear Emporium (USA)    
2 Bears R Us (USA)       
3 Doll House Inc. (USA)  
4 Fun and Games (England)
5 Furball Inc. (USA)     
6 Jouets et ours (France)

# 7.3
tbl(mydb,"OrderItems") %>% 
  filter(order_num == 20008) %>% 
  transmute(prod_id,quantity,item_price,
            expanded_price = quantity * item_price)

# Source:   SQL [5 x 4]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
  prod_id quantity item_price expanded_price
  <chr>      <int>      <dbl>          <dbl>
1 RGAN01         5       4.99           25.0
2 BR03           5      12.0            60.0
3 BNBG01        10       3.49           34.9
4 BNBG02        10       3.49           34.9
5 BNBG03        10       3.49           34.9

7.4 练习

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