[데이터 추출] 내부 조인(Inner Join)을 만족하는 데이터 추출

포스팅 목차

95. Display those employees who are working in the same dept where his manager is working.

* 담당 관리자가 근무하는 부서와 동일한 부서에 근무하는 직원의 정보를 출력하시오.

파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
내부 조인(Inner Join)을 만족하는 데이터 추출
유사문제 : 91, 94, 95, 174, 210번

Oracle : 내부 조인, 상관 서브쿼리
파이썬 Pandas : emp.merge()
R 프로그래밍 : merge(), names(), %like% - 변수명 선택
R Dplyr Package : inner_join()
R sqldf Package : 내부 조인, 상관 서브쿼리
Python pandasql Package : 내부 조인, 상관 서브쿼리
R data.table Package : DT syntax 방식, data.table::merge() 방식, grep()
SAS Proc SQL : 내부 조인, 상관 서브쿼리
SAS Data Step : MERGE 구문, IF 조건문
Python Dfply Package : @pipe & def 사용자 정의 함수(inner_join_merge)
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

emp 테이블을 self join으로 결합하여서 해당 직원의 부서와 담당 관리자가 속한 부서가 동일한 직원의 정보를 출력한다.

Oracle Programming

select e.*
from   emp e,emp p 
where  e.mgr    = p.empno 
  and  e.deptno = p.deptno;

* emp 테이블을 상관 서브쿼리로 조회하여서 해당 직원의 부서와 담당 관리자가 속한 부서가 동일한 직원의 정보를 출력한다.

Oracle Programming

select * 
from   emp e 
where  deptno = (select deptno from emp where empno=e.mgr);

2. Python Pandas(파이썬)

emp 테이블을 self join으로 결합하여서 해당 직원의 부서와 담당 관리자가 속한 부서가 동일한 직원의 정보를 출력한다. filter 함수에 정규식을 사용하여서 변수명이 ‘x’ 형태를 포함하거나 ‘sal‘ 형태를 포함하는 기준 emp 테이블의 변수를 선택한다.

Python Programming

emp.merge(emp, how='inner',left_on=("mgr","deptno"), right_on=("empno","deptno")).filter(regex='_x|sal_').head()

Results

empno_x	ename_x	job_x	mgr_x	hiredate_x	sal_x	comm_x	sal_y
7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	3000
7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	2850
7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	2850
7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	2850
7844	TURNER	SALESMAN	7698.0	1981/09/08	1500	0.0	2850

3. R Programming (R Package)

emp 테이블을 self join으로 결합(merge)하여서 해당 직원의 부서와 담당 관리자가 속한 부서가 동일한 직원의 정보를 출력한다. %like% 함수를 사용하여서 변수명이 ‘.x’ 로 끝나는 기준 emp 테이블의 변수를 선택한다.

R Programming

%%R

withmooc <- merge(emp,
             emp,
             by.x=c("mgr","deptno"),
             by.y=c("empno","deptno"),all=F)

withmooc[,names(withmooc) %like% ".x"]

Results

   ename.x    job.x hiredate.x sal.x comm.x
1    SCOTT  ANALYST 1982-12-09  3000     NA
2     FORD  ANALYST 1981-12-03  3000     NA
3     WARD SALESMAN 1981-02-22  1250    500
4    ALLEN SALESMAN 1981-02-20  1600    300
5    JAMES    CLERK 1981-12-03   950     NA
6   MARTIN SALESMAN 1981-09-28  1250   1400
7   TURNER SALESMAN 1981-09-08  1500      0
8   MILLER    CLERK 1982-01-23  1300     NA
9    ADAMS    CLERK 1983-01-12  1100     NA
10   CLARK  MANAGER 1981-01-09  2450     NA
11   SMITH    CLERK 1980-12-17   800     NA

4. R Dplyr Package

emp 테이블을 self join으로 내부조인(inner_join)하여서 해당 직원의 부서와 담당 관리자가 속한 부서가 동일한 직원의 정보를 출력한다. ends_with 함수를 사용하여서 변수명이 ‘_emp’ 로 끝나는 기준 emp 테이블의 변수를 선택한다.

R Programming

%%R

emp %>% 
  inner_join (emp , by = c("mgr" = "empno","deptno") , suffix=c("_emp", "_mgr")) %>%
  dplyr::select(ends_with("_emp"), "sal_mgr") %>%
  head()

Results

# A tibble: 6 x 6
  ename_emp job_emp  hiredate_emp sal_emp comm_emp sal_mgr
  <chr>     <chr>    <date>         <dbl>    <dbl>   <dbl>
1 SMITH     CLERK    1980-12-17       800       NA    3000
2 ALLEN     SALESMAN 1981-02-20      1600      300    2850
3 WARD      SALESMAN 1981-02-22      1250      500    2850
4 MARTIN    SALESMAN 1981-09-28      1250     1400    2850
5 CLARK     MANAGER  1981-01-09      2450       NA    5000
6 SCOTT     ANALYST  1982-12-09      3000       NA    2975

5. R sqldf Package

emp 테이블을 self join으로 결합하여서 해당 직원의 부서와 담당 관리자가 속한 부서가 동일한 직원의 정보를 출력한다.

R Programming

%%R

sqldf(" select e.ename 
        from   emp e,emp p 
        where  e.mgr=p.empno 
          and  p.deptno=e.deptno;")

Results

    ename
1   SMITH
2   ALLEN
3    WARD
4  MARTIN
5   CLARK
6   SCOTT
7  TURNER
8   ADAMS
9   JAMES
10   FORD
11 MILLER

emp 테이블을 상관 서브쿼리로 조회하여서 해당 직원의 부서와 담당 관리자가 속한 부서가 동일한 직원의 정보를 출력한다.

R Programming

%%R

sqldf("select * from emp e 
       where  deptno = (select deptno from emp where empno=e.mgr);")

Results

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

6. Python pandasql Package

Python Programming

ps.sqldf("select e.ename from emp e,emp p where e.mgr=p.empno and p.deptno=e.deptno;").head()

Results

ename

SMITH

ALLEN

WARD

MARTIN

CLARK

Python Programming

ps.sqldf("select * from emp e \
          where  deptno = (select deptno from emp where empno=e.mgr);").head()

Results

empno	ename	job	mgr	hiredate	sal	comm	deptno
7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20
7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30
7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30
7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30
7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10

7. R data.table Package

변수 선택 : https://stackoverflow.com/questions/12391950/select-assign-to-data-table-when-variable-names-are-stored-in-a-character-vect

emp 테이블을 self join으로 조인(DT 방식 조인)하여서 해당 직원의 부서와 담당 관리자가 속한 부서가 동일한 직원의 정보를 출력한다. 변수 위치를 지정하여서 기준테이블의 변수를 선택한다.

R Programming

%%R

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

DT[DT, nomatch=NULL, on = .( mgr= empno, deptno=deptno), ][1:7 , empno:deptno]

Results

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

emp 테이블을 self join으로 조인(DT 방식 조인)하여서 해당 직원의 부서와 담당 관리자가 속한 부서가 동일한 직원의 정보를 출력한다. 정규식을 사용하여서 self Join의 우측 테이블의 변수에 해당하는 불필요한 변수를 제외한 기준테이블의 변수를 선택한다.

R Programming

%%R

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

withmooc <- DT[DT, nomatch=NULL, on = .( mgr= empno, deptno=deptno), ]

mycols <- grep('^i.', colnames(withmooc), value=TRUE)
withmooc[, !..mycols]

Results

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

8. SAS Proc SQL

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select e.*
    from   emp e,emp P
    where  e.mgr    = p. empno
      and  p.deptno = e.deptno;
QUIT;
PROC PRINT;RUN;

Results

empno	ename	job	mgr	hiredate	sal	comm	deptno
7369	SMITH	CLERK	7902	1980-12-17	800	.	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30
7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30
7782	CLARK	MANAGER	7839	1981-01-09	2450	.	10
7788	SCOTT	ANALYST	7566	1982-12-09	3000	.	20
7844	TURNER	SALESMAN	7698	1981-09-08	1500	0	30
7876	ADAMS	CLERK	7788	1983-01-12	1100	.	20
7900	JAMES	CLERK	7698	1981-12-03	950	.	30
7902	FORD	ANALYST	7566	1981-12-03	3000	.	20
7934	MILLER	CLERK	7782	1982-01-23	1300	.	10

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select e.* 
    from   emp e 
    where  deptno = (select deptno from emp where empno=e.mgr);
QUIT;
PROC PRINT;RUN;

Results

empno	ename	job	mgr	hiredate	sal	comm	deptno
7369	SMITH	CLERK	7902	1980-12-17	800	.	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30
7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30
7782	CLARK	MANAGER	7839	1981-01-09	2450	.	10
7788	SCOTT	ANALYST	7566	1982-12-09	3000	.	20
7844	TURNER	SALESMAN	7698	1981-09-08	1500	0	30
7876	ADAMS	CLERK	7788	1983-01-12	1100	.	20
7900	JAMES	CLERK	7698	1981-12-03	950	.	30
7902	FORD	ANALYST	7566	1981-12-03	3000	.	20
7934	MILLER	CLERK	7782	1982-01-23	1300	.	10

9. SAS Data Step

SAS Programming

%%SAS sas

PROC SORT DATA=emp OUT=EMP_1(RENAME=( mgr = MGR_EMPNO ) );
     BY deptno mgr;
RUN;

PROC SORT DATA=emp OUT=EMP_2(RENAME=( empno = MGR_EMPNO )
                              KEEP=deptno empno) NODUPKEY;
     BY deptno empno;
RUN;

DATA STATSAS_2;
 MERGE EMP_1(IN=A) EMP_2(IN=B);
     BY deptno MGR_EMPNO;
     IF A AND B THEN OUTPUT;
RUN;
proc sort data=STATSAS_2;
     by empno;
run;
PROC PRINT;RUN;

Results

empno	ename	job	MGR_EMPNO	hiredate	sal	comm	deptno
7369	SMITH	CLERK	7902	1980-12-17	800	.	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30
7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30
7782	CLARK	MANAGER	7839	1981-01-09	2450	.	10
7788	SCOTT	ANALYST	7566	1982-12-09	3000	.	20
7844	TURNER	SALESMAN	7698	1981-09-08	1500	0	30
7876	ADAMS	CLERK	7788	1983-01-12	1100	.	20
7900	JAMES	CLERK	7698	1981-12-03	950	.	30
7902	FORD	ANALYST	7566	1981-12-03	3000	.	20
7934	MILLER	CLERK	7782	1982-01-23	1300	.	10

10. Python Dfply Package

inner_join : 56번/94번 참고

Python Programming

@pipe
def inner_join_merge(df, other, left_on,right_on,suffixes):

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

emp >> \
  inner_join_merge( emp, left_on  = ["mgr","deptno"], right_on = ["empno","deptno"], suffixes=("_emp","_mgr")) >> \
  select(ends_with("_emp"), X.sal_mgr) >> \
  head()

Results

empno_emp	ename_emp	job_emp	mgr_emp	hiredate_emp	sal_emp	comm_emp	sal_mgr
7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	3000
7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	2850
7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	2850
7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	2850
7844	TURNER	SALESMAN	7698.0	1981/09/08	1500	0.0	2850

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

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

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

[데이터 추출] 비등가 조인에 해당하는 데이터 추출 - 97 (1)	2022.09.28
[데이터 추출] 값이 존재하지 않는 NULL 데이터 추출 - 96 (0)	2022.09.28
[데이터 추출] 비등가 내부 조인(Inner Join)을 만족하는 데이터 추출 - 94 (0)	2022.09.27
[데이터 추출] 비상관 서브쿼리(Uncorrelated Subqueyr) 를 만족하는 데이터 추출 - 비교 연산자(=) 과 Between 연산자 - 93 (0)	2022.09.27
[데이터 추출] 비상관 서브쿼리(Uncorrelated Subqueyr) 를 만족하는 데이터 추출 - 비교 연산자(=) - 92 (0)	2022.09.27

[데이터 추출] 내부 조인(Inner Join)을 만족하는 데이터 추출 - 95

95. Display those employees who are working in the same dept where his manager is working.

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

댓글

티스토리툴바

[데이터 추출] 내부 조인(Inner Join)을 만족하는 데이터 추출 - 95

95. Display those employees who are working in the same dept where his manager is working.

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

관련글

댓글

티스토리툴바