포스팅 목차
159. list ename, job, annual sal, deptno, dname and grade who earn 30000 per year and who are not clerks.
* 사무직(‘clerks’) 직원을 제외하고 년간 30000 달러를 초과하여 수령하는 직원의 정보를 출력하시오.
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- 유사문제 : 103 / 104
- [데이터 추출] 특정 조건을 만족하는 데이터 추출 - 비등가 데이터 결합, Between 연산, 결측치 처리, 다중 테이블 결합
- 데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql
|
1. Oracle(오라클)
사원정보(‘EMP’) 테이블을 기준으로 사무직 직원을 제외하고 수수료(‘comm’)를 고려한 연봉이 3000 달러를 초과하는 직원을 선택하고, 급여등급(‘SALGRADE’) 테이블을 비등가 방식으로 결합을 하여 급여가 속한 해당 급여등급(‘grade’)을 조회하고, 부서정보(‘DEPT’) 테이블을 조회하여 해당 부서명(‘dname’)을 조회하여 출력한다.
| Oracle Programming |
Select e.ename, e.job,
(e.sal+nvl(e.comm,0))*12 AS annualsal ,
e.deptno, d.dname, s.grade
from emp e, salgrade s , dept d
where e.sal between s.losal and s.hisal
and e.deptno = d.deptno
and (e.sal+nvl(comm,0))*12 > 30000
and e.job <> 'CLERK';
2. Python Pandas(파이썬)
사원정보(‘EMP’) 테이블과 부서정보(‘DEPT’) 테이블을 조회하여 부서명(‘dname’)을 출력하고, 급여등급 테이블을 Cross Join(카테시안 곱)을 수행 후 급여가 포함된 급여등급(‘salgrade’)을 조회한 후 사무직(‘clerk’) 직무를 제외하고, 수수료(‘comm’)를 포함한 년간 급여가 30000 달러를 초과하는 직원들의 정보를 출력한다.
| Python Programming |
pd.merge( pd.merge( emp,
dept, how='inner', left_on=['deptno'], right_on=['deptno'])[['job','sal',"deptno","ename","dname","comm"]].assign(foo=1),
salgrade.assign(foo=1), on ='foo').query("losal <= sal <= hisal & job != 'CLERK' ").loc[lambda x: (x['sal'] + x['comm'].replace(np.nan,0)) * 12 > 30000 , ["sal","deptno","ename","dname","comm","grade","losal","hisal"] ]
| Results |
| sal | deptno | ename | dname | comm | grade | losal | hisal | |
| 8 | 2975 | 20 | JONES | RESEARCH | NaN | 4 | 2001 | 3000 |
| 13 | 3000 | 20 | SCOTT | RESEARCH | NaN | 4 | 2001 | 3000 |
| 23 | 3000 | 20 | FORD | RESEARCH | NaN | 4 | 2001 | 3000 |
| 36 | 1250 | 30 | MARTIN | SALES | 1400.0 | 2 | 1201 | 1400 |
| 43 | 2850 | 30 | BLAKE | SALES | NaN | 4 | 2001 | 3000 |
| 64 | 5000 | 10 | KING | ACCOUNTING | NaN | 5 | 3001 | 9999 |
3. R Programming (R Package)
사원정보(‘EMP’) 테이블과 부서정보(‘DEPT’) 테이블을 부서코드(‘deptno’)를 기준으로 내부조인(inner Join)을 수행하여 부서명(‘dname’)을 출력하고, 앞의 조회 결과와 급여등급(‘SALGRADE’) 테이블을 Cross Join(카테시안 곱)을 수행 후 급여가 포함된 급여등급(‘salgrade’)을 조회하고, 수수료(‘comm’)를 포함한 년간 급여가 30000 달러를 초과하는 직원들 중에서 사무직(‘clerk’) 직무를 제외한 직원들의 정보를 출력한다.
| R Programming |
%%R
subset( base::merge( ( base::merge( emp ,
dept, by.x=c("deptno"),by.y=c("deptno"),all=F )[,c("job","sal","deptno","ename","dname","comm")]
),
salgrade, by = character(),all.x=TRUE, all.y=TRUE )[,c("job","sal","deptno","ename","dname","comm","losal","hisal")],
between(sal, losal, hisal) & (sal + ifelse(is.na(comm),0,comm)) *12 > 30000 & job != 'CLERK')
| Results |
job sal deptno ename dname comm losal hisal
26 SALESMAN 1250 30 MARTIN SALES 1400 1201 1400
48 MANAGER 2975 20 JONES RESEARCH NA 2001 3000
49 ANALYST 3000 20 FORD RESEARCH NA 2001 3000
50 ANALYST 3000 20 SCOTT RESEARCH NA 2001 3000
52 MANAGER 2850 30 BLAKE SALES NA 2001 3000
58 PRESIDENT 5000 10 KING ACCOUNTING NA 3001 9999
4. R Dplyr Package
사원정보(‘EMP’) 테이블과 부서정보(‘DEPT’) 테이블을 부서코드(‘deptno’)를 기준으로 내부조인(inner Join)을 수행하여 부서명(‘dname’)을 출력하고, 앞의 조회 결과와 급여등급(‘SALGRADE’) 테이블을 full_join() 함수를 사용하여 Cross Join(카테시안 곱)을 수행 후 급여가 포함된 급여등급(‘salgrade’)을 조회하고, 수수료(‘comm’)를 포함한 년간 급여가 30000 달러를 초과하는 직원들 중에서 사무직(‘clerk’) 직무를 제외한 직원들의 정보를 출력한다.
| R Programming |
%%R
emp %>%
dplyr::inner_join( dept , by = c('deptno' = 'deptno')) %>%
dplyr::full_join( salgrade , by = character()) %>%
dplyr::filter(between(sal, losal, hisal ) & ((sal + ifelse(is.na(comm),0,comm))) * 12 > 30000 & job != 'CLERK')
| Results |
# A tibble: 6 x 13
empno ename job mgr hiredate sal comm deptno dname loc grade losal hisal
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <dbl>
1 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 RESEARCH DALLAS 4 2001 3000
2 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 SALES CHICAGO 2 1201 1400
3 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 SALES CHICAGO 4 2001 3000
4 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 RESEARCH DALLAS 4 2001 3000
5 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 ACCOUNTING NEW YORK 5 3001 9999
6 7902 FORD ANALYST 7566 1981-12-03 3000 NA 20 RESEARCH DALLAS 4 2001 3000
5. R sqldf Package
사원정보(‘EMP’) 테이블을 기준으로 사무직 직원을 제외하고 수수료(‘comm’)를 고려한 연봉이 3000 달러를 초과하는 직원을 선택하고, 급여등급(‘SALGRADE’) 테이블을 비등가 방식으로 결합을 하여 급여가 속한 해당 급여등급(‘grade’)을 조회하고, 부서정보(‘DEPT’) 테이블을 조회하여 해당 부서명(‘dname’)을 조회하여 출력한다.
| R Programming |
%%R
sqldf(" Select e.ename, e.job,
(e.sal+ifnull(e.comm,0))*12 as annualsal ,
e.deptno, d.dname, s.grade
from emp e, salgrade s , dept d
where e.sal between s.losal and s.hisal
and e.deptno=d.deptno
and (e.sal+ifnull(comm,0))*12> 30000
and e.job <> 'CLERK'; ")
| Results |
ename job annualsal deptno dname grade
1 JONES MANAGER 35700 20 RESEARCH 4
2 MARTIN SALESMAN 31800 30 SALES 2
3 BLAKE MANAGER 34200 30 SALES 4
4 SCOTT ANALYST 36000 20 RESEARCH 4
5 KING PRESIDENT 60000 10 ACCOUNTING 5
6 FORD ANALYST 36000 20 RESEARCH 4
6. Python pandasql Package
| Python Programming |
ps.sqldf(" Select emp.ename, emp.job, (emp.sal+ifnull(emp.comm,0))*12 annualsal , emp.deptno, d.dname, salgrade.grade \
from emp , salgrade , dept d \
where emp.sal between salgrade.losal and salgrade.hisal \
and emp.deptno=d.deptno \
and (emp.sal+ifnull(comm,0))*12> 30000 \
and emp.job <> 'CLERK'; ")
| Results |
| ename | job | annualsal | deptno | dname | grade | |
| 0 | JONES | MANAGER | 35700.0 | 20 | RESEARCH | 4 |
| 1 | MARTIN | SALESMAN | 31800.0 | 30 | SALES | 2 |
| 2 | BLAKE | MANAGER | 34200.0 | 30 | SALES | 4 |
| 3 | SCOTT | ANALYST | 36000.0 | 20 | RESEARCH | 4 |
| 4 | KING | PRESIDENT | 60000.0 | 10 | ACCOUNTING | 5 |
| 5 | FORD | ANALYST | 36000.0 | 20 | RESEARCH | 4 |
7. R data.table Package
사원정보(‘EMP’) 테이블과 부서정보(‘DEPT’) 테이블을 부서코드(‘deptno’)를 기준으로 내부조인(inner Join)을 수행하여 부서명(‘dname’)을 출력하고, 앞의 조회 결과와 급여등급(‘SALGRADE’) 테이블을 비등가 방식으로 결합하여 급여가 포함된 급여등급(‘salgrade’)을 조회하고, 수수료(‘comm’)를 포함한 년간 급여가 30000 달러를 초과하는 직원들 중에서 사무직(‘clerk’) 직무를 제외한 직원들의 정보를 출력한다.
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
salgrade_DT <- data.table(salgrade)
DT[dept_DT, nomatch=NULL,
on = .( deptno=deptno ),
.(sal,deptno,ename,dname,job,comm)][salgrade_DT, nomatch=NULL,
on = .( sal >= losal , sal <= hisal),
.(x.sal,deptno,ename,dname,losal,hisal,job,comm)][((x.sal+fcoalesce(comm,0)) *12) > 30000 & (job != 'CLERK')]
| Results |
x.sal deptno ename dname losal hisal job comm
1: 1250 30 MARTIN SALES 1201 1400 SALESMAN 1400
2: 2975 20 JONES RESEARCH 2001 3000 MANAGER NA
3: 3000 20 SCOTT RESEARCH 2001 3000 ANALYST NA
4: 3000 20 FORD RESEARCH 2001 3000 ANALYST NA
5: 2850 30 BLAKE SALES 2001 3000 MANAGER NA
6: 5000 10 KING ACCOUNTING 3001 9999 PRESIDENT NA
8. SAS Proc SQL
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_1 AS
Select e.ename, e.job,
(e.sal+coalesce(e.comm,0))*12 AS annualsal ,
e.deptno, d.dname, s.grade
from emp e, salgrade s , dept d
where e.sal between s.losal and s.hisal
and e.deptno = d.deptno
and (e.sal+coalesce(comm,0))*12 > 30000
and e.job ne 'CLERK';
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | ename | job | annualsal | deptno | dname | grade |
| 1 | JONES | MANAGER | 35700 | 20 | RESEARCH | 4 |
| 2 | MARTIN | SALESMAN | 31800 | 30 | SALES | 2 |
| 3 | BLAKE | MANAGER | 34200 | 30 | SALES | 4 |
| 4 | SCOTT | ANALYST | 36000 | 20 | RESEARCH | 4 |
| 5 | KING | PRESIDEN | 60000 | 10 | ACCOUNTING | 5 |
| 6 | FORD | ANALYST | 36000 | 20 | RESEARCH | 4 |
9. SAS Data Step
- DATA STEP(CROSS JOIN-카테시안 곱);
| SAS Programming |
%%SAS sas
PROC SORT DATA=EMP OUT=EMP_1;
BY DEPTNO;
RUN;
PROC SORT DATA=dept OUT=dept_1;
BY DEPTNO;
RUN;
DATA STATSAS_2;
MERGE EMP_1(in=a) dept_1(in=b);
BY DEPTNO;
if a and b;
annualsal = (sal+coalesce(comm,0))*12;
DO I=1 TO KOBS;
SET salgrade NOBS=KOBS POINT=I;
IF (sal+coalesce(comm,0))*12 > 30000
and job ne 'CLERK'
and (sal >= losal AND sal <= hisal) THEN OUTPUT;
END;
keep ename job deptno dname grade annualsal;
RUN;
PROC PRINT;RUN;
| Results |
| OBS | ename | job | deptno | dname | annualsal | grade |
| 1 | KING | PRESIDEN | 10 | ACCOUNTING | 60000 | 5 |
| 2 | JONES | MANAGER | 20 | RESEARCH | 35700 | 4 |
| 3 | SCOTT | ANALYST | 20 | RESEARCH | 36000 | 4 |
| 4 | FORD | ANALYST | 20 | RESEARCH | 36000 | 4 |
| 5 | MARTIN | SALESMAN | 30 | SALES | 31800 | 2 |
| 6 | BLAKE | MANAGER | 30 | SALES | 34200 | 4 |
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.assign(foo=1) >> \
inner_join_merge ( dept, left_on=['deptno'], right_on=['deptno'], suffixes=["_x", "_y"]) >> \
full_join ( salgrade.assign(foo=1), by="foo") >> \
filter_by( X.sal.between(X.losal, X.hisal) ,
(X.sal+if_else(X.comm.isnull(),0,X.comm)) *12 >30000 ,
X.job != 'CLERK') >> \
select (~X.job, ~X.hiredate, ~X.comm, ~X.ename)
| Results |
| empno | mgr | sal | deptno | foo | dname | loc | grade | losal | hisal | |
| 8 | 7566 | 7839.0 | 2975 | 20 | 1 | RESEARCH | DALLAS | 4 | 2001 | 3000 |
| 13 | 7788 | 7566.0 | 3000 | 20 | 1 | RESEARCH | DALLAS | 4 | 2001 | 3000 |
| 23 | 7902 | 7566.0 | 3000 | 20 | 1 | RESEARCH | DALLAS | 4 | 2001 | 3000 |
| 36 | 7654 | 7698.0 | 1250 | 30 | 1 | SALES | CHICAGO | 2 | 1201 | 1400 |
| 43 | 7698 | 7839.0 | 2850 | 30 | 1 | SALES | CHICAGO | 4 | 2001 | 3000 |
| 64 | 7839 | NaN | 5000 | 10 | 1 | ACCOUNTING | NEW YORK | 5 | 3001 | 9999 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트
반응형
댓글