[비상관 서브쿼리(Not in 연산자)] 특정 조건에 해당하는 데이터 삭제

포스팅 목차

120. Delete those records from EMP table whose deptno not available in dept table?

* EMP 사원관리 테이블에서 dept 부서관리 테이블에 존재하지 않는 부서코드(‘depnot’)가 부여된 사원 정보를 삭제하시오.

파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
유사문제 : 86 번 / 119번
[데이터 추출] 특정 조건에 해당하는 데이터 삭제 - 비상관 서브쿼리(Not in 연산자)
데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql

Oracle : 비상관 서브쿼리, Delete 구문, Not in
파이썬 Pandas : isin(), notnull(), describe(), pd.merge(), query(), notnull()
R 프로그래밍 : %in%, unlist(), merge(), subset(), count(), is.na(), NOT('!') 연산자
R Dplyr Package : dplyr::left_join(), dplyr::tally(), NOT('!') 연산자
R sqldf Package : 비상관 서브쿼리, Delete 구문, Not in, Left Join, is not null
Python pandasql Package : 비상관 서브쿼리, in 연산자, Left Join, is not null, drop() - 변수 삭제
R data.table Package : %in%
SAS Proc SQL : 비상관 서브쿼리, Delete 구문, Not in, in 연산자, Left Join, is not null
SAS Data Step : Merge 구문, If 조건문
Python Dfply Package : left_join(), summarize(), count(), notnull()
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

서브쿼리를 사용하여서 emp 테이블의 부서번호(‘deptno’)가 dept 테이블의 부서코드에 존재하지 않는 직원 정보를 삭제한다.

Oracle Programming

create table emp_copy as
  select empno,ename,job,mgr,hiredate,sal,comm,
         case when empno=7934 then 100 else deptno end as deptno 
  from   emp;

delete from emp_copy 
where  deptno not in (select deptno from dept);

select *
from   emp_copy;

2. Python Pandas(파이썬)

isin 방식 이외 Join 방식으로 처리 가능

isin 함수를 사용하여서 emp 테이블의 부서번호(‘deptno’)가 dept 테이블의 부서코드에 존재하는 직원 정보를 선택한다.

코드 확인을 위하여 MILLER(7934)의 부서번호를 100으로 변경하였다. 부서번호 ‘100’은 부서테이블(‘DEPT’)에 존재하지 않으므로 선택되지 않는다.
isin 방식 이외 Join 방식으로 처리 가능

Python Programming

withmooc= emp.copy()

withmooc = ps.sqldf("select empno,ename,job,mgr,hiredate,sal,comm,              \
                     case when empno=7934 then 100 else deptno end as deptno from withmooc")
# display(withmooc)

withmooc[ withmooc['deptno'].isin(dept[dept['deptno'].notnull() ] ['deptno']) ].head()

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20
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
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30

부서코드테이블(‘DEPT’)에서 부서코드(‘deptno’)를 기준으로 관측치를 카운트 후 emp 테이블에 merge 함수로 left join 을 수행하여 부서코드테이블에 부서번호가 존재하는 직원들의 정보를 선택한다.

코드 확인을 위하여 MILLER(7934)의 부서번호를 100으로 변경하였다. 부서번호 ‘100’은 부서테이블(‘DEPT’)에 존재하지 않으므로 선택되지 않는다.

Python Programming

withmooc= emp.copy()

withmooc = ps.sqldf("select empno,ename,job,mgr,hiredate,sal,comm,                        \
                     case when empno=7934 then 100 else deptno end as deptno from withmooc")

pd.merge( withmooc ,
          ( dept.groupby(dept['deptno'])['deptno'].describe()[['count']].reset_index() ),
          "left", 
          left_on=['deptno'], right_on=['deptno']
        ).query('count.notnull()', engine='python').head()

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	count
0	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20	1.0
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	1.0
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	1.0
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20	1.0
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	1.0

3. R Programming (R Package)

%in% 함수를 사용하여서 emp 테이블의 부서번호(‘deptno’)가 dept 테이블의 부서코드에 존재하는 직원 정보를 선택한다.

코드 확인을 위하여 MILLER(7934)의 부서번호를 100으로 변경하였다. 부서번호 ‘100’은 부서테이블(‘DEPT’)에 존재하지 않으므로 선택되지 않는다.

R Programming

%%R
withmooc = emp

withmooc <- sqldf(c("update withmooc set deptno = 100 WHERE empno = 7934",
                    "select * from withmooc"))

withmooc[ (withmooc$deptno %in% unlist(dept[, "deptno" ])),] %>% 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

부서코드테이블(‘DEPT’)에서 부서코드(‘deptno’)별로 관측치를 카운트 후 emp 테이블을 기준으로 merge 함수로 left join 을 수행하여 부서코드테이블에 부서번호가 존재하는 직원들의 정보를 선택한다.

코드 확인을 위하여 MILLER(7934)의 부서번호를 100으로 변경하였다. 부서번호 ‘100’은 부서테이블(‘DEPT’)에 존재하지 않으므로 선택되지 않는다.

R Programming

%%R
withmooc = emp

withmooc <- sqldf(c("update withmooc set deptno = 100 WHERE empno = 7934",
                    "select * from withmooc"))
## print(withmooc)

subset( merge(withmooc,count(dept,deptno),by.x=c("deptno"),by.y=c("deptno"),all.x = TRUE) ,
        ! is.na(n) ) %>% head()

Results

  deptno empno ename       job  mgr   hiredate  sal comm n
1     10  7782 CLARK   MANAGER 7839 1981-01-09 2450   NA 1
2     10  7839  KING PRESIDENT   NA 1981-11-17 5000   NA 1
3     20  7369 SMITH     CLERK 7902 1980-12-17  800   NA 1
4     20  7876 ADAMS     CLERK 7788 1983-01-12 1100   NA 1
5     20  7566 JONES   MANAGER 7839 1981-04-02 2975   NA 1
6     20  7902  FORD   ANALYST 7566 1981-12-03 3000   NA 1

4. R Dplyr Package

부서코드 테이블(‘DEPT’)에서 부서코드(‘deptno’)별로 관측치를 카운트 후 emp 테이블을 기준으로 left_join 을 수행하여 부서코드테이블에 부서번호가 존재하는 직원들의 정보를 선택한다.

코드 확인을 위하여 MILLER(7934)의 부서번호를 100으로 변경하였다. 부서번호 ‘100’은 부서테이블(‘DEPT’)에 존재하지 않으므로 선택되지 않는다.

R Programming

%%R

withmooc = emp

withmooc <- sqldf(c("update withmooc set deptno = 100 WHERE empno = 7934",
                    "select * from withmooc"))
## print(withmooc)

withmooc %>% 
  dplyr::left_join( ( dept %>%
                        dplyr::group_by(deptno) %>%
                        dplyr::tally() ),
                     by = c("deptno" = "deptno")
                   ) %>%
  dplyr::filter(! is.na(n)) %>%
  head()

Results

  empno  ename      job  mgr   hiredate  sal comm deptno n
1  7369  SMITH    CLERK 7902 1980-12-17  800   NA     20 1
2  7499  ALLEN SALESMAN 7698 1981-02-20 1600  300     30 1
3  7521   WARD SALESMAN 7698 1981-02-22 1250  500     30 1
4  7566  JONES  MANAGER 7839 1981-04-02 2975   NA     20 1
5  7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30 1
6  7698  BLAKE  MANAGER 7839 1981-03-01 2850   NA     30 1

5. R sqldf Package

서브쿼리를 사용하여서 emp 테이블의 부서번호(‘deptno’)가 dept 테이블의 부서코드에 존재하지 않는 직원 정보를 삭제한다.

Sqlite 의 update / delete 구문 : select / where 구문으로 처리 가능 (98번 / 119번)
코드 확인을 위하여 MILLER(7934)의 부서번호를 100으로 변경하였다. 부서번호 ‘100’은 부서테이블(‘DEPT’)에 존재하지 않으므로 선택되지 않는다.

R Programming

%%R

withmooc = emp

withmooc <- sqldf(c("update withmooc set deptno = 100 WHERE empno = 7934",
                    "select * from withmooc"))

# print(withmooc)

withmooc <- sqldf(c("delete from withmooc where deptno not in(select deptno from dept);",
                    "select * from withmooc"))

print(withmooc[1:5, ])

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

코드 확인을 위하여 MILLER(7934)의 부서번호를 100으로 변경하였다. 부서번호 ‘100’은 부서테이블(‘DEPT’)에 존재하지 않으므로 선택되지 않는다.

R Programming

%%R
withmooc = emp

withmooc <- sqldf(c("update withmooc set deptno = 100 WHERE empno = 7934",
                    "select * from withmooc"))

## print(withmooc)

sqldf("select * 
       from withmooc a 
            left join ( select deptno,count(*) dept_cnt
                        from   dept
                        group by deptno) b
                 on    a.deptno = b.deptno
       where b.deptno is not null")

Results

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

6. Python pandasql Package

서브쿼리를 사용하여서 emp 테이블의 부서번호(‘deptno’)가 dept 테이블의 부서코드에 존재하는 직원 정보를 선택한다.

현재 pandasql에서 update 제대로 작동 안 함.

Python Programming

withmooc= emp.copy()

withmooc = ps.sqldf("select empno,ename,job,mgr,hiredate,sal,comm,              \
                     case when empno=7934 then 100 else deptno end as deptno from withmooc")
# display(withmooc)

ps.sqldf("select * from withmooc where deptno in (select deptno from dept)").head()

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20
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
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30

Python Programming

ps.sqldf("select *                                          \
       from emp a                                           \
            left join ( select deptno,count(*) dept_cnt     \
                        from   dept                         \
                        group by deptno) b                  \
                 on    a.deptno = b.deptno                  \
       where b.deptno is not null").drop(['hiredate','comm'],axis=1).head()

Results

	empno	ename	job	mgr	sal	deptno	deptno	dept_cnt
0	7369	SMITH	CLERK	7902.0	800	20	20	1
1	7499	ALLEN	SALESMAN	7698.0	1600	30	30	1
2	7521	WARD	SALESMAN	7698.0	1250	30	30	1
3	7566	JONES	MANAGER	7839.0	2975	20	20	1
4	7654	MARTIN	SALESMAN	7698.0	1250	30	30	1

7. R data.table Package

%in% 함수를 사용하여서 emp 테이블의 부서번호(‘deptno’)가 dept 테이블의 부서코드에 존재하는 직원 정보를 선택한다.

R Programming

%%R

withmooc = emp

withmooc <- sqldf(c("update withmooc set deptno = 100 WHERE empno = 7934",
                    "select * from withmooc"))
DT          <- data.table(withmooc)

DT[ (deptno %in% dept_DT[,deptno])  , ][1:5, ]

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

8. SAS Proc SQL

서브쿼리를 사용하여서 emp 테이블의 부서번호(‘deptno’)가 dept 테이블의 부서코드에 존재하지 않는 직원 정보를 삭제한다.

SAS Programming

%%SAS sas

PROC SQL;
  create table emp_copy as
    select *
    from   emp;

  update emp_copy 
    set deptno = 100 WHERE empno = 7934;

  delete from emp_copy 
    where  deptno not in (select deptno from dept);
QUIT;
PROC PRINT DATA=emp_copy(OBS=5);RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7369	SMITH	CLERK	7902	1980-12-17	800	.	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	.	20
5	7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30

SAS Programming

%%SAS sas

PROC SQL;
  create table emp_copy as
    select *
    from   emp;

  update emp_copy 
    set deptno = 100 WHERE empno = 7934;

  create table STATSAS_1 as
    select *
    from   emp_copy 
    where  deptno in (select deptno from dept);
QUIT;

PROC PRINT DATA=STATSAS_1(OBS=5);RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7369	SMITH	CLERK	7902	1980-12-17	800	.	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	.	20
5	7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30

SAS Programming

%%SAS sas

PROC SQL;
  create table emp_copy as
    select *
    from   emp;

  update emp_copy 
    set deptno = 100 WHERE empno = 7934;

  create table STATSAS_2 as
    select A.* 
       from emp_copy a 
            left join ( select deptno,count(*) AS dept_cnt
                        from   dept
                        group by deptno) b
                 on    a.deptno = b.deptno
       where b.deptno is not null;
QUIT;
PROC PRINT DATA=STATSAS_2(OBS=5);RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7839	KING	PRESIDEN	.	1981-11-17	5000	.	10
2	7782	CLARK	MANAGER	7839	1981-01-09	2450	.	10
3	7902	FORD	ANALYST	7566	1981-12-03	3000	.	20
4	7876	ADAMS	CLERK	7788	1983-01-12	1100	.	20
5	7369	SMITH	CLERK	7902	1980-12-17	800	.	20

9. SAS Data Step

SAS Programming

%%SAS sas

DATA emp_copy;
 SET EMP;
     IF EMPNO = 7934 THEN DEPTNO = 100;
RUN;

PROC SORT DATA=emp_copy OUT=emp_copy_1;
     BY DEPTNO;
RUN;

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

DATA STATSAS_3;
 MERGE emp_copy_1(IN=A) dept_1(IN=B);
     BY DEPTNO;
     IF A AND B THEN OUTPUT;
RUN;

PROC PRINT DATA=STATSAS_3(OBS=5 drop=job hiredate comm mgr);RUN;

Results

OBS	empno	ename	sal	deptno	dname	loc
1	7782	CLARK	2450	10	ACCOUNTING	NEW YORK
2	7839	KING	5000	10	ACCOUNTING	NEW YORK
3	7369	SMITH	800	20	RESEARCH	DALLAS
4	7566	JONES	2975	20	RESEARCH	DALLAS
5	7788	SCOTT	3000	20	RESEARCH	DALLAS

10. Python Dfply Package

조건문 116번 참고

Python Programming

withmooc = emp >> mutate( deptno = make_symbolic(np.where)(X.empno == 7934, 1000, X.deptno) )

withmooc >> \
  left_join( (dept >> group_by('deptno') >> summarize( emp_cnt = X.deptno.count() )), by="deptno" ) >> \
  filter_by(X.emp_cnt.notnull()) >> \
  head()

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	emp_cnt
0	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20	1.0
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	1.0
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	1.0
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20	1.0
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	1.0

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

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

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

[비상관 서브쿼리(최대값)] 특정 조건을 만족하는 데이터 추출 - 결측치 처리 - 122 (오라클 SQL, R, Python, SAS) (0)	2022.12.13
[비상관 서브쿼리(최대값, 최소값)] 특정 조건을 만족하는 데이터 추출 - 121 (오라클 SQL, R, Python, SAS) (0)	2022.12.12
[상관 서브쿼리] 특정 조건에 해당하는 데이터 삭제 - 119 (오라클 SQL, R, Python, SAS) (0)	2022.12.11
[비상관 서브쿼리(IN 연산자)] 특정 조건을 만족하는 데이터 추출 - 118 (오라클 SQL, R, Python, SAS) (0)	2022.12.11
[데이터 전처리] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리 - 117 (오라클 SQL, R, Python, SAS) (0)	2022.12.10

[비상관 서브쿼리(Not in 연산자)] 특정 조건에 해당하는 데이터 삭제 - 120 (오라클 SQL, R, Python, SAS)

120. Delete those records from EMP table whose deptno not available in dept table?

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

댓글

티스토리툴바

[비상관 서브쿼리(Not in 연산자)] 특정 조건에 해당하는 데이터 삭제 - 120 (오라클 SQL, R, Python, SAS)

120. Delete those records from EMP table whose deptno not available in dept table?

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

관련글

댓글

티스토리툴바