본문 바로가기
통계프로그램 비교 시리즈/프로그래밍비교(Oracle,Python,R,SAS)

[등가 데이터 결합] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리 - 150 (오라클 SQL, R, Python, SAS)

by 기서무나구물 2023. 1. 3.

포스팅 목차

    150. Print the details of all the employees who are sub ordinate to Blake.

     

    * 관리자 “BLAKE”가 관리하고 있는 부서원의 정보를 출력하시오.

    • 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
    • 유사문제 : 51, 52, 89번과 동일, 92, 107
    • [데이터 추출] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리, 등가 데이터 결합
    • 데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql
    • Oracle : 비상관 서브쿼리, 등가 데이터 결합
    • 파이썬 Pandas : .tolist()
    • R 프로그래밍 : unlist(), is.na(), NOT('!') 연산자
    • R Dplyr Package : unlist(), dplyr::filter()
    • R sqldf Package : 비상관 서브쿼리, 등가 데이터 결합
    • Python pandasql Package : 비상관 서브쿼리, 등가 데이터 결합
    • R data.table Package : 등호 비교 연산자('==')
    • SAS Proc SQL : 비상관 서브쿼리, 등가 데이터 결합
    • SAS Data Step : proc sort의 nodupkey, Rename 데이터 세트 옵션, Merge 구문, IF 조건문
    • Python Dfply Package : @dfpipe 와 def 사용자 정의 함수(pull_fun), filter_by()
    • 파이썬 Base 프로그래밍 :

     

    1. Oracle(오라클)

    서브쿼리에서 ‘BLAKE’의 사원번호(‘empno’)를 선택한 후 emp 테이블의 관리자 사원번호(‘mgr’)에서 “BLAKE”의 사원번호를 선택한다. 이를 통하여 “BLAKE”가 관리자로 근무하고 있는 부서원들의 정보를 추출한다.

     

    Oracle Programming 
    Select * 
from   emp 
where  mgr=(select empno from emp where ename='BLAKE');

     

    emp 테이블을 self join 형식으로 관리자 사원번호(‘mgr’)와 사원번호(‘empno’)를 기준으로 내부조인을 실행하여 관리자 사원번호(‘mgr’)가 ‘BLAKE’의 사원번호와 동일한 사원정보를 추출한다. 이를 통하여 “BLAKE”가 관리자로 근무하고 있는 부서원들의 정보를 추출한다.

     

    Oracle Programming 
    select a.empno,a.ename ,b.ename 
from   emp a, emp b 
where  a.mgr   = b.empno
  and  b.ename = 'BLAKE'

     

    2. Python Pandas(파이썬)

    관리자 사원번호(‘mgr’)가 “BLAKE”의 사원번호와 일치하는 직원 정보를 선택하여서 “BLAKE”가 관리자로 근무하고 있는 부서원들의 정보를 추출한다.

     

    Python Programming 
    emp[ ( emp['mgr']    == (emp[emp['ename']=='BLAKE']['empno'].tolist()[0]) ) ]

     

    Results
      empno ename job mgr hiredate sal comm deptno
    1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30
    2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30
    4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30
    9 7844 TURNER SALESMAN 7698.0 1981/09/08 1500 0.0 30
    11 7900 JAMES CLERK 7698.0 1981/12/03 950 NaN 30

     

    3. R Programming (R Package)

    관리자 사원번호(‘mgr’)가 “BLAKE”의 사원번호와 일치하는 직원들의 정보를 선택하여서 “BLAKE”가 관리자로 근무하고 있는 부서원들의 정보를 추출한다.

     

    R Programming 
    %%R

emp[ (!is.na(emp$mgr)) & emp$mgr == unlist(emp[emp$ename == 'BLAKE', "empno" ]) ,]

     

    Results 
    # A tibble: 5 x 8
  empno ename  job        mgr hiredate     sal  comm deptno
  <dbl> <chr>  <chr>    <dbl> <date>     <dbl> <dbl>  <dbl>
1  7499 ALLEN  SALESMAN  7698 1981-02-20  1600   300     30
2  7521 WARD   SALESMAN  7698 1981-02-22  1250   500     30
3  7654 MARTIN SALESMAN  7698 1981-09-28  1250  1400     30
4  7844 TURNER SALESMAN  7698 1981-09-08  1500     0     30
5  7900 JAMES  CLERK     7698 1981-12-03   950    NA     30

     

    4. R Dplyr Package

    dplyr::filter() 함수를 사용하여서 관리자 사원번호(‘mgr’)가 내부에서 선택한 “BLAKE”의 사원번호와 일치하는 직원들의 정보를 선택하여서 “BLAKE”가 관리자로 근무하고 있는 부서원들의 정보를 추출한다.

     

    R Programming 
    %%R

emp %>%
  dplyr::filter( mgr == ( emp  %>% 
                            dplyr::filter( ename == 'BLAKE' ) %>% 
                            dplyr::select(empno)              %>% 
                            unlist() )
               )

     

    Results 
    # A tibble: 5 x 8
  empno ename  job        mgr hiredate     sal  comm deptno
  <dbl> <chr>  <chr>    <dbl> <date>     <dbl> <dbl>  <dbl>
1  7499 ALLEN  SALESMAN  7698 1981-02-20  1600   300     30
2  7521 WARD   SALESMAN  7698 1981-02-22  1250   500     30
3  7654 MARTIN SALESMAN  7698 1981-09-28  1250  1400     30
4  7844 TURNER SALESMAN  7698 1981-09-08  1500     0     30
5  7900 JAMES  CLERK     7698 1981-12-03   950    NA     30

     

    5. R sqldf Package

    서브쿼리에서 ‘BLAKE’의 사원번호(‘empno’)를 선택한 후 emp 테이블의 관리자 사원번호(‘mgr’)에서 “BLAKE”의 사원번호를 선택한다. 이를 통하여 “BLAKE”가 관리자로 근무하고 있는 부서원들의 정보를 추출한다.

     

    R Programming 
    %%R

sqldf(" Select * 
        from   emp 
        where  mgr = (select empno from emp where ename='BLAKE');")

     

    Results 
      empno  ename      job  mgr   hiredate  sal comm deptno
1  7499  ALLEN SALESMAN 7698 1981-02-20 1600  300     30
2  7521   WARD SALESMAN 7698 1981-02-22 1250  500     30
3  7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30
4  7844 TURNER SALESMAN 7698 1981-09-08 1500    0     30
5  7900  JAMES    CLERK 7698 1981-12-03  950   NA     30

     

    emp 테이블을 self join 형식으로 관리자 사원번호(‘mgr’)와 사원번호(‘empno’)를 기준으로 내부조인을 실행하여 관리자 사원번호(‘mgr’)가 ‘BLAKE’의 사원번호와 동일한 사원정보를 추출한다. 이를 통하여 “BLAKE”가 관리자로 근무하고 있는 부서원들의 정보를 추출한다.

     

    R Programming 
    %%R

sqldf(" select a.empno,a.ename ,b.ename mgr_ename
        from   emp a, emp b 
        where  a.mgr   = b.empno 
          and  b.ename = 'BLAKE'")

     

    Results 
      empno  ename mgr_ename
1  7499  ALLEN     BLAKE
2  7521   WARD     BLAKE
3  7654 MARTIN     BLAKE
4  7844 TURNER     BLAKE
5  7900  JAMES     BLAKE

     

    6. Python pandasql Package

     

    Python Programming 
    ps.sqldf(" Select * from emp                                     \
           where  mgr=(select empno from emp where ename='BLAKE');")

     


    Results
      deptno ename job mgr hiredate sal comm deptno
    0 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30
    1 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30
    2 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30
    3 7844 TURNER SALESMAN 7698.0 1981/09/08 1500 0.0 30
    4 7900 JAMES CLERK 7698.0 1981/12/03 950 NaN 30

     

    Python Programming 
    ps.sqldf(" select a.empno,a.ename ,b.ename mgr_ename               \
           from emp a, emp b where a.mgr=b.empno and b.ename='BLAKE';")

     


    Results
      empno ename mgr_ename
    0 7499 ALLEN BLAKE
    1 7521 WARD BLAKE
    2 7654 MARTIN BLAKE
    3 7844 TURNER BLAKE
    4 7900 JAMES BLAKE

     

    7. R data.table Package

    1차로 ‘BLAKE’의 사원번호(‘empno’)를 선택한 후 emp 테이블의 관리자 사원번호(‘mgr’)에서 “BLAKE”의 사원번호에 해당하는 직원들의 정보를 선택한다. 이를 통하여 “BLAKE”가 관리자로 근무하고 있는 부서원들의 정보를 추출한다.

     

    R Programming 
    %%R
DT <- data.table(emp)

DT[ (mgr == DT[ename=='BLAKE', empno ])  , ]

     

    Results 
       empno  ename      job  mgr   hiredate  sal comm deptno
1:  7499  ALLEN SALESMAN 7698 1981-02-20 1600  300     30
2:  7521   WARD SALESMAN 7698 1981-02-22 1250  500     30
3:  7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30
4:  7844 TURNER SALESMAN 7698 1981-09-08 1500    0     30
5:  7900  JAMES    CLERK 7698 1981-12-03  950   NA     30

     

    8. SAS Proc SQL

     

    SAS Programming 
    %%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    Select * 
    from   emp 
    where  mgr=(select empno from emp where ename='BLAKE');
QUIT;
PROC PRINT;RUN;

     


    Results
    OBS empno ename job mgr hiredate sal comm deptno
    1 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
    2 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
    3 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
    4 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
    5 7900 JAMES CLERK 7698 1981-12-03 950 . 30

     

    SAS Programming 
    %%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_2 AS
    select a.empno,a.ename ,b.ename as mgr_ename
    from   emp a, emp b 
    where  a.mgr   = b.empno
      and  b.ename = 'BLAKE';
QUIT;
PROC PRINT;RUN;

     


    Results
    OBS empno ename mgr_ename
    1 7499 ALLEN BLAKE
    2 7521 WARD BLAKE
    3 7654 MARTIN BLAKE
    4 7844 TURNER BLAKE
    5 7900 JAMES BLAKE

     

    9. SAS Data Step

     

    SAS Programming 
    %%SAS sas

proc sort data=emp out=emp_1(rename=mgr=join_key);
     by mgr;
run;

proc sort data=emp out=emp_2(rename=(empno=join_key ename=mgr_ename) keep=empno ename) nodupkey;
     by empno;
     where ename = 'BLAKE';
run;

DATA STATSAS_2;
 merge emp_1(in=a) emp_2(in=b);
     by join_key;
     if a and b;
RUN;
PROC PRINT;RUN;

     

    Results
    OBS empno ename job join_key hiredate sal comm deptno mgr_ename
    1 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 BLAKE
    2 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 BLAKE
    3 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 BLAKE
    4 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 BLAKE
    5 7900 JAMES CLERK 7698 1981-12-03 950 . 30 BLAKE

     

    10. Python Dfply Package

     

    Python Programming 
    # [참고] 함수 사용. (52번 예제 참고)

@dfpipe
def pull_fun(df, column=-1):
    return df.loc[:, column].item()

emp >> \
  filter_by ( X.mgr == ( emp >> \
                           filter_by(X.ename == 'BLAKE') >>\
                           select(X.empno) >>\
                           pull_fun("empno")))

     


    Results
      empno ename job mgr hiredate sal comm deptno
    1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30
    2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30
    4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30
    9 7844 TURNER SALESMAN 7698.0 1981/09/08 1500 0.0 30
    11 7900 JAMES CLERK 7698.0 1981/12/03 950 NaN 30

     

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

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

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

    [데이터 상하 결합(UNION)] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리 - 152 (오라클 SQL, R, Python, SAS)  (0) 2023.01.04
    [중복 제거(Distinct)] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리(In) - 151 (오라클 SQL, R, Python, SAS)  (0) 2023.01.04
    [비등가 데이터 결합] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리(Any) - 149 (오라클 SQL, R, Python, SAS)  (0) 2023.01.03
    [날짜 함수] 특정 조건에 따라 해당 날짜 반환 - 148 (오라클 SQL, R, Python, SAS)  (1) 2023.01.03
    [문자함수와 조건문] 조건에 따라 값을 변경 - 147 (오라클 SQL, R, Python, SAS)  (0) 2023.01.02

    관련글

    댓글

    티스토리툴바