何时使用R，何时使用SQL？

Question

我有一个中等大小的数据库，其中包含许多连接和查找表。

我比较熟悉R语言而不是SQL，并且我正在使用MySQL。

我的问题：

在什么时候停止增加SQL语句的复杂性，转而使用R中数据子集功能（例如merge、*apply、maply、dlply等）会更加有利。

一方面，SQL的连接比选择每个表的所有内容并使用R中的merge函数将它们连接起来要更容易。此外，在SQL中执行条件选择将减少导入到R中的数据量；但速度差异不明显。

另一方面，带有复杂where条件子句的大型连接变得不如R语法易于理解。

下面是一些用于说明目的的未经测试代码：我在编写工作代码之前就提出了这个问题，回答我的问题不需要工作代码（虽然这总是受欢迎的） - “最优雅的方法”、“最少的行数”或“对X的惊人实现”总是受欢迎的，但我特别感兴趣的是“最合理/实际/规范/基于第一原则”的基础。

我对哪些步骤应该使用SQL的where子句以及哪些步骤可以更容易地使用R进行操作感兴趣。

示例：

数据库描述

有三个表：a、ab和b。表格a和b都有一个主键id。它们具有一个多对多关系，由查找表ab表示，其中包含字段ab.a_id和ab.b_id，它们连接到a.id和b.id，分别。两个表都有一个time字段，而表格a有一个group字段。

目标：

这是我想要进行连接和子集操作的最小示例；

（MySQL中的元素命名，例如a.id相当于R中的a$id）

1. 使用ab连接表格a和b，将与每个a.id相关联的多个b.time值附加为新列；

select a_time, b.time, a.id, b.id from 
       a join ab on a.id = ab.a_id 
       join b on b.id = ab.b_id and then append b.time for distinct values of b.id;

我不需要 b.time 重复的值，只需要一个 b.max 的值：对于每个 a.id 和重复的 b.time 值，b.max 是最接近但不大于 a.time 的 b.time 值。

b.max <- max(b.time[b.time < a.time))

将值dt <- a.time - b.max添加到表格中，例如在R中，

对于每个不同的值a.group，选择 which(min(x.dt)))

x.dt <- a.time - b.max

- David LeBauer

---

“最接近但不大于”的概念在data.table包中听起来像是使用roll=TRUE连接的操作。在SQL中，这种操作可能会非常缓慢，但在R中，语法简短且运行速度非常快。zoo、xts和其他包中也有locf函数可用。 - Matt Dowle

Answer 1

我通常会在SQL中进行数据处理， 直到我想要的数据在单个表中， 然后，我会在R中进行其他操作。 只有当存在性能问题时， 才开始将一些计算移到数据库中进行。 这已经是你正在做的事情了。

涉及时间戳的计算在SQL中往往难以阅读 （类似于ddply的"分析函数" 应该可以简化此过程， 但我认为它们不可用于MySQL）。

然而，你的例子可能完全可以使用以下SQL语句编写（未经测试）。

-- Join the tables and compute the maximum
CREATE VIEW t1 AS
SELECT a.id    AS a_id, 
       a.group AS a_group,
       b.id    AS b_id,
       a.time  AS a_time, 
       a.time - MAX(b.time) AS dt
FROM   a, b, ab
WHERE  a.id = ab.a_id AND b.id = ab.b_id
AND    b.time < a.time
GROUP  BY a.id, a.group, b.id;

-- Extract the desired rows
CREATE VIEW t2 AS 
SELECT t1.*
FROM t1, (SELECT group, MIN(dt) AS min_dt FROM t1) X
WHERE t1.a_id = X.a_id 
AND   t1.b_id = X.b_id 
AND   t1.a_group = X.a.group;

- Vincent Zoonekynd

---

精确地说，在SQL中使用子集，并仅将分析所需的内容带入R。 - Hansi

这是一个不错的想法，但在MySQL中使用视图比在R中使用data.table要慢（即使MySQL服务器比我的桌面电脑快得多）-我在dba.SE上提出了一个相关问题：http://dba.stackexchange.com/questions/16372/when-to-use-views-in-mysql，看起来视图已知速度较慢。尽管如此，速度惩罚大约为一分钟，如果有点烦人，但还是可以容忍的。 - David LeBauer

Answer 2

为什么不使用sqldf包在R中同时使用SQL和R呢？这些示例展示了如何使用sqldf函数来处理R数据框或通过连接到现有数据库。这样，您就可以根据需要灵活选择。

- Alex Reynolds

---

我实际上两种都使用。我有一个函数query，它包装了dbSendQuery()函数，这样我就可以像这样使用：query("select * from a ...")。但问题是，SQL查询应该有多复杂，什么时候应该将表导入R并在R中执行连接、选择和基本算术（min、max、+、-、*、/等）。我的问题是“哪些习惯用法最适合R vs MySQL。” - David LeBauer

好像人们一直在投票支持和反对这个。我想我会留下我的答案，以防对那些还没有完全理解这已经被使用的人有用，并且也许会帮助他们。再加上我喜欢过山车。 Whee！ - Alex Reynolds

+1；虽然在示例中没有展示，但sqldf可以直接连接到数据库；非常有用。谢谢。 - David LeBauer