포스팅 목차
119. Delete those department where no employee working?
* DEPT 테이블에서 직원이 근무하지 않는(배정되어 있지 않은) 부서 정보를 삭제하시오.
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- 참고문제 : 116번
- [데이터 추출] 특정 조건에 해당하는 데이터 삭제 - 상관 서브쿼리
- 데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql
|
1. Oracle(오라클)
dept 테이블에서 emp 테이블을 상관 서브쿼리로 조회 하여서 emp 테이블에 존재하지 않은 부서의 정보를 삭제한다.
| Oracle Programming |
create table dept_copy as
select * from dept;
delete from dept_copy d
where 0 = (select count(*) from emp where deptno = d.deptno);
select *
from dept_copy;
2. Python Pandas(파이썬)
emp 테이블에서 부서번호(‘deptno’)에 속한 직원수를 카운트 후 dept 테이블을 기준으로 left join 을 수행하여서 해당 부서에 속한 직원이 존재하는 부서를 선택한다.
| Python Programming |
pd.merge( dept,
(emp.groupby('deptno')['empno'].describe()[['count']].reset_index()),
"left",
left_on=['deptno'], right_on=['deptno']
).query("count > 0")
| Results |
| deptno | dname | loc | count | |
| 0 | 10 | ACCOUNTING | NEW YORK | 3.0 |
| 1 | 20 | RESEARCH | DALLAS | 5.0 |
| 2 | 30 | SALES | CHICAGO | 6.0 |
3. R Programming (R Package)
emp 테이블에서 부서번호(‘deptno’)에 속한 직원수를 카운트 후 dept 테이블을 기준으로 left join 을 수행하여서 해당 부서에 속한 직원이 존재하는 부서를 선택한다.
| R Programming |
%%R
subset( merge( dept,
( setNames ( aggregate(empno~deptno,data=emp,FUN=length) , c("deptno","emp_count") ) ),
by.x=c("deptno"),by.y=c("deptno"),all=T
),
emp_count > 0 )
| Results |
deptno dname loc emp_count
1 10 ACCOUNTING NEW YORK 3
2 20 RESEARCH DALLAS 5
3 30 SALES CHICAGO 6
[참고] 변수 이름 변경(renmae)하기
- setNames / names
| R Programming |
%%R
temp <- aggregate(empno~deptno,data=emp,FUN=length)
print(temp)
print("----------------------------")
names(temp)[names(temp)=="empno"] <- "emp_count"
print(temp)
| Results |
deptno empno
1 10 3
2 20 5
3 30 6
[1] "----------------------------"
deptno emp_count
1 10 3
2 20 5
3 30 6
4. R Dplyr Package
emp 테이블에서 부서번호(‘deptno’)에 속한 직원수를 카운트 후 dept 테이블을 기준으로 left join 을 수행하여서 해당 부서에 속한 직원이 존재하는 부서를 선택한다.
| R Programming |
%%R
dept %>%
dplyr::left_join ( ( emp %>%
dplyr::group_by( deptno ) %>%
dplyr::tally() ),
by = c("deptno" = "deptno") ) %>%
dplyr::filter(n >0)
| Results |
# A tibble: 3 x 4
deptno dname loc n
<dbl> <chr> <chr> <int>
1 10 ACCOUNTING NEW YORK 3
2 20 RESEARCH DALLAS 5
3 30 SALES CHICAGO 6
5. R sqldf Package
dept 테이블에서 emp 테이블을 상관 서브쿼리로 조회 하여서 emp 테이블에 존재하지 않은 부서의 정보를 삭제한다.
- Sqlite 의 update / delete 구문 : select / where 구문으로 처리 가능 (98번 / 119번)
| R Programming |
%%R
withmooc = dept
print(withmooc)
print("-----------------------------------")
withmooc <- sqldf(c(" delete from withmooc
where 0 = (select count(*) from emp where deptno=withmooc.deptno);",
"select * from withmooc"))
print(withmooc)
| Results |
# A tibble: 4 x 3
deptno dname loc
<dbl> <chr> <chr>
1 10 ACCOUNTING NEW YORK
2 20 RESEARCH DALLAS
3 30 SALES CHICAGO
4 40 OPERATIONS BOSTON
[1] "-----------------------------------"
deptno dname loc
1 10 ACCOUNTING NEW YORK
2 20 RESEARCH DALLAS
3 30 SALES CHICAGO
emp 테이블에서 부서번호(‘deptno’)에 속한 직원수를 카운트 후 dept 테이블에 left join 을 수행하여 직원수를 추가한 후 해당 부서에 속한 직원이 존재하는 부서를 선택한다.
| R Programming |
%%R
sqldf("select *
from dept a
left join ( select deptno,count(*) emp_cnt
from emp
group
by deptno) b
on a.deptno = b.deptno
where b.deptno is not null")
| Results |
deptno dname loc deptno emp_cnt
1 10 ACCOUNTING NEW YORK 10 3
2 20 RESEARCH DALLAS 20 5
3 30 SALES CHICAGO 30 6
6. Python pandasql Package
- 현재 pandasql에서 update 제대로 작동 안 함.
| Python Programming |
withmooc= dept.copy()
display(withmooc)
ps.sqldf("select * from withmooc where 0 < (select count(*) from emp where deptno=withmooc.deptno);")
| Results |
| deptno | dname | loc | |
| 10 | ACCOUNTING | NEW YORK | |
| 20 | RESEARCH | DALLAS | |
| 30 | SALES | CHICAGO | |
| 40 | OPERATIONS | BOSTON |
| Results |
| deptno | dname | loc | |
| 10 | ACCOUNTING | NEW YORK | |
| 20 | RESEARCH | DALLAS | |
| 30 | SALES | CHICAGO |
| Python Programming |
ps.sqldf("select * \
from dept a \
left join ( select deptno,count(*) \
from emp \
group by deptno) b \
on a.deptno = b.deptno \
where b.deptno is not null")
| Results |
| deptno | dname | loc | deptno | count(*) | |
| 0 | 10 | ACCOUNTING | NEW YORK | 10 | 3 |
| 1 | 20 | RESEARCH | DALLAS | 20 | 5 |
| 2 | 30 | SALES | CHICAGO | 30 | 6 |
7. R data.table Package
DT syntax 방식
emp 테이블에서 부서번호(‘deptno’)에 속한 직원수를 카운트 후 dept 테이블에 DT Syntax 방식으로 left join 을 수행하여 직원수를 추가한 후 해당 부서에 속한 직원이 존재하는 부서를 선택한다.
- DT syntax 방식으로 Left join 구현 시 기준 테이블(왼쪽)이 내부로 들어감. (dept_DT)
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
salgrade_DT <- data.table(salgrade)
DT[, .(`emp_count` = .N), keyby = .(deptno)][ dept_DT,
on = .(deptno = deptno) ][emp_count>0]
| Results |
deptno emp_count dname loc
1: 10 3 ACCOUNTING NEW YORK
2: 20 5 RESEARCH DALLAS
3: 30 6 SALES CHICAGO
data.table::merge() syntax
emp 테이블에서 부서번호(‘deptno’)에 속한 직원수를 카운트 후 dept 테이블에 merge Syntax 방식으로 left join 을 수행하여 직원수를 추가한 후 해당 부서에 속한 직원이 존재하는 부서를 선택한다.
| R Programming |
%%R
merge( dept_DT,
( DT[, .(`emp_count` = .N), keyby = .(deptno)] ),
by.x=c("deptno"),by.y=c("deptno"), all.x=T)[emp_count>0]
| Results |
deptno dname loc emp_count
1: 10 ACCOUNTING NEW YORK 3
2: 20 RESEARCH DALLAS 5
3: 30 SALES CHICAGO 6
| R Programming |
%%R
merge( dept_DT,
( DT[, .(`emp_count` = .N), keyby = .(deptno)] ),
by="deptno", all.x=T)[emp_count>0]
| Results |
deptno dname loc emp_count
1: 10 ACCOUNTING NEW YORK 3
2: 20 RESEARCH DALLAS 5
3: 30 SALES CHICAGO 6
8. SAS Proc SQL
dept 테이블에서 emp 테이블을 상관 서브쿼리로 조회 하여서 emp 테이블에 존재하지 않은 부서의 정보를 삭제한다.
| SAS Programming |
%%SAS sas
PROC SQL;
create table dept_copy as
select *
from dept;
delete from dept_copy d
where 0 = (select count(*) from emp where deptno = d.deptno);
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | deptno | dname | loc |
| 1 | 10 | ACCOUNTING | NEW YORK |
| 2 | 20 | RESEARCH | DALLAS |
| 3 | 30 | SALES | CHICAGO |
| SAS Programming |
%%SAS sas
PROC SQL;
create table STATSAS_1 as
select *
from dept d
where 0 < (select count(*) from emp where deptno = d.deptno);
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | deptno | dname | loc |
| 1 | 10 | ACCOUNTING | NEW YORK |
| 2 | 20 | RESEARCH | DALLAS |
| 3 | 30 | SALES | CHICAGO |
| SAS Programming |
%%SAS sas
PROC SQL;
create table STATSAS_2 as
select *
from dept a
left join ( select deptno,count(*) as emp_cnt
from emp
group
by deptno) b
on a.deptno = b.deptno
where b.deptno is not null;
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | deptno | dname | loc | emp_cnt |
| 1 | 10 | ACCOUNTING | NEW YORK | 3 |
| 2 | 20 | RESEARCH | DALLAS | 5 |
| 3 | 30 | SALES | CHICAGO | 6 |
9. SAS Data Step
| SAS Programming |
%%SAS sas
PROC SORT DATA=DEPT OUT=DEPT_1;
BY DEPTNO;
RUN;
PROC SUMMARY DATA=EMP;
CLASS DEPTNO;
VAR EMPNO;
OUTPUT OUT=EMP_1(DROP=_:) N=;
QUIT;
DATA STATSAS_3;
MERGE DEPT_1(IN=A) EMP_1(IN=B);
BY DEPTNO;
IF A AND B THEN OUTPUT;
DROP EMPNO;
RUN;
PROC PRINT;RUN;
| Results |
| OBS | deptno | dname | loc |
| 1 | 10 | ACCOUNTING | NEW YORK |
| 2 | 20 | RESEARCH | DALLAS |
| 3 | 30 | SALES | CHICAGO |
10. Python Dfply Package
| Python Programming |
dept >> \
left_join( ( emp >> group_by('deptno') >> summarize( emp_cnt = X.empno.count()) ) ,
by="deptno") >> \
filter_by(X.emp_cnt > 0)
| Results |
| deptno | dname | loc | emp_cnt | |
| 0 | 10 | ACCOUNTING | NEW YORK | 3.0 |
| 1 | 20 | RESEARCH | DALLAS | 5.0 |
| 2 | 30 | SALES | CHICAGO | 6.0 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트
반응형
댓글