mysql - RMySQL - 从列表中存在值的数据库中选择数据

Question

我试图仅在名称存在于已存在的数据框中的数据库中提取数据。我不确定如何将该列表包含到我的选择语句中。

奖励:我需要将所有数据重新格式化为更友好的格式。

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)

Answer 1

此查询将为您提供所需的结果。

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's online DLL 的一部分这可以使 table 没有停机时间。