chap8.qmd

---
title: "必知必会第8课：使用函数处理数据"
author: "黄天元"
format: html
editor: visual
---

## 数据库的连接

```{r}
library(pacman)
p_load(tidyverse,DBI,RSQLite)

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

## 基于SQL的数据操作

```{r}
# 8.2.1
dbGetQuery(mydb,"SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;") 
dbGetQuery(mydb,"SELECT cust_name, cust_contact
FROM Customers
WHERE cust_contact = 'Michael Green';")
dbGetQuery(mydb,"SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');")

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

## 基于tidyverse的数据操作

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

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

## 练习

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

```{r}
#| echo: false
#| eval: false 

## SQL

# 参考：https://forta.com/books/0135182794/challenges/

## R

# 1
tbl(mydb,"customers") %>% 
  transmute(cust_id,cust_name,
            user_login = str_c(str_sub(cust_contact,1,2),
                               str_sub(cust_city,1,3)) %>% 
              str_to_upper())

# 2
tbl(mydb,"Orders") %>% 
  filter(year(order_date)==2020 & month(order_date)==1) %>% 
  select(order_num)

```