r - 如何执行 dplyr inner_join col1 > col2

Question

当我不使用标准的“col1”=“col2”连接时，我很难让 dplyr 连接正常工作。这是我所经历的两个例子。

首先:

library(dplyr)

tableA <- data.frame(col1= c("a","b","c","d"),
                     col2 = c(1,2,3,4))

inner_join(tableA, tableA, by = c("col1"!="col1")) %>% 
  select(col1, col2.x) %>% 
  arrange(col1, col2.x)

Error: by must be a (named) character vector, list, or NULL for natural joins (not recommended in production code), not logical

当我复制此代码但使用 sql 时，我得到以下结果:

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

copy_to(con, tableA)

tbl(con, sql("select a.col1, b.col2
              from 
              tableA as a
              inner join 
              tableA as b
              on a.col1 <> b.col1")) %>% 
  arrange(col1, col2)

sql查询结果:

# Source:     SQL [?? x 2]
# Database:   sqlite 3.19.3 [:memory:]
# Ordered by: col1, col2
     col1  col2
     <chr> <dbl>
 1     a     2
 2     a     3
 3     a     4
 4     b     1
 5     b     3
 6     b     4
 7     c     1
 8     c     2
 9     c     4
10     d     1
# ... with more rows

第二部分与最后部分类似:

inner_join(tableA, tableA, by = c("col1" > "col1")) %>% 
   select(col1, col2.x) %>% 
   arrange(col1, col2.x)

Error: by must be a (named) character vector, list, or NULL for natural joins (not recommended in production code), not logical

Sql 等效项:

tbl(con, sql("select a.col1, b.col2
              from tableA as a
              inner join tableA as b
              on a.col1 > b.col1")) %>% 
   arrange(col1, col2)

第二个sql查询的结果:

# Source:     SQL [?? x 2]
# Database:   sqlite 3.19.3 [:memory:]
# Ordered by: col1, col2
   col1  col2
  <chr> <dbl>
1     b     1
2     c     1
3     c     2
4     d     1
5     d     2
6     d     3

有人知道如何使用 dplyr 代码创建这些 sql 示例吗？

Answer 1

对于您的第一个案例:

library(dplyr)
library(tidyr)

expand(tableA, col1, col2) %>% 
  left_join(tableA, by = 'col1') %>% 
  filter(col2.x != col2.y) %>% 
  select(col1, col2 = col2.x)

结果:

# A tibble: 12 x 2
     col1  col2
   <fctr> <dbl>
 1      a     2
 2      a     3
 3      a     4
 4      b     1
 5      b     3
 6      b     4
 7      c     1
 8      c     2
 9      c     4
10      d     1
11      d     2
12      d     3

对于第二种情况:

expand(tableA, col1, col2) %>% 
  left_join(tableA, by = 'col1') %>% 
  filter(col2.x < col2.y) %>% 
  select(col1, col2 = col2.x)

结果:

# A tibble: 6 x 2
    col1  col2
  <fctr> <dbl>
1      b     1
2      c     1
3      c     2
4      d     1
5      d     2
6      d     3