[데이터 필터링 - 상관 서브쿼리(Correlated Subqueyr) 를 만족하는 데이터 추출 - IN 연산자] 테이블 데이터 전처리 비교

포스팅 목차

57. Display the names of employees who earn highest salaries in their respective job groups.

* 개별 직무 내에서 최고 급여를 받는 직원의 정보를 출력하시오.

파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[데이터 추출] 상관 서브쿼리(Correlated Subqueyr) 를 만족하는 데이터 추출 - IN 연산자

Oracle : 상관 서브쿼리(Subquery), where 구문
파이썬 Pandas : groupby(), agg(), pd.merge : 내부조인(Inner join), 비교구문
R 프로그래밍 : merge - 내부조인(Inner join), 비교구문, FUN = function(x) (사용자정의 함수), names() - 변수명 변경, tapply(), class(), ave(), subset(), boolean 연산자(!!)
R Dplyr Package : inner_join() - 내부조인, group_by(), summarise()
R sqldf Package : 상관 서브쿼리(Subquery), where 구문
Python pandasql Package : 상관 서브쿼리(Subquery), where 구문
R data.table Package : merge - 내부조인, 비교구분, max() 함수
SAS Proc SQL : 서브쿼리(Subquery), where 구문
SAS Data Step : PROC SUMMARY, IF 조건문, PROC RANK, PROC SORT NODUPKEY
Python Dfply Package : inner_join - 내부조인, group_by(), summarize(), merge(), inner_join_fun 사용자 정의 함수
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

상관 서브쿼리를 사용하여서 직무 이름을 서브 쿼리에 전달하여서 해당 직무에서 최고 급여를 반환 받은 후 메인쿼리에서 최고 급여에 경우에 해당하는 사원 정보를 선택한다. ANALYST 직무의 경우 직무 내 최고 급여 3000을 받는 사원이 2명이 존재한다.

Oracle Programming

select * 
from   emp e 
where  sal in (select max(sal) from emp group by job having e.job=job)

Oracle Programming

select * 
from   emp e 
where  sal in (select max(sal) from emp where job=e.job )

2. Python Pandas(파이썬)

직무별 최대 급여를 계산 후 emp 테이블에 직무 이름과 최대 급여를 기준으로 내부조인(Inner Join)으로 결합하여서 직무별 최대 급여를 수령하는 사원을 선택한다.

Python Programming

pd.merge(emp, 
         (emp['sal'].groupby(emp['job']).agg([np.max])), 
         how='inner', 
         left_on=['job','sal'], right_on=['job','amax'])

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	amax
0	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	1600
1	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20	2975
2	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20	3000
3	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20	3000
4	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10	5000
5	7934	MILLER	CLERK	7782.0	1982/01/23	1300	NaN	10	1300

3. R Programming (R Package)

직무별 최대 급여를 계산 후 emp 테이블에 직무 이름과 최대 급여를 기준으로 내부조인(merge)으로 결합하여서 직무별 최대 급여를 수령하는 사원을 선택한다.

R Programming

%%R

merge(emp,
      ( aggregate(sal ~ job, data = emp, FUN = function(x) c(max = max(x) )) ),
      by.x=c("job","sal"),
      by.y=c("job","sal"),all=F)

Results

        job  sal empno  ename  mgr   hiredate comm deptno
1   ANALYST 3000  7902   FORD 7566 1981-12-03   NA     20
2   ANALYST 3000  7788  SCOTT 7566 1982-12-09   NA     20
3     CLERK 1300  7934 MILLER 7782 1982-01-23   NA     10
4   MANAGER 2975  7566  JONES 7839 1981-04-02   NA     20
5 PRESIDENT 5000  7839   KING   NA 1981-11-17   NA     10
6  SALESMAN 1600  7499  ALLEN 7698 1981-02-20  300     30

tapply 함수로 직무 그룹별 최대 급여를 계산 후 array로 반환된 자료를 data.frame으로 형태를 변환 후 내부조인 작업을 수행한다.

R Programming

%%R

merge(emp,
      ( data.frame(template=names(tapply(emp$sal, emp$job, max)), sal_max=  tapply(emp$sal, emp$job, max)) ),
      by.x=c("job","sal"),
      by.y=c("template","sal_max"),all=F)

Results

        job  sal empno  ename  mgr   hiredate comm deptno
1   ANALYST 3000  7902   FORD 7566 1981-12-03   NA     20
2   ANALYST 3000  7788  SCOTT 7566 1982-12-09   NA     20
3     CLERK 1300  7934 MILLER 7782 1982-01-23   NA     10
4   MANAGER 2975  7566  JONES 7839 1981-04-02   NA     20
5 PRESIDENT 5000  7839   KING   NA 1981-11-17   NA     10
6  SALESMAN 1600  7499  ALLEN 7698 1981-02-20  300     30

4. R Dplyr Package

직무별 최대 급여를 계산 후 emp 테이블에 직무 이름과 최대 급여를 기준으로 내부조인(Inner_Join)으로 결합하여서 직무별 최대 급여를 수령하는 사원을 선택한다.

R Programming

%%R

emp %>% 
  inner_join (emp %>% group_by(job) %>% summarise(max_amount = max(sal)) %>% ungroup(), by = c("job","sal" = "max_amount") )

Results

`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 6 x 8
  empno ename  job         mgr hiredate     sal  comm deptno
  <dbl> <chr>  <chr>     <dbl> <date>     <dbl> <dbl>  <dbl>
1  7499 ALLEN  SALESMAN   7698 1981-02-20  1600   300     30
2  7566 JONES  MANAGER    7839 1981-04-02  2975    NA     20
3  7788 SCOTT  ANALYST    7566 1982-12-09  3000    NA     20
4  7839 KING   PRESIDENT    NA 1981-11-17  5000    NA     10
5  7902 FORD   ANALYST    7566 1981-12-03  3000    NA     20
6  7934 MILLER CLERK      7782 1982-01-23  1300    NA     10

5. R sqldf Package

상관 서브쿼리를 사용하여서 직무 이름을 서브 쿼리에 전달하여서 해당 직무에서 최고 급여를 반환 받은 후 메인쿼리에서 최고 급여에 해당하는 경우에 사원 정보를 선택한다. ANALYST 직무의 경우 직무 내 최고 급여 3000을 받는 사원이 2명이 존재한다.

R Programming

%%R
sqldf("select * from emp e 
       where sal in (select max(sal) from emp group by job having e.job=job)")

Results

  empno  ename       job  mgr   hiredate  sal comm deptno
1  7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300     30
2  7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20
3  7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20
4  7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10
5  7902   FORD   ANALYST 7566 1981-12-03 3000   NA     20
6  7934 MILLER     CLERK 7782 1982-01-23 1300   NA     10

R Programming

%%R
sqldf("select * from emp e 
       where sal in (select max(sal) from emp where job=e.job )")

Results

  empno  ename       job  mgr   hiredate  sal comm deptno
1  7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300     30
2  7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20
3  7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20
4  7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10
5  7902   FORD   ANALYST 7566 1981-12-03 3000   NA     20
6  7934 MILLER     CLERK 7782 1982-01-23 1300   NA     10

6. Python pandasql Package

Python Programming

ps.sqldf("select * from emp e \
          where sal in (select max(sal) from emp group by job having e.job=job)")

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30
1	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
2	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20
3	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10
4	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20
5	7934	MILLER	CLERK	7782.0	1982/01/23	1300	NaN	10

7. R data.table Package

data.table의 테이블 결합(Join) 방식에는 DT 방식과 merge 방식을 지원한다. 상세 내용은 하단 페이지 참조
- JOINing data in R using data.table : https://rstudio-pubs-static.s3.amazonaws.com/52230_5ae0d25125b544caab32f75f0360e775.html

직무별 최대 급여를 계산 후 emp 테이블에 직무 이름과 최대 급여를 기준으로 내부조인(data.table::merge() syntax)을 수행하여서 직무별 최대 급여를 수령하는 사원을 선택한다.

R Programming

%%R

merge(DT, DT[ , .(sal_max = max(sal)), by = .(job)],
      by.x=c("job","sal"),
      by.y=c("job","sal_max"),all=F)

Results

         job  sal empno  ename  mgr   hiredate comm deptno
1:   ANALYST 3000  7788  SCOTT 7566 1982-12-09   NA     20
2:   ANALYST 3000  7902   FORD 7566 1981-12-03   NA     20
3:     CLERK 1300  7934 MILLER 7782 1982-01-23   NA     10
4:   MANAGER 2975  7566  JONES 7839 1981-04-02   NA     20
5: PRESIDENT 5000  7839   KING   NA 1981-11-17   NA     10
6:  SALESMAN 1600  7499  ALLEN 7698 1981-02-20  300     30

직무별 최대 급여를 계산 후 emp 테이블에 직무 이름과 최대 급여를 기준으로 내부조인(DT syntax)을 수행하여서 직무별 최대 급여를 수령하는 사원을 선택한다.

R Programming

%%R

DT <- data.table(emp)

DT[  DT[ , .(sal_max = max(sal)), by = .(job)] , on=c("job","sal" = "sal_max")]

Results

   empno  ename       job  mgr   hiredate  sal comm deptno
1:  7934 MILLER     CLERK 7782 1982-01-23 1300   NA     10
2:  7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300     30
3:  7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20
4:  7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20
5:  7902   FORD   ANALYST 7566 1981-12-03 3000   NA     20
6:  7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10

8. SAS Proc SQL

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select E.* 
    from   emp e 
    where  sal in (select max(sal) from emp group by job having e.job=job);
QUIT;
PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
2	7566	JONES	MANAGER	7839	1981-04-02	2975	.	20
3	7788	SCOTT	ANALYST	7566	1982-12-09	3000	.	20
4	7839	KING	PRESIDEN	.	1981-11-17	5000	.	10
5	7902	FORD	ANALYST	7566	1981-12-03	3000	.	20
6	7934	MILLER	CLERK	7782	1982-01-23	1300	.	10

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select E.* 
    from   emp e 
    where  sal in (select max(sal) from emp where job=e.job );
QUIT;
PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
2	7566	JONES	MANAGER	7839	1981-04-02	2975	.	20
3	7788	SCOTT	ANALYST	7566	1982-12-09	3000	.	20
4	7839	KING	PRESIDEN	.	1981-11-17	5000	.	10
5	7902	FORD	ANALYST	7566	1981-12-03	3000	.	20
6	7934	MILLER	CLERK	7782	1982-01-23	1300	.	10

9. SAS Data Step

SAS Programming

%%SAS sas

PROC SUMMARY DATA=EMP NWAY;
     CLASS JOB;
     VAR   SAL;
     OUTPUT OUT=SAL_MAX(DROP=_:) MAX=SAL_MAX;
RUN;

PROC SORT DATA=EMP OUT=EMP_1;
     BY JOB;
RUN;

DATA STATSAS_3; 
 MERGE EMP_1(IN=A) SAL_MAX(IN=B);
     BY JOB;
     IF SAL = SAL_MAX;
RUN;

PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno	SAL_MAX
1	7788	SCOTT	ANALYST	7566	1982-12-09	3000	.	20	3000
2	7902	FORD	ANALYST	7566	1981-12-03	3000	.	20	3000
3	7934	MILLER	CLERK	7782	1982-01-23	1300	.	10	1300
4	7566	JONES	MANAGER	7839	1981-04-02	2975	.	20	2975
5	7839	KING	PRESIDEN	.	1981-11-17	5000	.	10	5000
6	7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30	1600

PROC RANK 사용;

SAS Programming

%%SAS sas

PROC SORT DATA=EMP OUT=EMP_1;
     BY JOB;
RUN;

proc rank data=EMP_1 out= SAL_MAX(where=(SAL_rank=1)) ties=low descending;
      BY    JOB;
      var   SAL;
      ranks SAL_rank;
run;
PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno	SAL_rank
1	7788	SCOTT	ANALYST	7566	1982-12-09	3000	.	20	1
2	7902	FORD	ANALYST	7566	1981-12-03	3000	.	20	1
3	7934	MILLER	CLERK	7782	1982-01-23	1300	.	10	1
4	7566	JONES	MANAGER	7839	1981-04-02	2975	.	20	1
5	7839	KING	PRESIDEN	.	1981-11-17	5000	.	10	1
6	7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30	1

SAS Programming

%%SAS sas

PROC SORT DATA=EMP OUT=EMP_1;
     BY JOB DESCENDING SAL;
RUN;

PROC SORT DATA=EMP_1 OUT=SAL_MAX NODUPKEY;
     BY JOB;
RUN; 

PROC SORT DATA=EMP OUT=EMP_2;
     BY JOB SAL;
RUN;

DATA STATSAS_3; 
 MERGE EMP_2(IN=A) SAL_MAX(IN=B);
     BY JOB SAL;
     IF A AND B;
RUN;

PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7788	SCOTT	ANALYST	7566	1982-12-09	3000	.	20
2	7902	FORD	ANALYST	7566	1981-12-03	3000	.	20
3	7934	MILLER	CLERK	7782	1982-01-23	1300	.	10
4	7566	JONES	MANAGER	7839	1981-04-02	2975	.	20
5	7839	KING	PRESIDEN	.	1981-11-17	5000	.	10
6	7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30

10. Python Dfply Package

Python Programming

emp >> group_by('job') >> summarize(max_amount = X.sal.max())  >> ungroup()

Results

	job	max_amount
0	ANALYST	3000
1	CLERK	1300
2	MANAGER	2975
3	PRESIDENT	5000
4	SALESMAN	1600

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

저작자표시

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

[데이터 필터링 - 비상관 서브쿼리(Uncorrelated Subqueyr) 를 만족하는 데이터 추출 - 비교연산자] 테이블 데이터 전처리 비교 - 59 (0)	2021.08.20
[데이터 필터링 - 비상관 서브쿼리(Uncorrelated Subqueyr) 를 만족하는 데이터 추출 - 비교연산자] 테이블 데이터 전처리 비교 - 58 (0)	2021.08.20
[데이터 필터링] 상관 서브쿼리(Correlated Subqueyr) 를 만족하는 데이터 추출 - 최대값(MAX) - 56 (0)	2021.08.19
[데이터 필터링] 비상관 서브쿼리 결과를 만족하는 데이터 추출 - 55 (오라클 SQL, R, Python, SAS) (0)	2021.08.19
[데이터 필터링] 비상관 서브쿼리 결과를 만족하는 데이터 추출 - 54 (오라클 SQL, R, Python, SAS) (0)	2021.08.19

[데이터 필터링 - 상관 서브쿼리(Correlated Subqueyr) 를 만족하는 데이터 추출 - IN 연산자] 테이블 데이터 전처리 비교 - 57

57. Display the names of employees who earn highest salaries in their respective job groups.

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)' 카테고리의 다른 글

댓글

티스토리툴바

[데이터 필터링 - 상관 서브쿼리(Correlated Subqueyr) 를 만족하는 데이터 추출 - IN 연산자] 테이블 데이터 전처리 비교 - 57

57. Display the names of employees who earn highest salaries in their respective job groups.

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)' 카테고리의 다른 글

관련글

댓글

티스토리툴바