12 必知必会第12课：联结表

作者

黄天元

12.1 数据库的连接

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

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

12.2 基于SQL和tidyverse的数据操作

# 12.2
dbGetQuery(mydb,"SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;")

        vend_name           prod_name prod_price
1      Bears R Us   8 inch teddy bear       5.99
2      Bears R Us  12 inch teddy bear       8.99
3      Bears R Us  18 inch teddy bear      11.99
4 Doll House Inc.   Fish bean bag toy       3.49
5 Doll House Inc.   Bird bean bag toy       3.49
6 Doll House Inc. Rabbit bean bag toy       3.49
7 Doll House Inc.         Raggedy Ann       4.99
8   Fun and Games           King doll       9.49
9   Fun and Games          Queen doll       9.49

tbl(mydb,"Vendors") %>% 
  select(vend_name,vend_id) %>% 
  inner_join(
    tbl(mydb,"Products") %>% 
      select(prod_name,prod_price,vend_id)
  ) %>% 
  select(-vend_id)

Joining with `by = join_by(vend_id)`

# Source:   SQL [9 x 3]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
  vend_name       prod_name           prod_price
  <chr>           <chr>                    <dbl>
1 Bears R Us      8 inch teddy bear         5.99
2 Bears R Us      12 inch teddy bear        8.99
3 Bears R Us      18 inch teddy bear       12.0 
4 Doll House Inc. Fish bean bag toy         3.49
5 Doll House Inc. Bird bean bag toy         3.49
6 Doll House Inc. Rabbit bean bag toy       3.49
7 Doll House Inc. Raggedy Ann               4.99
8 Fun and Games   King doll                 9.49
9 Fun and Games   Queen doll                9.49

# 12.2.1
dbGetQuery(mydb,"SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;")

         vend_name           prod_name prod_price
1       Bears R Us   8 inch teddy bear       5.99
2       Bears R Us  12 inch teddy bear       8.99
3       Bears R Us  18 inch teddy bear      11.99
4       Bears R Us   Fish bean bag toy       3.49
5       Bears R Us   Bird bean bag toy       3.49
6       Bears R Us Rabbit bean bag toy       3.49
7       Bears R Us         Raggedy Ann       4.99
8       Bears R Us           King doll       9.49
9       Bears R Us          Queen doll       9.49
10   Bear Emporium   8 inch teddy bear       5.99
11   Bear Emporium  12 inch teddy bear       8.99
12   Bear Emporium  18 inch teddy bear      11.99
13   Bear Emporium   Fish bean bag toy       3.49
14   Bear Emporium   Bird bean bag toy       3.49
15   Bear Emporium Rabbit bean bag toy       3.49
16   Bear Emporium         Raggedy Ann       4.99
17   Bear Emporium           King doll       9.49
18   Bear Emporium          Queen doll       9.49
19 Doll House Inc.   8 inch teddy bear       5.99
20 Doll House Inc.  12 inch teddy bear       8.99
21 Doll House Inc.  18 inch teddy bear      11.99
22 Doll House Inc.   Fish bean bag toy       3.49
23 Doll House Inc.   Bird bean bag toy       3.49
24 Doll House Inc. Rabbit bean bag toy       3.49
25 Doll House Inc.         Raggedy Ann       4.99
26 Doll House Inc.           King doll       9.49
27 Doll House Inc.          Queen doll       9.49
28    Furball Inc.   8 inch teddy bear       5.99
29    Furball Inc.  12 inch teddy bear       8.99
30    Furball Inc.  18 inch teddy bear      11.99
31    Furball Inc.   Fish bean bag toy       3.49
32    Furball Inc.   Bird bean bag toy       3.49
33    Furball Inc. Rabbit bean bag toy       3.49
34    Furball Inc.         Raggedy Ann       4.99
35    Furball Inc.           King doll       9.49
36    Furball Inc.          Queen doll       9.49
37   Fun and Games   8 inch teddy bear       5.99
38   Fun and Games  12 inch teddy bear       8.99
39   Fun and Games  18 inch teddy bear      11.99
40   Fun and Games   Fish bean bag toy       3.49
41   Fun and Games   Bird bean bag toy       3.49
42   Fun and Games Rabbit bean bag toy       3.49
43   Fun and Games         Raggedy Ann       4.99
44   Fun and Games           King doll       9.49
45   Fun and Games          Queen doll       9.49
46  Jouets et ours   8 inch teddy bear       5.99
47  Jouets et ours  12 inch teddy bear       8.99
48  Jouets et ours  18 inch teddy bear      11.99
49  Jouets et ours   Fish bean bag toy       3.49
50  Jouets et ours   Bird bean bag toy       3.49
51  Jouets et ours Rabbit bean bag toy       3.49
52  Jouets et ours         Raggedy Ann       4.99
53  Jouets et ours           King doll       9.49
54  Jouets et ours          Queen doll       9.49

tbl(mydb,"Vendors") %>% 
  select(vend_name) %>% 
  cross_join(tbl(mydb,"Products") %>% 
               select(prod_name,prod_price)) %>% 
  collect()

# A tibble: 54 × 3
   vend_name     prod_name           prod_price
   <chr>         <chr>                    <dbl>
 1 Bears R Us    8 inch teddy bear         5.99
 2 Bears R Us    12 inch teddy bear        8.99
 3 Bears R Us    18 inch teddy bear       12.0 
 4 Bears R Us    Fish bean bag toy         3.49
 5 Bears R Us    Bird bean bag toy         3.49
 6 Bears R Us    Rabbit bean bag toy       3.49
 7 Bears R Us    Raggedy Ann               4.99
 8 Bears R Us    King doll                 9.49
 9 Bears R Us    Queen doll                9.49
10 Bear Emporium 8 inch teddy bear         5.99
# ℹ 44 more rows

# 12.2.2
dbGetQuery(mydb,"SELECT vend_name, prod_name, prod_price
FROM Vendors
INNER JOIN Products ON Vendors.vend_id = Products.vend_id;")

        vend_name           prod_name prod_price
1      Bears R Us   8 inch teddy bear       5.99
2      Bears R Us  12 inch teddy bear       8.99
3      Bears R Us  18 inch teddy bear      11.99
4 Doll House Inc.   Fish bean bag toy       3.49
5 Doll House Inc.   Bird bean bag toy       3.49
6 Doll House Inc. Rabbit bean bag toy       3.49
7 Doll House Inc.         Raggedy Ann       4.99
8   Fun and Games           King doll       9.49
9   Fun and Games          Queen doll       9.49

tbl(mydb,"Vendors") %>% 
  select(vend_name,vend_id) %>% 
  inner_join(
    tbl(mydb,"Products") %>% 
      select(prod_name,prod_price,vend_id)
  ) %>% 
  select(-vend_id)

Joining with `by = join_by(vend_id)`

# Source:   SQL [9 x 3]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
  vend_name       prod_name           prod_price
  <chr>           <chr>                    <dbl>
1 Bears R Us      8 inch teddy bear         5.99
2 Bears R Us      12 inch teddy bear        8.99
3 Bears R Us      18 inch teddy bear       12.0 
4 Doll House Inc. Fish bean bag toy         3.49
5 Doll House Inc. Bird bean bag toy         3.49
6 Doll House Inc. Rabbit bean bag toy       3.49
7 Doll House Inc. Raggedy Ann               4.99
8 Fun and Games   King doll                 9.49
9 Fun and Games   Queen doll                9.49

# 12.2.3
dbGetQuery(mydb,"SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;")

            prod_name       vend_name prod_price quantity
1  18 inch teddy bear      Bears R Us      11.99       50
2   Fish bean bag toy Doll House Inc.       3.49      100
3   Bird bean bag toy Doll House Inc.       3.49      100
4 Rabbit bean bag toy Doll House Inc.       3.49      100
5         Raggedy Ann Doll House Inc.       4.99       50

tbl(mydb,"Products") %>% 
  select(prod_id,prod_name,prod_price,vend_id) %>% 
  inner_join(
    tbl(mydb,"Vendors") %>% 
      select(vend_id,vend_name)
  ) %>% 
  inner_join(
    tbl(mydb,"OrderItems") %>% 
      filter(order_num == 20007) %>% 
      select(prod_id,quantity)
  ) %>% 
  transmute(prod_name,vend_name,prod_price,quantity)

Joining with `by = join_by(vend_id)`
Joining with `by = join_by(prod_id)`

# Source:   SQL [5 x 4]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
  prod_name           vend_name       prod_price quantity
  <chr>               <chr>                <dbl>    <int>
1 18 inch teddy bear  Bears R Us           12.0        50
2 Fish bean bag toy   Doll House Inc.       3.49      100
3 Bird bean bag toy   Doll House Inc.       3.49      100
4 Rabbit bean bag toy Doll House Inc.       3.49      100
5 Raggedy Ann         Doll House Inc.       4.99       50

dbGetQuery(mydb,"SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';")

      cust_name       cust_contact
1       Fun4All Denise L. Stephens
2 The Toy Store         Kim Howard

tbl(mydb,"Customers") %>% select(cust_name, cust_contact,cust_id) %>% 
  inner_join(
    tbl(mydb,"Orders") %>% select(order_num,cust_id)
  ) %>% 
  inner_join(
    tbl(mydb,"OrderItems") %>% 
      filter(prod_id == 'RGAN01')%>%
      select(order_num)
  ) %>% select(cust_name, cust_contact)

Joining with `by = join_by(cust_id)`
Joining with `by = join_by(order_num)`

# Source:   SQL [2 x 2]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
  cust_name     cust_contact      
  <chr>         <chr>             
1 Fun4All       Denise L. Stephens
2 The Toy Store Kim Howard

12.3 练习

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