포스팅 목차
60. Display the job groups having total salary greater than the maximum salary for managers.
* 직무별 총 급여가 관리자의 최대 급여보다 더 큰 직무 그룹을 출력하시오.
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- [데이터 추출-테이블 데이터 전처리 비교] Having 절에 비상관 서브쿼리 사용
|
1. Oracle(오라클)
- 직무별 급여 합계를 계산 후 서브쿼리에서 계산된 관리자의 최대 급여 보다 큰 직무 그룹을 출력한다.
- 현재 예제에서는 모든 직무의 급여 합계가 관리자의 최대 급여보다 크기 때문에 전체 관측치가 출력된다. 'MANAGER'를 'PRESIDENT'로 변경하여서 데이터 확인 가능.
| Oracle Programming |
select job, sum(sal)
from emp
group
by job
having sum(sal) > (select max(sal) from emp where job='MANAGER');
2. Python Pandas(파이썬)
filter 함수를 사용하여서 직무별 급여 합계가 관리자의 최대 급여보다 큰 직무에 해당하는 관측치를 선택하고, 추가로 직무별 급여 합계를 계산한다.
| Python Programming |
emp.groupby('job').filter(lambda x: x['sal'].sum() > ( max(emp[emp['job']=='MANAGER']['sal']) )).groupby('job')['sal'].sum()
| Results |
job
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
Name: sal, dtype: int64
직무별 급여 합계를 계산하고, 계산된 직무별 합계가 관리자의 최대 급여보다 큰 직무의 급여 합계를 선택한다.
| Python Programming |
emp.groupby('job')['sal'].sum().loc[lambda x:x > ( max(emp[emp['job']=='MANAGER']['sal']) )]
| Results |
job
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
Name: sal, dtype: int64
3. R Programming (R Package)
aggregate 함수를 사용하여서 직무별 급여 합계를 계산하고, subset 함수를 사용하여서 직무 그룹별 급여 합계가 관리자의 최대 급여보다 큰 직무의 급여 합계를 선택한다.
| R Programming |
%%R
subset( aggregate(sal ~ job, data = emp, FUN = function(x) c(sum_sal = sum(x) ) ), sal > max(emp[emp$job == 'MANAGER',]$sal) )
| Results |
job sal
1 ANALYST 6000
2 CLERK 4150
3 MANAGER 8275
4 PRESIDENT 5000
5 SALESMAN 5600
4. R Dplyr Package
직무별 급여 합계를 1차적으로 계산하고, filter 함수를 사용하여서 계산된 직무별 급여 합계가 관리자의 최대 급여보다 큰 직무 정보를 출력한다.
| R Programming |
%%R
emp %>%
group_by(job) %>%
summarise( sal_sum=sum(sal) ) %>%
filter(sal_sum > ( emp %>%
filter(job == "MANAGER") %>%
summarise(max(sal, na.rm = TRUE)) %>%
pull() )
)
| Results |
`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 5 x 2
job sal_sum
<chr> <dbl>
1 ANALYST 6000
2 CLERK 4150
3 MANAGER 8275
4 PRESIDENT 5000
5 SALESMAN 5600
5. R sqldf Package
- 직무별 급여 합계를 계산 후 서브쿼리에서 계산된 관리자의 최대 급여 보다 큰 직무 그룹을 출력한다.
- 현재 예제에서는 모든 직무의 급여 합계가 관리자의 최대 급여보다 크기 때문에 전체 관측치가 출력된다. 'MANAGER'를 'PRESIDENT'로 변경하여서 데이터 확인 가능.
| R Programming |
%%R
sqldf(" SELECT E.JOB
FROM emp E
GROUP BY E.JOB
HAVING SUM(E.SAL) > (SELECT MAX(E.SAL) FROM emp E WHERE E.job='MANAGER')")
| Results |
job
1 ANALYST
2 CLERK
3 MANAGER
4 PRESIDENT
5 SALESMAN
6. Python pandasql Package
| Python Programming |
ps.sqldf("SELECT E.JOB FROM emp E GROUP BY E.JOB HAVING SUM(E.SAL) > (SELECT MAX(E.SAL) FROM emp E WHERE E.job='MANAGER')")
| Results |
| job | |
| 0 | ANALYST |
| 1 | CLERK |
| 2 | MANAGER |
| 3 | PRESIDENT |
| 4 | SALESMAN |
7. R data.table Package
.SD(subset of Dataframe)을 사용하여서 keyby에서 지정한 직무를 기준으로 그룹(직무별)별 급여 합계가 관리자의 최대 급여보다 큰 직무를 선택한 후 해당 직무의 급여 합계를 계산한다.
| R Programming |
%%R
DT <- data.table(emp)
DT[, .SD[sum(sal, na.rm = TRUE) > (DT[job == "MANAGER", max(sal)]),
.('sum_sal' = sum(sal,na.rm = TRUE))], keyby = .(job)]
| Results |
job sum_sal
1: ANALYST 6000
2: CLERK 4150
3: MANAGER 8275
4: PRESIDENT 5000
5: SALESMAN 5600
8. SAS Proc SQL
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_1 AS
select job, sum(sal) AS SAL_SUM
from emp
group
by 1
having sum(sal) > (select max(sal) from emp where job='MANAGER');
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | job | SAL_SUM |
| 1 | ANALYST | 6000 |
| 2 | CLERK | 4150 |
| 3 | MANAGER | 8275 |
| 4 | PRESIDEN | 5000 |
| 5 | SALESMAN | 5600 |
9. SAS Data Step
- DATA STEP(CROSS JOIN-카테시안 곱);
| SAS Programming |
%%SAS sas
PROC SUMMARY DATA=EMP NWAY;
CLASS JOB;
VAR SAL;
OUTPUT OUT=STATSAS_2(DROP=_:) SUM=SAL_JOB;
RUN;
PROC SUMMARY DATA=EMP NWAY;
CLASS JOB;
VAR SAL;
OUTPUT OUT=MANAGER_MAX(DROP=_:) MAX=MANAGER_MAX;
WHERE job='MANAGER';
RUN;
DATA STATSAS_3;
SET MANAGER_MAX;
DO I=1 TO KOBS;
SET STATSAS_2 NOBS=KOBS POINT=I;
IF SAL_JOB > MANAGER_MAX THEN OUTPUT;
END;
RUN;
PROC PRINT;RUN;
| Results |
| OBS | job | MANAGER_MAX | SAL_JOB |
| 1 | ANALYST | 2975 | 6000 |
| 2 | CLERK | 2975 | 4150 |
| 3 | MANAGER | 2975 | 8275 |
| 4 | PRESIDEN | 2975 | 5000 |
| 5 | SALESMAN | 2975 | 5600 |
| SAS Programming |
%%SAS sas
PROC SUMMARY DATA=EMP NWAY;
CLASS JOB;
VAR SAL;
OUTPUT OUT=STATSAS_2(DROP=_:) SUM=SAL_JOB;
RUN;
proc sql noprint;
select MAX(SAL) into :SAL_MANAGER
from EMP
WHERE job='MANAGER';
quit;
%put &SAL_MANAGER;
DATA STATSAS_3;
SET STATSAS_2;
IF SAL_JOB > &SAL_MANAGER THEN OUTPUT;
RUN;
PROC PRINT;RUN;
| Results |
| OBS | job | SAL_JOB |
| 1 | ANALYST | 6000 |
| 2 | CLERK | 4150 |
| 3 | MANAGER | 8275 |
| 4 | PRESIDEN | 5000 |
| 5 | SALESMAN | 5600 |
10. Python Dfply Package
| Python Programming |
# [참고] 함수 사용. (52번 예제 참고)
@pipe
def pull_fun(df, column=-1):
return df.loc[:, column].item()
emp >> group_by('job') >> \
summarize( sal_sum = X.sal.sum()) >> \
filter_by( X.sal_sum > (emp >> filter_by(X.job == "MANAGER") >> summarize(sal_max = X.sal.max()) >> pull_fun("sal_max")) )
| Results |
| job | sal_sum | |
| 0 | ANALYST | 6000 |
| 1 | CLERK | 4150 |
| 2 | MANAGER | 8275 |
| 3 | PRESIDENT | 5000 |
| 4 | SALESMAN | 5600 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트
반응형
댓글