포스팅 목차
112. Display those managers name whose salary is more than an average salary of his employees.
* 담당하고 있는 부서에 속한 직원들의 평균 급여보다 더 많은 급여를 수령하고 있는 관리자 정보를 출력하시오.
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- 56번 참조(상관서브쿼리 - Correlated Subquery), 61/62번(Any / All)
- 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리, 평균
- 데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql
|
1. Oracle(오라클)
서브쿼리를 사용하여 관리자(‘mgr’) 사원번호 목록에 존재하는 사원번호(‘empno’)를 선택하여서 관리자 역할을 수행하는 직원을 선택하고, 상관서브쿼리를 사용하여서 관리자 본인이 관리하고 있는 부서 직원 평균 급여보다 더 많은 급여를 수령하는 관리자 정보를 출력한다.
| Oracle Programming |
select ename, sal
from emp e
where empno in (select mgr from emp)
and e.sal > (select avg(sal) from emp where mgr=e.empno);
2. Python Pandas(파이썬)
emp 테이블에서 사원번호가 관리자(‘mgr’) 사원번호에 존재하는 관리자들의 데이터를 선택하고, 관리자별 급여 평균을 계산한 후 2개의 추출 결과를 inner Join으로 결합하여 관리자의 급여가 담당하고 있는 부서 직원들의 평균 급여보다 더 많이 수령하는 관리자들을 추출한다.
| Python Programming |
pd.merge( emp[(emp.empno.isin(emp.mgr))],
(emp['sal'].groupby(emp['mgr']).agg([np.mean])),
how='inner',
left_on=['empno'], right_on=['mgr'] ).query('sal > mean')
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | mean | |
| 1 | 7698 | BLAKE | MANAGER | 7839.0 | 1981/03/01 | 2850 | NaN | 30 | 1310.000000 |
| 2 | 7782 | CLARK | MANAGER | 7839.0 | 1981/01/09 | 2450 | NaN | 10 | 1300.000000 |
| 3 | 7788 | SCOTT | ANALYST | 7566.0 | 1982/12/09 | 3000 | NaN | 20 | 1100.000000 |
| 4 | 7839 | KING | PRESIDENT | NaN | 1981/11/17 | 5000 | NaN | 10 | 2758.333333 |
| 5 | 7902 | FORD | ANALYST | 7566.0 | 1981/12/03 | 3000 | NaN | 20 | 800.000000 |
3. R Programming (R Package)
emp 테이블에서 사원번호(‘empno’)가 관리자(‘mgr’) 사원번호에 존재하는 관리자들의 데이터를 선택하고, 관리자별 급여 평균을 계산한 후 2개의 추출 결과를 inner Join으로 결합하여 관리자의 급여가 담당하고 있는 부서 직원들의 평균 급여보다 더 많이 수령하는 관리자들을 추출한다.
| R Programming |
%%R
subset( merge( (emp[emp$empno %in% emp$mgr, ]),
( aggregate(sal ~ mgr, data = emp, FUN = function(x) c(sal_mean = mean(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
2 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1310.000
3 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 1300.000
4 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 1100.000
5 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 2758.333
6 7902 FORD ANALYST 7566 1981-12-03 3000 NA 20 800.000
4. R Dplyr Package
emp 테이블에서 사원번호(‘empno’)가 관리자(‘mgr’) 사원번호에 존재하는 관리자들의 데이터를 선택하고, 관리자별 급여 평균을 계산한 후 2개의 추출 결과를 inner Join으로 결합하여 관리자의 급여가 담당하고 있는 부서 직원들의 평균 급여보다 더 많이 수령하는 관리자들을 추출한다.
| R Programming |
%%R
emp %>%
dplyr::filter(empno %in% ( emp%>%
dplyr::select(mgr) %>%
dplyr::pull() ) ) %>%
dplyr::inner_join (emp %>% group_by(mgr) %>% summarise(sal_mean = mean(sal)) , by = c("empno" = "mgr") ) %>%
dplyr::filter(sal > sal_mean)
| Results |
`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 5 x 9
empno ename job mgr hiredate sal comm deptno sal_mean
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <dbl>
1 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1310
2 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 1300
3 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 1100
4 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 2758.
5 7902 FORD ANALYST 7566 1981-12-03 3000 NA 20 800
5. R sqldf Package
서브쿼리를 사용하여 관리자(‘mgr’) 사원번호 목록에 존재하는 사원번호(‘empno’)를 선택하여서 관리자 역할을 수행하는 직원을 선택하고, 상관서브쿼리를 사용하여서 관리자 본인이 관리하고 있는 부서 직원 평균 급여보다 더 많은 급여를 수령하는 관리자 정보를 출력한다.
| R Programming |
%%R
sqldf(" select ename, sal from emp e
where empno in (select mgr from emp where mgr is not null)
and e.sal > (select avg(sal) from emp where mgr = e.empno);")
| Results |
ename sal
1 BLAKE 2850
2 CLARK 2450
3 SCOTT 3000
4 KING 5000
5 FORD 3000
emp 테이블의 사원번호(‘empno’)와 중복을 제거한 관리자(‘mgr’) 사원번호를 기준으로 결합하여서 관리자 역할을 수행하는 사원들을 선택하고, emp테이블에서 관리자 사원번호를 기준으로 평균 급여를 계산한 결과(관리자 산하에 근무하는 직원의 평균 급여)를 추가로 Inner Join을 수행하여서 관리자의 급여가 본인이 관리하고 있는 부서 직원 평균 급여보다 더 많은 급여를 수령하는 관리자 정보를 출력한다.
| R Programming |
%%R
sqldf("SELECT emp.*
FROM emp,
(select distinct MGR from emp) b,
(select mgr, avg(sal) sal_avg from emp group by mgr) c
WHERE emp.empno = b.mgr
and emp.empno = c.mgr
and emp.sal > sal_avg")
| Results |
empno ename job mgr hiredate sal comm deptno
1 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30
2 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10
3 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20
4 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10
5 7902 FORD ANALYST 7566 1981-12-03 3000 NA 20
6. Python pandasql Package
서브쿼리를 사용하여 관리자(‘mgr’) 사원번호 목록에 존재하는 사원번호(‘empno’)를 선택하여서 관리자 역할을 수행하는 직원을 선택하고, 상관서브쿼리를 사용하여서 관리자 본인이 관리하고 있는 부서 직원 평균 급여보다 더 많은 급여를 수령하는 관리자 정보를 출력한다.
| Python Programming |
ps.sqldf(" select ename, sal \
from emp e \
where empno in (select mgr from emp where mgr is not null) \
and e.sal>(select avg(sal) from emp where mgr=e.empno);")
| Results |
| ename | sal | |
| 0 | BLAKE | 2850 |
| 1 | CLARK | 2450 |
| 2 | SCOTT | 3000 |
| 3 | KING | 5000 |
| 4 | FORD | 3000 |
| Python Programming |
ps.sqldf("SELECT emp.* \
FROM emp, \
(select distinct MGR from emp) b, \
(select mgr,avg(sal) sal_avg from emp group by mgr) c \
WHERE emp.empno = b.mgr \
and emp.empno = c.mgr \
and emp.sal > sal_avg")
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | |
| 0 | 7698 | BLAKE | MANAGER | 7839.0 | 1981/03/01 | 2850 | None | 30 |
| 1 | 7782 | CLARK | MANAGER | 7839.0 | 1981/01/09 | 2450 | None | 10 |
| 2 | 7788 | SCOTT | ANALYST | 7566.0 | 1982/12/09 | 3000 | None | 20 |
| 3 | 7839 | KING | PRESIDENT | NaN | 1981/11/17 | 5000 | None | 10 |
| 4 | 7902 | FORD | ANALYST | 7566.0 | 1981/12/03 | 3000 | None | 20 |
7. R data.table Package
emp 테이블에서 사원번호(‘empno’)가 관리자(‘mgr’) 사원번호에 존재하는 관리자들의 데이터를 선택하고, emp 테이블에서 관리자(‘mgr’) 사원번호별 평균 급여를 계산한 후 2개의 추출 결과를 inner Join으로 결합하여 관리자의 급여가 담당하고 있는 부서 직원들의 평균 급여보다 더 많이 수령하는 관리자들을 추출한다.
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
salgrade_DT <- data.table(salgrade)
merge( DT[ DT[,(empno %in% DT[]$mgr) ] ],
DT[ , .(sal_mean = mean(sal)), by = .(mgr)],
by.x=c("empno"),
by.y=c("mgr"),all=F)[sal > sal_mean]
| Results |
empno ename job mgr hiredate sal comm deptno sal_mean
1: 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1310.000
2: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 1300.000
3: 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 1100.000
4: 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 2758.333
5: 7902 FORD ANALYST 7566 1981-12-03 3000 NA 20 800.000
8. SAS Proc SQL
서브쿼리를 사용하여 관리자(‘mgr’) 사원번호 목록에 존재하는 사원번호(‘empno’)를 선택하여서 관리자 역할을 수행하는 직원을 선택하고, 상관서브쿼리를 사용하여서 관리자 본인이 관리하고 있는 부서 직원 평균 급여보다 더 많은 급여를 수령하는 관리자 정보를 출력한다.
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_1 AS
select ename, sal
from emp e
where empno in (select mgr from emp)
and e.sal > (select avg(sal) from emp where mgr=e.empno);
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | ename | sal |
| 1 | BLAKE | 2850 |
| 2 | CLARK | 2450 |
| 3 | SCOTT | 3000 |
| 4 | KING | 5000 |
| 5 | FORD | 3000 |
9. SAS Data Step
| SAS Programming |
%%SAS sas
PROC SORT DATA=emp OUT=EMP_1(RENAME=( EMPNO = MGR_EMPNO));
BY EMPNO;
RUN;
PROC SORT DATA=emp OUT=EMP_2(RENAME=( MGR = MGR_EMPNO)
KEEP = MGR) NODUPKEY;
BY MGR;
RUN;
DATA STATSAS_2;
MERGE EMP_1(IN=A) EMP_2(IN=B);
BY MGR_EMPNO;
IF A AND B THEN OUTPUT;
KEEP ENAME MGR_EMPNO SAL;
RUN;
PROC SUMMARY DATA=EMP NWAY;
CLASS MGR;
VAR SAL;
OUTPUT OUT=EMP_S1(DROP=_:) MEAN=SAL_MEAN;
RUN;
PROC SORT DATA=STATSAS_2 OUT=EMP_3;
BY MGR_EMPNO;
RUN;
PROC SORT DATA=EMP_S1 OUT=EMP_S2(RENAME=( MGR = MGR_EMPNO) ) NODUPKEY;
BY MGR;
RUN;
DATA STATSAS_2;
MERGE EMP_3(IN=A) EMP_S2(IN=B);
BY MGR_EMPNO;
IF A AND B;
IF SAL > SAL_MEAN THEN OUTPUT;
RUN;
PROC PRINT;RUN;
| Results |
| OBS | MGR_EMPNO | ename | sal | SAL_MEAN |
| 1 | 7698 | BLAKE | 2850 | 1310 |
| 2 | 7782 | CLARK | 2450 | 1300 |
| 3 | 7788 | SCOTT | 3000 | 1100 |
| 4 | 7839 | KING | 5000 | 2758 |
| 5 | 7902 | FORD | 3000 | 800 |
10. Python Dfply Package
| Python Programming |
# [참고] 함수 사용. (52번 예제 참고) : AttributeError: 'DataFrame' object has no attribute 'ix'
@pipe
def pull_list(df, column=-1):
return df.loc[:, column]
@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 >> \
filter_by(X.empno.isin( emp >> select(X.mgr) >> pull_list("mgr"))) >> \
inner_join_merge( (emp >> group_by('mgr') >> summarize(sal_mean = X.sal.mean())) ,left_on = ["empno"], right_on = ["mgr"]) >> \
filter_by(X.sal > X.sal_mean) >> \
select(~X.job, ~X.hiredate, ~X.comm)
| Results |
| empno | ename | mgr_x | sal | deptno | mgr_y | sal_mean | |
| 1 | 7698 | BLAKE | 7839.0 | 2850 | 30 | 7698.0 | 1310.000000 |
| 2 | 7782 | CLARK | 7839.0 | 2450 | 10 | 7782.0 | 1300.000000 |
| 3 | 7788 | SCOTT | 7566.0 | 3000 | 20 | 7788.0 | 1100.000000 |
| 4 | 7839 | KING | NaN | 5000 | 10 | 7839.0 | 2758.333333 |
| 5 | 7902 | FORD | 7566.0 | 3000 | 20 | 7902.0 | 800.000000 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트
반응형
'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글
| [데이터 추출] 상관 서브쿼리 & 카테시안 곱(Cartesian Product) - 114 (0) | 2022.12.07 |
|---|---|
| [데이터 추출] 결측치 대체, 카테시안 곱(Cartesian Product) - Full Join, Any 연산자 - 113 (0) | 2022.12.07 |
| [데이터 추출] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리, 평균 - 111 (0) | 2022.12.06 |
| [데이터 추출] 해당 그룹에 대한 특정 조건을 만족하는 그룹 리스트 출력 - 110 (0) | 2022.12.06 |
| [데이터 추출] 그룹 집계 후 특정 조건을 만족하는 그룹의 집계 통계량 출력 - 109 (0) | 2022.12.05 |
댓글