[데이터 추출] 결측치 대체, 카테시안 곱(Cartesian Product) - Full Join, Any 연산자

포스팅 목차

113. Display employee name, Sal, comm and net pay for those employees whose net pay are greater than or equal to any other employee salary of the company?

* 수수료(commission)를 포함한 직원의 급여가 다른 직원의 일반 급여보다 크거나 같은 직원의 정보를 출력하시오.

56번 참조(상관(상호연관)서브쿼리 - Correlated Subquery / co-related subquery), 61/62번(Any / All)
Cross Join 생성(dummy) : 97번
fuzzyjoin::fuzzy_left_join : 116번
유사문제 : 116번
특정 조건을 만족하는 데이터 추출 - 결측치 대체, 카테시안 곱(Cartesian Product) - Full Join, Any 연산자
데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql

Oracle : nvl(), any(), min()
파이썬 Pandas : pd.merge(), assign() - Full Join, query(), Min()
R 프로그래밍 : merge(), subset(), by= character() - Full Join, aggregate(), ifelse() 조건절, is.na()
R Dplyr Package : dplyr::inner_join(), dplyr::full_join(), dplyr::summarise(), min(), ifelse() 조건절, is.na()
R sqldf Package : ifnull(), min(), EXISTS() 연산자
Python pandasql Package : ifnull(), min(), EXISTS() 연산자
R data.table Package : DT Syntax 데이터 결합(allow.cartesian), data.table::merge() syntax 데이터 결합, fuzzyjoin::fuzzy_left_join, fcoalesce(), min()
SAS Proc SQL : COALESCE(), any(), min(), EXISTS() 연산자
SAS Data Step : CROSS JOIN (카테시안 곱) 구현, PROC SUMMARY의 Min, Merge 구문, IF 조건문, COALESCE()
Python Dfply Package : @pipe & def 사용자 정의 함수(inner_join_merge), full_join(), summarize(), min(), make_symbolic(), np.where()
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

수수료(commission)를 포함한 직원의 급여가 (본인을 제외한) 한 사람 이상의 직원의 급여보다 크거나 같은 직원의 정보를 출력하시오. ANY 연산자를 사용하여서 서브쿼리에서 반환되는 값과 비교하여서 하나 이상을 만족하는 경우에 해당 결과를 반환한다.

Oracle Programming

select ename, sal, comm, sal+nvl(comm,0) netPay 
from   emp e
where  sal+nvl(comm,0) >= any(select sal from emp where empno!=e.empno);

수수료(commission)를 포함한 직원의 급여가 (본인을 제외한) 한 사람 이상의 직원의 급여보다 크거나 같은 직원의 정보를 출력하시오. 본인을 제외한 다른 직원들의 최소 급여보다 큰 경우에 해당 결과를 반환한다.

Oracle Programming

select ename, sal, comm, sal+nvl(comm,0) netPay 
from   emp e
where  sal+nvl(comm,0) >= (select min(sal) from emp where empno!=e.empno);

2. Python Pandas(파이썬)

emp 테이블을 self join 형식으로 카테시안 곱(Cartesian Product)을 수행한 후 오른쪽 emp테이블에서 본인의 사원번호를 제외한 다른 사원들의 급여 중 최소 급여를 검색하고, emp 테이블에 다시 이 최소 급여를 Inner join으로 결합하여서 사원의 급여가 다른 직원들의 최소 급여보다 큰 경우에 해당 결과를 반환한다.

Python Programming

pd.merge( emp, 
          ( pd.merge(emp.assign(foo=1), emp.assign(foo=1), on ='foo').query('empno_x != empno_y').groupby('empno_x')['sal_y'].min().reset_index() ), 
          how='inner', 
          left_on=['empno'], right_on=['empno_x']).query('sal>sal_y').head(7)

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	empno_x	sal_y
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	7499	800
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	7521	800
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20	7566	800
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	7654	800
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30	7698	800
6	7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10	7782	800
7	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20	7788	800

3. R Programming (R Package)

emp 테이블을 self join 형식으로 카테시안 곱(Cartesian Product)을 수행한 후 왼쪽 emp테이블의 사원번호를 기준으로 오른쪽 emp테이블에서 본인의 사원번호를 제외한 다른 사원들의 급여 중 최소 급여를 산출하고, emp 테이블에 다시 이 최소 급여를 left join으로 결합하여서 수수료를 포함한 사원 급여가 다른 직원들의 최소 급여보다 큰 경우에 해당 결과를 반환한다.

R Programming

%%R

subset(merge( emp,
              aggregate(sal.y~empno.x,FUN=min,data= ( subset( merge(emp, emp,by= character(),all.x=TRUE, all.y=TRUE),
                                                              empno.x != empno.y ) )),
              by.x=c("empno"),
              by.y=c("empno.x"),all.x=T),
       (sal + ifelse(is.na(comm),0,comm)) >= sal.y )

Results

   empno  ename       job  mgr   hiredate  sal comm deptno sal.y
2   7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300     30   800
3   7521   WARD  SALESMAN 7698 1981-02-22 1250  500     30   800
4   7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20   800
5   7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400     30   800
6   7698  BLAKE   MANAGER 7839 1981-03-01 2850   NA     30   800
7   7782  CLARK   MANAGER 7839 1981-01-09 2450   NA     10   800
8   7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20   800
9   7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10   800
10  7844 TURNER  SALESMAN 7698 1981-09-08 1500    0     30   800
11  7876  ADAMS     CLERK 7788 1983-01-12 1100   NA     20   800
12  7900  JAMES     CLERK 7698 1981-12-03  950   NA     30   800
13  7902   FORD   ANALYST 7566 1981-12-03 3000   NA     20   800
14  7934 MILLER     CLERK 7782 1982-01-23 1300   NA     10   800

4. R Dplyr Package

full_join에 옵션을 지정하여서 2개의 emp 테이블 사이에 카테시안 곱(Cartesian Product)을 수행한 후 왼쪽 emp테이블의 사원번호를 기준으로 오른쪽 emp테이블에서 본인의 사원번호를 제외한 다른 사원들의 급여 중 최소 급여를 산출하고, emp 테이블에 다시 이 최소 급여를 inner_join으로 결합하여서 수수료를 포함한 사원 급여가 다른 직원들의 최소 급여보다 큰 경우에 해당 결과를 반환한다.

Cross Join 생성(dummy) : 97번

R Programming

%%R

emp %>%
  dplyr::inner_join( ( emp %>%
                         dplyr::full_join(emp, by = character() ) %>%
                         dplyr::filter(empno.x != empno.y) %>%
                         dplyr::group_by(empno.x) %>%
                         dplyr::summarise(sal_min = min(sal.y)) ),
                      by = c('empno' = 'empno.x')
        ) %>%
  dplyr::filter( (sal + ifelse(is.na(comm),0,comm)) >= sal_min) %>%
  head(7)

Results

`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 7 x 9
  empno ename  job        mgr hiredate     sal  comm deptno sal_min
  <dbl> <chr>  <chr>    <dbl> <date>     <dbl> <dbl>  <dbl>   <dbl>
1  7499 ALLEN  SALESMAN  7698 1981-02-20  1600   300     30     800
2  7521 WARD   SALESMAN  7698 1981-02-22  1250   500     30     800
3  7566 JONES  MANAGER   7839 1981-04-02  2975    NA     20     800
4  7654 MARTIN SALESMAN  7698 1981-09-28  1250  1400     30     800
5  7698 BLAKE  MANAGER   7839 1981-03-01  2850    NA     30     800
6  7782 CLARK  MANAGER   7839 1981-01-09  2450    NA     10     800
7  7788 SCOTT  ANALYST   7566 1982-12-09  3000    NA     20     800

5. R sqldf Package

SQLite에서는 ANY / ALL 연산자를 지원하지 않음. Min / Max로 대체 가능.
ANY : sal > ANY(SELECT sal FROM emp WHERE job=’SALESMAN’)
min : sal > (SELECT min(sal) FROM emp WHERE job=’SALESMAN’)
ALL : sal > ALL(SELECT sal FROM emp WHERE job=’SALESMAN’)
max : sal > (SELECT max(sal) FROM emp WHERE job=’SALESMAN’)
수수료(commission)를 포함한 직원의 급여가 (본인을 제외한) 한 사람 이상의 직원의 급여보다 크거나 같은 직원의 정보를 출력하시오. 본인을 제외한 다른 직원들의 최소 급여보다 큰 경우에 해당 결과를 반환한다.

R Programming

%%R

sqldf(" select ename, sal, comm, sal+ifnull(comm,0) netPay,
               (select min(sal) from emp where e.empno!=empno) min_sal
        from   emp e 
        where  sal+ifnull(comm,0)>=(select min(sal) from emp where e.empno!=empno);")

Results

    ename  sal comm netPay min_sal
1   ALLEN 1600  300   1900     800
2    WARD 1250  500   1750     800
3   JONES 2975   NA   2975     800
4  MARTIN 1250 1400   2650     800
5   BLAKE 2850   NA   2850     800
6   CLARK 2450   NA   2450     800
7   SCOTT 3000   NA   3000     800
8    KING 5000   NA   5000     800
9  TURNER 1500    0   1500     800
10  ADAMS 1100   NA   1100     800
11  JAMES  950   NA    950     800
12   FORD 3000   NA   3000     800
13 MILLER 1300   NA   1300     800

상관서브쿼리와 EXISTS 연산자를 사용하여서 수수료(commission)를 포함한 직원의 급여보다 (본인을 제외한) 작은 급여를 수령하는 직원이 존재하면 해당하는 직원의 정보를 출력하시오.EXISTS 연산자를 사용하여서 서브쿼리에서 해당 조건을 만족하는 경우에 해당 결과를 반환한다.

R Programming

%%R

sqldf("select ename, sal, comm, sal+ifnull(comm,0) netPay
       from   emp e 
       where  EXISTS ( select sal from emp where e.sal+ifnull(e.comm,0) >= sal and e.empno!=empno)")

Results

    ename  sal comm netPay
1   ALLEN 1600  300   1900
2    WARD 1250  500   1750
3   JONES 2975   NA   2975
4  MARTIN 1250 1400   2650
5   BLAKE 2850   NA   2850
6   CLARK 2450   NA   2450
7   SCOTT 3000   NA   3000
8    KING 5000   NA   5000
9  TURNER 1500    0   1500
10  ADAMS 1100   NA   1100
11  JAMES  950   NA    950
12   FORD 3000   NA   3000
13 MILLER 1300   NA   1300

emp 테이블을 self join 형식으로 카테시안 곱(Cartesian Product)을 수행한 후 왼쪽 emp테이블의 사원번호를 기준으로 오른쪽 emp테이블에서 본인의 사원번호를 제외한 다른 사원들의 급여 중 최소 급여를 산출하고, emp 테이블에 다시 이 최소 급여를 inner join으로 결합하여서 수수료를 포함한 사원 급여가 다른 직원들의 최소 급여보다 큰 경우에 해당 결과를 반환한다.

R Programming

%%R

sqldf("SELECT *
       FROM   EMP A,
              ( SELECT a.empno,min(b.sal) sal_min 
                FROM   emp a,
                       emp b
                WHERE  a.empno <> b.empno
                group by a.empno ) B
       WHERE  A.empno = b.empno
         and  A.sal+ifnull(A.comm,0) >= SAL_MIN
       ")

Results

   empno  ename       job  mgr   hiredate  sal comm deptno empno sal_min
1   7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300     30  7499     800
2   7521   WARD  SALESMAN 7698 1981-02-22 1250  500     30  7521     800
3   7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20  7566     800
4   7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400     30  7654     800
5   7698  BLAKE   MANAGER 7839 1981-03-01 2850   NA     30  7698     800
6   7782  CLARK   MANAGER 7839 1981-01-09 2450   NA     10  7782     800
7   7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20  7788     800
8   7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10  7839     800
9   7844 TURNER  SALESMAN 7698 1981-09-08 1500    0     30  7844     800
10  7876  ADAMS     CLERK 7788 1983-01-12 1100   NA     20  7876     800
11  7900  JAMES     CLERK 7698 1981-12-03  950   NA     30  7900     800
12  7902   FORD   ANALYST 7566 1981-12-03 3000   NA     20  7902     800
13  7934 MILLER     CLERK 7782 1982-01-23 1300   NA     10  7934     800

6. Python pandasql Package

Python Programming

ps.sqldf("select ename, sal, comm, sal+ifnull(comm,0) netPay \
          from emp e                                         \
          where sal+ifnull(comm,0)>=(select min(sal) from emp where e.empno!=empno);").head()

Results

	ename	sal	comm	netPay
0	ALLEN	1600	300.0	1900.0
1	WARD	1250	500.0	1750.0
2	JONES	2975	NaN	2975.0
3	MARTIN	1250	1400.0	2650.0
4	BLAKE	2850	NaN	2850.0

Python Programming

ps.sqldf("select ename, sal, comm, sal+ifnull(comm,0) netPay \
          from emp e                                         \
          where EXISTS ( select sal from emp where e.sal+ifnull(e.comm,0) >= sal and e.empno!=empno)").head()

Results

	ename	sal	comm	netPay
0	ALLEN	1600	300.0	1900.0
1	WARD	1250	500.0	1750.0
2	JONES	2975	NaN	2975.0
3	MARTIN	1250	1400.0	2650.0
4	BLAKE	2850	NaN	2850.0

Python Programming

ps.sqldf("SELECT *                                     \
          FROM   EMP A,                                \
                 ( SELECT a.empno,min(b.sal) sal_min   \
                   FROM   emp a,                       \
                          emp b                        \
                   WHERE  a.empno <> b.empno           \
                   group by a.empno ) B                \
          WHERE  A.empno = b.empno                     \
            and  A.sal+ifnull(A.comm,0) >= SAL_MIN").head()

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	empno	sal_min
0	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	7499	800
1	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	7521	800
2	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20	7566	800
3	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	7654	800
4	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30	7698	800

7. R data.table Package

Cross Join 생성 (cartesian Product) : 105 / 113 참조
DT syntax : DT방식의 on에서 != 는 현재 지원 안함

2개의 emp 테이블 사이에 카테시안 곱(Cartesian Product)을 수행한 후 왼쪽 emp테이블의 사원번호를 기준으로 오른쪽 emp테이블에서 본인의 사원번호를 제외한 다른 사원들의 급여 중 최소 급여를 산출하고, emp 테이블에 다시 이 최소 급여를 inner_join으로 결합하여서 수수료를 포함한 사원 급여가 다른 직원들의 최소 급여보다 큰 경우에 해당 결과를 반환한다.

R Programming

%%R

DT          <- data.table(emp)
dept_DT     <- data.table(dept)
salgrade_DT <- data.table(salgrade)

DT[  ( DT[,k:=1][DT[,k:=1], allow.cartesian=TRUE,nomatch=NULL,on = .(k),][empno != i.empno][, .(`sal_min` = min(i.sal)), keyby = .(empno)] ),
      on = .(empno = empno)][(sal+fcoalesce(comm,0)) >= sal_min][1:7, ]

Results

    empno  ename       job  mgr   hiredate  sal comm deptno k sal_min
 1:  7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300     30 1     800
 2:  7521   WARD  SALESMAN 7698 1981-02-22 1250  500     30 1     800
 3:  7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20 1     800
 4:  7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400     30 1     800
 5:  7698  BLAKE   MANAGER 7839 1981-03-01 2850   NA     30 1     800
 6:  7782  CLARK   MANAGER 7839 1981-01-09 2450   NA     10 1     800
 7:  7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20 1     800
 8:  7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10 1     800
 9:  7844 TURNER  SALESMAN 7698 1981-09-08 1500    0     30 1     800
10:  7876  ADAMS     CLERK 7788 1983-01-12 1100   NA     20 1     800
11:  7900  JAMES     CLERK 7698 1981-12-03  950   NA     30 1     800
12:  7902   FORD   ANALYST 7566 1981-12-03 3000   NA     20 1     800
13:  7934 MILLER     CLERK 7782 1982-01-23 1300   NA     10 1     800

data.table::merge() syntax

R Programming

%%R

merge( DT, 
        (merge(DT[,k:=1],DT[,k:=1], by="k",allow.cartesian=TRUE)[empno.x != empno.y][, .(`sal_min` = min(sal.y)), keyby = .(empno.x)])
      ,
       by.x=c("empno"),by.y=c("empno.x"), all.x=T)[sal+fcoalesce(comm,0) >= sal_min][1:7, ]

Results

   empno  ename      job  mgr   hiredate  sal comm deptno k sal_min
1:  7499  ALLEN SALESMAN 7698 1981-02-20 1600  300     30 1     800
2:  7521   WARD SALESMAN 7698 1981-02-22 1250  500     30 1     800
3:  7566  JONES  MANAGER 7839 1981-04-02 2975   NA     20 1     800
4:  7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30 1     800
5:  7698  BLAKE  MANAGER 7839 1981-03-01 2850   NA     30 1     800
6:  7782  CLARK  MANAGER 7839 1981-01-09 2450   NA     10 1     800
7:  7788  SCOTT  ANALYST 7566 1982-12-09 3000   NA     20 1     800

fuzzyjoin::fuzzy_left_join 방식으로 카테시안 곱(Cartesian Product)을 수행한다.

R Programming

%%R

Dtable_1 = data.table(fuzzyjoin::fuzzy_left_join( DT,DT, by = c("sal" = "sal"),match_fun = list(`!=`) ))[, .(`sal_min` = min(sal.y)), keyby = list(empno.x)]
Dtable_1
Dtable_1[DT, nomatch=NULL, on = .( empno.x = empno), ][sal+fcoalesce(comm,0) >= sal_min][1:7, ] # DT 가 left join의 기준 테이블

Results

   empno.x sal_min  ename      job  mgr   hiredate  sal comm deptno k
1:    7499     800  ALLEN SALESMAN 7698 1981-02-20 1600  300     30 1
2:    7521     800   WARD SALESMAN 7698 1981-02-22 1250  500     30 1
3:    7566     800  JONES  MANAGER 7839 1981-04-02 2975   NA     20 1
4:    7654     800 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30 1
5:    7698     800  BLAKE  MANAGER 7839 1981-03-01 2850   NA     30 1
6:    7782     800  CLARK  MANAGER 7839 1981-01-09 2450   NA     10 1
7:    7788     800  SCOTT  ANALYST 7566 1982-12-09 3000   NA     20 1

[참고] 카테시안조인 예제 - Merge 방식

비등가 조인(“sal>i.sal”)을 바로 사용 시 완벽하지 않아서 위에서 카테시안 곱을 수행 후 외부에서 조건을 부여하여 처리.

7369(SMITH) 가 제대로 처리 안 됨.

R Programming

%%R

# 문제 : 7369(SMITH) 가 삭제가 안 됨. "sal.x > sal.y" 제대로 적용 안 됨.
# 196건이 반환 됨.
# merge(DT[,k:=1],DT[,k:=1], by="k",allow.cartesian=TRUE,on = .(sal.x > sal.y),)

# 내부에서 비등가조인을 수행 시 문제가 있어서 적용카테시안 조인을 수행 후 처리된 1차 결과에서 조건문("sal.x > sal.y")을 수행. 7369(SMITH) 가 조건문에 의하여 삭제 됨
# 89건이 반환 됨.
print( merge(DT[,k:=1],DT[,k:=1], by="k",allow.cartesian=TRUE)[sal.x > sal.y] %>% head(5) )

print("-------------------------------------------------------------------------------------------------------------------------------")
# fuzzyjoin::fuzzy_inner_join 을 사용하여서 내부에서 비등가조인을 수행하여서 처리 가능.
# 89건이 반환 됨.
data.table(fuzzyjoin::fuzzy_inner_join(DT[,k:=1],DT[,k:=1], by = c("sal" = "sal"),match_fun = list(`>`) )) %>% head(5)

Results

   k empno.x ename.x    job.x mgr.x hiredate.x sal.x comm.x deptno.x empno.y ename.y    job.y mgr.y hiredate.y sal.y comm.y deptno.y
1: 1    7499   ALLEN SALESMAN  7698 1981-02-20  1600    300       30    7369   SMITH    CLERK  7902 1980-12-17   800     NA       20
2: 1    7499   ALLEN SALESMAN  7698 1981-02-20  1600    300       30    7521    WARD SALESMAN  7698 1981-02-22  1250    500       30
3: 1    7499   ALLEN SALESMAN  7698 1981-02-20  1600    300       30    7654  MARTIN SALESMAN  7698 1981-09-28  1250   1400       30
4: 1    7499   ALLEN SALESMAN  7698 1981-02-20  1600    300       30    7844  TURNER SALESMAN  7698 1981-09-08  1500      0       30
5: 1    7499   ALLEN SALESMAN  7698 1981-02-20  1600    300       30    7876   ADAMS    CLERK  7788 1983-01-12  1100     NA       20
[1] "-------------------------------------------------------------------------------------------------------------------------"
   empno.x ename.x    job.x mgr.x hiredate.x sal.x comm.x deptno.x k.x empno.y ename.y    job.y mgr.y hiredate.y sal.y comm.y deptno.y k.y
1:    7499   ALLEN SALESMAN  7698 1981-02-20  1600    300       30   1    7369   SMITH    CLERK  7902 1980-12-17   800     NA       20   1
2:    7499   ALLEN SALESMAN  7698 1981-02-20  1600    300       30   1    7521    WARD SALESMAN  7698 1981-02-22  1250    500       30   1
3:    7499   ALLEN SALESMAN  7698 1981-02-20  1600    300       30   1    7654  MARTIN SALESMAN  7698 1981-09-28  1250   1400       30   1
4:    7499   ALLEN SALESMAN  7698 1981-02-20  1600    300       30   1    7844  TURNER SALESMAN  7698 1981-09-08  1500      0       30   1
5:    7499   ALLEN SALESMAN  7698 1981-02-20  1600    300       30   1    7876   ADAMS    CLERK  7788 1983-01-12  1100     NA       20   1

[참고] 카테시안조인 예제 - DT Syntax 방식
- 7369(SMITH) 값이 NA로 변경 됨. (51: NA NA 7839 NA 5000 5000)
  : DT Syntax에서는 카테시안조인 실행 시 내부에서 비등가조인 수행 가능.

R Programming

%%R

# 문제 : 90건이 반환 됨.
# DT[,k:=1][DT[,k:=1], allow.cartesian=TRUE,on = .(sal>sal),.(x.empno,empno,i.empno,  x.sal,sal,i.sal)]

# 89건이 반환 됨.
print( DT[,k:=1][DT[,k:=1], allow.cartesian=TRUE,on = .(k),.(x.empno,empno,i.empno,  x.sal,sal,i.sal)][x.sal > i.sal] %>% head(10) )

print("-------------------------------------------------------------------------------------------------------------------------------")
# nomatch 을 부여하여 처리 가능 : 89건이 반환 됨.
print( DT[,k:=1][DT[,k:=1], allow.cartesian=TRUE, nomatch=NULL, on = .(sal > sal)] %>% head(10) )

print("-------------------------------------------------------------------------------------------------------------------------------")
# fuzzyjoin::fuzzy_inner_join 을 사용하여서 내부에서 비등가조인을 수행하여서 처리 가능.
# 89건이 반환 됨.
data.table(fuzzyjoin::fuzzy_inner_join(DT[,k:=1],DT[,k:=1], by = c("sal" = "sal"),match_fun = list(`>`) )) %>% head(5)

Results

    x.empno empno i.empno x.sal  sal i.sal
 1:    7499  7499    7369  1600 1600   800
 2:    7521  7521    7369  1250 1250   800
 3:    7566  7566    7369  2975 2975   800
 4:    7654  7654    7369  1250 1250   800
 5:    7698  7698    7369  2850 2850   800
 6:    7782  7782    7369  2450 2450   800
 7:    7788  7788    7369  3000 3000   800
 8:    7839  7839    7369  5000 5000   800
 9:    7844  7844    7369  1500 1500   800
10:    7876  7876    7369  1100 1100   800
[1] "-------------------------------------------------------------------------------------------------------------------------------"
    empno  ename       job  mgr   hiredate sal comm deptno k i.empno i.ename i.job i.mgr i.hiredate i.comm i.deptno i.k
 1:  7499  ALLEN  SALESMAN 7698 1981-02-20 800  300     30 1    7369   SMITH CLERK  7902 1980-12-17     NA       20   1
 2:  7521   WARD  SALESMAN 7698 1981-02-22 800  500     30 1    7369   SMITH CLERK  7902 1980-12-17     NA       20   1
 3:  7566  JONES   MANAGER 7839 1981-04-02 800   NA     20 1    7369   SMITH CLERK  7902 1980-12-17     NA       20   1
 4:  7654 MARTIN  SALESMAN 7698 1981-09-28 800 1400     30 1    7369   SMITH CLERK  7902 1980-12-17     NA       20   1
 5:  7698  BLAKE   MANAGER 7839 1981-03-01 800   NA     30 1    7369   SMITH CLERK  7902 1980-12-17     NA       20   1
 6:  7782  CLARK   MANAGER 7839 1981-01-09 800   NA     10 1    7369   SMITH CLERK  7902 1980-12-17     NA       20   1
 7:  7788  SCOTT   ANALYST 7566 1982-12-09 800   NA     20 1    7369   SMITH CLERK  7902 1980-12-17     NA       20   1
 8:  7839   KING PRESIDENT   NA 1981-11-17 800   NA     10 1    7369   SMITH CLERK  7902 1980-12-17     NA       20   1
 9:  7844 TURNER  SALESMAN 7698 1981-09-08 800    0     30 1    7369   SMITH CLERK  7902 1980-12-17     NA       20   1
10:  7876  ADAMS     CLERK 7788 1983-01-12 800   NA     20 1    7369   SMITH CLERK  7902 1980-12-17     NA       20   1
[1] "-------------------------------------------------------------------------------------------------------------------------------"
   empno.x ename.x    job.x mgr.x hiredate.x sal.x comm.x deptno.x k.x empno.y ename.y    job.y mgr.y hiredate.y sal.y comm.y deptno.y k.y
1:    7499   ALLEN SALESMAN  7698 1981-02-20  1600    300       30   1    7369   SMITH    CLERK  7902 1980-12-17   800     NA       20   1
2:    7499   ALLEN SALESMAN  7698 1981-02-20  1600    300       30   1    7521    WARD SALESMAN  7698 1981-02-22  1250    500       30   1
3:    7499   ALLEN SALESMAN  7698 1981-02-20  1600    300       30   1    7654  MARTIN SALESMAN  7698 1981-09-28  1250   1400       30   1
4:    7499   ALLEN SALESMAN  7698 1981-02-20  1600    300       30   1    7844  TURNER SALESMAN  7698 1981-09-08  1500      0       30   1
5:    7499   ALLEN SALESMAN  7698 1981-02-20  1600    300       30   1    7876   ADAMS    CLERK  7788 1983-01-12  1100     NA       20   1

8. SAS Proc SQL

수수료(commission)를 포함한 직원의 급여가 (본인을 제외한) 임의의 한 사람 이상의 직원의 급여보다 크거나 같은 직원의 정보를 출력하시오. ANY 연산자를 사용하여서 서브쿼리에서 반환되는 값과 비교하여서 하나 이상을 만족하는 경우에 해당 결과를 반환한다.

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select ename, sal, comm, 
           sal+COALESCE(comm,0)   AS netPay,
           sal+IFN(comm=.,0,COMM) AS netPay_IFN
    from   emp E
    where  sal+COALESCE(comm,0) >= any(select sal from emp where empno NE E.empno);
QUIT;
PROC PRINT data=STATSAS_1(obs=5);RUN;

Results

OBS	ename	sal	comm	netPay	netPay_IFN
1	ALLEN	1600	300	1900	1900
2	WARD	1250	500	1750	1750
3	JONES	2975	.	2975	2975
4	MARTIN	1250	1400	2650	2650
5	BLAKE	2850	.	2850	2850

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_2 AS
    select ename, sal, comm, 
           sal+COALESCE(comm,0) AS netPay 
    from   emp E
    where  sal+COALESCE(comm,0) >= (select min(sal) from emp where empno NE E.empno);
QUIT;
PROC PRINT data=STATSAS_2(obs=5);RUN;

Results

OBS	ename	sal	comm	netPay
1	ALLEN	1600	300	1900
2	WARD	1250	500	1750
3	JONES	2975	.	2975
4	MARTIN	1250	1400	2650
5	BLAKE	2850	.	2850

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_3 AS
    select ename, sal, comm, sal+COALESCE(comm,0) AS netPay
    from   emp e 
    where  EXISTS ( select sal from emp where e.sal+COALESCE(e.comm,0) >= sal and empno NE E.empno);
QUIT;
PROC PRINT data=STATSAS_3(obs=5);RUN;

Results

OBS	ename	sal	comm	netPay
1	ALLEN	1600	300	1900
2	WARD	1250	500	1750
3	JONES	2975	.	2975
4	MARTIN	1250	1400	2650
5	BLAKE	2850	.	2850

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_4 AS
    SELECT A.*,
           SAL_MIN
       FROM   EMP A,
              ( SELECT a.empno,min(b.sal) AS sal_min 
                FROM   emp a,
                       emp b
                WHERE  a.empno NE b.empno
                group by a.empno ) B
       WHERE  A.empno = b.empno
         and  A.sal+COALESCE(A.comm,0) >= SAL_MIN;
QUIT;
PROC PRINT data=STATSAS_4(obs=5);RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno	sal_min
1	7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30	800
2	7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30	800
3	7566	JONES	MANAGER	7839	1981-04-02	2975	.	20	800
4	7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30	800
5	7698	BLAKE	MANAGER	7839	1981-03-01	2850	.	30	800

9. SAS Data Step

SAS Programming

%%SAS sas

* DATA STEP(CROSS JOIN-카테시안 곱);
DATA STATSAS_3; 
 SET EMP(RENAME=(SAL=SAL_MIN EMPNO=MIN_EMPNO) KEEP=EMPNO SAL);
     DO I=1 TO KOBS; 
        SET EMP NOBS=KOBS POINT=I;
        IF EMPNO NE MIN_EMPNO THEN OUTPUT;
     END;    
RUN;

PROC SUMMARY DATA=STATSAS_3 NWAY;
     CLASS EMPNO;
     VAR   SAL_MIN;
     OUTPUT OUT=SAL_MIN(DROP=_:) MIN=;
QUIT;

PROC SORT DATA=emp OUT=EMP_1;
     BY EMPNO;
RUN;

PROC SORT DATA=SAL_MIN;
     BY EMPNO;
RUN;

DATA STATSAS_2;
 MERGE emp(IN=A) SAL_MIN(IN=B);
     BY EMPNO;
     IF A AND B;
     IF sal+COALESCE(comm,0) >= SAL_MIN;
RUN;
PROC PRINT data=STATSAS_2(obs=5);RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno	SAL_MIN
1	7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30	800
2	7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30	800
3	7566	JONES	MANAGER	7839	1981-04-02	2975	.	20	800
4	7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30	800
5	7698	BLAKE	MANAGER	7839	1981-03-01	2850	.	30	800

10. Python Dfply Package

Python Programming

@pipe
def inner_join_merge(df, other, left_on,right_on,suffixes=['_x','_y']):

    joined = df.merge(other, how='inner', left_on=left_on,
                      right_on=right_on , suffixes=suffixes)
    return joined

emp >> \
  inner_join_merge( ( emp.assign(foo=1) >> \
                        full_join(emp.assign(foo=1), by='foo') >> \
                        filter_by(X.empno_x != X.empno_y)      >> \
                        group_by('empno_x')                    >> \
                        summarize(sal_min = X.sal_y.min() ) ) ,   \
                    left_on  = ["empno"], right_on = ["empno_x"] )                       >> \
  filter_by( ( X.sal + make_symbolic(np.where)(X.comm.isna(), 0, X.comm) ) >= X.sal_min) >> \
  select(~X.job, ~X.hiredate, ~X.comm)                                                   >> \
  head()

Results

	empno	ename	mgr	sal	deptno	empno_x	sal_min
1	7499	ALLEN	7698.0	1600	30	7499	800
2	7521	WARD	7698.0	1250	30	7521	800
3	7566	JONES	7839.0	2975	20	7566	800
4	7654	MARTIN	7698.0	1250	30	7654	800
5	7698	BLAKE	7839.0	2850	30	7698	800

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트

저작자표시 비영리 변경금지

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

[데이터 추출] 전체 합계 추가하기 - 스칼라 서브쿼리와 Cross join - 115 (오라클 SQL, R, Python, SAS) (0)	2022.12.09
[데이터 추출] 상관 서브쿼리 & 카테시안 곱(Cartesian Product) - 114 (0)	2022.12.07
[데이터 추출] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리, 평균 - 112 (0)	2022.12.07
[데이터 추출] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리, 평균 - 111 (0)	2022.12.06
[데이터 추출] 해당 그룹에 대한 특정 조건을 만족하는 그룹 리스트 출력 - 110 (0)	2022.12.06

[데이터 추출] 결측치 대체, 카테시안 곱(Cartesian Product) - Full Join, Any 연산자 - 113

113. Display employee name, Sal, comm and net pay for those employees whose net pay are greater than or equal to any other employee salary of the company?

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

6. Python pandasql Package

7. R data.table Package

8. SAS Proc SQL

9. SAS Data Step

10. Python Dfply Package

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

댓글

티스토리툴바

[데이터 추출] 결측치 대체, 카테시안 곱(Cartesian Product) - Full Join, Any 연산자 - 113

113. Display employee name, Sal, comm and net pay for those employees whose net pay are greater than or equal to any other employee salary of the company?

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

6. Python pandasql Package

7. R data.table Package

8. SAS Proc SQL

9. SAS Data Step

10. Python Dfply Package

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

관련글

댓글

티스토리툴바