[데이터 추출] 비등가 FULL 조인(NON-EQUI FULL JOIN)을 통한 데이터 결합 및 데이터 정렬(SORT)

포스팅 목차

104. List out all the employees name, job, and salary grade and department name for every one in the company except ‘CLERK’. Sort on salary display the highest salary.

* 사무직(‘CLERK’) 직원을 제외한 직원들의 이름, 직무, 부서명, 직원 급여 등급을 높은(내림차순 기준) 급여 기준으로 정렬하여서 출력하시오.

다중 테이블 결합 : 103 / 104
파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
비등가 FULL 조인(NON-EQUI FULL JOIN)을 통한 데이터 결합 및 데이터 정렬(SORT)

Oracle : 비등가조인(NON-EQUI JOIN), Order by
파이썬 Pandas : pd.merge, assign(), query(), sort_values()
R 프로그래밍 : base::merge(), subset(), order()
R Dplyr Package : dplyr::inner_join(), dplyr::full_join(), dplyr::filter(), dplyr::arrange()
R sqldf Package : 비등가조인(NON-EQUI JOIN), Order by
Python pandasql Package : 비등가조인(NON-EQUI JOIN), Order by
R data.table Package : 비등가조인(NON-EQUI JOIN) 방식 데이터 결합, order()
SAS Proc SQL : 비등가조인(NON-EQUI JOIN), Order by
SAS Data Step : Merge 구문, IF 조건문, CROSS JOIN (카테시안 곱) 구현, proc sort
Python Dfply Package : @pipe & Def 사용자 정의 함수(inner_join_merge), full_join(), arrange()
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

사무직(‘CLERK’) 직원을 제외한 emp 테이블과 dept 테이블을 결합(merge)하여 부서명을 추가한 후 salgrade 테이블을 비등가 조인(NON-EQUI JOIN)을 수행하여서 해당 급여를 포함하는 범위의 급여 등급을 선택하여 출력한다.

Oracle Programming

select empno, ename, sal, dname, grade 
from  emp e, dept d, salgrade s 
where e.deptno = d.deptno 
  and e.sal between s.losal and s.hisal 
  and e.job <> 'CLERK' 
order by sal desc;

2. Python Pandas(파이썬)

emp 테이블을 self Join하여서 관리자 이름을 추가하고, dept 테이블을 결합(merge)하여 부서명을 추가한 후 salgrade 테이블을 비등가 조인(NON-EQUI JOIN)을 수행하여서 해당 급여를 포함하는 범위의 급여 등급을 선택하여 출력한다.

Python Programming

pd.merge( pd.merge(emp, dept, how='inner', left_on=['deptno'], right_on=['deptno'])[['job','sal',"deptno","ename","dname"]].assign(foo=1),
          salgrade.assign(foo=1), on ='foo').query("losal <= sal <= hisal & job != 'CLERK' ")[["sal","deptno","ename","dname","losal","hisal"]].sort_values(by=['sal'], ascending=False)

Results

sal	deptno	ename	dname	losal	hisal
5000	10	KING	ACCOUNTING	3001	9999
3000	20	SCOTT	RESEARCH	2001	3000
3000	20	FORD	RESEARCH	2001	3000
2975	20	JONES	RESEARCH	2001	3000
2850	30	BLAKE	SALES	2001	3000
2450	10	CLARK	ACCOUNTING	2001	3000
1600	30	ALLEN	SALES	1401	2000
1500	30	TURNER	SALES	1401	2000
1250	30	WARD	SALES	1201	1400
1250	30	MARTIN	SALES	1201	1400

위 테이블 결합 작업을 개별적으로 분리하여서 처리

Python Programming

withmooc_1 = pd.merge(emp, dept, how='inner', left_on=['deptno'], right_on=['deptno'])[['job','sal',"deptno","ename","dname"]]
withmooc_2 = pd.merge( withmooc_1.assign(foo=1),salgrade.assign(foo=1), on ='foo').query("losal <= sal <= hisal & job != 'CLERK' ")
withmooc_2[["sal","deptno","ename","dname","losal","hisal"]].sort_values(by=['sal'], ascending=False).head()

Results

sal	deptno	ename	dname	losal	hisal
5000	10	KING	ACCOUNTING	3001	9999
3000	20	SCOTT	RESEARCH	2001	3000
3000	20	FORD	RESEARCH	2001	3000
2975	20	JONES	RESEARCH	2001	3000
2850	30	BLAKE	SALES	2001	3000

3. R Programming (R Package)

emp 테이블을 self Join하여서 관리자 이름을 추가하고, dept 테이블을 결합(merge)하여 부서명을 추가한 후에 salgrade 테이블을 비등가 조인(NON-EQUI JOIN)을 수행하여서 해당 급여를 포함하는 범위의 급여 등급을 선택하여 출력한다. with 구문을 사용하여서 테이블 명 지정하지 않고 변수명을 바로 지정할 수 있다.

R Programming

%%R
withmooc <- subset( base::merge( ( base::merge( emp ,
                                                dept, by.x=c("deptno"),by.y=c("deptno"),all=F )[,c("job","sal","deptno","ename","dname")]
                                  ),
                                  salgrade, by = character(),all.x=TRUE, all.y=TRUE )[,c("job","sal","deptno","ename","dname","losal","hisal")],
                    between(sal, losal, hisal) & job != 'CLERK')

withmooc[order(-withmooc$sal), ]

withmooc[with(withmooc, order(-sal)), ] # with 구문을 사용하여서 테이블 명 지정하지 않고 변수명을 바로 지정 할 수 있다.

Results

         job  sal deptno  ename      dname losal hisal
58 PRESIDENT 5000     10   KING ACCOUNTING  3001  9999
49   ANALYST 3000     20   FORD   RESEARCH  2001  3000
50   ANALYST 3000     20  SCOTT   RESEARCH  2001  3000
48   MANAGER 2975     20  JONES   RESEARCH  2001  3000
52   MANAGER 2850     30  BLAKE      SALES  2001  3000
43   MANAGER 2450     10  CLARK ACCOUNTING  2001  3000
37  SALESMAN 1600     30  ALLEN      SALES  1401  2000
41  SALESMAN 1500     30 TURNER      SALES  1401  2000
25  SALESMAN 1250     30   WARD      SALES  1201  1400
26  SALESMAN 1250     30 MARTIN      SALES  1201  1400

위 테이블 결합 작업을 개별적으로 분리하여서 처리

R Programming

%%R

withmooc_1 <- base::merge( emp , dept, by.x=c("deptno"),by.y=c("deptno"),all=F )[,c("job","sal","deptno","ename","dname")]
withmooc_2 <- base::merge( withmooc_1, salgrade, by = character(),all.x=TRUE, all.y=TRUE )[,c("job","sal","deptno","ename","dname","losal","hisal")]
withmooc_3 <- subset( withmooc_2, between(sal, losal, hisal) & job != 'CLERK' )

withmooc_3[with(withmooc_3, order(-sal)), ]

Results

         job  sal deptno  ename      dname losal hisal
58 PRESIDENT 5000     10   KING ACCOUNTING  3001  9999
49   ANALYST 3000     20   FORD   RESEARCH  2001  3000
50   ANALYST 3000     20  SCOTT   RESEARCH  2001  3000
48   MANAGER 2975     20  JONES   RESEARCH  2001  3000
52   MANAGER 2850     30  BLAKE      SALES  2001  3000
43   MANAGER 2450     10  CLARK ACCOUNTING  2001  3000
37  SALESMAN 1600     30  ALLEN      SALES  1401  2000
41  SALESMAN 1500     30 TURNER      SALES  1401  2000
25  SALESMAN 1250     30   WARD      SALES  1201  1400
26  SALESMAN 1250     30 MARTIN      SALES  1201  1400

4. R Dplyr Package

emp 테이블을 self Join하여서 관리자 이름을 추가하고, dept 테이블을 결합(inner_join)하여 부서명을 추가한 후에 salgrade 테이블을 비등가 조인(NON-EQUI JOIN)을 수행하여서 해당 급여를 포함하는 범위의 급여 등급을 선택하여 출력한다.

R Programming

%%R

emp %>% 
  dplyr::inner_join( dept    , by = c('deptno' = 'deptno'))   %>%
  dplyr::full_join( salgrade , by = character())              %>%
  dplyr::filter(between(sal, losal, hisal ) & job != 'CLERK') %>%
  dplyr::select(empno,dname,grade,sal )                       %>%
  dplyr::arrange(-sal)

Results

# A tibble: 10 x 4
   empno dname      grade   sal
   <dbl> <chr>      <dbl> <dbl>
 1  7839 ACCOUNTING     5  5000
 2  7788 RESEARCH       4  3000
 3  7902 RESEARCH       4  3000
 4  7566 RESEARCH       4  2975
 5  7698 SALES          4  2850
 6  7782 ACCOUNTING     4  2450
 7  7499 SALES          3  1600
 8  7844 SALES          3  1500
 9  7521 SALES          2  1250
10  7654 SALES          2  1250

5. R sqldf Package

R Programming

%%R

sqldf("select empno, ename, sal, dname, grade 
       from  emp e, dept d, salgrade s 
       where e.deptno = d.deptno 
         and e.sal between s.losal and s.hisal 
         and e.job <> 'CLERK' 
       order by sal desc;")

Results

   empno  ename  sal      dname grade
1   7839   KING 5000 ACCOUNTING     5
2   7788  SCOTT 3000   RESEARCH     4
3   7902   FORD 3000   RESEARCH     4
4   7566  JONES 2975   RESEARCH     4
5   7698  BLAKE 2850      SALES     4
6   7782  CLARK 2450 ACCOUNTING     4
7   7499  ALLEN 1600      SALES     3
8   7844 TURNER 1500      SALES     3
9   7521   WARD 1250      SALES     2
10  7654 MARTIN 1250      SALES     2

6. Python pandasql Package

Python Programming

ps.sqldf("select e.empno, e.ename, e.sal, d.dname, s.grade  \
          from emp e                                        \
               inner join dept d                            \
                     ON e.deptno=d.deptno                   \
               inner join salgrade s                        \
                     ON e.sal between s.losal and s.hisal   \
         where e.job<>'CLERK'                               \
         order by e.sal desc").head()

Results

empno	ename	sal	dname	grade
7839	KING	5000	ACCOUNTING	5
7788	SCOTT	3000	RESEARCH	4
7902	FORD	3000	RESEARCH	4
7566	JONES	2975	RESEARCH	4
7698	BLAKE	2850	SALES	4

7. R data.table Package

1차 : emp 테이블을 양쪽에 지정하여서 self Join을 수행하여 관리자 이름을 추가
2차 : dept 테이블을 결합(DT 문법)하여 부서명
3차 : salgrade 테이블을 비등가조인(NON-EQUI JOIN) 방식으로 결합하여 해당 급여를 포함하는 범위의 급여 등급을 선택하여 출력.

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)][salgrade_DT, nomatch=NULL,
                                                        on = .( sal >= losal , sal <= hisal),
                                                        .(x.sal,deptno,ename,dname,losal,hisal,job)][ job != 'CLERK' ,][order(- x.sal)]

Results

    x.sal deptno  ename      dname losal hisal       job
 1:  5000     10   KING ACCOUNTING  3001  9999 PRESIDENT
 2:  3000     20  SCOTT   RESEARCH  2001  3000   ANALYST
 3:  3000     20   FORD   RESEARCH  2001  3000   ANALYST
 4:  2975     20  JONES   RESEARCH  2001  3000   MANAGER
 5:  2850     30  BLAKE      SALES  2001  3000   MANAGER
 6:  2450     10  CLARK ACCOUNTING  2001  3000   MANAGER
 7:  1600     30  ALLEN      SALES  1401  2000  SALESMAN
 8:  1500     30 TURNER      SALES  1401  2000  SALESMAN
 9:  1250     30   WARD      SALES  1201  1400  SALESMAN
10:  1250     30 MARTIN      SALES  1201  1400  SALESMAN

위 테이블 결합 작업을 개별적으로 분리하여서 처리

R Programming

%%R

DT_1 = DT[dept_DT      , nomatch=NULL, on = .( deptno=deptno )             , .(sal,deptno,ename,dname,job)]
DT_2 = DT_1[salgrade_DT, nomatch=NULL, on = .( sal >= losal , sal <= hisal), .(x.sal,deptno,ename,dname,losal,hisal,job)]

DT_2[ job != 'CLERK' ,][order(- x.sal)]

Results

    x.sal deptno  ename      dname losal hisal       job
 1:  5000     10   KING ACCOUNTING  3001  9999 PRESIDENT
 2:  3000     20  SCOTT   RESEARCH  2001  3000   ANALYST
 3:  3000     20   FORD   RESEARCH  2001  3000   ANALYST
 4:  2975     20  JONES   RESEARCH  2001  3000   MANAGER
 5:  2850     30  BLAKE      SALES  2001  3000   MANAGER
 6:  2450     10  CLARK ACCOUNTING  2001  3000   MANAGER
 7:  1600     30  ALLEN      SALES  1401  2000  SALESMAN
 8:  1500     30 TURNER      SALES  1401  2000  SALESMAN
 9:  1250     30   WARD      SALES  1201  1400  SALESMAN
10:  1250     30 MARTIN      SALES  1201  1400  SALESMAN

8. SAS Proc SQL

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select empno, ename, sal, dname, grade 
    from  emp e, dept d, salgrade s 
    where e.deptno=d.deptno 
      and e.sal between s.losal and s.hisal 
      and e.job<>'CLERK' 
    order 
       by sal;
QUIT;

PROC SORT DATA=STATSAS_1;
      BY EMPNO;
RUN;
PROC PRINT data=STATSAS_1(obs=3);RUN;

Results

empno	ename	sal	dname	grade
7499	ALLEN	1600	SALES	3
7521	WARD	1250	SALES	2
7566	JONES	2975	RESEARCH	4

9. SAS Data Step

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_3; 
 merge EMP_1(in=a) dept_1(in=b);
     by deptno;
     if a and b;
RUN;

DATA STATSAS_4; 
 SET STATSAS_3;
     DO I=1 TO KOBS; 
        SET salgrade NOBS=KOBS POINT=I;
        IF  (SAL >= losal AND SAL <= hisal) AND job NE 'CLERK' THEN OUTPUT;
     END;
     KEEP EMPNO ENAME SAL DNAME GRADE;
RUN;
PROC SORT DATA=STATSAS_4;
      BY EMPNO;
RUN;
PROC PRINT data=STATSAS_4(obs=3);RUN;

Results

empno	ename	sal	dname	grade
7499	ALLEN	1600	SALES	3
7521	WARD	1250	SALES	2
7566	JONES	2975	RESEARCH	4

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( dept , left_on  = ["deptno"], right_on = ["deptno"])    >> \
  full_join( salgrade.assign(foo=1), by='foo' )                             >> \
  filter_by( X.sal.between(X.losal, X.hisal), X.job != 'CLERK')             >> \
  select(X.empno,X.dname,X.ename,X.grade,X.sal )                            >> \
  arrange(~ X.sal)                                                          >> \
  head()

Results

empno	dname	ename	grade	sal
7839	ACCOUNTING	KING	5	5000
7788	RESEARCH	SCOTT	4	3000
7902	RESEARCH	FORD	4	3000
7566	RESEARCH	JONES	4	2975
7698	SALES	BLAKE	4	2850

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트

저작자표시 비영리 변경금지

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

[데이터 추출] 상위 Top 5 관측치 추출하기 - 106 (0)	2022.10.03
[데이터 추출] Left Join 과 데이터 상하 결합(UNION) - 105 (0)	2022.10.03
[데이터 추출] 비등가 FULL 조인(NON-EQUI FULL JOIN)을 통한 데이터 결합 - 103 (0)	2022.10.02
[데이터 추출] 비상관 서브쿼리(Uncorrelated Subquery) 와 상관 서브쿼리(Correlated Subquery)를 만족하는 데이터 추출 - 비등가조인(NON-EQUI JOIN) 데이터 결합 - 102 (0)	2022.09.29
[데이터 추출] 내부조인(Inner join)을 만족하는 데이터 추출 - 101 (0)	2022.09.29

[데이터 추출] 비등가 FULL 조인(NON-EQUI FULL JOIN)을 통한 데이터 결합 및 데이터 정렬(SORT) - 104

104. List out all the employees name, job, and salary grade and department name for every one in the company except ‘CLERK’. Sort on salary display the highest salary.

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

6. Python pandasql Package

7. R data.table Package

8. SAS Proc SQL

9. SAS Data Step

10. Python Dfply Package

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

댓글

티스토리툴바

[데이터 추출] 비등가 FULL 조인(NON-EQUI FULL JOIN)을 통한 데이터 결합 및 데이터 정렬(SORT) - 104

104. List out all the employees name, job, and salary grade and department name for every one in the company except ‘CLERK’. Sort on salary display the highest salary.

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

6. Python pandasql Package

7. R data.table Package

8. SAS Proc SQL

9. SAS Data Step

10. Python Dfply Package

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

관련글

댓글

티스토리툴바