포스팅 목차
132. Display the grade of Jones?
* Jones의 급여에 해당하는 급여 등급을 출력하시오.
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- 유사문제 : 97 / 103 / 104
- [데이터 추출] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리(부등호), 비등가 조인
- 데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql
|
1. Oracle(오라클)
서브쿼리를 사용하여서 emp테이블에서 ‘JONES’의 급여를 선택 후 급여등급테이블(SALGRADE)에서 급여 등급 하한값(‘losal’)과 상한값(‘hisal’)을 조회하여서 해당 급여를 포함하고 있는 급여 등급을 출력한다.
| Oracle Programming |
select grade
from salgrade
where losal <= (select sal from emp where ename='JONES')
and hisal >= (select sal from emp where ename='JONES');
직원정보테이블(EMP)와 급여등급테이블(SALGRADE)을 비등가 조인으로 결합하여서 직원 ‘JONES’의 급여가 포함된 급여등급을 출력한다.
| Oracle Programming |
SELECT ENAME,GRADE
FROM EMP , SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL
AND Ename='JONES';
서브쿼리에서 급여등급테이블(SALGRADE)와 직원정보테이블(EMP)을 비등가 조인으로 결합하여서 직원 ‘JONES’가 포함된 급여 등급과 해당 급여를 출력(4등급, 급여 2975)하고, 본 쿼리에서 emp 테이블과 salgrade 테이블을 Cross Join으로 결합 후 앞에서 선택한 급여 등급과 해당 급여에 해당하는 직원 정보(4등급, 급여 2975)를 출력한다.
| Oracle Programming |
select ename,grade
from emp, salgrade
where (grade,sal) = ( select grade,sal
from salgrade, emp
where sal between losal and hisal
and ename = 'JONES')
2. Python Pandas(파이썬)
직원정보테이블(EMP)과 급여등급테이블(SALGRADE)을 Cross join(카테시안 곱-Cartesian Product)을 수행한 후 query() 함수를 사용하여서 직원 이름이 “JONES”이고 JONES의 급여가 포함된 급여 등급(4등급)에 해당하는 관측치 값을 출력한다.
| Python Programming |
pd.merge(emp.assign(foo=1), salgrade.assign(foo=1), on ='foo').query(' ename == "JONES" & losal <= sal <= hisal ')
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | foo | grade | losal | hisal | |
| 18 | 7566 | JONES | MANAGER | 7839.0 | 1981/04/02 | 2975 | NaN | 20 | 1 | 4 | 2001 | 3000 |
3. R Programming (R Package)
직원정보테이블(EMP)과 급여등급테이블(SALGRADE)을 Cross join(카테시안 곱-Cartesian Product)을 수행한 후 subset() 함수를 사용하여서 직원 이름이 “JONES”이고 JONES의 급여가 포함된 급여 등급(4등급)에 해당하는 관측치 값을 출력한다.
| R Programming |
%%R
subset( merge(emp, salgrade,by= character(),all.x=TRUE, all.y=TRUE),
ename == 'JONES' & between(sal, losal, hisal) )
| Results |
empno ename job mgr hiredate sal comm deptno grade losal hisal
46 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 4 2001 3000
4. R Dplyr Package
직원정보테이블(EMP)과 급여등급테이블(SALGRADE)을 dplyr::full_join() 함수를 사용하여서 Cross join(카테시안 곱-Cartesian Product)을 수행한 후 dplyr::filter() 함수를 사용하여서 직원 이름이 “JONES”이고 JONES의 급여가 포함된 급여 등급(4등급)에 해당하는 관측치 값을 출력한다.
| R Programming |
%%R
emp %>%
dplyr::full_join( salgrade , by = character()) %>%
dplyr::filter(ename == 'JONES' , between(sal, losal, hisal ))
| Results |
# A tibble: 1 x 11
empno ename job mgr hiredate sal comm deptno grade losal hisal
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 4 2001 3000
5. R sqldf Package
서브쿼리를 사용하여서 emp테이블에서 ‘JONES’의 급여를 선택 후 급여등급테이블(SALGRADE)에서 급여 등급 하한값(‘losal’)과 상한값(‘hisal’)을 조회하여서 해당 급여를 포함하고 있는 급여 등급을 출력한다.
| R Programming |
%%R
sqldf(" select grade
from salgrade
where losal <= (select(sal) from emp where ename='JONES')
and hisal >= (select(sal) from emp where ename='JONES');")
| Results |
grade
1 4
직원정보테이블(EMP)와 급여등급테이블(SALGRADE)을 비등가 조인으로 결합하여서 직원 ‘JONES’의 급여가 포함된 급여등급을 출력한다.
| R Programming |
%%R
sqldf(" SELECT ENAME,GRADE
FROM emp,salgrade
WHERE SAL BETWEEN LOSAL AND HISAL
AND Ename='JONES';")
| Results |
ename grade
1 JONES 4
서브쿼리에서 급여등급테이블(SALGRADE)와 직원정보테이블(EMP)을 비등가 조인으로 결합하여서 직원 ‘JONES’가 포함된 급여 등급과 해당 급여를 출력(4등급, 급여 2975)하고, 본 쿼리에서 emp 테이블과 salgrade 테이블을 Cross Join으로 결합 후 앞에서 선택한 급여 등급과 해당 급여에 해당하는 직원 정보(4등급, 급여 2975)를 출력한다.
| R Programming |
%%R
sqldf(" select ename,grade
from emp, salgrade
where (grade,sal) = ( select grade,sal
from salgrade, emp
where sal between losal and hisal
and ename='JONES') ")
| Results |
grade sal
1 4 2975
6. Python pandasql Package
서브쿼리를 사용하여서 emp테이블에서 ‘JONES’의 급여를 선택 후 급여등급테이블(SALGRADE)에서 급여 등급 하한값(‘losal’)과 상한값(‘hisal’)을 조회하여서 해당 급여를 포함하고 있는 급여 등급을 출력한다.
| Python Programming |
ps.sqldf(" select grade from salgrade \
where losal <= (select(sal) from emp where ename='JONES') \
and hisal >= (select(sal) from emp where ename='JONES');")
| Results |
| grade | |
| 0 | 4 |
ps.sqldf(" SELECT ENAME,GRADE \
FROM emp, salgrade \
WHERE SAL BETWEEN LOSAL AND HISAL \
AND Ename='JONES';")
| Results |
| ename | grade | |
| 0 | JONES | 4 |
ps.sqldf(" select ename, grade \
from emp, salgrade \
where ( grade,sal) = ( select grade,sal \
from salgrade, emp \
where sal between losal and hisal and ename='JONES')")
| Results |
| ename | grade | |
| 0 | JONES | 4 |
7. R data.table Package
직원정보테이블(EMP)과 급여등급테이블(SALGRADE)을 DT 조인 방식으로 비등가 조인을 수행하여서 EMP테이블 직원 급여에 해당하는 급여 등급을 추가한 후 직원 이름이 “JONES” 인 직원의 정보를 출력한다.
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
salgrade_DT <- data.table(salgrade)
DT[salgrade_DT, nomatch=NULL, on = .( sal >= losal , sal <= hisal), ][ename == "JONES" ,empno:grade]
| Results |
empno ename job mgr hiredate sal comm deptno sal.1 grade
1: 7566 JONES MANAGER 7839 1981-04-02 2001 NA 20 3000 4
8. SAS Proc SQL
서브쿼리를 사용하여서 emp테이블에서 ‘JONES’의 급여를 선택 후 급여등급테이블(SALGRADE)에서 급여 등급 하한값(‘losal’)과 상한값(‘hisal’)을 조회하여서 해당 급여를 포함하고 있는 급여 등급을 출력한다.
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_1 AS
select grade
from salgrade
where losal <= (select sal from emp where ename='JONES')
and hisal >= (select sal from emp where ename='JONES');
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | grade |
| 1 | 4 |
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_2 AS
SELECT ENAME,GRADE
FROM EMP , SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL
AND Ename='JONES';
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | ename | grade |
| 1 | JONES | 4 |
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_3 AS
select ename,grade
from emp, salgrade
where (PUT(grade,1.)||PUT(sal,4.)) = ( select PUT(grade,1.)||PUT(sal,4.)
from salgrade, emp
where sal between losal and hisal
and ename='JONES');
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | ename | grade |
| 1 | JONES | 4 |
9. SAS Data Step
- DATA STEP(CROSS JOIN-카테시안 곱);
| SAS Programming |
%%SAS sas
DATA STATSAS_4;
SET EMP;
DO I=1 TO KOBS;
SET salgrade NOBS=KOBS POINT=I;
IF Ename='JONES' AND SAL >= losal AND SAL <= hisal THEN OUTPUT;
END;
RUN;
PROC PRINT;RUN;
| Results |
| OBS | empno | ename | job | mgr | hiredate | sal | comm | deptno | grade | losal | hisal |
| 1 | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | . | 20 | 4 | 2001 | 3000 |
10. Python Dfply Package
| Python Programming |
emp.assign(foo=1) >> full_join (salgrade.assign(foo=1), by="foo") >>\
filter_by(X.ename == 'JONES', X.sal.between(X.losal,X.hisal)) >>\
select(~X.job, ~X.mgr, ~X.deptno)
| Results |
| empno | ename | hiredate | sal | comm | foo | grade | losal | hisal | |
| 18 | 7566 | JONES | 1981/04/02 | 2975 | NaN | 1 | 4 | 2001 | 3000 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트
댓글