[데이터 전처리] 비상관 서브쿼리(Uncorrelated Subqueyr) 를 만족하는 데이터 추출 - 상하 결합 UNION 연산자

포스팅 목차

86. Display those who are not managers and who are managers any one.

* 관리자 이 외의 직원과 관리자 직무를 수행하는 직원을 출력하시오.

파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[데이터 추출] 비상관 서브쿼리(Uncorrelated Subqueyr) 를 만족하는 데이터 추출 - 상하 결합 UNION 연산자

Oracle : in 연산자, not in 연산자, union
파이썬 Pandas : pd.concat(), isin(), NOT('~') 연산자, notnull(), drop_duplicates(), reset_index()
R 프로그래밍 : unique(), rbind(), NOT('!') 연산자, %in%, unlist()
R Dplyr Package : dplyr::union(), dplyr::filter(), NOT('!') 연산자, %in%
R sqldf Package : in 연산자, not in 연산자, union
Python pandasql Package : in 연산자, not in 연산자, union, distinct
R data.table Package : data.table::funion(), NOT('!') 연산자, %in%
SAS Proc SQL : in 연산자, not in 연산자, union
SAS Data Step : SET 구문, Merge 구문, IF 조건문
Python Dfply Package : union (), filter_by(), NOT('~') 연산자, isin()
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

emp테이블에서 관리자('mgr') 사원번호를 조회하여서 관리자('mgr') 역할을 수행하는 직원과 관리자('mgr') 역할을 수행하지 않는 직원을 선택한다.

union은 개별적으로 선택된 데이터에 대하여 중복을 제거하고 상하 결합을 수행한다.

Oracle Programming

select * from emp where empno     in (select mgr from emp) 
union
select * from emp where empno not in (select mgr from emp where mgr is not null);

Results

2. Python Pandas(파이썬)

emp테이블에서 관리자('mgr') 사원번호를 조회하여서 관리자('mgr') 역할을 수행하는 직원과 관리자('mgr') 역할을 수행하지 않는 직원을 선택한다.

pd.concat은 개별적으로 선택된 데이터에 대하여 상하 결합을 수행하고, drop_duplicates()으로 중복을 제거한다.

Programming

pd.concat( [(emp[  emp['empno'].isin(emp['mgr']) ]) ,
            (emp[~ emp['empno'].isin(emp[emp['mgr'].notnull() ] ['mgr']) ])] ,
           ignore_index=True ).drop_duplicates().reset_index(drop=True).head()

Results

empno	ename	job	mgr	hiredate	sal	comm	deptno
7566	JONES	MANAGER	7839.0	1981/04/02	2975		20
7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10
7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20
7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10

3. R Programming (R Package)

emp테이블에서 관리자('mgr') 사원번호를 조회하여서 관리자('mgr') 역할을 수행하는 직원과 관리자('mgr') 역할을 수행하지 않는 직원을 선택한다.

rbind 함수는 개별적으로 선택된 데이터에 대하여 상하 결합을 수행하고, unique()으로 중복을 제거한다.

Programming

%%R
unique( rbind(emp[ (emp$empno %in% unlist(emp[, "mgr" ])),] ,
              emp[ ! (emp$empno %in% unlist(emp[!is.na(emp$mgr), "mgr" ])),]  )) %>% head()

Results

# A tibble: 6 x 8
  empno ename job         mgr hiredate     sal  comm deptno
                  
1  7566 JONES MANAGER    7839 1981-04-02  2975    NA     20
2  7698 BLAKE MANAGER    7839 1981-03-01  2850    NA     30
3  7782 CLARK MANAGER    7839 1981-01-09  2450    NA     10
4  7788 SCOTT ANALYST    7566 1982-12-09  3000    NA     20
5  7839 KING  PRESIDENT    NA 1981-11-17  5000    NA     10
6  7902 FORD  ANALYST    7566 1981-12-03  3000    NA     20

4. R Dplyr Package

emp테이블에서 관리자('mgr') 사원번호를 조회하여서 관리자('mgr') 역할을 수행하는 직원과 관리자('mgr') 역할을 수행하지 않는 직원을 선택한다.

dplyr::union는 개별적으로 선택된 데이터에 대하여 상하 결합을 수행한다.

Programming

%%R

  dplyr::union( emp %>% dplyr::filter( empno %in% ( emp  %>% dplyr::select(mgr) %>% unlist() ) ) ,

                emp %>% dplyr::filter(! empno %in% ( emp %>% dplyr::filter(!is.na(mgr)) %>% dplyr::select(mgr) %>% unlist() ) )
               ) %>% 
  head()

Results

# A tibble: 6 x 8
  empno ename job         mgr hiredate     sal  comm deptno
                  
1  7566 JONES MANAGER    7839 1981-04-02  2975    NA     20
2  7698 BLAKE MANAGER    7839 1981-03-01  2850    NA     30
3  7782 CLARK MANAGER    7839 1981-01-09  2450    NA     10
4  7788 SCOTT ANALYST    7566 1982-12-09  3000    NA     20
5  7839 KING  PRESIDENT    NA 1981-11-17  5000    NA     10
6  7902 FORD  ANALYST    7566 1981-12-03  3000    NA     20

5. R sqldf Package

https://en.wikibooks.org/wiki/SQL_Dialects_Reference/Functions_and_expressions/String_functions

emp테이블에서 관리자('mgr') 사원번호를 조회하여서 관리자('mgr') 역할을 수행하는 직원과 관리자('mgr') 역할을 수행하지 않는 직원을 선택한다.

union은 개별적으로 선택된 데이터에 대하여 중복을 제거하고 상하 결합을 수행한다.

Programming

%%R

sqldf("select * from emp 
       where  empno in (select mgr from emp) 

       union
       select * from emp 
       where  empno not in (select mgr from emp where mgr is not null);") %>% head()

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  7566  JONES  MANAGER 7839 1981-04-02 2975   NA     20
5  7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30
6  7698  BLAKE  MANAGER 7839 1981-03-01 2850   NA     30

emp테이블에서 관리자('mgr') 사원번호를 조회하여서 관리자('mgr') 역할을 수행하는 직원과 관리자('mgr') 역할을 수행하지 않는 직원을 선택한다.

union은 개별적으로 선택된 데이터에 대하여 중복을 제거하고 상하 결합을 수행한다.

Programming

%%R

sqldf("select m.* 
       from   emp e,emp m 
       where  m.empno = e.mgr 

       UNION
       select * from emp 
       where  ename not in (select distinct(m.ename) from emp e,emp m where m.empno=e.mgr)") %>% head()

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  7566  JONES  MANAGER 7839 1981-04-02 2975   NA     20
5  7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30
6  7698  BLAKE  MANAGER 7839 1981-03-01 2850   NA     30

6. Python pandasql Package

emp테이블에서 관리자('mgr') 사원번호를 조회하여서 관리자('mgr') 역할을 수행하는 직원과 관리자('mgr') 역할을 수행하지 않는 직원을 선택한다.

union은 개별적으로 선택된 데이터에 대하여 중복을 제거하고 상하 결합을 수행한다.

Programming

ps.sqldf("select * from emp where empno in(select mgr from emp) \
          union \
          select * from emp where empno not in(select mgr from emp where mgr is not null); ").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
7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30

Programming

ps.sqldf("select m.* from emp e,emp m                           \
          where m.empno=e.mgr                                   \
          UNION                                                 \
          select * from emp                                     \
          where ename not in (select distinct(m.ename) from emp e,emp m where m.empno=e.mgr) ").head()

Results

empno	ename	job	mgr	hiredate	comm	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
7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30

7. R data.table Package

emp테이블에서 관리자('mgr') 사원번호를 조회하여서 관리자('mgr') 역할을 수행하는 직원과 관리자('mgr') 역할을 수행하지 않는 직원을 선택한다.

data.table::funion 을 사용하여서 개별적으로 선택된 데이터에 대하여 상하 결합을 수행한다.

Programming

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

data.table::funion(DT[ (empno %in% DT[,mgr])  , ] , DT[ !(empno %in% DT[!is.na(mgr), mgr ])  , ] )[1:7, ]

Results

   empno ename       job  mgr   hiredate  sal comm deptno
1:  7566 JONES   MANAGER 7839 1981-04-02 2975   NA     20
2:  7698 BLAKE   MANAGER 7839 1981-03-01 2850   NA     30
3:  7782 CLARK   MANAGER 7839 1981-01-09 2450   NA     10
4:  7788 SCOTT   ANALYST 7566 1982-12-09 3000   NA     20
5:  7839  KING PRESIDENT   NA 1981-11-17 5000   NA     10
6:  7902  FORD   ANALYST 7566 1981-12-03 3000   NA     20
7:  7369 SMITH     CLERK 7902 1980-12-17  800   NA     20

8. SAS Proc SQL

emp테이블에서 관리자('mgr') 사원번호를 조회하여서 관리자('mgr') 역할을 수행하는 직원과 관리자('mgr') 역할을 수행하지 않는 직원을 선택한다.

union은 개별적으로 선택된 데이터에 대하여 중복을 제거하고 상하 결합을 수행한다.

Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select * 
    from   emp 
    where  empno     in (select mgr from emp) 

    union
    select * 
    from   emp 
    where  empno not in (select mgr from emp where mgr is not null);;
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
7566	JONES	MANAGER	7839	1981-04-02	2975	.	20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981-03-01	2850	.	30
7782	CLARK	MANAGER	7839	1981-01-09	2450	.	10
7788	SCOTT	ANALYST	7566	1982-12-09	3000	.	20
7839	KING	PRESIDEN	.	1981-11-17	5000	.	10
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

Programming

%%SAS sas

PROC SORT DATA=emp OUT=EMP_1(RENAME=EMPNO=EMPNO_BASE);
     BY EMPNO;
RUN;

PROC SORT DATA=emp OUT=EMP_2(RENAME=mgr=EMPNO_BASE) NODUPKEY;
     BY mgr;
RUN;

DATA STATSAS_1;
 MERGE EMP_1(IN=A) EMP_2(IN=B);
     BY EMPNO_BASE;
     IF A AND B;
RUN;
PROC PRINT;RUN;

PROC SORT DATA=emp OUT=EMP_1(RENAME=EMPNO=EMPNO_BASE);
     BY EMPNO;
RUN;

PROC SORT DATA=emp OUT=EMP_2(RENAME=mgr=EMPNO_BASE) NODUPKEY;
     BY mgr;
     where mgr is not null;
RUN;

DATA STATSAS_2;
 MERGE EMP_1(IN=A) EMP_2(IN=B);
     BY EMPNO_BASE;
     IF A AND B=0;
RUN;
PROC PRINT;RUN;

DATA STATSAS_3;
 SET STATSAS_1
     STATSAS_2;
RUN;
PROC PRINT;RUN;

Results

empno_base	ename	job	mgr	hireate	sal	comm	deptno	empno
7566	SCOTT	ANALYST	7839	1982-12-09	3000	.	20	7788
7698	ALLEN	SALESMAN	7839	1981-02-20	1600	300	30	7499
7782	MILLER	CLERK	7839	1982-01-23	1300	.	10	7934
7788	ADAMS	CLERK	7566	1983-01-12	1100	.	20	7876
7839	JONES	MANAGER	.	1981-04-02	2975	.	20	7566
7902	SMITH	CLERK	7566	1980-12-17	800	.	20	7369

empno_base	ename	job	mgr	hireate	sal	comm	deptno	empno
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	.
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	.
7934	MILLER	CLERK	7782	1982-01-23	1300	.	10	.

empno_base	ename	job	mgr	hireate	sal	comm	deptno	empno
7566	SCOTT	ANALYST	7839	1982-12-09	3000	.	20	7788
7698	ALLEN	SALESMAN	7839	1981-02-20	1600	300	30	7499
7782	MILLER	CLERK	7839	1982-01-23	1300	.	10	7934
7788	ADAMS	CLERK	7566	1983-01-12	1100	.	20	7876
7839	JONES	MANAGER	.	1981-04-02	2975	.	20	7566
7902	SMITH	CLERK	7566	1980-12-17	800	.	20	7369
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	.
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	.
7934	MILLER	CLERK	7782	1982-01-23	1300	.	10	.

10. Python Dfply Package

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"))) >> \
  union ( emp >> filter_by (~ X.empno.isin( emp >> filter_by(X.mgr.notnull()) >> select(X.mgr) >> pull_list("mgr"))) ) >>\
  head()

Results

empno	ename	job	mgr	hiredate	sal	comm	deptno
7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10
7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20
7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10

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

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

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

[문자 함수] 문자 검색 함수 - 88 (1)	2022.09.26
[문자 함수] 문자 길이 측정 - 87 (1)	2022.09.26
[데이터 전처리 비교- 데이터 추출] 비상관 서브쿼리를 만족하는 데이터 추출 & 부등호 연산 - 85 (0)	2022.06.13
[데이터 전처리 비교- 테이블 결합] LEFT JOIN (왼쪽 결합) 으로 2개 테이블 결합 - 오른쪽 테이블에 미존재 데이터 추출 - 84 (0)	2021.11.15
[데이터 전처리 비교- 데이터 추출] 비상관 서브쿼리를 만족하는 데이터 추출 - NOT IN 연산자 - 83 (0)	2021.11.10

[데이터 전처리] 비상관 서브쿼리(Uncorrelated Subqueyr) 를 만족하는 데이터 추출 - 상하 결합 UNION 연산자 - 86

86. Display those who are not managers and who are managers any one.

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) 를 만족하는 데이터 추출 - 상하 결합 UNION 연산자 - 86

86. Display those who are not managers and who are managers any one.

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

관련글

댓글

티스토리툴바