포스팅 목차
149. Display those mangers who are getting less than his employees Sal.
* 담당하고 있는 부서의 부서원들 보다 적은 급여를 수령하는 관리자의 정보를 출력하시오.
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- Any : 61/62/113/114
- 상관서브쿼리 참고 : 112
- [데이터 추출] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리(Any), 비등가 데이터 결합
- 데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql
|
1. Oracle(오라클)
상관 서브쿼리를 사용하여 서브쿼리 내에서 본인이 관리자로 담당하고 있는 부서의 부서원 급여와 메인(외부)쿼리에 위치한 본인의 급여를 비교하여서 본인의 급여가 담당부서 직원의 급여보다 적은 경우가 존재하면(직원 한 사람 이상이 본인 급여 보다 더 많이 수령하는 경우) 급여를 수령하는 관리자의 직원 정보를 출력하시오. ANY 연산자를 사용하여서 서브쿼리에서 반환되는 값과 비교하여서 하나 이상을 만족하는 경우에 해당 결과를 반환한다.
| Oracle Programming |
Select empno
from emp e
where sal < any(select sal from emp where mgr = e.empno);
인라인뷰(Inline view)에서 관리자 사원번호를 기준으로 소속된 직원들의 최소 급여를 집계 후 emp 테이블과 self join 형식으로 결합하여서 본인이 관리자(‘mgr’)로 담당하고 있는 부서의 부서원들의 최소 급여(‘b.min_sal’)보다 적은 급여(a.sal)를 수령하고 있는 관리자의 직원 정보(‘a.*’)를 출력한다.
| Oracle Programming |
select a.deptno,a.sal,a.empno,a.ename
from emp a, (select mgr,min(sal) min_sal from emp group by mgr) b
where a.empno = b.mgr
and a.sal < b.min_sal
2. Python Pandas(파이썬)
emp 테이블의 사원번호(‘empno’)가 관리자 사원번호(‘mgr’)에 존재하는 데이터를 추출하고, 관리자 사원번호를 기준으로 속해있는 사원들의 급여(‘sal’) 중에서 최소 급여를 추출하여 float64로 형 변환한다. 이 2개의 결과를 merge() 함수로 사원번호와 관리자 사원번호를 기준으로 내부조인(inner Join)을 수행하여 관리자의 급여(‘sal’)가 관리하고 있는 부서원의 최소 급여(‘amin’)보다 적게 수령하는 관리자의 사원 정보(왼쪽 emp 테이블)를 출력한다.
- emp 테이블의 급여(‘sal’)가 float64 형식으로 저장되어 있어서 최소 급여에 대하여 float64로 변환을 해야 Join 이 실행 됨.
| Python Programming |
pd.merge(emp[(emp.empno.isin(emp.mgr))],
(emp['sal'].groupby(emp['mgr']).agg([np.min]).reset_index().astype(np.float64)),
how='inner',
left_on=['empno'], right_on=['mgr']).query('sal < amin')
| Results |
| empno | ename | job | mgr_x | hiredate | sal | comm | deptno | mgr_y | amin | |
| 0 | 7566 | JONES | MANAGER | 7839.0 | 1981/04/02 | 2975 | NaN | 20 | 7566.0 | 3000.0 |
- [참고] emp 테이블의 속성 정보
| Python Programming |
emp.info()
| Results |
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 14 entries, 0 to 13
Data columns (total 8 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 empno 14 non-null int64
1 ename 14 non-null object
2 job 14 non-null object
3 mgr 13 non-null float64
4 hiredate 14 non-null object
5 sal 14 non-null int64
6 comm 4 non-null float64
7 deptno 14 non-null int64
dtypes: float64(2), int64(3), object(3)
memory usage: 1.0+ KB
3. R Programming (R Package)
관리자 사원번호를 기준으로 관리하고 있는 사원들의 급여(‘sal’) 중에서 최소 급여를 추출한다. emp 테이블과 앞에서 추출한 결과를 merge() 함수로 사원번호와 관리자 사원번호를 기준으로 내부조인(inner Join)을 수행하여 관리자의 급여(‘sal.x’)가 관리하고 있는 부서원의 최소 급여(‘sal.y’)보다 적게 수령하는 관리자의 사원 정보(왼쪽 emp 테이블)를 출력한다.
| R Programming |
%%R
subset( merge( emp,
( aggregate(sal ~ mgr, data = emp, FUN = function(x) c(sal_min = min(x) )) ),
by.x=c("empno"),
by.y=c("mgr"),all=F) ,
(sal.x < sal.y) )
| Results |
empno ename job mgr hiredate sal.x comm deptno sal.y
1 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 3000
4. R Dplyr Package
inner_join() 함수 내부에서 관리자 사원번호를 기준으로 관리하고 있는 사원들의 급여(‘sal’) 중에서 최소 급여(‘sal_min’)를 집계한다. emp 테이블과 앞에서 추출한 결과를 dplyr::inner_join() 함수로 사원번호와 관리자 사원번호를 기준으로 내부조인(inner Join)을 수행하여 관리자의 급여(‘sal’)가 관리하고 있는 부서원의 최소 급여(‘sal_min’)보다 적게 수령하는 관리자의 사원 정보(왼쪽 emp 테이블 정보)를 출력한다.
| R Programming |
%%R
emp %>%
dplyr::inner_join (emp %>% group_by(mgr) %>% summarise(sal_min = min(sal, na.rm=TRUE)) , by = c("empno" = "mgr") ) %>%
dplyr::filter(sal < sal_min)
| Results |
`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 1 x 9
empno ename job mgr hiredate sal comm deptno sal_min
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <dbl>
1 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 3000
5. R sqldf Package
- SQLite에서는 ANY 구문을 지원하지 않음.
- ANY : sal > ANY(SELECT sal FROM emp WHERE job=’SALESMAN’)
- min : sal > (SELECT min(sal) FROM emp WHERE job=’SALESMAN’)
- ALL : sal > ALL(SELECT sal FROM emp WHERE job=’SALESMAN’)
- max : sal > (SELECT max(sal) FROM emp WHERE job=’SALESMAN’)
상관 서브쿼리를 사용하여 서브쿼리 내에서 본인이 관리자로 담당하고 있는 부서의 부서원 최소급여와 메인(외부)쿼리에 위치한 본인의 급여를 비교하여서 본인의 급여가 담당부서 직원의 최소급여보다 적게 수령하는 관리자의 직원 정보를 출력한다. 서브쿼리에서 반환되는 최소 급여와 비교하여서 관리자의 급여가 적은 경우 해당 결과를 반환한다.
| R Programming |
%%R
sqldf("select ename,sal,deptno
from emp e
where sal < (select min(sal) from emp where mgr=e.empno)")
| Results |
ename sal deptno
1 JONES 2975 20
인라인뷰(Inline view)에서 관리자 사원번호를 기준으로 소속된 직원들의 최소 급여를 집계 후 emp 테이블과 self join 형식으로 결합하여서 본인이 관리자(‘mgr’)로 담당하고 있는 부서의 부서원들의 최소 급여(‘b.min_sal’)보다 적은 급여(a.sal)를 수령하고 있는 관리자의 직원 정보(‘a.*’)를 출력한다.
| R Programming |
%%R
sqldf(" select a.deptno,a.sal,a.empno,a.ename
from emp a, ( select mgr,min(sal) min_sal from emp group by mgr) b
where a.empno = b.mgr
and a.sal < b.min_sal")
| Results |
deptno sal empno ename
1 20 2975 7566 JONES
6. Python pandasql Package
| Python Programming |
ps.sqldf(" select distinct a.sal,a.empno,a.ename,b.sal \
from emp a, emp b \
where a.empno =b.mgr and a.sal<b.sal")
| Results |
| sal | empno | ename | sal | |
| 0 | 2975 | 7566 | JONES | 3000 |
7. R data.table Package
emp 테이블의 사원번호(‘empno’)가 관리자 사원번호(‘mgr’)에 존재하는 데이터를 추출하고, 관리자 사원번호를 기준으로 관리하고 있는 사원들의 급여(‘sal’) 중에서 최소 급여(‘sal_min’)를 추출한다. 이 2개의 결과를 merge() 함수로 사원번호(‘empno’)와 관리자 사원번호(‘mgr’)를 기준으로 내부조인(inner Join)을 수행하여 관리자의 급여(‘sal’)가 관리하고 있는 부서원의 최소 급여(‘sal_min’)보다 적게 수령하는 관리자의 사원 정보(왼쪽 emp 테이블)를 출력한다.
| R Programming |
%%R
DT <- data.table(emp)
merge( DT[ DT[,(empno %in% DT[]$mgr) ] ],
DT[ , .(sal_min = min(sal)), by = .(mgr)],
by.x=c("empno"),
by.y=c("mgr"),all=F)[sal < sal_min]
| Results |
empno ename job mgr hiredate sal comm deptno sal_min
1: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 3000
8. SAS Proc SQL
상관 서브쿼리를 사용하여 서브쿼리 내에서 본인이 관리자로 담당하고 있는 부서의 부서원 급여와 메인(외부)쿼리에 위치한 본인의 급여를 비교하여서 본인의 급여가 담당부서 직원의 급여보다 적은 경우가 존재하면(직원 한 사람 이상이 본인 급여 보다 더 많이 수령하는 경우) 급여를 수령하는 관리자의 직원 정보를 출력하시오. ANY 연산자를 사용하여서 서브쿼리에서 반환되는 값과 비교하여서 하나 이상을 만족하는 경우에 해당 결과를 반환한다.
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_1 AS
Select empno, sal, deptno
from emp e
where sal < any(select sal from emp where mgr = e.empno);
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | empno | sal | deptno |
| 1 | 7566 | 2975 | 20 |
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_2 AS
select ename,sal,deptno
from emp e
where sal < (select min(sal) from emp where mgr=e.empno);
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | empno | sal | deptno |
| 1 | JONES | 2975 | 20 |
인라인뷰(Inline view)에서 관리자 사원번호를 기준으로 소속된 직원들의 최소 급여를 집계 후 emp 테이블과 self join 형식으로 결합하여서 본인이 관리자(‘mgr’)로 담당하고 있는 부서의 부서원들의 최소 급여(‘b.min_sal’)보다 적은 급여(a.sal)를 수령하고 있는 관리자의 직원 정보(‘a.*’)를 출력한다.
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_3 AS
select a.deptno,a.sal,a.empno,a.ename
from emp a, ( select mgr,min(sal) as min_sal from emp group by mgr) b
where a.empno = b.mgr
and a.sal < b.min_sal;
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | deptno | sal | empno | ename |
| 1 | 20 | 2975 | 7566 | JONES |
9. SAS Data Step
| SAS Programming |
%%SAS sas
proc sort data=emp out=emp_1(rename=empno=join_key);
by empno;
run;
proc summary data=emp nway;
class mgr;
var sal;
output out=sal_min(drop=_:) min=sal_min;
quit;
proc sort data=sal_min out=sal_min_1(rename=mgr=join_key keep=mgr sal_min);
by mgr;
run;
DATA STATSAS_4;
merge emp_1(in=a) sal_min_1(in=b);
by join_key;
if a and b;
if sal < sal_min then output;
rename join_key = empno;
keep sal join_key ename sal_min;
RUN;
PROC PRINT;RUN;
| Results |
| OBS | empno | ename | sal | sal_min |
| 1 | 7566 | JONES | 2975 | 3000 |
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 >> group_by('mgr') >> summarize ( sal_min = X.sal.min())),
left_on = ['empno'] , right_on = ['mgr']) >>\
filter_by(X.sal < X.sal_min)
| Results |
| empno | ename | job | mgr_x | hiredate | sal | comm | deptno | mgr_y | sal_min | |
| 0 | 7566 | JONES | MANAGER | 7839.0 | 1981/04/02 | 2975 | NaN | 20 | 7566.0 | 3000 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트
댓글