从多个MySQL表中获取具有合并值的R数据

Question

我有一个MySQL数据库，其中有许多大表，格式如下：

mysql> select * from Table1 limit 2;
+-------+----------+-------------+
| chrom | site     | methylation |
+-------+----------+-------------+
|     1 | 10003581 |          76 |
|     1 | 10003584 |           0 |
+-------+----------+-------------+

我想在R中创建一个大的合并表，它将包含每个表的甲基化值覆盖的所有站点。例如，如果我有4个mysql表，R数据框架将包含以下列：

chrom    site    table1    table2    table3    table4

到目前为止，我已经：

library(RMySQL)

#Open database
mydb = dbConnect(MySQL(), user='root', password='', dbname='DataBase')

#Create function to get values
GetVal <- function(TableName, ColumnName){
  rs = dbSendQuery(mydb, paste("SELECT chrom, site, methylation FROM ", TableName))
  data = fetch(rs, n=-1)
  res <- rename(data, c("chrom" = "Chr", "site" = "start", "methylation" = ColumnName))
  return(res)
}

Table1 <- GetVal("Table1", "Table1")
Table2 <- GetVal("Table2", "Table2")
Table3 <- GetVal("Table3", "Table3")
Table4 <- GetVal("Table4", "Table4")

然后我将所有的表合并在一起。然而，我认为应该有一个更快和更有效的方法来做到这一点。

Answer 1

假设您所处理的表的数量是可变的，这将是更一般的。它还按照原始函数中所希望的方式重命名这些列：

library(RMySQL)

##  Open database:
mydb = dbConnect(MySQL(), user='root', password='', dbname='DataBase')

##  Create function to get values:
GetVals <- function(TableNames) {
    query <- paste0("SELECT ", Tables[1], ".Chr AS chrom, ", Tables[1], ".start AS site, ")
    query <- paste0(query, paste0(Tables, ".methylation AS ", Tables, collapse=", "))
    query <- paste0(query, " FROM ", Tables[1], paste0(" JOIN ", Tables[-1], " ON ", Tables[1], ".Chr=", Tables[-1], ".Chr AND ", Tables[1], ".start=", Tables[-1], ".start", collapse=""))

  rs <- dbSendQuery(mydb, query)
  data <- fetch(rs, n=-1)
  return(data)
}

Tables <- c("Table1", "Table2", "Table3", "Table4")

my_data <- GetVals(Tables)

这是为上面的Tables变量生成的查询：

> query
[1] "SELECT Table1.Chr AS chrom, Table1.start AS site, Table1.methylation AS Table1, Table2.methylation AS Table2, Table3.methylation AS Table3, Table4.methylation AS Table4 FROM Table1 JOIN Table2 ON Table1.Chr=Table2.Chr AND Table1.start=Table2.start JOIN Table3 ON Table1.Chr=Table3.Chr AND Table1.start=Table3.start JOIN Table4 ON Table1.Chr=Table4.Chr AND Table1.start=Table4.start"

Answer 2

尝尝这个

dbSendQuery(mydb, 'insert into chrom_sites
select distinct chrom,site from table1
union
select distinct chrom,site from table2
union
select distinct chrom,site from table3
union
select distinct chrom,site from table4
union
select distinct chrom,site from table5')

x <- dbSendQuery(mydb, 'select chrom,
site,
t1.methylation as table1,
t2.methylation as table2,
t3.methylation as table3,
t4.methylation as table4,
t5.methalation as table5
from chrom_sites as a
join table1 as t1 on a.chrom = t1.chrom and a.site = t1.site
join table2 as t2 on a.chrom = t2.chrom and a.site = t2.site
join table3 as t3 on a.chrom = t3.chrom and a.site = t3.site
join table4 as t4 on a.chrom = t4.chrom and a.site = t4.site
join table5 as t5 on a.chrom = t5.chrom and a.site = t5.site')

这应该做的是在MySQL中创建一个包含chrom和site的唯一值的MySQL表。

之后，它使用它作为起点，然后以所需的方式填充表(数据帧)。

也许有更好的方法来完成第一部分，但我不确定。如果您有很多表，那么编写一个函数来实现这一点可能是有意义的。