포스팅 목차
97. Display grade and employees name for the dept no 10 or 30 but grade is not 4, while joined the company before 31-dec-82.
* 부서번호 10 또는 30 에 근무하고, 급여 등급이 4 등급 이외 등급이고, 1982년 12월 31일 이전에 입사한 직원의 정보를 출력하시오.
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- CROSS JOIN 구문
- 비등가 조인에 해당하는 데이터 추출
|
1. Oracle(오라클)
조건절에 위에 명시한 조건을 지정하여서 해당 직원의 정보를 출력한다. salgrade 테이블은 비등가조인으로 해당 급여를 포함하는 범위의 급여 등급을 선택한다.
| Oracle Programming |
select ename,grade
from emp,salgrade
where sal between losal and hisal
and deptno in (10,30)
and grade <> 4
and hiredate < '31-DEC-82';
2. Python Pandas(파이썬)
emp 테이블과 salgrade 테이블에 임시 변수(‘foo’)를 지정하여서 임시 변수를 기준으로 cross join(Cartesian Product)을 수행한 후 query 구문에서 왼쪽 테이블의 기준 값을 기준으로 오른쪽 테이블에서 local <= sal <= hisal 사이에 포함된 값(‘grade’)을 선택하고, 이 값을 포함한 추가 조건을 만족하는 직원의 정보를 출력한다.
| Python Programming |
pd.merge(emp.assign(foo=1), salgrade.assign(foo=1), on ='foo').query('losal <= sal <= hisal & deptno == [10,30] & grade != 4 & hiredate<"1982/12/31"').drop(['job','comm','deptno'],axis=1)
| Results |
| empno | ename | mgr | hiredate | sal | foo | grade | sal | hisal |
| 7499 | ALLEN | 7698.0 | 1981/02/20 | 1600 | 1 | 3 | 1401 | 2000 |
| 7521 | WARD | 7698.0 | 1981/02/22 | 1250 | 1 | 2 | 1201 | 1400 |
| 7654 | MARTIN | 7698.0 | 1981/09/28 | 1250 | 1 | 2 | 1201 | 1400 |
| 7839 | KING | NaN | 1981/11/17 | 5000 | 1 | 5 | 3001 | 9999 |
| 7844 | TURNER | 7698.0 | 1981/09/08 | 1500 | 1 | 3 | 1401 | 2000 |
| 7900 | JAMES | 7698.0 | 1981/12/03 | 950 | 1 | 1 | 700 | 1200 |
| 7934 | MILLER | 7782.0 | 1982/01/23 | 1300 | 1 | 2 | 1201 | 1400 |
3. R Programming (R Package)
emp 테이블과 salgrade 테이블을 기준 변수에 character()을 지정하여서 cross join(Cartesian Product)을 수행한 후 subset 구문에서 왼쪽 테이블의 기준 값을 기준으로 오른쪽 테이블에서 between(sal, losal, hisal) 사이에 포함된 값(‘grade’)을 선택하고 하고, 이 값을 포함한 추가 조건을 만족하는 직원의 정보를 출력한다.
| R Programming |
%%R
subset( merge(emp, salgrade,by= character(),all.x=TRUE, all.y=TRUE),
between(sal, losal, hisal) & deptno %in% c(10,30) & grade != 4 & hiredate<'1982-12-31' )
| Results |
empno ename job mgr hiredate sal comm deptno fake.x grade losal hisal fake.y
12 7900 JAMES CLERK 7698 1981-12-03 950 NA 30 1 1 700 1200 1
17 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1 2 1201 1400 1
19 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1 2 1201 1400 1
28 7934 MILLER CLERK 7782 1982-01-23 1300 NA 10 1 2 1201 1400 1
30 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1 3 1401 2000 1
38 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 1 3 1401 2000 1
65 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 1 5 3001 9999 1
4. R Dplyr Package
emp 테이블과 salgrade 테이블을 기준 변수에 character()을 지정하여서 full_join 문법을 사용하여서 cross join(Cartesian Product)을 수행한 후 filter 함수에서 왼쪽 테이블의 기준 값을 기준으로 오른쪽 테이블에서 between(sal, losal, hisal) 사이에 포함된 값(‘grade’)을 선택하고 하고, 이 값을 포함한 추가 조건을 만족하는 직원의 정보를 출력한다.
| R Programming |
%%R
emp %>%
dplyr::full_join(salgrade, by = character()) %>%
dplyr::filter( between(sal, losal, hisal ) & deptno %in% c(10,30) & grade != 4 & hiredate<'1982-12-31' )
| Results |
# A tibble: 7 x 13
empno ename job mgr hiredate sal comm deptno fake.x grade losal hisal fake.y
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1 3 1401 2000 1
2 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1 2 1201 1400 1
3 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1 2 1201 1400 1
4 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 1 5 3001 9999 1
5 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 1 3 1401 2000 1
6 7900 JAMES CLERK 7698 1981-12-03 950 NA 30 1 1 700 1200 1
7 7934 MILLER CLERK 7782 1982-01-23 1300 NA 10 1 2 1201 1400 1
- Cross Join 생성 예제(dummy= 사용 방식)
emp 테이블과 salgrade 테이블에 임시 dummy 변수를 지정하고, left_join 문법을 사용하여서 임시 변수를 기준으로 cross join(Cartesian Product)을 수행하여서 해당 급여가 포함하는 범위의 급여 등급을 선택한다.
| R Programming |
%%R
emp %>%
dplyr::mutate(dummy=TRUE) %>%
dplyr::left_join ( salgrade %>%mutate(dummy=TRUE) ) %>%
head()
| Results |
Joining, by = c("fake", "dummy")
# A tibble: 6 x 13
empno ename job mgr hiredate sal comm deptno fake dummy grade losal hisal
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <dbl> <lgl> <dbl> <dbl> <dbl>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1 TRUE 1 700 1200
2 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1 TRUE 2 1201 1400
3 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1 TRUE 3 1401 2000
4 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1 TRUE 4 2001 3000
5 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1 TRUE 5 3001 9999
6 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1 TRUE 1 700 1200
- Cross Join 생성 예제(가변수 생성 방식)
emp 테이블과 salgrade 테이블에 임시 fake 변수를 지정하고, full_join 문법을 사용하여서 임시 변수(‘fake’)를 기준으로 cross join(Cartesian Product)을 수행하여서 해당 급여가 포함하는 범위의 급여 등급을 선택한다.
| R Programming |
%%R
emp$fake <- 1
salgrade$fake <- 1
full_join(emp, salgrade, by = "fake") %>%
head()
| Results |
# A tibble: 6 x 12
empno ename job mgr hiredate sal comm deptno fake grade losal hisal
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1 1 700 1200
2 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1 2 1201 1400
3 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1 3 1401 2000
4 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1 4 2001 3000
5 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1 5 3001 9999
6 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1 1 700 1200
5. R sqldf Package
조건절에 위에서 명시한 조건을 지정하여서 해당 직원의 정보를 출력한다. salgrade 테이블은 비등가조인(NON-EQUI JOIN) 으로 해당 급여를 포함하는 범위의 급여 등급을 선택한다.
| R Programming |
%%R
sqldf("select ename,grade
from emp,salgrade
where sal between losal and hisal
and deptno in (10,30)
and grade <> 4
and hiredate < '31-DEC-82';")
| Results |
ename grade
1 ALLEN 3
2 WARD 2
3 MARTIN 2
4 KING 5
5 TURNER 3
6 JAMES 1
7 MILLER 2
6. Python pandasql Package
조건절에 위에 명시한 조건을 지정하여서 해당 직원의 정보를 출력한다. salgrade 테이블은 비등가조인으로 해당 급여를 포함하는 범위의 급여 등급을 선택한다.
| Python Programming |
ps.sqldf("select ename,grade \
from emp,salgrade \
where sal between losal and hisal \
and deptno in (10,30) \
and grade <> 4 \
and hiredate<'31-DEC-82';")
| Results |
| ename | grade |
| ALLEN | 3 |
| WARD | 2 |
| MARTIN | 2 |
| KING | 5 |
| TURNER | 3 |
| JAMES | 1 |
| MILLER | 2 |
7. R data.table Package
emp 테이블과 salgrade 테이블을 기준으로 비등가조인(NON-EQUI JOIN) 으로 해당 급여를 포함하는 범위의 급여 등급을 선택하고, 이 값을 포함한 추가 조건을 만족하는 직원의 정보를 출력한다.
- data.table은 비등가조인(NON-EQUI JOIN)을 지원하는 것이 장점이다.
| 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), ][deptno %in% c(10,30) & grade!=4 & hiredate<'1982-12-31' ,empno:deptno]
| Results |
empno ename job mgr hiredate sal comm deptno
1: 7900 JAMES CLERK 7698 1981-12-03 700 NA 30
2: 7521 WARD SALESMAN 7698 1981-02-22 1201 500 30
3: 7654 MARTIN SALESMAN 7698 1981-09-28 1201 1400 30
4: 7934 MILLER CLERK 7782 1982-01-23 1201 NA 10
5: 7499 ALLEN SALESMAN 7698 1981-02-20 1401 300 30
6: 7844 TURNER SALESMAN 7698 1981-09-08 1401 0 30
7: 7839 KING PRESIDENT NA 1981-11-17 3001 NA 10
8. SAS Proc SQL
조건절에 위에 명시한 조건을 지정하여서 해당 직원의 정보를 출력한다. salgrade 테이블은 비등가조인으로 해당 급여를 포함하는 범위의 급여 등급을 선택한다.
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_1 AS
select ename,grade
from emp,salgrade
where sal between losal and hisal
and deptno in(10,30)
and grade NE 4
and hiredate < '31dec82'd;
QUIT;
PROC PRINT;RUN;
| Results |
| ename | grade |
| ALLEN | 3 |
| WARD | 2 |
| MARTIN | 2 |
| KING | 5 |
| TURNER | 3 |
| JAMES | 1 |
| MILLER | 2 |
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_2 AS
select ename,grade
from emp , salgrade
where sal between losal and hisal
and deptno in (10,30)
and grade NE 4
and hiredate < mdy(12,31,1982);
QUIT;
PROC PRINT;RUN;
| Results |
| ename | grade |
| ALLEN | 3 |
| WARD | 2 |
| MARTIN | 2 |
| KING | 5 |
| TURNER | 3 |
| JAMES | 1 |
| MILLER | 2 |
9. SAS Data Step
- DATA STEP(CROSS JOIN-카테시안 곱)
| SAS Programming |
%%SAS sas
DATA STATSAS_3;
SET EMP;
DO I=1 TO KOBS;
SET salgrade NOBS=KOBS POINT=I;
IF SAL >= losal AND SAL <= hisal
and deptno in (10,30)
and grade NE 4
and hiredate < mdy(12,31,1982) THEN OUTPUT;
END;
RUN;
PROC PRINT;RUN;
10. Python Dfply Package
| Python Programming |
@pipe
def inner_join_merge(df, other, left_on,right_on,suffixes=['_x','_y']):
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( salgrade.assign(foo=1), left_on = ["foo"], right_on = ["foo"] ) >> \
filter_by(X.sal.between(X.losal, X.hisal, inclusive = True) , \
X.deptno.isin([10,30]), \
X.grade != 4, \
X.hiredate < "1982/12/31") >> \
select(~X.job, ~X.hiredate, ~X.comm)
| Results |
| empno | ename | mgr | sal | deptno | foo | grade | losal | hisal |
| 7499 | ALLEN | 7698.0 | 1600 | 30 | 1 | 3 | 1401 | 2000 |
| 7521 | WARD | 7698.0 | 1250 | 30 | 1 | 2 | 1201 | 1400 |
| 7654 | MARTIN | 7698.0 | 1250 | 30 | 1 | 2 | 1201 | 1400 |
| 7839 | KING | NaN | 5000 | 10 | 1 | 5 | 3001 | 9999 |
| 7844 | TURNER | 7698.0 | 1500 | 30 | 1 | 3 | 1401 | 2000 |
| 7900 | JAMES | 7698.0 | 950 | 30 | 1 | 1 | 700 | 1200 |
| 7934 | MILLER | 7782.0 | 1300 | 10 | 1 | 2 | 1201 | 1400 |
| Python Programming |
emp >> \
mutate(dummy=1) >> \
left_join( salgrade >> mutate(dummy=1) ) >> \
filter_by(X.sal.between(X.losal, X.hisal, inclusive = True) , \
X.deptno.isin([10,30]), \
X.grade != 4, \
X.hiredate < "1982/12/31") >> \
select(~X.job, ~X.hiredate, ~X.comm)
| Results |
| empno | ename | mgr | sal | deptno | dummy | grade | losal | hisal |
| 7499 | ALLEN | 7698.0 | 1600 | 30 | 1 | 3 | 1401 | 2000 |
| 7521 | WARD | 7698.0 | 1250 | 30 | 1 | 2 | 1201 | 1400 |
| 7654 | MARTIN | 7698.0 | 1250 | 30 | 1 | 2 | 1201 | 1400 |
| 7839 | KING | NaN | 5000 | 10 | 1 | 5 | 3001 | 9999 |
| 7844 | TURNER | 7698.0 | 1500 | 30 | 1 | 3 | 1401 | 2000 |
| 7900 | JAMES | 7698.0 | 950 | 30 | 1 | 1 | 700 | 1200 |
| 7934 | MILLER | 7782.0 | 1300 | 10 | 1 | 2 | 1201 | 1400 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트
반응형
'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글
| [데이터 삭제] Where 조건절을 만족하는 데이터 삭제-비상관서브쿼리 - 99 (0) | 2022.09.28 |
|---|---|
| [데이터 변환] 조건절을 만족하는 데이터 변경 - 98 (0) | 2022.09.28 |
| [데이터 추출] 값이 존재하지 않는 NULL 데이터 추출 - 96 (0) | 2022.09.28 |
| [데이터 추출] 내부 조인(Inner Join)을 만족하는 데이터 추출 - 95 (0) | 2022.09.27 |
| [데이터 추출] 비등가 내부 조인(Inner Join)을 만족하는 데이터 추출 - 94 (0) | 2022.09.27 |
댓글