[데이터 추출] 비상관 서브쿼리(Uncorrelated Subqueyr) 를 만족하는 데이터 추출 - In 연산자와 내부조인(Inner Join)

포스팅 목차

100. Display employee name, job, deptname, location for all who are working as managers.

* 관리자 역할을 수행하는 직원의 정보를 출력하시오.

파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
비상관 서브쿼리(Uncorrelated Subqueyr) 를 만족하는 데이터 추출 - In 연산자와 내부조인(Inner Join)

Oracle : 비상관 서브쿼리, 내부조인
파이썬 Pandas : query(), drop_duplicates(), merge(), lambda 함수, loc[], isin(), pd.merge()
R 프로그래밍 : merge()
R Dplyr Package : dplyr::inner_join()
R sqldf Package : 비상관 서브쿼리, 내부조인
Python pandasql Package : 비상관 서브쿼리, 내부조인
R data.table Package : unlist(), DT syntax 방식 데이터 결합, is.list(), is.vector(), typeof()
SAS Proc SQL : 비상관 서브쿼리, 내부조인
SAS Data Step : Merge, IF 조건문, Proc Sort의 Nodupkey 옵션
Python Dfply Package : inner_join()
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

emp 테이블에서 관리자(‘mgr’) 사원번호에 해당하는 직원정보를 선택하고, 이 직원의 부서 정보를 추가한다.

Oracle Programming

select ename,job,dname,loc 
from   emp e, dept d 
where  e.deptno = d.deptno 
  and  empno in (select mgr from emp);

Oracle Programming

select e.ename, e.job,
       d.dname, d.loc 
from   emp e, dept d, (select distinct mgr from emp) f
where  e.deptno = d.deptno
  and  e.empno  = f.mgr;

2. Python Pandas(파이썬)

테이블.loc[lambda] 과 칼럼.apply(lambda)

관리자(‘mgr’) 사원번호를 선택 후 중복을 제거하고, query 메소드에서 선택된 변수명(‘list_of_values’)에 “@”를 붙여서 외부 변수값을 참조하여 관리자 역할을 수행하는 사원들을 선택 후 dept 테이블에서 부서 정보를 추가한다.

Python Programming

list_of_values = list(emp['mgr'].drop_duplicates())

emp.query("empno in @list_of_values").merge(dept, how='inner', left_on=['deptno'], right_on=['deptno']).drop(['job','comm','hiredate'],axis=1)

Results

empno	ename	mgr	sal	deptno	dname	loc
7566	JONES	7839.0	2975	20	RESEARCH	DALLAS
7788	SCOTT	7566.0	3000	20	RESEARCH	DALLAS
7902	FORD	7566.0	3000	20	RESEARCH	DALLAS
7698	BLAKE	7839.0	2850	30	SALES	CHICAGO
7782	CLARK	7839.0	2450	10	ACCOUNTING	NEW YORK
7839	KING	NaN	5000	10	ACCOUNTING	NEW YORK

관리자(‘mgr’) 사원번호를 선택 후 중복을 제거하고, lambda 함수를 사용하여서 관리자 역할을 수행하는 사원들을 선택 후 dept 테이블과 결합(Join)하여 부서 정보를 추가한다.

Python Programming

emp.loc[lambda emp: emp.empno.isin(list(emp['mgr'].drop_duplicates())) ].merge(dept, how='inner', left_on=['deptno'], right_on=['deptno'])[['ename','job','dname','loc']]

Results

ename	job	dname	loc
JONES	MANAGER	RESEARCH	DALLAS
SCOTT	ANALYST	RESEARCH	DALLAS
FORD	ANALYST	RESEARCH	DALLAS
BLAKE	MANAGER	SALES	CHICAGO
CLARK	MANAGER	ACCOUNTING	NEW YORK
KING	PRESIDENT	ACCOUNTING	NEW YORK

emp 테이블과 dept 테이블을 결합(Join)하여서 부서 정보를 추가한 후 다시 emp 테이블과 self join을 하여서 관리자(‘mgr’) 역할을 수행하는 직원 정보를 추출한다.

Python Programming

pd.merge(emp, dept, how='inner', left_on=['deptno'], right_on=['deptno']).merge((emp['mgr'].drop_duplicates()), how='inner', left_on=['empno'], right_on=['mgr'] ).drop(['job','comm','hiredate'],axis=1) # [['ename','job','dname','loc']]

Results

empno	ename	mgr_x	sal	deptno	dname	loc	mgr_y
7566	JONES	7839.0	2975	20	RESEARCH	DALLAS	7566.0
7788	SCOTT	7566.0	3000	20	RESEARCH	DALLAS	7788.0
7902	FORD	7566.0	3000	20	RESEARCH	DALLAS	7902.0
7698	BLAKE	7839.0	2850	30	SALES	CHICAGO	7698.0
7782	CLARK	7839.0	2450	10	ACCOUNTING	NEW YORK	7782.0
7839	KING	NaN	5000	10	ACCOUNTING	NEW YORK	7839.0

3. R Programming (R Package)

%in% 연산자를 사용하여 사원번호(‘empno’)에서 관리자(‘mgr’) 사원번호를 선택한 후 dept 테이블과 결합(Join)하여 부서 정보를 추가한다.

R Programming

%%R

merge(emp[emp$empno %in% emp$mgr,  ],dept,by.x=c("deptno"),
                                          by.y=c("deptno"),all.x=T)

Results

  deptno empno ename       job  mgr   hiredate  sal comm      dname      loc
1     10  7782 CLARK   MANAGER 7839 1981-01-09 2450   NA ACCOUNTING NEW YORK
2     10  7839  KING PRESIDENT   NA 1981-11-17 5000   NA ACCOUNTING NEW YORK
3     20  7566 JONES   MANAGER 7839 1981-04-02 2975   NA   RESEARCH   DALLAS
4     20  7788 SCOTT   ANALYST 7566 1982-12-09 3000   NA   RESEARCH   DALLAS
5     20  7902  FORD   ANALYST 7566 1981-12-03 3000   NA   RESEARCH   DALLAS
6     30  7698 BLAKE   MANAGER 7839 1981-03-01 2850   NA      SALES  CHICAGO

4. R Dplyr Package

filter 함수에 %in% 연산자를 사용하여 사원번호(‘empno’) 중에서 관리자(‘mgr’) 사원번호를 선택하고, dept 테이블과 결합(Join)하여 부서 정보를 추가한다.

R Programming

%%R

withmooc <- emp

emp %>% 
  dplyr::filter( empno %in% ( emp %>% dplyr::select(mgr) %>% pull(mgr) ) ) %>%
  dplyr::inner_join(dept, by = c('deptno' = "deptno"))

Results

# A tibble: 6 x 10
  empno ename job         mgr hiredate     sal  comm deptno dname      loc     
  <dbl> <chr> <chr>     <dbl> <date>     <dbl> <dbl>  <dbl> <chr>      <chr>   
1  7566 JONES MANAGER    7839 1981-04-02  2975    NA     20 RESEARCH   DALLAS  
2  7698 BLAKE MANAGER    7839 1981-03-01  2850    NA     30 SALES      CHICAGO 
3  7782 CLARK MANAGER    7839 1981-01-09  2450    NA     10 ACCOUNTING NEW YORK
4  7788 SCOTT ANALYST    7566 1982-12-09  3000    NA     20 RESEARCH   DALLAS  
5  7839 KING  PRESIDENT    NA 1981-11-17  5000    NA     10 ACCOUNTING NEW YORK
6  7902 FORD  ANALYST    7566 1981-12-03  3000    NA     20 RESEARCH   DALLAS

5. R sqldf Package

emp 테이블에서 관리자(‘mgr’) 사원번호에 해당하는 직원정보를 선택하고, 이 직원의 부서 정보를 추가한다.

R Programming

%%R

withmooc <- emp

sqldf(" select e.ename, e.job,
               d.dname, d.loc 
        from   emp e, dept d 
        where  e.deptno = d.deptno 
          and  empno in (select mgr from emp);")

Results

  ename       job      dname      loc
1 JONES   MANAGER   RESEARCH   DALLAS
2 BLAKE   MANAGER      SALES  CHICAGO
3 CLARK   MANAGER ACCOUNTING NEW YORK
4 SCOTT   ANALYST   RESEARCH   DALLAS
5  KING PRESIDENT ACCOUNTING NEW YORK
6  FORD   ANALYST   RESEARCH   DALLAS

6. Python pandasql Package

emp 테이블에서 관리자(‘mgr’) 사원번호에 해당하는 직원정보를 선택하고, 이 직원의 부서 정보를 추가한다.

Python Programming

import copy
withmooc =copy.copy(emp)

ps.sqldf("SELECT ename,job,dname,loc FROM emp , dept  WHERE emp.deptno=dept.deptno and empno in (select mgr from emp)")

Results

ename	job	dname	loc
JONES	MANAGER	RESEARCH	DALLAS
BLAKE	MANAGER	SALES	CHICAGO
CLARK	MANAGER	ACCOUNTING	NEW YORK
SCOTT	ANALYST	RESEARCH	DALLAS
KING	PRESIDENT	ACCOUNTING	NEW YORK
FORD	ANALYST	RESEARCH	DALLAS

7. R data.table Package

%in% 연산자를 사용하여 사원번호(‘empno’)에서 관리자(‘mgr’) 사원번호를 선택한 후 dept 테이블과 결합(Join)하여 부서 정보를 추가한다. unlist 함수는 리스트를 벡터로 변경한다.

R Programming

%%R

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

XT <- DT[DT[,empno %in% unlist( DT[, .(mgr)] ),]]
XT[dept_DT, nomatch=NULL, on = "deptno",.(ename,job,dname,loc)]

Results

   ename       job      dname      loc
1: CLARK   MANAGER ACCOUNTING NEW YORK
2:  KING PRESIDENT ACCOUNTING NEW YORK
3: JONES   MANAGER   RESEARCH   DALLAS
4: SCOTT   ANALYST   RESEARCH   DALLAS
5:  FORD   ANALYST   RESEARCH   DALLAS
6: BLAKE   MANAGER      SALES  CHICAGO

R Programming

%%R

print( is.list(DT[, .(mgr)]) )
print( is.vector( unlist( DT[, .(mgr)] ) ) )

print( typeof(DT[, .(mgr)]) )
print( typeof( unlist( DT[, .(mgr)] ) ) )

Results

[1] TRUE
[1] TRUE
[1] "list"
[1] "double"

8. SAS Proc SQL

emp 테이블에서 관리자(‘mgr’) 사원번호에 해당하는 직원정보를 선택하고, 이 직원의 부서 정보를 추가한다.

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select e.ename,e.job,d.dname,d.loc 
    from   emp e, dept d 
    where  e.deptno = d.deptno 
      and  empno in (select mgr from emp);
QUIT;
PROC PRINT;RUN;

Results

ename	job	dname	loc
CLARK	MANAGER	ACCOUNTING	NEW YORK
KING	PRESIDEN	ACCOUNTING	NEW YORK
JONES	MANAGER	RESEARCH	DALLAS
FORD	ANALYST	RESEARCH	DALLAS
SCOTT	ANALYST	RESEARCH	DALLAS
BLAKE	MANAGER	SALES	CHICAGO

9. SAS Data Step

SAS Programming

%%SAS sas

PROC SORT DATA=EMP OUT=EMP_1;
     BY DEPTNO;
RUN;

PROC SORT DATA=dept OUT=dept_1;
     BY DEPTNO;
RUN;

data STATSAS_2;
 merge EMP_1(in=a) dept_1(in=b keep=deptno dname loc);
     by deptno;
     if a and b then output;
run;

PROC SORT DATA=STATSAS_2 OUT=STATSAS_21(rename= (empno = mgr_empno));
     BY empno;
RUN;

PROC SORT DATA=EMP OUT=mgr_1(rename= (MGR = mgr_empno) keep=mgr) NODUPKEY;
     BY MGR;
RUN;

data STATSAS_3;
 merge STATSAS_21(in=a) mgr_1(in=b);
     by mgr_empno;
     if a and b then output;
     keep ename job dname loc;
run;
proc sort data=STATSAS_3;
     by ename;
run;
PROC PRINT;RUN;

Results

ename	job	dname	loc
BLAKE	MANAGER	SALES	CHICAGO
CLARK	MANAGER	ACCOUNTING	NEW YORK
FORD	ANALYST	RESEARCH	DALLAS
JONES	MANAGER	RESEARCH	DALLAS
KING	PRESIDEN	ACCOUNTING	NEW YORK
SCOTT	ANALYST	RESEARCH	DALLAS

10. Python Dfply Package

Python Programming

# [참고] 함수 사용. (52번 예제 참고) : AttributeError: 'DataFrame' object has no attribute 'ix'
@pipe
def pull_list(df, column=-1):
    return df.loc[:, column]

emp >> \
  filter_by(X.empno.isin( emp >> select(X.mgr) >> pull_list("mgr"))) >> \
  inner_join(dept, by="deptno")                                      >> \
  select(~X.job, ~X.hiredate, ~X.comm)

Results

empno	ename	mgr	sal	deptno	dname	loc
7566	JONES	7839.0	2975	20	RESEARCH	DALLAS
7788	SCOTT	7566.0	3000	20	RESEARCH	DALLAS
7902	FORD	7566.0	3000	20	RESEARCH	DALLAS
7698	BLAKE	7839.0	2850	30	SALES	CHICAGO
7782	CLARK	7839.0	2450	10	ACCOUNTING	NEW YORK
7839	KING	NaN	5000	10	ACCOUNTING	NEW YORK

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

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

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

[데이터 추출] 비상관 서브쿼리(Uncorrelated Subquery) 와 상관 서브쿼리(Correlated Subquery)를 만족하는 데이터 추출 - 비등가조인(NON-EQUI JOIN) 데이터 결합 - 102 (0)	2022.09.29
[데이터 추출] 내부조인(Inner join)을 만족하는 데이터 추출 - 101 (0)	2022.09.29
[데이터 삭제] Where 조건절을 만족하는 데이터 삭제-비상관서브쿼리 - 99 (0)	2022.09.28
[데이터 변환] 조건절을 만족하는 데이터 변경 - 98 (0)	2022.09.28
[데이터 추출] 비등가 조인에 해당하는 데이터 추출 - 97 (1)	2022.09.28

[데이터 추출] 비상관 서브쿼리(Uncorrelated Subqueyr) 를 만족하는 데이터 추출 - In 연산자와 내부조인(Inner Join) - 100

100. Display employee name, job, deptname, location for all who are working as managers.

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)' 카테고리의 다른 글

댓글

티스토리툴바

[데이터 추출] 비상관 서브쿼리(Uncorrelated Subqueyr) 를 만족하는 데이터 추출 - In 연산자와 내부조인(Inner Join) - 100

100. Display employee name, job, deptname, location for all who are working as managers.

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)' 카테고리의 다른 글

관련글

댓글

티스토리툴바