index.cn.qmd

---
title: "SQL handbook"
author: "Tony Duan"
execute:
  warning: false
  error: false
format:
  html:
    toc: true
    toc-location: right
    code-fold: show
    code-tools: true
    number-sections: true
    code-block-bg: true
    code-block-border-left: "#31BAE9"
---


本文档提供了使用 SQL、R 和 Python 执行常见数据操作任务的综合指南。它可作为理解如何在这三种流行的数据分析工具中实现相似结果的参考。

# 设置与配置

```{r}
#| code-fold: true
#| output: false
library(reticulate)
py_require(c("pandas","Great-Tables","polars","pyarrow"))

# Load libraries for database interaction, data manipulation, and connections.
library(DBI)
library(tidyverse)
library(RSQLite)
library(connections)
library(duckdb)

# Prepare the R dataframes.
# Remove the existing mtcars dataset if it exists.
rm(mtcars)
# Create the mtcars dataframe from the base R dataset, adding the row names as a new column.
mtcars = cbind(model_name = rownames(mtcars), mtcars) |> head(10)
# Create the iris dataframe from the base R dataset.
iris = iris |> head(10)

# Remove the database file if it already exists to start with a clean slate.
if (file.exists("my-db.duckdb")) {
  file.remove("my-db.duckdb")
}
# Establish a connection to the DuckDB database.
con <- dbConnect(duckdb(), dbdir = "my-db.duckdb", read_only = FALSE)
# Write the iris and mtcars dataframes to the database as tables.
dbWriteTable(con, "iris_table", iris, overwrite = TRUE)
dbWriteTable(con, "mtcars_table", mtcars, overwrite = TRUE)

```


```{python}
#| code-fold: true
#| output: false
import pandas as pd
import polars as pl
import os

from platform import python_version
#print(python_version())

cities_pd = pd.DataFrame({
    'country': ['NL', 'NL', 'NL', 'US', 'US', 'US', 'US', 'US', 'US'],
    'name': ['Amsterdam', 'Amsterdam', 'Amsterdam', 'Seattle', 'Seattle', 'Seattle', 'New York City', 'New York City', 'New York City'],
    'year': [2000, 2010, 2020, 2000, 2010, 2020, 2000, 2010, 2020],
    'population': [1005, 1065, 1158, 564, 608, 738, 8015, 8175, 8772]
})

# Make the R dataframes available in the Python environment.
mtcars_pd = r.mtcars
iris_pd = r.iris
mtcars_pl = pl.from_pandas(mtcars_pd)
iris_pl = pl.from_pandas(iris_pd)
cities_pl=pl.from_pandas(cities_pd)
```


- DuckDB 数据库 `my-db.duckdb` 中有 `iris_table` 和 `mtcars_table` 表。
- R 环境中有 `iris` 和 `mtcars` 数据框。
- Python 环境中有 `iris` 和 `mtcars` 数据框。

# 显示所有表

本节演示如何列出每个环境中的所有可用表或数据框。

::: panel-tabset

## SQL

```{sql}
#| connection: con
-- 显示所连接数据库中的所有表。
SHOW ALL TABLES;
```

## R

```{r}
# 列出当前 R 环境中的所有数据框。
dflist <- Filter(is.data.frame, as.list(.GlobalEnv))
names(dflist)
```

## Python pandas

```{python}
# 列出当前 Python 环境中的所有 pandas 数据框。
import pandas as pd
alldfs = [var for var in dir() if isinstance(eval(var), pd.core.frame.DataFrame)]
print(alldfs)
```

## Python Polars

```{python}
import polars as pl

alldfs = [name for name, val in globals().items() if isinstance(val, pl.DataFrame)]
print(alldfs)
```


:::

# 描述表

本节介绍如何获取表结构和统计信息的摘要。

::: panel-tabset

## SQL

```{sql}
#| connection: con
-- 描述 mtcars_table 的列和数据类型。
DESCRIBE mtcars_table;
```

## R

```{r}
# 提供 mtcars 数据框的详细摘要。
skimr::skim(mtcars)
```

## Python pandas

```{python}
# 生成 mtcars 数据框的描述性统计信息。
mtcars_pd.describe(include='all')
```



## Python Polars

```{python}
mtcars_pl.describe()
```




:::

# 显示列名

本节演示如何检索表的列名。

::: panel-tabset

## SQL

```{sql}
#| connection: con
-- 显示 mtcars_table 中列的信息。
PRAGMA table_info(mtcars_table);
```

## R

```{r}
# 获取 mtcars 数据框的列名。
names(mtcars)
```

## Python pandas

```{python}
# 从 mtcars 数据框获取列名列表。
import pandas as pd
list(mtcars_pd.columns.values)
```


## Python Polars

```{python}
# 从 mtcars 数据框获取列名列表。
mtcars_pl.columns
```



:::

# 选择

## 选择前 6 行并重命名

本节介绍如何选择列的子集、重命名它们并限制返回的行数。

::: panel-tabset

## SQL

```{sql}
#| connection: con
-- 选择并重命名列，将结果限制为前 6 行。
SELECT model_name as model, mpg, cyl FROM mtcars_table LIMIT 6;
```

## R

```{r}
# 从 mtcars 数据框的前 6 行中选择并重命名列。
head(mtcars, 6) |> select(model = model_name, mpg, cyl)
```

## Python pandas

```{python}
# 重命名 mtcars 数据框中的列。
mtcars_pd.rename(columns={'model_name': 'model'})
```

## Python Polars

```{python}
mtcars_pl.select(pl.col("model_name").alias("model"), pl.col("mpg"), pl.col("cyl")).head(6)
```

:::

## 选择不同行

本节演示如何根据指定的列检索唯一的行。

::: panel-tabset

## SQL

```{sql}
#| connection: con
-- 选择 mpg 和 cyl 的不同组合。
SELECT DISTINCT mpg, cyl FROM mtcars_table;
```

## R

```{r}
# 根据 mpg 和 cyl 获取不同的行。
mtcars |> distinct(mpg, cyl)
```

## Python pandas

```{python}
# 选择特定列并删除重复行。
df = mtcars_pd[["mpg", "cyl"]]
print(df.drop_duplicates())
```

## Python Polars

```{python}
mtcars_pl.select(pl.col("mpg"), pl.col("cyl")).unique()
```

:::

# 检查行数和列数

本节介绍如何查找表的维度。

::: panel-tabset

## SQL

```{sql}
#| connection: con
-- 获取总行数。
SELECT count(*) AS row_number FROM mtcars_table;
```

```{sql}
#| connection: con
-- 获取总列数。
SELECT count(*) AS column_number FROM (DESCRIBE mtcars_table);
```

## R

```{r}
# 获取行数。
nrow(mtcars)
```

```{r}
# 获取列数。
ncol(mtcars)
```

## Python pandas

```{python}
# 获取行数。
mtcars_pd.shape[0]
```

```{python}
# 获取列数。
mtcars_pd.shape[1]
```



## Python Polars

```{python}
# 获取行数。
mtcars_pl.shape[0]
```

```{python}
# 获取列数。
mtcars_pl.shape[1]
```



:::

# 创建列

本节演示如何基于现有数据向表中添加新列。

::: panel-tabset

## SQL

```{sql}
#| connection: con
-- 通过对现有列执行计算来创建新列。
SELECT *, mpg + 1 AS new_mpg FROM mtcars_table;
```

## R

```{r}
# 向数据框添加新列。
mtcars |> mutate(new_mpg = mpg + 1)
```

## Python pandas

```{python}
# 在数据框中创建新列。
mtcars_pd["new_mpg"] = mtcars_pd["mpg"] + 1
mtcars_pd
```

## Python Polars

```{python}
mtcars_pl.with_columns((pl.col("mpg") + 1).alias("new_mpg"))
```

:::

# 筛选

本节介绍如何选择满足特定条件的行。

::: panel-tabset

## SQL

```{sql}
#| connection: con
-- 使用 AND 运算符根据多个条件筛选行。
SELECT * FROM mtcars_table WHERE mpg = 21 AND cyl = 6;
```

```{sql}
#| connection: con
-- 使用 OR 运算符筛选行。
SELECT * FROM mtcars_table WHERE mpg = 21 OR cyl = 6;
```

## R

```{r}
# 使用 & 运算符进行“与”筛选。
mtcars |> filter(mpg == 21 & cyl == 6)
```

```{r}
# 使用 | 运算符进行“或”筛选。
mtcars |> filter(mpg == 21 | cyl == 6)
```

## Python pandas

```{python}
# 使用 query 方法进行“与”筛选。
mtcars_pd.query('mpg == 21 and cyl == 6')
```

```{python}
# 使用 query 方法进行“或”筛选。
mtcars_pd.query('mpg == 21 or cyl == 6')
```

## Python Polars

```{python}
# polars 的“与”筛选
mtcars_pl.filter((pl.col("mpg") == 21) & (pl.col("cyl") == 6))
```

```{python}
# polars 的“或”筛选
mtcars_pl.filter((pl.col("mpg") == 21) | (pl.col("cyl") == 6))
```

:::

# 排序

本节演示如何根据一个或多个列对表进行排序。

::: panel-tabset

## SQL

```{sql}
#| connection: con
-- 按 mpg 降序对结果进行排序，并显示前 3 行。
SELECT model_name AS model, mpg, cyl FROM mtcars_table ORDER BY mpg DESC LIMIT 3;
```

## R

```{r}
# 按 mpg 降序排列数据框。
mtcars |> select(model = model_name, mpg, cyl) |> arrange(desc(mpg)) |> head(3)
```

## Python pandas

```{python}
# 按 mpg 降序对数据框进行排序。
mtcars_pd[["model_name", "mpg", "cyl"]].sort_values(by='mpg', ascending=False).head(3)
```

## Python Polars

```{python}
mtcars_pl.select("model_name", "mpg", "cyl").sort("mpg", descending=True).head(3)
```

:::

# 分组

本节介绍如何对行进行分组并执行聚合计算。

::: panel-tabset

## SQL

```{sql}
#| connection: con
-- 按 model_name 分组，并计算 mpg 的总和和 cyl 的平均值。
SELECT model_name, sum(mpg) AS total_mpg, mean(cyl) AS cyl_mean FROM mtcars_table
GROUP BY 1 LIMIT 5;
```

## R

```{r}
# 按 model_name 分组并汇总数据。
mtcars |> group_by(model_name) |> summarise(total_mpg = sum(mpg), cyl_mean = mean(cyl)) |> head(5)
```

## Python pandas

```{python}
# 按 model_name 分组并聚合数据。
mtcars_pd.groupby('model_name').agg({'mpg': 'sum', 'cyl': 'mean'}).head(5)
```

## Python Polars

```{python}
mtcars_pl.group_by("model_name").agg([
    pl.sum("mpg").alias("total_mpg"),
    pl.mean("cyl").alias("cyl_mean")
]).head(5)
```

:::

# 创建表

## CREATE OR REPLACE

此命令创建新表或覆盖现有表。

```{sql}
#| connection: con
-- 创建一个临时表，如果它已存在则替换它。
CREATE OR REPLACE TEMP TABLE mtcars_table_group AS
SELECT model_name, sum(mpg) AS total_mpg, mean(cyl) AS cyl_mean FROM mtcars_table
GROUP BY 1;
```

## CREATE TABLE IF NOT EXISTS

此命令仅在表尚不存在时创建表。

```{sql}
#| connection: con
-- 仅在尚不存在的情况下创建新表。
CREATE TABLE IF NOT EXISTS new_mtcars_table_group AS
SELECT model_name, sum(mpg) AS total_mpg, mean(cyl) AS cyl_mean FROM mtcars_table
GROUP BY 1;
```

# 唯一值

## 检查唯一值

本节演示如何验证列中值的唯一性。

::: panel-tabset

### SQL

```{sql}
#| connection: con
-- 计算总行数和不同 model_name 的数量。
SELECT count(*), count(DISTINCT model_name) FROM mtcars_table;
```

### Python pandas

```{python}
# 计算总行数和不同 model_name 的数量。
print(len(mtcars_pd), mtcars_pd.model_name.nunique())
```

### Python Polars

```{python}
# 计算总行数和不同 model_name 的数量。
mtcars_pl.select(pl.count(), pl.col("model_name").n_unique())
```

:::

## 获取重复和非重复数据

本节演示如何根据单个列获取重复和非重复数据。

::: panel-tabset

### SQL

#### 显示所有重复项

```{sql}
#| connection: con
-- 根据 mpg 列获取重复行。
SELECT * FROM mtcars_table WHERE mpg IN (SELECT mpg FROM mtcars_table GROUP BY mpg HAVING count(*) > 1);
```

保留非重复项

```{sql}
#| connection: con
-- 根据 mpg 列获取非重复行。
SELECT * FROM mtcars_table WHERE mpg IN (SELECT mpg FROM mtcars_table GROUP BY mpg HAVING count(*) = 1);
```

### R

#### 显示所有重复项


```{r}
# 根据 mpg 列获取重复行。
mtcars |> filter(duplicated(mpg) | duplicated(mpg, fromLast = TRUE))
```

#### 保留非重复项


```{r}
# 根据 mpg 列获取非重复行。
mtcars |> filter(!duplicated(mpg) & !duplicated(mpg, fromLast = TRUE))
```

### Python pandas

#### 显示所有重复项


```{python}
# 根据 mpg 列获取重复行。
mtcars_pd[mtcars_pd.duplicated(subset=['mpg'], keep=False)]
```
#### 保留非重复项

```{python}
# 根据 mpg 列获取非重复行。
mtcars_pd[~mtcars_pd.duplicated(subset=['mpg'], keep=False)]
```

### Python Polars

```{python}
# 根据 mpg 列获取重复行。
mtcars_pl.filter(pl.col("mpg").is_duplicated())
```

#### 保留非重复项

```{python}
# 根据 mpg 列获取非重复行。
mtcars_pl.filter(pl.col("mpg").is_unique())
```

:::


# 连接

```{sql}
#| connection: con
-- 从新创建的临时表中选择所有数据。
SELECT * FROM mtcars_table_group t1;
```

## 左连接

本节介绍如何执行左连接以组合来自两个表的数据。

::: panel-tabset

# SQL

```{sql}
#| connection: con
-- 在 mtcars_table 和 mtcars_table_group 之间执行左连接。
SELECT t1.model_name, t1.mpg, t1.cyl, t2.* FROM mtcars_table t1
LEFT JOIN mtcars_table_group t2 ON t1.model_name = t2.model_name
LIMIT 5;
```

# R

```{r}
# 对 mtcars 数据框与其自身执行左连接。
mtcars |> left_join(mtcars, by = join_by(model_name == model_name)) |> head(5)
```

# Python pandas

```{python}
# 使用 pandas 执行左连接。
pd.merge(mtcars_pd, mtcars_pd, left_on='model_name', right_on='model_name', how='left').head(5)
```

# Python Polars

```{python}
mtcars_pl.join(mtcars_pl, on="model_name", how="left").head(5)
```

:::

## 内连接

本节演示如何执行内连接。

::: panel-tabset

# SQL

```{sql}
#| connection: con
-- 使用子查询执行内连接。
SELECT t1.model_name, t1.mpg, t1.cyl, t2.* FROM mtcars_table t1
INNER JOIN (SELECT * FROM mtcars_table_group LIMIT 5) t2 ON t1.model_name = t2.model_name;
```

# R

```{r}
# 执行内连接。
mtcars |> inner_join(mtcars, by = join_by(model_name == model_name)) |> head(5)
```

# Python pandas

```{python}
# 使用 pandas 执行内连接。
pd.merge(mtcars_pd, mtcars_pd, left_on='model_name', right_on='model_name', how='inner').head(5)
```

# Python Polars

```{python}
mtcars_pl.join(mtcars_pl, on="model_name", how="inner").head(5)
```

:::

# 追加行

## 追加时不消除重复项 (`union all`)

本节介绍如何组合来自两个表的行，并保留所有重复项。

::: panel-tabset

### SQL

```{sql}
#| connection: con
-- 计算原始表中的行数。
SELECT count(*) FROM mtcars_table;
```

```{sql}
#| connection: con
-- 通过将 mtcars_table 追加到自身（包括重复项）来创建新表。
CREATE TEMP TABLE double_mtcars_table AS
SELECT * FROM mtcars_table
UNION ALL
SELECT * FROM mtcars_table;
```

```{sql}
#| connection: con
-- 计算新表中的行数。
SELECT count(*) FROM double_mtcars_table;
```

### R

```{r}
# 使用 bind_rows 追加行。
mtcars %>% bind_rows(mtcars)
```

### Python pandas

```{python}
# 连接数据框，保留所有行。
pd.concat([mtcars_pd, mtcars_pd], ignore_index=True)
```

:::

## 追加时消除重复项 (`union`)

本节演示如何在组合行时删除重复条目。

::: panel-tabset

### SQL

```{sql}
#| connection: con
-- 创建一个表，删除重复行。
CREATE OR REPLACE TEMP TABLE double_mtcars_table AS
SELECT * FROM mtcars_table
UNION
SELECT * FROM mtcars_table;
```

```{sql}
#| connection: con
-- 计算新表中的行数。
SELECT count(*) FROM double_mtcars_table;
```

```{sql}
#| connection: con
-- 验证不同行的计数。
SELECT count(*) FROM (
  SELECT DISTINCT * FROM double_mtcars_table
);
```

### R

```{r}
# 追加行然后删除重复项。
mtcars %>% bind_rows(mtcars) |> distinct()
```

### Python pandas

```{python}
# 连接数据框然后删除重复项。
pd.concat([mtcars_pd, mtcars_pd], ignore_index=True).drop_duplicates()
```

:::

# 删除行

本节介绍如何根据条件从表中删除行。

::: panel-tabset

## SQL

```{sql}
#| connection: con
-- 从表中删除特定行。
DELETE FROM mtcars_table WHERE model_name = 'Mazda RX4';
```

```{sql}
#| connection: con
-- 验证删除后的行数。
SELECT count(*) FROM mtcars_table;
```

## R

```{r}
# 筛选掉指定的行。
mtcars |> filter(model_name != 'Mazda RX4')
```

## Python pandas

```{python}
# 选择除指定行之外的所有行。
mtcars_pd[mtcars_pd['model_name'] != 'Mazda RX4']
```

## Python Polars

```{python}
mtcars_pl.filter(pl.col("model_name") != 'Mazda RX4')
```

:::

# 更新行

本节演示如何修改表中的现有数据。

::: panel-tabset

## SQL

```{sql}
#| connection: con
-- 选择要更新的行。
SELECT model_name, mpg FROM mtcars_table WHERE model_name = 'Mazda RX4 Wag';
```

```{sql}
#| connection: con
-- 更新特定行的列值。
UPDATE mtcars_table
SET mpg = 999
WHERE model_name = 'Mazda RX4 Wag';
```

```{sql}
#| connection: con
-- 验证更新。
SELECT model_name, mpg FROM mtcars_table WHERE model_name = 'Mazda RX4 Wag';
```

## R

```{r}
# 在筛选的选择中更新值。
mtcars |> filter(model_name == 'Mazda RX4 Wag') |> mutate(mpg = 999)
```

## Python pandas

```{python}
# 使用 .loc 更新特定值。
mtcars_pd.loc[mtcars_pd['model_name'] == 'Mazda RX4 Wag', 'mpg'] = 999
mtcars_pd
```

## Python Polars

```{python}
mtcars_pl.with_columns(
    pl.when(pl.col("model_name") == 'Mazda RX4 Wag')
    .then(999)
    .otherwise(pl.col("mpg"))
    .alias("mpg")
)
```

:::

# 删除表

本节介绍如何从数据库中删除表。

删除表之前：

```{sql}
#| connection: con
-- 在删除一个表之前显示所有表。
SHOW ALL TABLES;
```

删除表：

```{sql}
#| connection: con
-- 如果存在，则删除指定的表。
DROP TABLE IF EXISTS mtcars_table_group;
```

删除表之后：

```{sql}
#| connection: con
-- 再次显示所有表以确认删除。
SHOW ALL TABLES;
```

# PIVOT (透视)

本节演示如何将数据从长格式转换为宽格式。

```{sql}
#| connection: con
-- 为透视演示创建一个临时表。
DROP TABLE IF EXISTS cities;
CREATE TEMP TABLE cities (
    country VARCHAR, name VARCHAR, year INTEGER, population INTEGER
);
INSERT INTO cities VALUES
    ('NL', 'Amsterdam', 2000, 1005),
    ('NL', 'Amsterdam', 2010, 1065),
    ('NL', 'Amsterdam', 2020, 1158),
    ('US', 'Seattle', 2000, 564),
    ('US', 'Seattle', 2010, 608),
    ('US', 'Seattle', 2020, 738),
    ('US', 'New York City', 2000, 8015),
    ('US', 'New York City', 2010, 8175),
    ('US', 'New York City', 2020, 8772);
```

```{sql}
#| connection: con
-- 查看原始数据。
SELECT * FROM cities;
```

## 对一列进行透视

::: panel-tabset

### SQL

```{sql}
#| connection: con
-- 对 'year' 列进行透视。
PIVOT cities
ON year
USING sum(population)
GROUP BY country;
```

### Python pandas

```{python}
cities_pd.pivot_table(index='country', columns='year', values='population', aggfunc='sum').reset_index()
```

### Python Polars

```{python}
cities_pl.pivot(index='country', columns='year', values='population', aggregate_function='sum')
```

:::

## 对两列进行透视

::: panel-tabset

### SQL

```{sql}
#| connection: con
-- 对 'country' 和 'name' 进行透视。
PIVOT cities
ON country, name
USING sum(population);
```

### Python pandas

```{python}
cities_pd.pivot_table(index=['country', 'name'], columns='year', values='population', aggfunc='sum').reset_index()
```

### Python Polars

```{python}
cities_pl.pivot(index=['country', 'name'], columns='year', values='population', aggregate_function='sum')
```

:::

# UNPIVOT (逆透视)

本节介绍如何将数据从宽格式转换为长格式。

::: panel-tabset

### SQL

```{sql}
#| connection: con
-- 对先前透视的表进行逆透视。
UNPIVOT
(
  PIVOT cities
  ON year
  USING sum(population)
  GROUP BY country
)
ON COLUMNS(* EXCLUDE (country))
INTO
    NAME year
    VALUE population;
```

### Python pandas




```{python}
cities_pivot_pd = cities_pd.pivot_table(index='country', columns='year', values='population', aggfunc='sum').reset_index()
cities_pivot_pd.melt(id_vars='country', value_vars=[2000, 2010, 2020], var_name='year', value_name='population')
```

### Python Polars

```{python}
cities_pl = pl.from_pandas(cities_pd)
```


```{python}
cities_pivot_pl = cities_pl.pivot(index='country', columns='year', values='population', aggregate_function='sum')
cities_pivot_pl.unpivot(index='country', on=['2000', '2010', '2020'], variable_name='year', value_name='population')
```

:::

# EXPLAIN (解释)

本节演示如何分析查询的执行计划。

```{sql}
#| eval: false
#| connection: con
-- 显示查询的逻辑执行计划。
EXPLAIN
SELECT t1.model_name, t1.mpg, t1.cyl, t2.* FROM mtcars_table t1
INNER JOIN (SELECT * FROM mtcars_table LIMIT 5) t2 ON t1.model_name = t2.model_name;
```

```{sql}
#| eval: false
#| connection: con
-- 显示详细的执行计划，包括计时信息。
EXPLAIN ANALYZE
SELECT t1.model_name, t1.mpg, t1.cyl, t2.* FROM mtcars_table t1
INNER JOIN (SELECT * FROM mtcars_table LIMIT 5) t2 ON t1.model_name = t2.model_name;
```

```{r}
# 断开与数据库的连接并关闭它。
dbDisconnect(con, shutdown = TRUE)
```

# 显示精美的表格

本节介绍如何创建格式精美的表格以供演示。

::: panel-tabset

## R

```{r}
# 使用 gt 库创建格式化的表格。
library(gt)
mtcars |> gt()
```

## Python pandas

```{python}
# 使用 great_tables 库创建格式化的表格。
from great_tables import GT
GT(mtcars_pd)
```

## Python Polars

```{python}
# 使用 great_tables 库创建格式化的表格。
from great_tables import GT
GT(mtcars_pl)
```




:::

# 参考资料:

- [DuckDB R 客户端文档](https://duckdb.org/docs/stable/clients/r.html)
- [DuckDB SQL 语句](https://duckdb.org/docs/stable/sql/statements)