mysql - RMySQL - 从列表中存在值的数据库中选择数据
问题描述
我正在尝试仅在名称存在于已存在的数据框中的数据库中提取数据。我不确定如何将列表包含在我的选择语句中。
奖励:我需要将所有数据重新格式化为更友好的格式。
library(RMySQL)
library(DBI)
db_con <- dbconnect(RMySQL::MySQL(),
username: "user",
password: "1234",
dbname = "test")
df <- as.data.frame(tbl(db_con, sql("SELECT name, loc, type, value FROM data"))
这会读入整个数据表。我需要根据名称字段限制输入的内容。
df$names <- "cat, hamster"
name_filter <- df$names
当前df:
# name loc type val
1 cat ab12 a 1
2 cat 1233 a 0
3 dog ab12 a 0
4 dog 45ff b 1
5 hamster ab12 a 1
6 hamster 45ff b 0
不需要读取整个表的所需输出:SELECT * WHERE name IN name_filter?
# loc type cat hamster
1 ab12 a 1 1
2 1233 a 0 0
3 45ff b 0 0
编辑:尝试在数据上使用 library(reshape) 并不顺利。不过,我可能做错了......我目前正在提取一个包含大约 4500 万行的截断数据库。(完整的 db 大约是 3 亿。)
cast(df, loc+type ~ name, value = "val")
这会导致很长的停顿,然后是“聚合需要 fun.aggregate:长度用作默认值”错误,然后是似乎永无止境的停顿。
EDIT2:名称列表有几百个项目,每次请求时都不同。每个名称有数万个值。
EDIT3:我在另一个线程中发现了这个,用于尝试基于值列表生成选择语句。我不确定它是否有用,因为我也无法让它工作。
list <- list$Name
sel <- "SELECT name, loc, type, val FROM data WHERE name IN (%s)"
sql1 <- sprintf(sel,paste(list, collapse =","))
yields:
"SELECT name, loc, type, val FROM data WHERE name IN (dog,cat,hamster,square,triangle,circle,ball,horse,lion)
EDIT4:尝试使用以下内容,但收到有关 MariaDB 语法的错误。MySQL 和 MariaDB 之间的 select 语句有区别吗?
list <- df$Name
sqlqry <- "SELECT data.loc, data.type"
for (i in 1:length(list)) {
sqlqry <- paste0(sqlqry, sprintf(", MAX(CASE WHEN data.name = '%s'
THEN 1 ELSE 0 END) AS %s",list[i], list[i]))
}
sqlqry <- paste0(sqlqry, "FROM
data
GROUP BY
data.loc
, data.type")
test <- dbSendQuery(db_con, sqlqry)
解决方案
此查询将为您提供所需的结果。
SELECT
data.loc
, data.type
, MAX(CASE WHEN data.name = 'cat' THEN 1 ELSE 0 END) AS cat
, MAX(CASE WHEN data.name = 'hamster' THEN 1 ELSE 0 END) AS hamster
FROM
data
GROUP BY
data.loc
, data.type
为了避免扫描整个表,您将需要(覆盖)索引。
该索引应该使上述查询的选择速度更快。
ALTER TABLE data ADD INDEX <index_name> (loc, type, name)
, ALGORITHM=INPLACE
, LOCK=NONE
, ALGORITHM=INPLACE, LOCK=NONE
仅适用于 InnoDB 引擎。它是InnoDB 的在线 DLL 的一部分,可以使表没有停机时间。
推荐阅读
- swift - CoreData:NSFetchRequestResultType.dictionaryResultType 和 managedObjectResultType 之间的不同结果
- ios - 在 iOS13 的场景中添加新的 UIWindow
- json - 如何在颤振本地化中读取嵌套的 json?
- javascript - 密码的正则表达式,至少包含 1 个大小写字母、1 个数字和 1 个特殊字符,最小长度为 8
- angular - 如何将三元运算符结果的字符串放入角度
- json - 从 MongoDB 读取数据并在 Scala Spark 连接器中对多级 JSON 应用过滤器
- selenium - 发送密钥不起作用,正确的 xpath 和 id (Selenium)
- php - 尝试对部署到 Google Script 的脚本进行 API 调用时出现 403 错误
- ssl - GCP 管理的 SSL 证书卡在 FAILED_RATE_LIMITED?
- python - KeyError:使用 Trepan3k 时出现“深蓝色”