[데이터 추출] 비상관 서브쿼리(Uncorrelated Subquery) 와 상관 서브쿼리(Correlated Subquery)를 만족하는 데이터 추출 - 비등가조인(NON-EQUI JOIN) 데이터 결합 - 102

 

102. Display name and salary of ford if his Sal is equal to high Sal of his grade.

 

* 직원 Ford의 급여가 해당 급여 등급의 최고 급여(‘hisal’)와 같은 경우에 이름과 연봉을 출력하시오.

---

• 97번 예제 참고

• 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크

• [데이터 추출] 비상관 서브쿼리(Uncorrelated Subquery) 와 상관 서브쿼리(Correlated Subquery)를 만족하는 데이터 추출 - 비등가조인(NON-EQUI JOIN) 데이터 결합

Oracle : 비상관 서브쿼리와 상관 서브쿼리, 비등가 데이터 결합 파이썬 Pandas : pd.merge(), query() R 프로그래밍 : merge(), Subset() R Dplyr Package : dplyr::full_join(), dplyr::filter() R sqldf Package : 비상관 서브쿼리와 상관 서브쿼리, 비등가 데이터 결합 Python pandasql Package : 비상관 서브쿼리와 상관 서브쿼리, 비등가 데이터 결합 R data.table Package : 비등가조인(NON-EQUI JOIN) SAS Proc SQL : 비상관 서브쿼리와 상관 서브쿼리, 비등가 데이터 결합 SAS Data Step : CROSS JOIN (카테시안 곱) 구현 Python Dfply Package : full_join(), filter_by() 파이썬 Base 프로그래밍 :

 

---

1. Oracle(오라클)

서브쿼리를 사용하여서 직원 Ford의 급여가 해당 급여 등급의 최고 급여(‘hisal’)와 같은 경우에 이름과 연봉을 출력하시오.

 

Oracle Programming

select ename,sal 
from   emp e 
where  ename = 'FORD' 
  and  sal = (select hisal from salgrade where grade = (select grade from salgrade where e.sal>=losal and e.sal<=hisal));

 

---

EMP 테이블과 SALGRADE 테이블을 비등가 조인으로 결합하여서 직원 Ford의 급여가 해당 급여 등급의 최고 급여(‘hisal’)와 같은 경우에 이름과 연봉을 출력하시오.

 

Oracle Programming

select ename,sal,grade 
from   emp, salgrade 
where  sal between losal and hisal 
  and  ename = 'FORD' 
  AND  HISAL = SAL;

 

---

2. Python Pandas(파이썬)

emp 테이블과 salgrade 테이블에 임시 변수(‘foo’)를 지정한 후에 임시 변수를 기준으로 cross join(Cartesian Product)을 수행한 후 query 구문에서 왼쪽 테이블의 기준 값을 기준으로 오른쪽 테이블에서 local <= sal <= hisal 조건을 만족하는 급여 등급을 선택하고 하고, 직원 ‘FORD’의 급여가 해당 급여 등급의 최고 급여(‘hisal’)과 같은 경우에 이름과 연봉을 출력한다.

 

Python Programming

pd.merge(emp.assign(foo=1), salgrade.assign(foo=1), on ='foo').query(" losal <= sal <= hisal & sal==hisal & ename == 'FORD' ").drop(['job','comm','hiredate'],axis=1)

 

Results

empno	ename	mgr	sal	deptno	foo	grade	losal	hisal
7902	FORD	7566.0	3000	20	1	4	2001	3000

 

---

3. R Programming (R Package)

emp 테이블과 salgrade 테이블을 기준 변수에 character()을 지정하여서 cross join(Cartesian Product)을 수행한 후 subset 구문에서 왼쪽 테이블의 기준 값을 기준으로 오른쪽 테이블에서 between(sal, losal, hisal) 조건을 만족하는 급여 등급을 선택하고 하고, 직원 ‘FORD’의 급여가 해당 급여 등급의 최고 급여(‘hisal’)과 같은 경우에 이름과 연봉을 출력한다.

 

R Programming

%%R

subset( merge(emp, salgrade,by= character(),all.x=TRUE, all.y=TRUE),
        between(sal, losal, hisal) & sal==hisal & ename == 'FORD' )

 

Results

   empno ename     job  mgr   hiredate  sal comm deptno grade losal hisal
55  7902  FORD ANALYST 7566 1981-12-03 3000   NA     20     4  2001  3000

 

---

4. R Dplyr Package

emp 테이블과 salgrade 테이블을 기준 변수에 character()을 지정하여서 full_join 문법을 사용하여서 cross join(Cartesian Product)을 수행한 후 filter 함수에서 왼쪽 테이블의 기준 값을 기준으로 오른쪽 테이블에서 between(sal, losal, hisal) 조건을 만족하는 급여 등급을 선택하고 하고, 직원 ‘FORD’의 급여가 해당 급여 등급의 최고 급여(‘hisal’)과 같은 경우에 이름과 연봉을 출력한다.

 

R Programming

%%R

emp %>% 
  dplyr::filter(ename == 'FORD') %>%
  dplyr::full_join(salgrade, by = character()) %>%
  dplyr::filter( between(sal, losal, hisal ) & sal == hisal)

 

Results

# A tibble: 1 x 11
  empno ename job       mgr hiredate     sal  comm deptno grade losal hisal
  <dbl> <chr> <chr>   <dbl> <date>     <dbl> <dbl>  <dbl> <dbl> <dbl> <dbl>
1  7902 FORD  ANALYST  7566 1981-12-03  3000    NA     20     4  2001  3000

 

---

5. R sqldf Package

서브쿼리를 사용하여서 직원 Ford의 급여가 해당 급여 등급의 최고 급여(‘hisal’)와 같은 경우에 이름과 연봉을 출력하시오.

 

R Programming

%%R

sqldf(" select ename,sal 
        from   emp e 
        where  ename='FORD' 
          and  sal=(select hisal from salgrade where grade=(select grade from salgrade where e.sal>=losal and e.sal<=hisal));")

 

Results

  ename  sal
1  FORD 3000

 

---

EMP 테이블과 SALGRADE 테이블을 비등가 조인으로 결합하여서 직원 Ford의 급여가 해당 급여 등급의 최고 급여(‘hisal’)와 같은 경우에 이름과 연봉을 출력하시오.

 

R Programming

%%R

sqldf(" select ename,sal,grade 
        from   emp,salgrade 
        where  sal between losal and hisal 
          and  ename ='FORD' AND HISAL=SAL;")

 

Results

  ename  sal grade
1  FORD 3000     4

 

---

6. Python pandasql Package

서브쿼리를 사용하여서 직원 Ford의 급여가 해당 급여 등급의 최고 급여(‘hisal’)와 같은 경우에 이름과 연봉을 출력하시오.

 

Python Programming

ps.sqldf("select ename,sal from emp e                                   \
           where ename='FORD'                                           \
             and sal = ( select hisal from salgrade                     \
                         where grade = ( select grade from salgrade     \
                                         where  e.sal>=losal            \
                                           and  e.sal<=hisal));")

 

Results

ename	sal
FORD	3000

 

---

 

Python Programming

ps.sqldf(" select ename,sal,grade from   emp,salgrade where  sal between losal and hisal and  ename ='FORD' AND HISAL=SAL;")

 

Results

ename	sal	grade
FORD	3000	4

 

---

7. R data.table Package

emp 테이블과 salgrade 테이블을 기준으로 비등가조인(NON-EQUI JOIN) 을 수행하여서 해당 급여를 포함하는 범위의 급여 등급을 선택하고, 직원 ‘FORD’의 급여가 해당 급여 등급의 최고 급여(‘hisal’)과 같은 경우에 이름과 연봉을 출력한다.

 

• data.talbe은 비등가조인(NON-EQUI JOIN)을 지원하는 것이 장점이다.

R Programming

%%R

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

DT[salgrade_DT, nomatch=NULL, on = .( sal >= losal , sal <= hisal,sal = hisal), ][ ename== 'FORD' ,.(empno,ename,job,hiredate,sal = sal.1, grade)]

 

Results

   empno ename     job   hiredate  sal grade
1:  7902  FORD ANALYST 1981-12-03 3000     4

 

---

8. SAS Proc SQL

• 85번 참고;

서브쿼리를 사용하여서 직원 Ford의 급여가 해당 급여 등급의 최고 급여(‘hisal’)와 같은 경우에 해당 사원의 이름과 연봉을 출력하시오.

 

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select ename,sal 
    from   emp e 
    where  ename='FORD' 
      and  sal=( select hisal from salgrade 
                 where grade=(select grade from salgrade where e.sal>=losal and e.sal<=hisal));
QUIT;
PROC PRINT;RUN;

 

Results

ename	sal
FORD	3000

 

---

 

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_2 AS
    select ename,sal,grade 
    from   emp, salgrade 
    where  sal between losal and hisal 
      and  ename = 'FORD' 
      AND  HISAL = SAL;;
QUIT;
PROC PRINT;RUN;

 

Results

ename	sal	grade
FORD	3000	4

 

---

9. SAS Data Step

• DATA STEP(CROSS JOIN-카테시안 곱);

SAS Programming

%%SAS sas

DATA salgrade_3;
 SET salgrade;
     KEEP LOSAL HISAL GRADE;
RUN;

DATA STATSAS_3; 
 SET EMP;
     DO I=1 TO KOBS; 
        SET salgrade NOBS=KOBS POINT=I;
        IF  SAL >= losal AND SAL <= hisal
        and ename = 'FORD' 
        AND SAL   = HISAL THEN OUTPUT;
     END;    
RUN;

PROC PRINT;RUN;

 

---

10. Python Dfply Package

 

Python Programming

emp.assign(foo=1)                                                            >> \
  filter_by(X.ename == 'FORD')                                               >> \
  full_join( salgrade.assign(foo=1), by='foo')                               >> \
  filter_by(X.sal.between(X.losal,X.hisal), X.sal==X.hisal, X.ename=='FORD') >> \
  select(~X.job, ~X.hiredate, ~X.comm)

 

Results

empno	ename	mgr	sal	deptno	foo	grade	losal	hisal
7902	FORD	7566.0	3000	20	1	4	2001	3000

 

---

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