使用merge()和sqldf获得相同的交叉联接：：sqldf()

Question

我有两个数据帧：Sales和Clients。我希望使用sqldf::sqldf()和merge()对这些数据帧执行交叉连接，并获得与这两种方法完全相同的结果。

到目前为止，我只能获得两个不同排序行的数据帧。

这是生成Sales和Clients数据帧的代码：

set.seed(1)

Sales <- data.frame(
  Product = sample(c("Toaster", "Radio", "TV"), size = 7, replace = TRUE),
  CustomerID = c(rep("1_2019", 2), paste(2:3, "2019", sep = "_"), paste(1:3, "2020", sep = "_"))
  )

Sales$Price <- round(ifelse(Sales$Product == "TV", rnorm(1, 400, 20),
                            ifelse(Sales$Product == "Toaster", rnorm(1, 40, 2), 
                                   rnorm(1, 35, 2))))

Clients <- data.frame(
  CustomerID = c(paste(2:4, "2019", sep = "_"), paste(1:2, "2020", sep = "_")),
  State = sample(c("CA", "AZ", "IL", "MA"), size = 5, replace = TRUE)
  )

这就是我得到的：

library(sqldf)

# cross join with base R
out1 <- merge(x = Sales, y = Clients, by = NULL)

# cross join with sqldf      
out2 <- sqldf("SELECT *
               FROM Sales
               CROSS JOIN Clients")

out1和out2有不同的行顺序。如何调整sqldf()调用以使out1和out2完全相同？

这是我最接近的：

merge(x = Sales, y = Clients, by = NULL)  

sqldf("SELECT *
       FROM Sales
       CROSS JOIN Clients 
       ORDER BY State DESC, Clients.CustomerID")

Answer 1

我认为在ORDER BY中包括sqldf是很重要的，因为它证明了这样一个事实:在sqldf中，除非明确指示，否则排序永远不会得到保证。

如果您只是对两个变量进行简单的ORDER BY，那么在R中向order的转换将是直接的。但是，由于一个变量在减少，另一个变量在增加，因此order本身并不处理这个问题。然而，正如https://stackoverflow.com/a/3316719所建议的那样，我们也可以对xtfrm做同样的事情。

out1 <- merge(x = Sales, y = Clients, by = NULL)
out1 <- out1[order(-xtfrm(out1$State), out1$CustomerID.y),]

out2 <- sqldf::sqldf(
  "SELECT *
   FROM Sales
   CROSS JOIN Clients 
   ORDER BY State DESC, Clients.CustomerID")

### proof they are identical
all(unlist(Map(`==`, out1, out2)))
# [1] TRUE

这里的xtfrm助手函数允许我们为了排序的目的否定列的“值”。来自?xtfrm

是一个通用的辅助函数，它生成一个数字向量，它将按照'x‘的相同顺序排序。

如果字段已经是数字字段，我们只需要执行order(-State, CustomerID.y)，但是它是character这一事实还需要进一步的步骤。Argo xtfrm.

编辑：在注释中，确定OP希望模仿SQL语句中merge的排序顺序。不幸的是，因为这是两个框架的笛卡儿积，所以不应用排序：merge只是针对第二帧的第一行对第一帧的所有行进行cbind，然后与第二帧的每一行重复。

这可以通过使用merge中的一些代码来演示。

nx <- nrow(x) # Sales
ny <- nrow(y) # Clients
expand.grid(seq_len(nx), seq_len(ny))
#    Var1 Var2
# 1     1    1
# 2     2    1
# 3     3    1
# 4     4    1
# 5     5    1
# 6     1    2
# ...
# 33    3    7
# 34    4    7
# 35    5    7

其中每个数字是来自各自帧的一行(x表示Var1，y表示Var2)。如果原始数据是：

## Sales                        ## Clients        
  Product CustomerID Price        CustomerID State
1 Toaster     1_2019    37      1     2_2019    AZ
2   Radio     1_2019    33      2     3_2019    MA
3   Radio     2_2019    33      3     4_2019    AZ
4      TV     3_2019   408      4     1_2020    IL
5 Toaster     1_2020    37      5     2_2020    MA
6      TV     2_2020   408
7      TV     3_2020   408

然后这会导致

out1
#    Product CustomerID.x Price CustomerID.y State
# 1  Toaster       1_2019    37       2_2019    AZ
# 2    Radio       1_2019    33       2_2019    AZ
# 3    Radio       2_2019    33       2_2019    AZ
# 4       TV       3_2019   408       2_2019    AZ
# 5  Toaster       1_2020    37       2_2019    AZ
# 6       TV       2_2020   408       2_2019    AZ
# 7       TV       3_2020   408       2_2019    AZ
# 8  Toaster       1_2019    37       3_2019    MA
# ...
# 33 Toaster       1_2020    37       2_2020    MA
# 34      TV       2_2020   408       2_2020    MA
# 35      TV       3_2020   408       2_2020    MA

这将极大地破坏x (Sales)中的任何排序，即使y (Clients)是预先排序的(它确实是这样做的)。

因为有了这个，如果您想要R和SQL交叉连接解决方案之间的一致性，我建议最透明/最清晰的方法是在R中使用merge，然后以类似于SQL的方式应用merge后排序。事实上，从教育学的角度问这个问题：“什么秩序对人类来说是有意义的？”如果您在课程计划中断言，在明确地对流程进行强武装(通过dplyr::arrange、x[order(...),]或SQL的ORDER BY子句)之前，排序可能无法得到保证。查找数据的直观顺序，然后在R和SQL中演示这一点。

边注：

1. 您的sqldf查询会产生同名列，如果您开始使用列，则会产生一些错误。不幸的是，用field-naming.
2. Lexicographic对您的数据进行排序可以减轻这一点--不幸的是，在客户id结束时的年份表示(是的，我正在推断)客户入职时间线，但它们将按照领先的数字进行排序。类似于"2020-05-04"如何正确排序，即使是一个字符串，而"05/04/2020"没有，它可能支持更直观的排序，使最重要的部分是id字符串的前导部分。或者把它们变成整数。或者UUID(当然是v4)，这些都很有趣。