포스팅 목차
164. find out the employees who earned the min Sal for their job in ascending order.
* 개별 직무에서 최소 급여를 받는 사원들의 정보를 출력하시오
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- [데이터 추출] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리(Min)
- 데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql
|
1. Oracle(오라클)
상관 서브쿼리를 사용하여서 개별 직무에서 최소 급여를 받는 사원들의 정보를 출력한다.
| Oracle Programming |
select *
from emp e
where sal = (select min(sal) from emp where job=e.job)
order
by sal;
2. Python Pandas(파이썬)
emp 테이블에서 개별 직무(‘job’)별로 최소 급여를 집계 후에 직무 코드(‘job’)와 최소 급여(‘amin’)를 기준으로 사원 정보(EMP) 테이블의 직무 코드(‘job’)과 급여(‘sal’)와 매칭되는 사원 정보를 출력한다.결과적으로 직무별로 최소 급여를 수령하는 사원 정보가 출력된다.
| Python Programming |
pd.merge(emp,
(emp['sal'].groupby(emp['job']).agg([np.min])),
how='inner',
left_on=['job','sal'], right_on=['job','amin']).drop(['amin'], axis='columns').sort_values(by=['sal'],axis = 0)
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | |
| 0 | 7369 | SMITH | CLERK | 7902.0 | 1980/12/17 | 800 | NaN | 20 |
| 1 | 7521 | WARD | SALESMAN | 7698.0 | 1981/02/22 | 1250 | 500.0 | 30 |
| 2 | 7654 | MARTIN | SALESMAN | 7698.0 | 1981/09/28 | 1250 | 1400.0 | 30 |
| 3 | 7782 | CLARK | MANAGER | 7839.0 | 1981/01/09 | 2450 | NaN | 10 |
| 4 | 7788 | SCOTT | ANALYST | 7566.0 | 1982/12/09 | 3000 | NaN | 20 |
| 5 | 7902 | FORD | ANALYST | 7566.0 | 1981/12/03 | 3000 | NaN | 20 |
| 6 | 7839 | KING | PRESIDENT | NaN | 1981/11/17 | 5000 | NaN | 10 |
3. R Programming (R Package)
emp 테이블에서 aggregate() 함수를 사용하여서 개별 직무(‘job’)별로 최소 급여를 집계 후에 직무 코드(‘job’)와 최소 급여(‘sal’)를 기준으로 사원 정보(EMP) 테이블의 직무 코드(‘job’)과 급여(‘sal’)와 매칭되는 사원 정보를 출력하여 직무별로 최소 급여를 수령하는 사원 정보를 출력된다.
- 출력 결과를 arrage() 함수를 사용하여 급여를 기준으로 오름차순으로 정렬하여 출력한다.
| R Programming |
%%R
arrange ( merge( emp,
( aggregate(sal ~ job, data = emp, FUN = function(x) c( sal_min = min(x) )) ),
by.x=c("job","sal"),
by.y=c("job","sal"),all=F) ,
sal )
| Results |
job sal empno ename mgr hiredate comm deptno
1 CLERK 800 7369 SMITH 7902 1980-12-17 NA 20
2 SALESMAN 1250 7654 MARTIN 7698 1981-09-28 1400 30
3 SALESMAN 1250 7521 WARD 7698 1981-02-22 500 30
4 MANAGER 2450 7782 CLARK 7839 1981-01-09 NA 10
5 ANALYST 3000 7902 FORD 7566 1981-12-03 NA 20
6 ANALYST 3000 7788 SCOTT 7566 1982-12-09 NA 20
7 PRESIDENT 5000 7839 KING NA 1981-11-17 NA 10
출력 결과를 별도의 테이블로 생성 후 급여를 기준으로 오름차순으로 정렬하여 출력한다.
| R Programming |
%%R
withmooc <- merge( emp,
( aggregate(sal ~ job, data = emp, FUN = function(x) c( sal_min = min(x) )) ),
by.x=c("job","sal"),
by.y=c("job","sal"),all=F)
withmooc[ order(withmooc$sal), ]
| Results |
job sal empno ename mgr hiredate comm deptno
3 CLERK 800 7369 SMITH 7902 1980-12-17 NA 20
6 SALESMAN 1250 7654 MARTIN 7698 1981-09-28 1400 30
7 SALESMAN 1250 7521 WARD 7698 1981-02-22 500 30
4 MANAGER 2450 7782 CLARK 7839 1981-01-09 NA 10
1 ANALYST 3000 7902 FORD 7566 1981-12-03 NA 20
2 ANALYST 3000 7788 SCOTT 7566 1982-12-09 NA 20
5 PRESIDENT 5000 7839 KING NA 1981-11-17 NA 10
출력 결과를 별도의 테이블로 생성 후 테이블에서 2번째 위치한 급여(‘sal’) 칼럼을 기준으로 오름차순으로 정렬하여 출력한다.
| R Programming |
%%R
withmooc <- merge( emp,
( aggregate(sal ~ job, data = emp, FUN = function(x) c( sal_min = min(x) )) ),
by.x=c("job","sal"),
by.y=c("job","sal"),all=F)
withmooc[ order(withmooc[,2]), ]
| Results |
job sal empno ename mgr hiredate comm deptno
3 CLERK 800 7369 SMITH 7902 1980-12-17 NA 20
6 SALESMAN 1250 7654 MARTIN 7698 1981-09-28 1400 30
7 SALESMAN 1250 7521 WARD 7698 1981-02-22 500 30
4 MANAGER 2450 7782 CLARK 7839 1981-01-09 NA 10
1 ANALYST 3000 7902 FORD 7566 1981-12-03 NA 20
2 ANALYST 3000 7788 SCOTT 7566 1982-12-09 NA 20
5 PRESIDENT 5000 7839 KING NA 1981-11-17 NA 10
4. R Dplyr Package
emp 테이블에서 dplyr::summarise() 함수를 사용하여서 개별 직무(‘job’)별로 최소 급여를 집계 후에 직무 코드(‘job’)와 최소 급여(‘sal_min’)를 기준으로 사원 정보(EMP) 테이블의 직무 코드(‘job’)과 급여(‘sal’)와 매칭되는 사원 정보를 출력하여 직무별로 최소 급여를 수령하는 사원 정보를 출력된다.
| R Programming |
%%R
emp %>%
dplyr::inner_join (emp %>% group_by(job) %>% dplyr::summarise(sal_min = min(sal)) %>% ungroup(),
by = c("job","sal" = "sal_min") ) %>%
dplyr::arrange(sal)
| Results |
`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 7 x 8
empno ename job mgr hiredate sal comm deptno
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20
2 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
3 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
4 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10
5 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20
6 7902 FORD ANALYST 7566 1981-12-03 3000 NA 20
7 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10
5. R sqldf Package
상관 서브쿼리를 사용하여서 개별 직무에서 최소 급여를 받는 사원들의 정보를 출력한다.
| R Programming |
%%R
sqldf(" select *
from emp e
where sal = (select min(sal) from emp where job=e.job) order by sal;")
| Results |
empno ename job mgr hiredate sal comm deptno
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20
2 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
3 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
4 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10
5 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20
6 7902 FORD ANALYST 7566 1981-12-03 3000 NA 20
7 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10
6. Python pandasql Package
상관 서브쿼리를 사용하여서 개별 직무에서 최소 급여를 받는 사원들의 정보를 출력한다.
| Python Programming |
ps.sqldf(" select * from emp e \
where sal = (select min(sal) from emp where job=e.job) order by sal; ")
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | |
| 0 | 7369 | SMITH | CLERK | 7902.0 | 1980/12/17 | 800 | NaN | 20 |
| 1 | 7521 | WARD | SALESMAN | 7698.0 | 1981/02/22 | 1250 | 500.0 | 30 |
| 2 | 7654 | MARTIN | SALESMAN | 7698.0 | 1981/09/28 | 1250 | 1400.0 | 30 |
| 3 | 7782 | CLARK | MANAGER | 7839.0 | 1981/01/09 | 2450 | NaN | 10 |
| 4 | 7788 | SCOTT | ANALYST | 7566.0 | 1982/12/09 | 3000 | NaN | 20 |
| 5 | 7902 | FORD | ANALYST | 7566.0 | 1981/12/03 | 3000 | NaN | 20 |
| 6 | 7839 | KING | PRESIDENT | NaN | 1981/11/17 | 5000 | NaN | 10 |
7. R data.table Package
emp 테이블에서 개별 직무(‘job’)별로 최대 급여(‘sal_max’)를 집계 후에 merge 결합 방식으로 직무 코드(‘job’)와 최소 급여(‘sal_min’)를 기준으로 사원 정보(EMP) 테이블의 직무 코드(‘job’)과 급여(‘sal’)와 매칭되는 사원 정보를 출력하여 직무별로 최소 급여를 수령하는 사원 정보를 출력된다.
- key에 의한 정렬 방법은 고려 안 함.
- 출력 결과를 order() 함수를 사용하여 급여를 기준으로 오름차순으로 정렬하여 출력한다.
| R Programming |
%%R
DT <- data.table(emp)
merge(DT, DT[ , .(sal_min = min(sal)), by = .(job)],
by.x=c("job","sal"),
by.y=c("job","sal_min"),all=F)[order(sal)]
| Results |
job sal empno ename mgr hiredate comm deptno
1: CLERK 800 7369 SMITH 7902 1980-12-17 NA 20
2: SALESMAN 1250 7521 WARD 7698 1981-02-22 500 30
3: SALESMAN 1250 7654 MARTIN 7698 1981-09-28 1400 30
4: MANAGER 2450 7782 CLARK 7839 1981-01-09 NA 10
5: ANALYST 3000 7788 SCOTT 7566 1982-12-09 NA 20
6: ANALYST 3000 7902 FORD 7566 1981-12-03 NA 20
7: PRESIDENT 5000 7839 KING NA 1981-11-17 NA 10
emp 테이블에서 개별 직무(‘job’)별로 최소 급여(‘sal_min’)를 집계 후에 DT 결합 방식으로 직무 코드(‘job’)와 최소 급여(‘sal_min’)를 기준으로 사원 정보(EMP) 테이블의 직무 코드(‘job’)과 급여(‘sal’)와 매칭되는 사원 정보를 출력하여 직무별로 최소 급여를 수령하는 사원 정보를 출력된다.
- 출력 결과를 order() 함수를 사용하여 급여를 기준으로 오름차순으로 정렬하여 출력한다.
| R Programming |
%%R
DT <- data.table(emp)
DT[ DT[ , .(sal_min = min(sal)), by = .(job)] , on=c("job","sal" = "sal_min")][order(sal)]
| Results |
empno ename job mgr hiredate sal comm deptno
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20
2: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
3: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
4: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10
5: 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20
6: 7902 FORD ANALYST 7566 1981-12-03 3000 NA 20
7: 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10
8. SAS Proc SQL
상관 서브쿼리를 사용하여서 개별 직무에서 최소 급여를 받는 사원들의 정보를 출력한다.
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_1 AS
select *
from emp e
where sal = (select min(sal) from emp where job=e.job)
order
by sal;
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 | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 3 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 4 | 7782 | CLARK | MANAGER | 7839 | 1981-01-09 | 2450 | . | 10 |
| 5 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | . | 20 |
| 6 | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000 | . | 20 |
| 7 | 7839 | KING | PRESIDEN | . | 1981-11-17 | 5000 | . | 10 |
9. SAS Data Step
| SAS Programming |
%%SAS sas
PROC SORT DATA=EMP OUT=EMP_1(RENAME=(JOB=JOIN_KEY1 SAL=JOIN_KEY2));
BY JOB SAL;
RUN;
PROC SUMMARY DATA=EMP NWAY;
CLASS JOB;
VAR SAL;
OUTPUT OUT=EMP_GRP(DROP=_:) MIN=MIN_SAL;
QUIT;
PROC SORT DATA=EMP_GRP OUT=EMP_GRP_1(RENAME=(JOB=JOIN_KEY1 MIN_SAL=JOIN_KEY2));
BY JOB MIN_SAL;
RUN;
DATA STATSAS_2;
MERGE EMP_1(IN=A) EMP_GRP_1(IN=B);
BY JOIN_KEY1 JOIN_KEY2;
IF A AND B;
RENAME JOIN_KEY1 = JOB
JOIN_KEY2 = SAL;
RUN;
PROC SORT DATA=STATSAS_2;
BY SAL;
RUN;
PROC PRINT;RUN;
| Results |
| OBS | empno | ename | JOB | mgr | hiredate | SAL | comm | deptno |
| 1 | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | . | 20 |
| 2 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 3 | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 4 | 7782 | CLARK | MANAGER | 7839 | 1981-01-09 | 2450 | . | 10 |
| 5 | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000 | . | 20 |
| 6 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | . | 20 |
| 7 | 7839 | KING | PRESIDEN | . | 1981-11-17 | 5000 | . | 10 |
10. Python Dfply Package
| Python Programming |
@pipe
def inner_join_merge(df, other, left_on,right_on,suffixes):
joined = df.merge(other, how='inner', left_on=left_on,
right_on=right_on , suffixes=suffixes)
return joined
emp >> \
inner_join_merge( ( emp >> group_by('job') >> summarize(sal_min = X.sal.min()) ),
left_on=['job','sal'], right_on=['job','sal_min'], suffixes=["_x", "_y"])
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | sal_min | |
| 0 | 7369 | SMITH | CLERK | 7902.0 | 1980/12/17 | 800 | NaN | 20 | 800 |
| 1 | 7521 | WARD | SALESMAN | 7698.0 | 1981/02/22 | 1250 | 500.0 | 30 | 1250 |
| 2 | 7654 | MARTIN | SALESMAN | 7698.0 | 1981/09/28 | 1250 | 1400.0 | 30 | 1250 |
| 3 | 7782 | CLARK | MANAGER | 7839.0 | 1981/01/09 | 2450 | NaN | 10 | 2450 |
| 4 | 7788 | SCOTT | ANALYST | 7566.0 | 1982/12/09 | 3000 | NaN | 20 | 3000 |
| 5 | 7902 | FORD | ANALYST | 7566.0 | 1981/12/03 | 3000 | NaN | 20 | 3000 |
| 6 | 7839 | KING | PRESIDENT | NaN | 1981/11/17 | 5000 | NaN | 10 | 5000 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트
반응형
댓글