포스팅 목차
166. display ename, sal and deptno for each employee who earn a Sal greater than the avg of their department order by deptno
* 본인이 근무하고 있는 부서의 평균 급여보다 더 많은 급여를 수령하는 직원들의 정보를 출력하시오.
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- [데이터 추출] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리(평균)
- 데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql
- 상호연관쿼리 : 94 / 106 / 112 / 116 / 117 /121
- 유사문제(order by) : 164
|
1. Oracle(오라클)
상관 서브쿼리를 사용하여서 본인이 속한 부서의 평균 급여를 계산하고, 반환된 평균 급여보다 더 많은 급여를 수령하는 직원 정보를 출력한다.
| Oracle Programming |
select ename,sal,deptno
from emp e
where sal > (select avg(sal) from emp where deptno=e.deptno)
order
by deptno;
2. Python Pandas(파이썬)
부서별 평균 급여(‘mean’)를 계산 후 emp테이블과 self join 형식으로 결합하여서 부서별 평균 급여보다 더 많은 급여를 수령하는 직원 정보를 출력한다.
| Python Programming |
pd.merge(emp,
(emp['sal'].groupby(emp['deptno']).agg([np.mean])),
how='inner',
left_on=['deptno'], right_on=['deptno']).query('sal > mean').sort_values(by=['deptno'],axis = 0)
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | mean | |
| 12 | 7839 | KING | PRESIDENT | NaN | 1981/11/17 | 5000 | NaN | 10 | 2916.666667 |
| 1 | 7566 | JONES | MANAGER | 7839.0 | 1981/04/02 | 2975 | NaN | 20 | 2175.000000 |
| 2 | 7788 | SCOTT | ANALYST | 7566.0 | 1982/12/09 | 3000 | NaN | 20 | 2175.000000 |
| 4 | 7902 | FORD | ANALYST | 7566.0 | 1981/12/03 | 3000 | NaN | 20 | 2175.000000 |
| 5 | 7499 | ALLEN | SALESMAN | 7698.0 | 1981/02/20 | 1600 | 300.0 | 30 | 1566.666667 |
| 8 | 7698 | BLAKE | MANAGER | 7839.0 | 1981/03/01 | 2850 | NaN | 30 | 1566.666667 |
3. R Programming (R Package)
aggregate() 함수를 사용하여서 부서별 평균 급여(‘mean’)를 계산 후 emp테이블과 self join 형식으로 결합하여서 부서별 평균 급여(‘sal.y’)보다 더 많은 급여를 수령하는 직원 정보를 출력한다.
| R Programming |
%%R
arrange ( subset( merge( emp,
( aggregate(sal ~ deptno, data = emp, FUN = function(x) c(sal_mean = mean(x) )) ),
by.x=c("deptno"),
by.y=c("deptno"),all=F) ,
(sal.x > sal.y) ),
deptno)
| Results |
deptno empno ename job mgr hiredate sal.x comm sal.y
1 10 7839 KING PRESIDENT NA 1981-11-17 5000 NA 2916.667
2 20 7566 JONES MANAGER 7839 1981-04-02 2975 NA 2175.000
3 20 7902 FORD ANALYST 7566 1981-12-03 3000 NA 2175.000
4 20 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 2175.000
5 30 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 1566.667
6 30 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 1566.667
4. R Dplyr Package
dplyr::summarise() 함수를 사용하여서 부서별 평균 급여(‘sal_mean’)를 계산 후 emp테이블과 self join 형식으로 결합하여서 부서별 평균 급여(‘sal_mean’)보다 더 많은 급여를 수령하는 직원 정보를 출력한다.
| R Programming |
%%R
emp %>%
dplyr::inner_join (emp %>% group_by(deptno) %>% dplyr::summarise(sal_mean = mean(sal)) , by = c("deptno" = "deptno") ) %>%
dplyr::filter(sal > sal_mean) %>%
dplyr::arrange(deptno)
| Results |
`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 6 x 9
empno ename job mgr hiredate sal comm deptno sal_mean
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <dbl>
1 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 2917.
2 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 2175
3 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 2175
4 7902 FORD ANALYST 7566 1981-12-03 3000 NA 20 2175
5 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1567.
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1567.
5. R sqldf Package
상관 서브쿼리를 사용하여서 본인이 속한 부서의 평균 급여를 계산하고, 반환된 평균 급여보다 더 많은 급여를 수령하는 직원 정보를 출력한다.
| R Programming |
%%R
sqldf(" select ename,sal,deptno
from emp e
where sal > (select avg(sal) from emp where deptno=e.deptno) order by deptno;")
| Results |
ename sal deptno
1 KING 5000 10
2 JONES 2975 20
3 SCOTT 3000 20
4 FORD 3000 20
5 ALLEN 1600 30
6 BLAKE 2850 30
6. Python pandasql Package
상관 서브쿼리를 사용하여서 본인이 속한 부서의 평균 급여를 계산하고, 반환된 평균 급여보다 더 많은 급여를 수령하는 직원 정보를 출력한다.
| Python Programming |
ps.sqldf(" select ename,sal,deptno from emp e \
where sal > (select avg(sal) from emp where deptno=e.deptno) order by deptno; ")
| Results |
| ename | sal | deptno | |
| 0 | KING | 5000 | 10 |
| 1 | JONES | 2975 | 20 |
| 2 | SCOTT | 3000 | 20 |
| 3 | FORD | 3000 | 20 |
| 4 | ALLEN | 1600 | 30 |
| 5 | BLAKE | 2850 | 30 |
7. R data.table Package
부서별 평균 급여(‘sal_mean’)를 계산 후 emp테이블과 DT 결합 방식으로 내부조인(Inner Join)을 수행여서 부서별 평균 급여(‘sal_mean’)보다 더 많은 급여를 수령하는 직원 정보를 출력한다.
| R Programming |
%%R
DT <- data.table(emp)
merge( DT,
DT[ , .(sal_mean = mean(sal)), by = .(deptno)],
by.x=c("deptno"),
by.y=c("deptno"),all=F)[sal > sal_mean][order(rank(deptno))]
| Results |
deptno empno ename job mgr hiredate sal comm sal_mean
1: 10 7839 KING PRESIDENT NA 1981-11-17 5000 NA 2916.667
2: 20 7566 JONES MANAGER 7839 1981-04-02 2975 NA 2175.000
3: 20 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 2175.000
4: 20 7902 FORD ANALYST 7566 1981-12-03 3000 NA 2175.000
5: 30 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 1566.667
6: 30 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 1566.667
8. SAS Proc SQL
상관 서브쿼리를 사용하여서 본인이 속한 부서의 평균 급여를 계산하고, 반환된 평균 급여보다 더 많은 급여를 수령하는 직원 정보를 출력한다.
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_1 AS
select empno,ename,sal,deptno
from emp e
where sal > (select avg(sal) from emp where deptno=e.deptno)
order
by deptno;
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | empno | ename | sal | deptno |
| 1 | 7839 | KING | 5000 | 10 |
| 2 | 7902 | FORD | 3000 | 20 |
| 3 | 7566 | JONES | 2975 | 20 |
| 4 | 7788 | SCOTT | 3000 | 20 |
| 5 | 7499 | ALLEN | 1600 | 30 |
| 6 | 7698 | BLAKE | 2850 | 30 |
9. SAS Data Step
| SAS Programming |
%%SAS sas
PROC SORT DATA=EMP OUT=EMP_1(RENAME=(deptno=JOIN_KEY1));
BY deptno;
RUN;
PROC SUMMARY DATA=EMP NWAY;
class deptno;
VAR SAL;
OUTPUT OUT=EMP_GRP(DROP=_:) mean=sal_avg;
QUIT;
PROC SORT DATA=EMP_GRP OUT=EMP_GRP_1(RENAME=(deptno=JOIN_KEY1));
BY deptno;
RUN;
DATA STATSAS_2;
MERGE EMP_1(IN=A) EMP_GRP_1(IN=B);
BY JOIN_KEY1;
IF A AND B;
rename JOIN_KEY1 = deptno;
if sal > sal_avg then output;
keep empno ename sal JOIN_KEY1;
RUN;
PROC SORT DATA=STATSAS_2;
BY deptno;
RUN;
PROC PRINT;RUN;
| Results |
| OBS | empno | ename | sal | deptno |
| 1 | 7839 | KING | 5000 | 10 |
| 2 | 7566 | JONES | 2975 | 20 |
| 3 | 7788 | SCOTT | 3000 | 20 |
| 4 | 7902 | FORD | 3000 | 20 |
| 5 | 7499 | ALLEN | 1600 | 30 |
| 6 | 7698 | BLAKE | 2850 | 30 |
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('deptno') >> summarize(sal_mean = X.sal.mean()) ),
left_on=['deptno'], right_on=['deptno'], suffixes=["_x", "_y"]) >>\
filter_by(X.sal > X.sal_mean) >> \
arrange(X.deptno)
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | sal_mean | |
| 12 | 7839 | KING | PRESIDENT | NaN | 1981/11/17 | 5000 | NaN | 10 | 2916.666667 |
| 1 | 7566 | JONES | MANAGER | 7839.0 | 1981/04/02 | 2975 | NaN | 20 | 2175.000000 |
| 2 | 7788 | SCOTT | ANALYST | 7566.0 | 1982/12/09 | 3000 | NaN | 20 | 2175.000000 |
| 4 | 7902 | FORD | ANALYST | 7566.0 | 1981/12/03 | 3000 | NaN | 20 | 2175.000000 |
| 5 | 7499 | ALLEN | SALESMAN | 7698.0 | 1981/02/20 | 1600 | 300.0 | 30 | 1566.666667 |
| 8 | 7698 | BLAKE | MANAGER | 7839.0 | 1981/03/01 | 2850 | NaN | 30 | 1566.666667 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트
반응형
댓글