포스팅 목차
- 105. Display employee name, his job and his manager. Display also employees who are without manager.
- 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
105. Display employee name, his job and his manager. Display also employees who are without manager.
* 직원 이름, 직무, 담당 관리자명을 출력하시오. 더불어 담당 관리자가 없는 직원도 함께 출력하시오.
- Cross Join 생성 :113번 예제 참조
- 유사문제 : 103 / 105 / 212
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- Left Join 과 데이터 상하 결합(UNION)
|
1. Oracle(오라클)
emp 테이블(‘mgr’)을 기준으로 emp 테이블(‘empno’)을 left join을 수행하여서 해당 관리자의 이름을 검색한다. 만약 담당 관리자의 이름이 존재하지 않는 경우 case when 조건문을 사용하여서 ‘no manager’로 표시한다.
| Oracle Programming |
select e.ename, e.job,
case when m.ename is not null then m.ename else 'no manager' end Manager
from emp e
left join emp m
on e.mgr = m.empno
emp 테이블을 Self Join 하여서 담당 관리자의 이름을 추가하고, 별도로 관리자 사원번호가 없는 직원의 정보를 집합 연산자 union all 지정하여서 두 개 쿼리 결과를 함께 출력한다.
| Oracle Programming |
select e.ename, e.job, m.ename Manager
from emp e,emp m
where e.mgr = m.empno
union all
select ename,job,'no manager'
from emp
where mgr is null;
2. Python Pandas(파이썬)
emp 테이블을 Self Join하여서 담당 관리자의 이름을 추가(withmooc_1)하고, 별도로 관리자 사원번호가 없는 직원의 정보(withmooc_2)를 추출한 후에 pd.concat 로 두 개 쿼리 결과를 상하 결합하여 함께 출력한다.
- 정규식 패턴을 사용하여서 self join으로 추가된 변수명의 접미어(suffix) “_x”와 “_y”를 제거한다.
| Python Programming |
import re
# 칼럼명 일괄 변경 처리 : withmooc.columns = withmooc.columns.str.replace('_x$|_y$', '')
withmooc_1 = pd.merge(emp, emp, how='inner', left_on=['mgr'], right_on=['empno'])[['job_x','sal_x',"deptno_x","ename_y"]].rename(columns=lambda x: re.sub('_x|_y','',x))
withmooc_2 = emp[emp['mgr'].isna()][["job","sal","deptno"]].assign(ename = "No Manager")
pd.concat([withmooc_1,withmooc_2])
| Results |
| job | sal | deptno | ename |
| CLERK | 800 | 20 | FORD |
| SALESMAN | 1600 | 30 | BLAKE |
| SALESMAN | 1250 | 30 | BLAKE |
| SALESMAN | 1250 | 30 | BLAKE |
| SALESMAN | 1500 | 30 | BLAKE |
| CLERK | 950 | 30 | BLAKE |
| MANAGER | 2975 | 20 | KING |
| MANAGER | 2850 | 30 | KING |
| MANAGER | 2450 | 10 | KING |
| ANALYST | 3000 | 20 | JONES |
| ANALYST | 3000 | 20 | JONES |
| CLERK | 1100 | 20 | SCOTT |
| CLERK | 1300 | 10 | CLARK |
| PRESIDENT | 5000 | 10 | No Manager |
emp 테이블(‘mgr’)을 기준으로 emp 테이블(‘empno’)을 left join을 수행하여서 해당 관리자의 이름을 검색한다. 만약 담당 관리자의 이름이 존재하지 않는 경우 case when 조건문을 사용하여서 ‘no manager’로 표시한다.
- 정규식 패턴을 사용하여서 self join으로 추가된 변수명의 접미어(suffix) “_x”와 “_y”를 제거한다.
| Python Programming |
withmooc = pd.merge(emp, emp, how='left', left_on=['mgr'], right_on=['empno'])[['job_x','sal_x',"deptno_x","ename_y"]]
withmooc["ename_y"] = np.where(withmooc['ename_y'].isnull(), "No Manager", withmooc['ename_y'])
withmooc.columns = withmooc.columns.str.replace('_x$|_y$', '')
withmooc
| Results |
| job | sal | deptno | ename |
| CLERK | 800 | 20 | FORD |
| SALESMAN | 1600 | 30 | BLAKE |
| SALESMAN | 1250 | 30 | BLAKE |
| MANAGER | 2975 | 20 | KING |
| SALESMAN | 1250 | 30 | BLAKE |
| MANAGER | 2850 | 30 | KING |
| MANAGER | 2450 | 10 | KING |
| ANALYST | 3000 | 20 | JONES |
| PRESIDENT | 5000 | 10 | No Manager |
| SALESMAN | 1500 | 30 | BLAKE |
| CLERK | 1100 | 20 | SCOTT |
| CLERK | 950 | 30 | BLAKE |
| ANALYST | 3000 | 20 | JONES |
| CLERK | 1300 | 10 | CLARK |
3. R Programming (R Package)
emp 테이블을 Self Join(base::merge) 하여서 담당 관리자의 이름을 추가하고, 별도로 관리자 사원번호가 없는 직원의 정보를 집합 연산자 union all 지정하여서 두 개 쿼리 결과를 함께 출력한다.
| R Programming |
%%R
rbind( rename( base::merge(emp,emp,by.x=c("mgr"),by.y=c("empno"),all=F)[,c("ename.x","job.x","ename.y")],
ename = ename.x,
job = job.x,
Manager = ename.y) ,
cbind((emp[is.na(emp$mgr),c('ename','job')]), Manager="no manager") )
| Results |
ename job Manager
1 SCOTT ANALYST JONES
2 FORD ANALYST JONES
3 ALLEN SALESMAN BLAKE
4 WARD SALESMAN BLAKE
5 JAMES CLERK BLAKE
6 TURNER SALESMAN BLAKE
7 MARTIN SALESMAN BLAKE
8 MILLER CLERK CLARK
9 ADAMS CLERK SCOTT
10 CLARK MANAGER KING
11 JONES MANAGER KING
12 BLAKE MANAGER KING
13 SMITH CLERK FORD
14 KING PRESIDENT no manager
emp 테이블(‘mgr’)을 기준으로 emp 테이블(‘empno’)을 left join을 수행하여서 해당 관리자의 이름을 검색한다. 만약 담당 관리자의 이름이 존재하지 않는 경우 IFELSE 조건문을 사용하여서 ‘no manager’로 표시한다.
- 조건문 예제는 73번 예제 참조.
| R Programming |
%%R
withmooc <- rename( merge(emp,emp, by.x="mgr", by.y="empno", all.x=TRUE)[,c("ename.x","job.x","ename.y")] , ename = ename.x,
job = job.x,
Manager = ename.y)
withmooc$Manager <- ifelse( is.na(withmooc$Manager),"no manager" ,withmooc$Manager )
withmooc
| Results |
ename job Manager
1 SCOTT ANALYST JONES
2 FORD ANALYST JONES
3 ALLEN SALESMAN BLAKE
4 WARD SALESMAN BLAKE
5 JAMES CLERK BLAKE
6 TURNER SALESMAN BLAKE
7 MARTIN SALESMAN BLAKE
8 MILLER CLERK CLARK
9 ADAMS CLERK SCOTT
10 CLARK MANAGER KING
11 JONES MANAGER KING
12 BLAKE MANAGER KING
13 SMITH CLERK FORD
14 KING PRESIDENT no manager
emp 테이블(‘mgr’)을 기준으로 emp 테이블(‘empno’)을 left join을 수행하여서 해당 관리자의 이름을 검색한다. 만약 담당 관리자의 이름이 존재하지 않는 경우 IFELSE 조건문을 사용하여서 ‘no manager’로 표시한다.
- transform 구문을 사용하여서 Manager 변수에 대한 처리를 수행한다.
| R Programming |
%%R
withmooc <- rename( merge(emp,emp, by.x="mgr", by.y="empno", all.x=TRUE)[,c("ename.x","job.x","ename.y")] , ename = ename.x,
job = job.x,
Manager = ename.y)
transform(withmooc, Manager = ifelse( is.na(Manager),"no manager" ,Manager ) )
| Results |
ename job Manager
1 SCOTT ANALYST JONES
2 FORD ANALYST JONES
3 ALLEN SALESMAN BLAKE
4 WARD SALESMAN BLAKE
5 JAMES CLERK BLAKE
6 TURNER SALESMAN BLAKE
7 MARTIN SALESMAN BLAKE
8 MILLER CLERK CLARK
9 ADAMS CLERK SCOTT
10 CLARK MANAGER KING
11 JONES MANAGER KING
12 BLAKE MANAGER KING
13 SMITH CLERK FORD
14 KING PRESIDENT no manager
4. R Dplyr Package
emp 테이블을 Self Join(inner_join) 하여서 담당 관리자의 이름을 추가하고, 별도로 관리자 사원번호가 없는 직원의 정보를 집합 연산자 union 지정하여서 두 개 쿼리 결과를 함께 출력한다.
| R Programming |
%%R
emp %>%
dplyr::inner_join( emp , by = c('mgr' = 'empno')) %>%
dplyr::select(ename.x, job.x, ename.y) %>%
dplyr::rename(ename = ename.x,job=job.x, Manager = ename.y) %>%
dplyr::union ( emp %>% dplyr::filter(is.na(mgr)) %>% dplyr::select(ename,job) %>% mutate(Manager='no manager') )
| Results |
# A tibble: 14 x 3
ename job Manager
<chr> <chr> <chr>
1 SMITH CLERK FORD
2 ALLEN SALESMAN BLAKE
3 WARD SALESMAN BLAKE
4 JONES MANAGER KING
5 MARTIN SALESMAN BLAKE
6 BLAKE MANAGER KING
7 CLARK MANAGER KING
8 SCOTT ANALYST JONES
9 TURNER SALESMAN BLAKE
10 ADAMS CLERK SCOTT
11 JAMES CLERK BLAKE
12 FORD ANALYST JONES
13 MILLER CLERK CLARK
14 KING PRESIDENT no manager
emp 테이블(‘mgr’)을 기준으로 emp 테이블(‘empno’)을 left_join을 수행하여서 해당 관리자의 이름을 검색한다. 만약 담당 관리자의 이름이 존재하지 않는 경우 IFELSE 조건문을 사용하여서 ‘no manager’로 표시한다.
| R Programming |
%%R
emp %>%
dplyr::left_join( emp , by = c('mgr' = 'empno')) %>%
dplyr::select(ename.x,job.x,ename.y) %>%
dplyr::rename(ename = ename.x, job = job.x, Manager = ename.y) %>%
dplyr::mutate( Manager = ifelse(is.na(Manager), "no manager" , Manager) )
| Results |
# A tibble: 14 x 3
ename job Manager
<chr> <chr> <chr>
1 SMITH CLERK FORD
2 ALLEN SALESMAN BLAKE
3 WARD SALESMAN BLAKE
4 JONES MANAGER KING
5 MARTIN SALESMAN BLAKE
6 BLAKE MANAGER KING
7 CLARK MANAGER KING
8 SCOTT ANALYST JONES
9 KING PRESIDENT no manager
10 TURNER SALESMAN BLAKE
11 ADAMS CLERK SCOTT
12 JAMES CLERK BLAKE
13 FORD ANALYST JONES
14 MILLER CLERK CLARK
5. R sqldf Package
emp 테이블을 Self Join하여서 담당 관리자의 이름을 추가하고, 별도로 관리자 사원번호가 없는 직원의 정보를 집합 연산자 union all 지정하여서 두 개 쿼리 결과를 함께 출력한다.
| R Programming |
%%R
sqldf("select e.ename, e.job, m.ename Manager
from emp e,emp m
where e.mgr = m.empno
union all
select ename,job,'no manager'
from emp
where mgr is null")
| Results |
ename job Manager
1 SMITH CLERK FORD
2 ALLEN SALESMAN BLAKE
3 WARD SALESMAN BLAKE
4 JONES MANAGER KING
5 MARTIN SALESMAN BLAKE
6 BLAKE MANAGER KING
7 CLARK MANAGER KING
8 SCOTT ANALYST JONES
9 TURNER SALESMAN BLAKE
10 ADAMS CLERK SCOTT
11 JAMES CLERK BLAKE
12 FORD ANALYST JONES
13 MILLER CLERK CLARK
14 KING PRESIDENT no manager
emp 테이블(‘mgr’)을 기준으로 emp 테이블(‘empno’)을 left join을 수행하여서 해당 관리자의 이름을 검색한다. 만약 담당 관리자의 이름이 존재하지 않는 경우 case when 조건문을 사용하여서 ‘no manager’로 표시한다.
| R Programming |
%%R
sqldf(" select e.ename, e.job,
case when m.ename is not null then m.ename else 'no manager' end Manager
from emp e
left join emp m
on e.mgr = m.empno")
| Results |
ename job Manager
1 SMITH CLERK FORD
2 ALLEN SALESMAN BLAKE
3 WARD SALESMAN BLAKE
4 JONES MANAGER KING
5 MARTIN SALESMAN BLAKE
6 BLAKE MANAGER KING
7 CLARK MANAGER KING
8 SCOTT ANALYST JONES
9 KING PRESIDENT no manager
10 TURNER SALESMAN BLAKE
11 ADAMS CLERK SCOTT
12 JAMES CLERK BLAKE
13 FORD ANALYST JONES
14 MILLER CLERK CLARK
6. Python pandasql Package
emp 테이블을 Self Join 하여서 담당 관리자의 이름을 추가하고, 별도로 관리자 사원번호가 없는 직원의 정보를 집합 연산자 union all 지정하여서 두 개 쿼리 결과를 함께 출력한다.
| Python Programming |
ps.sqldf("select e.ename, e.job, m.ename Manager \
from emp e,emp m \
where e.mgr=m.empno \
union \
select ename,job,'no manager' \
from emp \
where mgr is null").head()
| Results |
| ename | job | Manager |
| ADAMS | CLERK | SCOTT |
| ALLEN | SALESMAN | BLAKE |
| BLAKE | MANAGER | KING |
| CLARK | MANAGER | KING |
| FORD | ANALYST | JONES |
7. R data.table Package
emp 테이블을 DT Syntax 방식으로 Self Join 하여서 담당 관리자의 이름을 추가하고, 별도로 관리자 사원번호가 없는 직원의 정보를 집합 연산자 data.table::funion 지정하여서 두 개 쿼리 결과를 함께 출력한다.
- Cross Join 생성 :113번 예제 참조
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
salgrade_DT <- data.table(salgrade)
data.table::funion ( DT[DT, nomatch=NULL, on = .( mgr=empno ), .(ename, job, manager = i.ename)] ,
DT[is.na(mgr) , .(ename, job, manager = 'no manager') ]
)
| Results |
ename job manager
1: SCOTT ANALYST JONES
2: FORD ANALYST JONES
3: ALLEN SALESMAN BLAKE
4: WARD SALESMAN BLAKE
5: MARTIN SALESMAN BLAKE
6: TURNER SALESMAN BLAKE
7: JAMES CLERK BLAKE
8: MILLER CLERK CLARK
9: ADAMS CLERK SCOTT
10: JONES MANAGER KING
11: BLAKE MANAGER KING
12: CLARK MANAGER KING
13: SMITH CLERK FORD
14: KING PRESIDENT no manager
emp 테이블(‘mgr’)을 기준으로 emp 테이블(‘empno’)을 DT Syntax 방식으로 left join을 수행하여서 해당 관리자의 이름을 검색한다. 만약 담당 관리자의 이름이 존재하지 않는 경우 ifelse 조건문을 사용하여서 ‘no manager’로 표시한다.
- DT 방식의 Left join으로 조인 : Join 내에서 .() 구문으로 변수 선택 시 i.ename이 null인 “King”의 자료가 반환이 안 됨.
| R Programming |
%%R
# 첫 번째 결합문 내에서 .()을 통하여 칼럼 선택과 조건문 사용 시 i.ename이 null인 "King"의 자료가 반환이 안 됨.
# 'King' 누락 : DT[DT, nomatch=NULL, on = .( mgr = empno), .(ename , job)]
DT[DT, nomatch=NULL, on = .( mgr = empno), manager := i.ename][,.(ename , job, manager = ifelse(is.na(manager), "no manager" , manager ) ) ]
| Results |
ename job manager
1: SMITH CLERK FORD
2: ALLEN SALESMAN BLAKE
3: WARD SALESMAN BLAKE
4: JONES MANAGER KING
5: MARTIN SALESMAN BLAKE
6: BLAKE MANAGER KING
7: CLARK MANAGER KING
8: SCOTT ANALYST JONES
9: KING PRESIDENT no manager
10: TURNER SALESMAN BLAKE
11: ADAMS CLERK SCOTT
12: JAMES CLERK BLAKE
13: FORD ANALYST JONES
14: MILLER CLERK CLARK
emp 테이블(‘mgr’)을 기준으로 emp 테이블(‘empno’)을 DT Syntax 방식으로 right join을 수행하여서 해당 관리자의 이름을 검색한다. 만약 담당 관리자의 이름이 존재하지 않는 경우 ifelse 조건문을 사용하여서 ‘no manager’로 표시한다.
- DT 방식 Right Join으로 처리 : Left join으로 처리 시 i.ename이 null인 “King”의 자료가 반환이 안 됨.
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
salgrade_DT <- data.table(salgrade)
DT[DT, on = .( empno=mgr ),.(i.ename,i.job, manager = ifelse(is.na(ename), "no manager" , ename )) ]
| Results |
i.ename i.job manager
1: SMITH CLERK FORD
2: ALLEN SALESMAN BLAKE
3: WARD SALESMAN BLAKE
4: JONES MANAGER KING
5: MARTIN SALESMAN BLAKE
6: BLAKE MANAGER KING
7: CLARK MANAGER KING
8: SCOTT ANALYST JONES
9: KING PRESIDENT no manager
10: TURNER SALESMAN BLAKE
11: ADAMS CLERK SCOTT
12: JAMES CLERK BLAKE
13: FORD ANALYST JONES
14: MILLER CLERK CLARK
emp 테이블(‘mgr’)을 기준으로 emp 테이블(‘empno’)을 Merge 방식으로 left join을 수행하여서 해당 관리자의 이름을 검색한다. 만약 담당 관리자의 이름이 존재하지 않는 경우 ifelse 조건문을 사용하여서 ‘no manager’로 표시한다.
- merge 방식 Left join 수행
| R Programming |
%%R
merge(DT,DT, by.x="mgr", by.y="empno", all.x=TRUE)[,.(enme = ename.x, job = job.x, manager = ifelse(is.na(ename.y), "no manager" , ename.y )) ]
| Results |
enme job manager
1: KING PRESIDENT no manager
2: SCOTT ANALYST JONES
3: FORD ANALYST JONES
4: ALLEN SALESMAN BLAKE
5: WARD SALESMAN BLAKE
6: MARTIN SALESMAN BLAKE
7: TURNER SALESMAN BLAKE
8: JAMES CLERK BLAKE
9: MILLER CLERK CLARK
10: ADAMS CLERK SCOTT
11: JONES MANAGER KING
12: BLAKE MANAGER KING
13: CLARK MANAGER KING
14: SMITH CLERK FORD
8. SAS Proc SQL
emp 테이블을 Self Join 하여서 담당 관리자의 이름을 추가하고, 별도로 관리자 사원번호가 없는 직원의 정보를 집합 연산자 union all 지정하여서 두 개 쿼리 결과를 함께 출력한다.
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_1 AS
SELECT E.ENAME, E.JOB, M.ENAME AS MANAGER
FROM EMP E,EMP M
WHERE E.MGR=M.EMPNO
UNION
SELECT ENAME,JOB,'NO MANAGER' AS MANAGER
FROM EMP
WHERE MGR IS NULL;
QUIT;
PROC PRINT;RUN;
| Results |
| ename | job | MANAGER |
| ADAMS | CLERK | SCOTT |
| ALLEN | SALESMAN | BLAKE |
| BLAKE | MANAGER | KING |
| CLARK | MANAGER | KING |
| FORD | ANALYST | JONES |
| JAMES | CLERK | BLAKE |
| JONES | MANAGER | KING |
| KING | PRESIDEN | NO MANAGER |
| MARTIN | SALESMAN | BLAKE |
| MILLER | CLERK | CLARK |
| SCOTT | ANALYST | JONES |
| SMITH | CLERK | FORD |
| TURNER | SALESMAN | BLAKE |
| WARD | SALESMAN | BLAKE |
9. SAS Data Step
| SAS Programming |
%%SAS sas
PROC SORT DATA=EMP OUT=EMP_1( RENAME=( MGR = MGR_EMPNO
SAL = EMP_SAL));
BY MGR;
RUN;
PROC SORT DATA=EMP OUT=EMP_2( RENAME=( EMPNO = MGR_EMPNO
ENAME = MGR_ENAME)
KEEP=EMPNO ENAME);
BY EMPNO;
RUN;
DATA STATSAS_2;
MERGE EMP_1(IN=A) EMP_2(IN=B);
BY MGR_EMPNO;
IF A AND B THEN OUTPUT;
KEEP ENAME JOB MGR_ENAME;
RUN;
DATA STATSAS_3;
SET EMP;
MGR_ENAME = 'NO MGR';
KEEP ENAME JOB MGR_ENAME;
WHERE MGR = .;
RUN;
DATA STATSAS_4;
SET STATSAS_2
STATSAS_3;
RUN;
PROC PRINT data=STATSAS_4;RUN;
| Results |
| ename | job | MGR_ENAME |
| SCOTT | ANALYST | JONES |
| FORD | ANALYST | JONES |
| ALLEN | SALESMAN | BLAKE |
| WARD | SALESMAN | BLAKE |
| MARTIN | SALESMAN | BLAKE |
| TURNER | SALESMAN | BLAKE |
| JAMES | CLERK | BLAKE |
| MILLER | CLERK | CLARK |
| ADAMS | CLERK | SCOTT |
| JONES | MANAGER | KING |
| BLAKE | MANAGER | KING |
| CLARK | MANAGER | KING |
| SMITH | CLERK | FORD |
| KING | PRESIDEN | NO MGR |
10. Python Dfply Package
| Python Programming |
@pipe
def inner_join_merge(df, other, left_on,right_on,suffixes=['_x','_y']):
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"], right_on = ["empno"]) >> \
select(X.ename_x, X.job_x, X.ename_y) >> \
rename( ename = X.ename_x, job = X.job_x, Manager = X.ename_y) >> \
union ( emp >> filter_by( X.mgr.isna() ) >> select(X.ename,X.job) >> mutate(Manager = 'no manager') )
| Results |
| ename | job | Manager |
| SMITH | CLERK | FORD |
| ALLEN | SALESMAN | BLAKE |
| WARD | SALESMAN | BLAKE |
| MARTIN | SALESMAN | BLAKE |
| TURNER | SALESMAN | BLAKE |
| JAMES | CLERK | BLAKE |
| JONES | MANAGER | KING |
| BLAKE | MANAGER | KING |
| CLARK | MANAGER | KING |
| SCOTT | ANALYST | JONES |
| FORD | ANALYST | JONES |
| ADAMS | CLERK | SCOTT |
| MILLER | CLERK | CLARK |
| KING | PRESIDENT | no manager |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트
반응형
댓글