--- 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" --- # 设置与配置 ```{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. = cbind (model_name = rownames (mtcars), mtcars) |> head (10 )# Create the iris dataframe from the base R dataset. = 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. <- 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 pdimport polars as plimport osfrom platform import python_version#print(python_version()) = 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. = r.mtcars= r.iris= pl.from_pandas(mtcars_pd)= pl.from_pandas(iris_pd)= pl.from_pandas(cities_pd)``` - DuckDB 数据库 `my-db.duckdb` 中有 `iris_table` 和 `mtcars_table` 表。- R 环境中有 `iris` 和 `mtcars` 数据框。- Python 环境中有 `iris` 和 `mtcars` 数据框。# 显示所有表 ## SQL ```{sql} #| connection: con -- 显示所连接数据库中的所有表。 SHOW ALL TABLES ;``` ## R ```{r} # 列出当前 R 环境中的所有数据框。 <- Filter (is.data.frame, as.list (.GlobalEnv))names (dflist)``` ## Python pandas ```{python} # 列出当前 Python 环境中的所有 pandas 数据框。 import pandas as pd= [var for var in dir () if isinstance (eval (var), pd.core.frame.DataFrame)]print (alldfs)``` ## Python Polars ```{python} import polars as pl= [name for name, val in globals ().items() if isinstance (val, pl.DataFrame)]print (alldfs)``` # 描述表 ## SQL ```{sql} #| connection: con -- 描述 mtcars_table 的列和数据类型。 DESCRIBE mtcars_table;``` ## R ```{r} # 提供 mtcars 数据框的详细摘要。 :: skim (mtcars)``` ## Python pandas ```{python} # 生成 mtcars 数据框的描述性统计信息。 = 'all' )``` ## Python Polars ```{python} ``` # 显示列名 ## SQL ```{sql} #| connection: con -- 显示 mtcars_table 中列的信息。 ``` ## R ```{r} # 获取 mtcars 数据框的列名。 names (mtcars)``` ## Python pandas ```{python} # 从 mtcars 数据框获取列名列表。 import pandas as pdlist (mtcars_pd.columns.values)``` ## Python Polars ```{python} # 从 mtcars 数据框获取列名列表。 ``` # 选择 ## 选择前 6 行并重命名 ## 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 数据框中的列。 = {'model_name' : 'model' })``` ## Python Polars ```{python} "model_name" ).alias("model" ), pl.col("mpg" ), pl.col("cyl" )).head(6 )``` ## 选择不同行 ## SQL ```{sql} #| connection: con -- 选择 mpg 和 cyl 的不同组合。 SELECT DISTINCT mpg, cyl FROM mtcars_table;``` ## R ```{r} # 根据 mpg 和 cyl 获取不同的行。 |> distinct (mpg, cyl)``` ## Python pandas ```{python} # 选择特定列并删除重复行。 = mtcars_pd[["mpg" , "cyl" ]]print (df.drop_duplicates())``` ## Python Polars ```{python} "mpg" ), pl.col("cyl" )).unique()``` # 检查行数和列数 ## 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} # 获取行数。 0 ]``` ```{python} # 获取列数。 1 ]``` ## Python Polars ```{python} # 获取行数。 0 ]``` ```{python} # 获取列数。 1 ]``` # 创建列 ## SQL ```{sql} #| connection: con -- 通过对现有列执行计算来创建新列。 SELECT * , mpg + 1 AS new_mpg FROM mtcars_table;``` ## R ```{r} # 向数据框添加新列。 |> mutate (new_mpg = mpg + 1 )``` ## Python pandas ```{python} # 在数据框中创建新列。 "new_mpg" ] = mtcars_pd["mpg" ] + 1 ``` ## Python Polars ```{python} "mpg" ) + 1 ).alias("new_mpg" ))``` # 筛选 ## 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} # 使用 & 运算符进行“与”筛选。 |> filter (mpg == 21 & cyl == 6 )``` ```{r} # 使用 | 运算符进行“或”筛选。 |> filter (mpg == 21 | cyl == 6 )``` ## Python pandas ```{python} # 使用 query 方法进行“与”筛选。 'mpg == 21 and cyl == 6' )``` ```{python} # 使用 query 方法进行“或”筛选。 'mpg == 21 or cyl == 6' )``` ## Python Polars ```{python} # polars 的“与”筛选 filter ((pl.col("mpg" ) == 21 ) & (pl.col("cyl" ) == 6 ))``` ```{python} # polars 的“或”筛选 filter ((pl.col("mpg" ) == 21 ) | (pl.col("cyl" ) == 6 ))``` # 排序 ## 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 降序排列数据框。 |> select (model = model_name, mpg, cyl) |> arrange (desc (mpg)) |> head (3 )``` ## Python pandas ```{python} # 按 mpg 降序对数据框进行排序。 "model_name" , "mpg" , "cyl" ]].sort_values(by= 'mpg' , ascending= False ).head(3 )``` ## Python Polars ```{python} "model_name" , "mpg" , "cyl" ).sort("mpg" , descending= True ).head(3 )``` # 分组 ## SQL ```{sql} #| connection: con -- 按 model_name 分组，并计算 mpg 的总和和 cyl 的平均值。 SELECT model_name, sum (mpg) AS total_mpg, mean(cyl) AS cyl_mean FROM mtcars_tableGROUP BY 1 LIMIT 5 ;``` ## R ```{r} # 按 model_name 分组并汇总数据。 |> group_by (model_name) |> summarise (total_mpg = sum (mpg), cyl_mean = mean (cyl)) |> head (5 )``` ## Python pandas ```{python} # 按 model_name 分组并聚合数据。 'model_name' ).agg({'mpg' : 'sum' , 'cyl' : 'mean' }).head(5 )``` ## Python Polars ```{python} "model_name" ).agg([sum ("mpg" ).alias("total_mpg" ),"cyl" ).alias("cyl_mean" )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_tableGROUP 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_tableGROUP BY 1 ;``` # 唯一值 ## 检查唯一值 ### 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 的数量。 "model_name" ).n_unique())``` ## 获取重复和非重复数据 ### 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 列获取重复行。 |> filter (duplicated (mpg) | duplicated (mpg, fromLast = TRUE ))``` #### 保留非重复项 ```{r} # 根据 mpg 列获取非重复行。 |> filter (! duplicated (mpg) & ! duplicated (mpg, fromLast = TRUE ))``` ### Python pandas #### 显示所有重复项 ```{python} # 根据 mpg 列获取重复行。 = ['mpg' ], keep= False )]``` #### 保留非重复项 ```{python} # 根据 mpg 列获取非重复行。 ~ mtcars_pd.duplicated(subset= ['mpg' ], keep= False )]``` ### Python Polars ```{python} # 根据 mpg 列获取重复行。 filter (pl.col("mpg" ).is_duplicated())``` #### 保留非重复项 ```{python} # 根据 mpg 列获取非重复行。 filter (pl.col("mpg" ).is_unique())``` # 连接 ```{sql} #| connection: con -- 从新创建的临时表中选择所有数据。 SELECT * FROM mtcars_table_group t1;``` ## 左连接 # SQL ```{sql} #| connection: con -- 在 mtcars_table 和 mtcars_table_group 之间执行左连接。 SELECT t1. model_name, t1. mpg, t1. cyl, t2. * FROM mtcars_table t1LEFT JOIN mtcars_table_group t2 ON t1. model_name = t2. model_nameLIMIT 5 ;``` # R ```{r} # 对 mtcars 数据框与其自身执行左连接。 |> left_join (mtcars, by = join_by (model_name == model_name)) |> head (5 )``` # Python pandas ```{python} # 使用 pandas 执行左连接。 = 'model_name' , right_on= 'model_name' , how= 'left' ).head(5 )``` # Python Polars ```{python} = "model_name" , how= "left" ).head(5 )``` ## 内连接 # SQL ```{sql} #| connection: con -- 使用子查询执行内连接。 SELECT t1. model_name, t1. mpg, t1. cyl, t2. * FROM mtcars_table t1INNER JOIN (SELECT * FROM mtcars_table_group LIMIT 5 ) t2 ON t1. model_name = t2. model_name;``` # R ```{r} # 执行内连接。 |> inner_join (mtcars, by = join_by (model_name == model_name)) |> head (5 )``` # Python pandas ```{python} # 使用 pandas 执行内连接。 = 'model_name' , right_on= 'model_name' , how= 'inner' ).head(5 )``` # Python Polars ```{python} = "model_name" , how= "inner" ).head(5 )``` # 追加行 ## 追加时不消除重复项 (`union all`) ### SQL ```{sql} #| connection: con -- 计算原始表中的行数。 SELECT count (* ) FROM mtcars_table;``` ```{sql} #| connection: con -- 通过将 mtcars_table 追加到自身（包括重复项）来创建新表。 CREATE TEMP TABLE double_mtcars_table AS SELECT * FROM mtcars_tableUNION ALL SELECT * FROM mtcars_table;``` ```{sql} #| connection: con -- 计算新表中的行数。 SELECT count (* ) FROM double_mtcars_table;``` ### R ```{r} # 使用 bind_rows 追加行。 %>% bind_rows (mtcars)``` ### Python pandas ```{python} # 连接数据框，保留所有行。 = True )``` ## 追加时消除重复项 (`union`) ### SQL ```{sql} #| connection: con -- 创建一个表，删除重复行。 CREATE OR REPLACE TEMP TABLE double_mtcars_table AS SELECT * FROM mtcars_tableUNION 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} # 追加行然后删除重复项。 %>% bind_rows (mtcars) |> distinct ()``` ### Python pandas ```{python} # 连接数据框然后删除重复项。 = True ).drop_duplicates()``` # 删除行 ## SQL ```{sql} #| connection: con -- 从表中删除特定行。 DELETE FROM mtcars_table WHERE model_name = 'Mazda RX4' ;``` ```{sql} #| connection: con -- 验证删除后的行数。 SELECT count (* ) FROM mtcars_table;``` ## R ```{r} # 筛选掉指定的行。 |> filter (model_name != 'Mazda RX4' )``` ## Python pandas ```{python} # 选择除指定行之外的所有行。 'model_name' ] != 'Mazda RX4' ]``` ## Python Polars ```{python} filter (pl.col("model_name" ) != 'Mazda RX4' )``` # 更新行 ## SQL ```{sql} #| connection: con -- 选择要更新的行。 SELECT model_name, mpg FROM mtcars_table WHERE model_name = 'Mazda RX4 Wag' ;``` ```{sql} #| connection: con -- 更新特定行的列值。 UPDATE mtcars_tableSET 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} # 在筛选的选择中更新值。 |> filter (model_name == 'Mazda RX4 Wag' ) |> mutate (mpg = 999 )``` ## Python pandas ```{python} # 使用 .loc 更新特定值。 'model_name' ] == 'Mazda RX4 Wag' , 'mpg' ] = 999 ``` ## Python Polars ```{python} "model_name" ) == 'Mazda RX4 Wag' )999 )"mpg" ))"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 (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;``` ## 对一列进行透视 ### SQL ```{sql} #| connection: con -- 对 'year' 列进行透视。 ON year USING sum (population)GROUP BY country;``` ### Python pandas ```{python} = 'country' , columns= 'year' , values= 'population' , aggfunc= 'sum' ).reset_index()``` ### Python Polars ```{python} = 'country' , columns= 'year' , values= 'population' , aggregate_function= 'sum' )``` ## 对两列进行透视 ### SQL ```{sql} #| connection: con -- 对 'country' 和 'name' 进行透视。 ON country, nameUSING sum (population);``` ### Python pandas ```{python} = ['country' , 'name' ], columns= 'year' , values= 'population' , aggfunc= 'sum' ).reset_index()``` ### Python Polars ```{python} = ['country' , 'name' ], columns= 'year' , values= 'population' , aggregate_function= 'sum' )``` # UNPIVOT (逆透视) ### SQL ```{sql} #| connection: con -- 对先前透视的表进行逆透视。 ON year USING sum (population)GROUP BY countryON COLUMNS (* EXCLUDE (country))INTO year ``` ### Python pandas ```{python} = cities_pd.pivot_table(index= 'country' , columns= 'year' , values= 'population' , aggfunc= 'sum' ).reset_index()= 'country' , value_vars= [2000 , 2010 , 2020 ], var_name= 'year' , value_name= 'population' )``` ### Python Polars ```{python} = pl.from_pandas(cities_pd)``` ```{python} = cities_pl.pivot(index= 'country' , columns= 'year' , values= 'population' , aggregate_function= 'sum' )= '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 t1INNER 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 t1INNER JOIN (SELECT * FROM mtcars_table LIMIT 5 ) t2 ON t1. model_name = t2. model_name;``` ```{r} # 断开与数据库的连接并关闭它。 dbDisconnect (con, shutdown = TRUE )``` # 显示精美的表格 ## R ```{r} # 使用 gt 库创建格式化的表格。 library (gt)|> gt ()``` ## Python pandas ```{python} # 使用 great_tables 库创建格式化的表格。 from great_tables import GT``` ## Python Polars ```{python} # 使用 great_tables 库创建格式化的表格。 from great_tables import GT``` # 参考资料: - [ DuckDB R 客户端文档 ](https://duckdb.org/docs/stable/clients/r.html) - [ DuckDB SQL 语句 ](https://duckdb.org/docs/stable/sql/statements)