포스팅 목차
122. Display employee name, Sal, comm. and whose net pay is greater than any other in the company?
* 수수료(commission)를 포함한 직원의 급여를 가장 많이 수령하는 직원의 정보를 출력하시오.
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- 유사문제 : 51 번/113번
- [데이터 추출] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리(최대값), 결측치 처리
- 데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql
|
1. Oracle(오라클)
서브쿼리를 사용하여서 수수료(commission)를 포함한 직원의 최대 급여를 산출 후 EMP테이블에서 최대 급여와 같은 급여를 수령하는 직원 정보를 출력한다.
| Oracle Programming |
Select ename, sal, comm
from emp
where sal+nvl(comm.,0) = (select max(sal+nvl(comm.,0)) from emp);
2. Python Pandas(파이썬)
수수료(commission)를 포함한 직원의 최대 급여를 계산 후 EMP테이블에서 최대 급여와 같은 급여를 수령하는 직원 정보를 출력한다.
- OR(|) 연산자 양쪽에 괄호 없는 경우 에러
| Python Programming |
emp[ np.where(emp['comm'].isna(), emp['sal'], emp['sal']+emp['comm']) == max(np.where(emp['comm'].isna(), emp['sal'], emp['sal']+emp['comm']))]
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | |
| 8 | 7839 | KING | PRESIDENT | NaN | 1981/11/17 | 5000 | NaN | 10 |
3. R Programming (R Package)
수수료(commission)를 포함한 직원의 최대 급여를 계산 후 EMP테이블에서 최대 급여와 같은 급여를 수령하는 직원 정보를 출력한다.
| R Programming |
%%R
emp[ (emp$sal + ifelse(is.na(emp$comm),0,emp$comm)) == max((emp$sal + ifelse(is.na(emp$comm),0,emp$comm))), ]
| Results |
# A tibble: 1 x 8
empno ename job mgr hiredate sal comm deptno
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl>
1 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10
4. R Dplyr Package
수수료(commission)를 포함한 직원의 최대 급여를 계산 후 EMP테이블에서 최대 급여와 같은 급여를 수령하는 직원 정보를 출력한다.
| R Programming |
%%R
emp %>%
filter( (sal + ifelse(is.na(comm),0,comm)) == max((sal + ifelse(is.na(comm),0,comm))) )
| Results |
# A tibble: 1 x 8
empno ename job mgr hiredate sal comm deptno
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl>
1 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10
5. R sqldf Package
서브쿼리를 사용하여서 수수료(commission)를 포함한 직원의 최대 급여를 산출 후 EMP테이블에서 최대 급여와 같은 급여를 수령하는 직원 정보를 출력한다.
| R Programming |
%%R
sqldf(" Select ename, sal, comm
from emp
where sal+ifnull(comm,0) = (select max(sal+ifnull(comm,0)) from emp);")
| Results |
ename sal comm
1 KING 5000 NA
6. Python pandasql Package
서브쿼리를 사용하여서 수수료(commission)를 포함한 직원의 최대 급여를 산출 후 EMP테이블에서 최대 급여와 같은 급여를 수령하는 직원 정보를 출력한다.
| Python Programming |
ps.sqldf("Select ename, sal, comm \
from emp \
where sal+ifnull(comm,0) = (select max(sal+ifnull(comm,0)) from emp);")
| Results |
| ename | sal | comm | |
| 0 | KING | 5000 | None |
7. R data.table Package
수수료(commission)를 포함한 직원의 최대 급여를 계산 후 EMP테이블에서 최대 급여와 같은 급여를 수령하는 직원 정보를 출력한다.
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
salgrade_DT <- data.table(salgrade)
DT[ (sal+fcoalesce(comm,0)) == unlist(DT[, .(`max_value` = max((sal+fcoalesce(comm,0)), na.rm = TRUE))]),
.(ename, sal, comm)]
| Results |
ename sal comm
1: KING 5000 NA
8. SAS Proc SQL
서브쿼리를 사용하여서 수수료(commission)를 포함한 직원의 최대 급여를 산출 후 EMP테이블에서 최대 급여와 같은 급여를 수령하는 직원 정보를 출력한다.
- 113번과 유사하게 비교구문을 추가하여 문제 해결
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_1 AS
select ename, sal, comm,
sal+COALESCE(comm,0) AS netPay,
sal+IFN(comm=.,0,COMM) AS netPay_IFN
from emp E
where sal+COALESCE(comm,0) >= all(select sal+COALESCE(comm,0) from emp where empno NE E.empno);
QUIT;
PROC ;RUN;
| Results |
| OBS | ename | sal | comm | netPay | netPay_IFN |
| 1 | KING | 5000 | . | 5000 | 5000 |
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_2 AS
select ename, sal, comm,
sal+COALESCE(comm,0) AS netPay
from emp E
where sal+COALESCE(comm,0) >= (select max(sal+COALESCE(comm,0)) from emp where empno NE E.empno);
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | ename | sal | comm | netPay |
| 1 | KING | 5000 | . | 5000 |
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_3 AS
select ename, sal, comm, sal+COALESCE(comm,0) AS netPay
from emp e
where NOT EXISTS ( select sal from emp where (sal+COALESCE(comm,0)) > e.sal+COALESCE(e.comm,0) and empno NE E.empno);
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | ename | sal | comm | netPay |
| 1 | KING | 5000 | . | 5000 |
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_4 AS
SELECT A.*,
netPay_max
FROM EMP A,
( SELECT a.empno,max(B.sal+COALESCE(B.comm,0)) AS netPay_max
FROM emp a,
emp b
WHERE a.empno NE b.empno
group by a.empno ) B
WHERE A.empno = b.empno
and A.sal+COALESCE(A.comm,0) >= netPay_max;
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | empno | ename | job | mgr | hiredate | sal | comm | deptno | netPay_max |
| 1 | 7839 | KING | PRESIDEN | . | 1981-11-17 | 5000 | . | 10 | 3000 |
9. SAS Data Step
| SAS Programming |
%%SAS sas
DATA EMP_1;
SET EMP;
netPay = sal+COALESCE(comm,0);
RUN;
DATA STATSAS_3;
SET EMP_1(RENAME=(EMPNO=Max_EMPNO) KEEP=EMPNO netPay);
DO I=1 TO KOBS;
SET EMP NOBS=KOBS POINT=I;
IF EMPNO NE Max_EMPNO THEN OUTPUT;
END;
RUN;
PROC SUMMARY DATA=STATSAS_3 NWAY;
CLASS EMPNO;
VAR netPay;
OUTPUT OUT=SAL_max(DROP=_:) max=netPay_max;
QUIT;
PROC SORT DATA=emp OUT=EMP_1;
BY EMPNO;
RUN;
PROC SORT DATA=SAL_max;
BY EMPNO;
RUN;
DATA STATSAS_2;
MERGE emp(IN=A) SAL_max(IN=B);
BY EMPNO;
IF A AND B;
IF sal+COALESCE(comm,0) >= netPay_max;
Drop job mgr hiredate comm;
RUN;
PROC PRINT;RUN;
| Results |
| OBS | empno | ename | sal | deptno | netPay_max |
| 1 | 7839 | KING | 5000 | 10 | 3000 |
10. Python Dfply Package
- 조건문 116번 참고
| Python Programming |
emp >> \
filter_by( ( X.sal + make_symbolic(np.where)(X.comm.isnull(), 0, X.comm) ) ==
( X.sal + make_symbolic(np.where)(X.comm.isnull(), 0, X.comm) ).max() )
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | |
| 8 | 7839 | KING | PRESIDENT | NaN | 1981/11/17 | 5000 | NaN | 10 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트
반응형
댓글