복붙노트

[SQL] dplyr left_join 이하,보다 의해 조건보다

SQL

dplyr left_join 이하,보다 의해 조건보다

이 질문에 다소 문제가 효율적으로 적지 않은 기준에 두 개의 데이터 프레임을 병합 및 날짜가 연구에서 두 날짜 사이 인 경우 검사 관련이있다. 내가 여기에 게시 한 하나의 기능에 해당하는 경우 요청 : GitHub의 문제

내가 dplyr을 사용하여 두 dataframes 가입 :: left_join 찾고 있어요 (). I 가입하도록 사용 조건은,보다 덜보다 큰 즉, <= 및>. dplyr :: left_join ()이 기능을 지원합니까? 또는 키는 그들 사이에 = 연산자를 취할 않습니다. 이것은 SQL에서 실행되도록 간단하다 (I 데이터베이스에서 dataframe을 가정)

다음은 MWE입니다 : 두 번째는 5 년마다 한 번 발생 설문 조사 데이터를 정렬하는 동안 나는 두 개의 데이터 세트 한 회사 년 (fdata)가 있습니다. 따라서이 조사 년 사이에있는 fdata의 모든 년 동안, 나는 해당 설문 조사 년 데이터를 가입 할 수 있습니다.

id <- c(1,1,1,1,
        2,2,2,2,2,2,
        3,3,3,3,3,3,
        5,5,5,5,
        8,8,8,8,
        13,13,13)

fyear <- c(1998,1999,2000,2001,1998,1999,2000,2001,2002,2003,
       1998,1999,2000,2001,2002,2003,1998,1999,2000,2001,
       1998,1999,2000,2001,1998,1999,2000)

byear <- c(1990,1995,2000,2005)
eyear <- c(1995,2000,2005,2010)
val <- c(3,1,5,6)

sdata <- tbl_df(data.frame(byear, eyear, val))

fdata <- tbl_df(data.frame(id, fyear))

test1 <- left_join(fdata, sdata, by = c("fyear" >= "byear","fyear" < "eyear"))

나는 얻다

left_join는 조건을 처리 할 수 ​​있습니다,하지만 내 구문 뭔가를 놓친 경우를 제외?

해결법

  1. ==============================

    1.필터를 사용합니다. (하지만이 대답은 올바른 LEFT를 생성하지 않습니다는 가입,하지만 MWE은 INNER 대신 조인과 올바른 결과를 제공합니다.)

    필터를 사용합니다. (하지만이 대답은 올바른 LEFT를 생성하지 않습니다는 가입,하지만 MWE은 INNER 대신 조인과 올바른 결과를 제공합니다.)

    그래서 다음에,이 목적을 위해 두 테이블에서 더미 변수를 확인에 병합하는 일없이 병합 두 테이블을 요청, 다음 필터는 다음 더미를 삭제하면 dplyr 패키지는 행복하지 않다 :

    fdata %>% 
    mutate(dummy=TRUE) %>%
    left_join(sdata %>% mutate(dummy=TRUE)) %>%
    filter(fyear >= byear, fyear < eyear) %>%
    select(-dummy)

    그리고 주 (예) PostgreSQL의에서이 작업을 수행하는 경우, 쿼리 최적화 프로그램은 다음과 같은 두 가지 쿼리 설명에 의해 입증 더미 변수를 통해 볼 수 있음 :

    > fdata %>% 
+     mutate(dummy=TRUE) %>%
+     left_join(sdata %>% mutate(dummy=TRUE)) %>%
+     filter(fyear >= byear, fyear < eyear) %>%
+     select(-dummy) %>%
+     explain()
Joining by: "dummy"
<SQL>
SELECT "id" AS "id", "fyear" AS "fyear", "byear" AS "byear", "eyear" AS "eyear", "val" AS "val"
FROM (SELECT * FROM (SELECT "id", "fyear", TRUE AS "dummy"
FROM "fdata") AS "zzz136"

LEFT JOIN 

(SELECT "byear", "eyear", "val", TRUE AS "dummy"
FROM "sdata") AS "zzz137"

USING ("dummy")) AS "zzz138"
WHERE "fyear" >= "byear" AND "fyear" < "eyear"


<PLAN>
Nested Loop  (cost=0.00..50886.88 rows=322722 width=40)
  Join Filter: ((fdata.fyear >= sdata.byear) AND (fdata.fyear < sdata.eyear))
  ->  Seq Scan on fdata  (cost=0.00..28.50 rows=1850 width=16)
  ->  Materialize  (cost=0.00..33.55 rows=1570 width=24)
        ->  Seq Scan on sdata  (cost=0.00..25.70 rows=1570 width=24)

    및 SQL 더 명확하게 그 일을 정확히 같은 결과를 제공합니다 :

    > tbl(pg, sql("
+     SELECT *
+     FROM fdata 
+     LEFT JOIN sdata 
+     ON fyear >= byear AND fyear < eyear")) %>%
+     explain()
<SQL>
SELECT "id", "fyear", "byear", "eyear", "val"
FROM (
    SELECT *
    FROM fdata 
    LEFT JOIN sdata 
    ON fyear >= byear AND fyear < eyear) AS "zzz140"


<PLAN>
Nested Loop Left Join  (cost=0.00..50886.88 rows=322722 width=40)
  Join Filter: ((fdata.fyear >= sdata.byear) AND (fdata.fyear < sdata.eyear))
  ->  Seq Scan on fdata  (cost=0.00..28.50 rows=1850 width=16)
  ->  Materialize  (cost=0.00..33.55 rows=1570 width=24)
        ->  Seq Scan on sdata  (cost=0.00..25.70 rows=1570 width=24)
  2. ==============================

    2.data.table가 아닌 동등이 V 1.9.8부터 조인 추가

    data.table가 아닌 동등이 V 1.9.8부터 조인 추가

    library(data.table) #v>=1.9.8
setDT(sdata); setDT(fdata) # converting to data.table in place

fdata[sdata, on = .(fyear >= byear, fyear < eyear), nomatch = 0,
      .(id, x.fyear, byear, eyear, val)]
#    id x.fyear byear eyear val
# 1:  1    1998  1995  2000   1
# 2:  2    1998  1995  2000   1
# 3:  3    1998  1995  2000   1
# 4:  5    1998  1995  2000   1
# 5:  8    1998  1995  2000   1
# 6: 13    1998  1995  2000   1
# 7:  1    1999  1995  2000   1
# 8:  2    1999  1995  2000   1
# 9:  3    1999  1995  2000   1
#10:  5    1999  1995  2000   1
#11:  8    1999  1995  2000   1
#12: 13    1999  1995  2000   1
#13:  1    2000  2000  2005   5
#14:  2    2000  2000  2005   5
#15:  3    2000  2000  2005   5
#16:  5    2000  2000  2005   5
#17:  8    2000  2000  2005   5
#18: 13    2000  2000  2005   5
#19:  1    2001  2000  2005   5
#20:  2    2001  2000  2005   5
#21:  3    2001  2000  2005   5
#22:  5    2001  2000  2005   5
#23:  8    2001  2000  2005   5
#24:  2    2002  2000  2005   5
#25:  3    2002  2000  2005   5
#26:  2    2003  2000  2005   5
#27:  3    2003  2000  2005   5
#    id x.fyear byear eyear val

    당신은 또한 조금 더 많은 노력과 1.9.6에서 foverlaps으로 작업이를 얻을 수 있습니다.

  3. ==============================

    3.이 같은이 모습은 패키지 fuzzyjoin 주소를 해당 작업의 일종이다. dplyr 유사한 패키지의 모양과 작품의 다양한 기능은 기능을 가입 할 수 있습니다.

    이 같은이 모습은 패키지 fuzzyjoin 주소를 해당 작업의 일종이다. dplyr 유사한 패키지의 모양과 작품의 다양한 기능은 기능을 가입 할 수 있습니다.

    퍼지이 경우 하나에서 _ * _ 기능을 당신을 위해 작동합니다 가입 할 수 있습니다. 주요 차이점 dplyr :: left_join 및 fuzzyjoin :: fuzzy_left_join 당신이 match.fun 인수와 일치하는 과정에서 사용하는 기능의 목록을 제공하는 것이. 인수하여주의는 여전히 left_join에서와 같은 기록됩니다.

    다음은 예입니다. I가 일치하는 데 사용되는 함수는> = 및 

    library(fuzzyjoin)

fuzzy_left_join(fdata, sdata, 
             by = c("fyear" = "byear", "fyear" = "eyear"), 
             match_fun = list(`>=`, `<`))

Source: local data frame [27 x 5]

      id fyear byear eyear   val
   (dbl) (dbl) (dbl) (dbl) (dbl)
1      1  1998  1995  2000     1
2      1  1999  1995  2000     1
3      1  2000  2000  2005     5
4      1  2001  2000  2005     5
5      2  1998  1995  2000     1
6      2  1999  1995  2000     1
7      2  2000  2000  2005     5
8      2  2001  2000  2005     5
9      2  2002  2000  2005     5
10     2  2003  2000  2005     5
..   ...   ...   ...   ...   ...
  4. ==============================

    4.하나의 옵션은 열 unnest 다음 목록 컬럼으로 행 방향에 가입하는 것입니다, 그리고 :

    하나의 옵션은 열 unnest 다음 목록 컬럼으로 행 방향에 가입하는 것입니다, 그리고 :

    # evaluate each row individually
fdata %>% rowwise() %>% 
    # insert list column of single row of sdata based on conditions
    mutate(s = list(sdata %>% filter(fyear >= byear, fyear < eyear))) %>% 
    # unnest list column
    tidyr::unnest()

# Source: local data frame [27 x 5]
# 
#       id fyear byear eyear   val
#    (dbl) (dbl) (dbl) (dbl) (dbl)
# 1      1  1998  1995  2000     1
# 2      1  1999  1995  2000     1
# 3      1  2000  2000  2005     5
# 4      1  2001  2000  2005     5
# 5      2  1998  1995  2000     1
# 6      2  1999  1995  2000     1
# 7      2  2000  2000  2005     5
# 8      2  2001  2000  2005     5
# 9      2  2002  2000  2005     5
# 10     2  2003  2000  2005     5
# ..   ...   ...   ...   ...   ...

    5. from https://stackoverflow.com/questions/37289405/dplyr-left-join-by-less-than-greater-than-condition by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 제 3 표에 자신의 FKS 2 개 테이블을 조인하는 데 필요  (0) 2020.03.23
[SQL] 왜 SQL Server는 밀리 초를 잃고있다?  (0) 2020.03.22
[SQL] 오라클 SQL에서 작은 따옴표를 처리하는 방법  (0) 2020.03.22
[SQL] MySQL의 등가 generate_series ()  (0) 2020.03.22
[SQL] MS SQL 서버에서 테이블 예약 된 단어 이름 / 키워드 만들기 [마감]  (0) 2020.03.22
,

티스토리툴바