포스팅 목차
161. find out the all employees who joined the company before their manager.
* 담당 관리자 보다 먼저 입사한 직원들의 정보를 출력하시오.
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- 참고 프로그램 : 동적 프로그램 실행 - 154번,161번, 162번
- [데이터 추출] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리(In), 우선 순위 비교
- 데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql
|
1. Oracle(오라클)
상관 서브쿼리를 사용하여서 해당 관리자의 입사일(‘hiredate’)을 조회하여서 관리자의 입사일 보다 이전에 입사한 직원들의 정보를 출력한다.
| Oracle Programming |
Select *
from emp e
where hiredate < (select hiredate from emp where empno = e.mgr);
2. Python Pandas(파이썬)
emp 테이블을 self join 방식으로 관리자 사원번호(‘mgr’)와 사원번호(‘empno’)를 기준으로 결합한 후 해당 사원의 입사일(‘hiredate_x’)이 관리자의 입사일(‘hiredate_y’)보다 적은(이전에 입사한) 직원들의 정보를 출력한다.
- lambda 함수를 사용하여서 사원정보를 조회한 왼쪽(‘_x’) 테이블의 변수와 관리자의 정보를 조회한 오른쪽 테이블(‘_y’)에서 입사일(hiredate)을 선택하여 출력한다.
| Python Programming |
pd.merge(emp,
emp,
how='inner',
left_on=['mgr'], right_on=['empno']).query('hiredate_x < hiredate_y')[ lambda x: (col for col in x if col.endswith('_x')|col.endswith('hiredate_y')) ]
| Results |
| empno_x | ename_x | job_x | mgr_x | hiredate_x | sal_x | comm_x | deptno_x | hiredate_y | |
| 0 | 7369 | SMITH | CLERK | 7902.0 | 1980/12/17 | 800 | NaN | 20 | 1981/12/03 |
| 1 | 7499 | ALLEN | SALESMAN | 7698.0 | 1981/02/20 | 1600 | 300.0 | 30 | 1981/03/01 |
| 2 | 7521 | WARD | SALESMAN | 7698.0 | 1981/02/22 | 1250 | 500.0 | 30 | 1981/03/01 |
| 6 | 7566 | JONES | MANAGER | 7839.0 | 1981/04/02 | 2975 | NaN | 20 | 1981/11/17 |
| 7 | 7698 | BLAKE | MANAGER | 7839.0 | 1981/03/01 | 2850 | NaN | 30 | 1981/11/17 |
| 8 | 7782 | CLARK | MANAGER | 7839.0 | 1981/01/09 | 2450 | NaN | 10 | 1981/11/17 |
3. R Programming (R Package)
merge() 함수를 사용하여서 emp 테이블을 self join 방식으로 관리자 사원번호(‘mgr’)와 사원번호(‘empno’)를 기준으로 결합한 후 subset() 함수를 사용하여 해당 사원의 입사일(‘hiredate.x’)이 관리자의 입사일(‘hiredate.y’)보다 적은(이전에 입사한) 직원들의 정보를 출력한다.
- grep() 함수를 사용하여서 사원정보를 조회한 왼쪽(‘.x’) 테이블의 변수명과 관리자의 입사일을 조회한 오른쪽 테이블(‘.y’)에서 입사일(hiredate) 변수명을 선택하여 emp 테이블에서 해당 변수를 선택하여 출력한다.
| R Programming |
%%R
withmooc <- subset( merge(emp, emp,,by.x=c("mgr"),by.y=c("empno"),all=F), hiredate.x < hiredate.y )
withmooc[,grep("mgr|empno|.x$", names(withmooc))]
| Results |
mgr empno ename.x job.x hiredate.x sal.x comm.x deptno.x mgr.y
3 7698 7499 ALLEN SALESMAN 1981-02-20 1600 300 30 7839
4 7698 7521 WARD SALESMAN 1981-02-22 1250 500 30 7839
10 7839 7782 CLARK MANAGER 1981-01-09 2450 NA 10 NA
11 7839 7566 JONES MANAGER 1981-04-02 2975 NA 20 NA
12 7839 7698 BLAKE MANAGER 1981-03-01 2850 NA 30 NA
13 7902 7369 SMITH CLERK 1980-12-17 800 NA 20 7566
- [참고] 접미어 .x를 포함한 변수 선택
base::endsWith() 함수를 사용하여서 사원정보를 조회한 왼쪽(‘.x’) 테이블의 변수명(변수명이 ‘.x’로 끝나는 변수명)과 “emono”,”mgr” 변수명을 선택하여 emp 테이블에서 해당 변수를 선택하여 출력한다.
| R Programming |
%%R
withmooc[base::endsWith(names(withmooc), ".x")| names(withmooc) %in% c("empno","mgr")]
| Results |
mgr empno ename.x job.x hiredate.x sal.x comm.x deptno.x
3 7698 7499 ALLEN SALESMAN 1981-02-20 1600 300 30
4 7698 7521 WARD SALESMAN 1981-02-22 1250 500 30
10 7839 7782 CLARK MANAGER 1981-01-09 2450 NA 10
11 7839 7566 JONES MANAGER 1981-04-02 2975 NA 20
12 7839 7698 BLAKE MANAGER 1981-03-01 2850 NA 30
13 7902 7369 SMITH CLERK 1980-12-17 800 NA 20grepl() 함수를 사용하여서 왼쪽 emp 테이블에서 사원정보를 조회한 왼쪽(‘.x’) 테이블의 변수명(변수명이 ‘.x’로 끝나는 변수명)과 “emono”,”mgr” 변수명을 선택하여 emp 테이블에서 해당 변수를 선택하여 출력한다.
| R Programming |
%%R
withmooc[sapply( names(withmooc) , function(x) grepl("mgr$|empno$|.x$", x) )]
| Results |
mgr empno ename.x job.x hiredate.x sal.x comm.x deptno.x
3 7698 7499 ALLEN SALESMAN 1981-02-20 1600 300 30
4 7698 7521 WARD SALESMAN 1981-02-22 1250 500 30
5 7698 7900 JAMES CLERK 1981-12-03 950 NA 30
6 7698 7844 TURNER SALESMAN 1981-09-08 1500 0 30
7 7698 7654 MARTIN SALESMAN 1981-09-28 1250 1400 30
8 7782 7934 MILLER CLERK 1982-01-23 1300 NA 10
9 7788 7876 ADAMS CLERK 1983-01-12 1100 NA 20
10 7839 7782 CLARK MANAGER 1981-01-09 2450 NA 10
11 7839 7566 JONES MANAGER 1981-04-02 2975 NA 20
12 7839 7698 BLAKE MANAGER 1981-03-01 2850 NA 30
13 7902 7369 SMITH CLERK 1980-12-17 800 NA 20
4. R Dplyr Package
inner_join() 함수를 사용하여서 emp 테이블을 self join 방식으로 관리자 사원번호(‘mgr’)와 사원번호(‘empno’)를 기준으로 결합한 후 filter() 함수를 사용하여 해당 사원의 입사일(‘hiredate.x’)이 관리자의 입사일(‘hiredate.y’)보다 적은(이전에 입사한) 직원들의 정보를 출력한다.
- dplyr::select() 함수를 사용하여서 사원정보를 조회한 왼쪽(‘.x’) 테이블의 변수명(변수명이 ‘.x’로 끝나는 변수명)과 관리자의 입사일을 조회한 오른쪽 테이블(‘.y’)에서 입사일(hiredate.y) 변수명을 선택하여 emp 테이블에서 해당 변수를 선택하여 출력한다.
- dplyr::rename_all() 함수를 사용해서 변수명의 접미어(‘.x’)를 일괄적으로 제거한다.
| R Programming |
%%R
emp %>%
dplyr::inner_join( emp, by = c('mgr' = 'empno') ) %>%
dplyr::filter( hiredate.x < hiredate.y) %>%
dplyr::select(empno, ends_with('.x'), ends_with('hiredate.y')) %>%
dplyr::rename_all(~ gsub(".x", "", .))
| Results |
# A tibble: 6 x 8
empno ename job hiredate sal comm deptno hiredate.y
<dbl> <chr> <chr> <date> <dbl> <dbl> <dbl> <date>
1 7369 SMITH CLERK 1980-12-17 800 NA 20 1981-12-03
2 7499 ALLEN SALESMAN 1981-02-20 1600 300 30 1981-03-01
3 7521 WARD SALESMAN 1981-02-22 1250 500 30 1981-03-01
4 7566 JONES MANAGER 1981-04-02 2975 NA 20 1981-11-17
5 7698 BLAKE MANAGER 1981-03-01 2850 NA 30 1981-11-17
6 7782 CLARK MANAGER 1981-01-09 2450 NA 10 1981-11-17
5. R sqldf Package
상관 서브쿼리를 사용하여서 해당 관리자의 입사일(‘hiredate’)을 조회하여서 관리자의 입사일 보다 이전에 입사한 직원들의 정보를 출력한다.
| R Programming |
%%R
sqldf(" Select *
from emp e
where hiredate < (select hiredate 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 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20
5 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30
6 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10
6. Python pandasql Package
| Python Programming |
ps.sqldf(" Select *
from emp e
where hiredate < (select hiredate from emp where empno=e.mgr); ")
| 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 | 7698 | BLAKE | MANAGER | 7839.0 | 1981/03/01 | 2850 | NaN | 30 |
| 5 | 7782 | CLARK | MANAGER | 7839.0 | 1981/01/09 | 2450 | NaN | 10 |
7. R data.table Package
emp 테이블을 DT 조인 방식의 self join 방식으로 관리자 사원번호(‘mgr’)와 사원번호(‘empno’)를 기준으로 결합한 후 해당 사원의 입사일(‘hiredate’)이 관리자의 입사일(‘i.hiredate’)보다 적은(이전에 입사한) 직원들의 정보를 출력한다.
- 사원정보를 조회한 왼쪽 테이블의 변수명(변수명이 ‘.x’로 끝나는 변수명)과 관리자의 입사일을 조회한 오른쪽 테이블(‘i.’)에서 입사일(‘i.hiredate’) 변수명을 선택하여 emp 테이블에서 해당 변수를 선택하여 출력한다.
- data.table에서 변수선택 : https://stackoverflow.com/questions/28094645/select-subset-of-columns-in-data-table-r
Select subset of columns in data.table R
I have a data table with a bunch of columns, e.g.: dt<-data.table(matrix(runif(10*10),10,10)) I want to perform some operation on the data table, such as producing a correlation matrix (cor(dt...
stackoverflow.com
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
salgrade_DT <- data.table(salgrade)
DT[DT, nomatch=NULL, on = .( mgr= empno), ][hiredate < i.hiredate, c(colnames(DT),'i.hiredate'), with=FALSE] # .( mgr= empno, sal<sal) 은 반환 값이 이상.
| Results |
empno ename job mgr hiredate sal comm deptno i.hiredate
1: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-03-01
2: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-03-01
3: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-11-17
4: 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1981-11-17
5: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 1981-11-17
6: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1981-12-03
8. SAS Proc SQL
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_1 AS
Select *
from emp e
where hiredate < (select hiredate from emp where empno = e.mgr);
QUIT;
PROC PRINT;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 | 7698 | BLAKE | MANAGER | 7839 | 1981-03-01 | 2850 | . | 30 |
| 6 | 7782 | CLARK | MANAGER | 7839 | 1981-01-09 | 2450 | . | 10 |
9. SAS Data Step
| SAS Programming |
%%SAS sas
PROC SORT DATA=EMP OUT=EMP_1(RENAME=MGR=JOIN_KEY);
BY MGR;
RUN;
PROC SORT DATA=EMP OUT=EMP_2(RENAME=(EMPNO=JOIN_KEY HIREDATE=MGR_HIREDATE) KEEP=EMPNO HIREDATE);
BY empno;
RUN;
DATA STATSAS_2;
MERGE EMP_1(in=a) EMP_2(in=b);
BY JOIN_KEY;
IF A AND B;
IF HIREDATE < MGR_HIREDATE THEN OUTPUT;
RUN;
PROC PRINT;RUN;
| Results |
| OBS | empno | ename | job | JOIN_KEY | hiredate | sal | comm | deptno | MGR_HIREDATE |
| 1 | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 1981-03-01 |
| 2 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 1981-03-01 |
| 3 | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | . | 20 | 1981-11-17 |
| 4 | 7698 | BLAKE | MANAGER | 7839 | 1981-03-01 | 2850 | . | 30 | 1981-11-17 |
| 5 | 7782 | CLARK | MANAGER | 7839 | 1981-01-09 | 2450 | . | 10 | 1981-11-17 |
| 6 | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | . | 20 | 1981-12-03 |
10. Python Dfply Package
- 변수명 일괄적으로 rename() 적용하기 : 114 번 참조
- 하나의 로직으로 처리하기 위하여 emp 테이블의 칼럼명을 모두 변경 로직으로 생성하여 적용
| Python Programming |
@pipe
def rename_fun(df, col_name):
return df.rename(columns=col_name)
from ast import literal_eval
s = '{'+','.join([ "'" + str(col) + "_x'" + ": '" + str(col) + "'" for col in emp.columns ]) +'}'
print(s)
emp >> \
inner_join_merge ( emp, left_on=['mgr'], right_on=['empno'], suffixes=["_x", "_y"]) >>\
filter_by( X.hiredate_x < X.hiredate_y) >> \
select(X.empno_x, ends_with('_x'), ends_with('hiredate_y')) >> \
rename_fun( literal_eval(s) )
| Results |
{'empno_x': 'empno','ename_x': 'ename','job_x': 'job','mgr_x': 'mgr','hiredate_x': 'hiredate','sal_x': 'sal','comm_x': 'comm','deptno_x': 'deptno'}| empno | ename | job | mgr | hiredate | sal | comm | deptno | hiredate_y | |
| 0 | 7369 | SMITH | CLERK | 7902.0 | 1980/12/17 | 800 | NaN | 20 | 1981/12/03 |
| 1 | 7499 | ALLEN | SALESMAN | 7698.0 | 1981/02/20 | 1600 | 300.0 | 30 | 1981/03/01 |
| 2 | 7521 | WARD | SALESMAN | 7698.0 | 1981/02/22 | 1250 | 500.0 | 30 | 1981/03/01 |
| 6 | 7566 | JONES | MANAGER | 7839.0 | 1981/04/02 | 2975 | NaN | 20 | 1981/11/17 |
| 7 | 7698 | BLAKE | MANAGER | 7839.0 | 1981/03/01 | 2850 | NaN | 30 | 1981/11/17 |
| 8 | 7782 | CLARK | MANAGER | 7839.0 | 1981/01/09 | 2450 | NaN | 10 | 1981/11/17 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트
반응형
댓글