r - 使用glue_sql() 并避免粘贴在动态SELECT 语句中的方法?
问题描述
我正在学习如何从 R 中查询 SQLite dbs,并使用glue_sql()
. 下面是我的工作流程中子查询的简化示例。有没有一种方法可以创建s10_wtX
而不s20_wtX
使用paste0()
,如下面的代码所示?
library(DBI)
library(dplyr)
library(glue)
# example database
set.seed(1)
ps <- data.frame(plot = rep(1:3, each = 4),
spp = rep(1:3*10, 2),
wtX = rnorm(12, 10, 2) %>% round(1))
con <- dbConnect(RSQLite::SQLite(), "")
dbWriteTable(con, "ps", ps)
# species of interest
our_spp <- c(10, 20)
# for the spp of interest, sum wtX on each plot
sq <- glue_sql(paste0(
'SELECT ps.plot,\n',
paste0('SUM(CASE WHEN ps.spp = ', our_spp,
' THEN (ps.wtX) END) AS s', our_spp,
'_wtX',
collapse = ',\n'), '\n',
' FROM ps
WHERE ps.spp IN ({our_spp*}) -- spp in our sample
GROUP BY ps.plot'),
.con = con)
# the result of the query should look like:
dbGetQuery(con, sq)
plot s10_wtX s20_wtX
1 1 21.9 10.4
2 2 11.0 22.2
3 3 9.4 13.0
在我的实际工作流程中,我有两种以上的兴趣,所以我宁愿不完整地写出每一行(例如,SUM(CASE WHEN ps.spp = 10 THEN (ps.wtX) END) AS s10_wtX
)。
解决方案
为了稍微正式化一点(即使它不是您最终使用的),这里是我的评论,详细:
out <- DBI::dbGetQuery(con, "
select ps.plot, ps.spp, sum(ps.wtX) as wtX
from ps
where ps.spp in (10,20)
group by ps.plot, ps.spp")
out
# plot spp wtX
# 1 1 10 21.9
# 2 1 20 10.4
# 3 2 10 11.0
# 4 2 20 22.2
# 5 3 10 9.4
# 6 3 20 13.0
这可以很容易地根据您的需要进行调整。tidyr::pivot_wider
例如,使用,
tidyr::pivot_wider(out, plot, names_from="spp", values_from="wtX")
# # A tibble: 3 x 3
# plot `10` `20`
# <int> <dbl> <dbl>
# 1 1 21.9 10.4
# 2 2 11 22.2
# 3 3 9.4 13
(名称将需要清理。)
推荐阅读
- php - PHP Laravel DocuSign 嵌入式签名:输入字符串的格式不正确
- c# - 向目标游戏对象移动和旋转相机
- sonarqube - 适用于 TIBCO BusinessWorks5 的 SonarQube 插件
- arrays - 如何在只有某些条件需要为 TRUE 的数组公式中包含 OR - Excel 第 3 部分
- java - OneToOne JPA 不保存 null
- react-native - 在 React Native 中的条件下禁用动画
- reactjs - 使用 create-react-app 调试 jest 单元测试不会在 VSCode 中达到断点
- asp.net - ASP.NET - 如何使用来自另一个模型的数据加载模型,该模型通过 2 个外键删除了两次
- javascript - 为什么输入返回空?
- mysql - 错误 MariaDB 服务器版本的原因是什么,以便在附近使用正确的语法