[상관 서브쿼리(Max)] 특정 조건을 만족하는 데이터 추출

포스팅 목차

163. find out the employees who earned the highest Sal in each job typed sort in descending Sal order.

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

파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[데이터 추출] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리(Max)
데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql

Oracle : 상관 서브쿼리, Max()
파이썬 Pandas : pd.merge(), agg(), np.max
R 프로그래밍 : merge(), aggregate() 의 function(x) 사용자 정의 함수(Max)
R Dplyr Package : inner_join(), dplyr::summarise() 의 Max()
R sqldf Package : 상관 서브쿼리, Max()
Python pandasql Package : 상관 서브쿼리, Max()
R data.table Package : Merge 방식의 데이터 결합, Max()
SAS Proc SQL : 상관 서브쿼리, Max()
SAS Data Step : PROC SUMMARY의 Max, Merge 구문, IF 조건문
Python Dfply Package : @pipe & def 사용자 정의 함수(inner_join_merge), summarize()의 max()
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

상관 서브쿼리를 사용하여서 개별 직무에서 최대 급여를 받는 사원들의 정보를 출력한다.

Oracle Programming

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

2. Python Pandas(파이썬)

emp 테이블에서 개별 직무(‘job’)별로 최대 급여를 집계 후에 직무 코드(‘job’)와 최대 급여(‘amax’)를 기준으로 사원 정보(EMP) 테이블의 직무 코드(‘job’)과 급여(‘sal’)와 매칭되는 사원 정보를 출력한다.결과적으로 직무별로 최대 급여를 수령하는 사원 정보가 출력된다.

Python Programming

pd.merge(emp, 
         (emp['sal'].groupby(emp['job']).agg([np.max])), 
         how='inner', 
         left_on=['job','sal'], right_on=['job','amax']).drop(['amax'], axis='columns')   # , inplace=True

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	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20
4	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10
5	7934	MILLER	CLERK	7782.0	1982/01/23	1300	NaN	10

3. R Programming (R Package)

emp 테이블에서 aggregate() 함수를 사용하여서 개별 직무(‘job’)별로 최대 급여를 집계 후에 직무 코드(‘job’)와 최대 급여(‘sal’)를 기준으로 사원 정보(EMP) 테이블의 직무 코드(‘job’)과 급여(‘sal’)와 매칭되는 사원 정보를 출력하여 직무별로 최대 급여를 수령하는 사원 정보를 출력된다.

R Programming

%%R

merge(emp,
      ( aggregate(sal ~ job, data = emp, FUN = function(x) c( sal_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

4. R Dplyr Package

emp 테이블에서 dplyr::summarise() 함수를 사용하여서 개별 직무(‘job’)별로 최대 급여를 집계 후에 직무 코드(‘job’)와 최대 급여(‘sal_max’)를 기준으로 사원 정보(EMP) 테이블의 직무 코드(‘job’)과 급여(‘sal’)와 매칭되는 사원 정보를 출력하여 직무별로 최대 급여를 수령하는 사원 정보를 출력된다.

R Programming

%%R

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

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

상관 서브쿼리를 사용하여서 개별 직무에서 최대 급여를 받는 사원들의 정보를 출력한다.

R Programming

%%R

sqldf(" select * 
        from   emp e 
        where  sal = (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 = (select max(sal) from emp where job=e.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

emp 테이블에서 개별 직무(‘job’)별로 최대 급여(‘sal_max’)를 집계 후에 merge 결합 방식으로 직무 코드(‘job’)와 최대 급여(‘sal_max’)를 기준으로 사원 정보(EMP) 테이블의 직무 코드(‘job’)과 급여(‘sal’)와 매칭되는 사원 정보를 출력하여 직무별로 최대 급여를 수령하는 사원 정보를 출력된다.

R Programming

%%R

DT <- data.table(emp)

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 테이블에서 개별 직무(‘job’)별로 최대 급여(‘sal_max’)를 집계 후에 DT 결합 방식으로 직무 코드(‘job’)와 최대 급여(‘sal_max’)를 기준으로 사원 정보(EMP) 테이블의 직무 코드(‘job’)과 급여(‘sal’)와 매칭되는 사원 정보를 출력하여 직무별로 최대 급여를 수령하는 사원 정보를 출력된다.

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 * 
    from   emp e 
    where  sal = (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 SORT DATA=EMP OUT=EMP_1(RENAME=(JOB=JOIN_KEY1 SAL=JOIN_KEY2));
     BY JOB SAL;
RUN;

PROC SUMMARY DATA=EMP NWAY;
     CLASS JOB;
     VAR   SAL;
     OUTPUT OUT=EMP_GRP(DROP=_:) MAX=MAX_SAL;
QUIT;

PROC SORT DATA=EMP_GRP OUT=EMP_GRP_1(RENAME=(JOB=JOIN_KEY1 MAX_SAL=JOIN_KEY2));
     BY JOB MAX_SAL;
RUN;

DATA STATSAS_4;
 MERGE EMP_1(IN=A) EMP_GRP_1(IN=B);
     BY JOIN_KEY1 JOIN_KEY2;
     IF A AND B;
RUN;
PROC PRINT;RUN;

Results

OBS	empno	ename	JOIN_KEY1	mgr	hiredate	JOIN_KEY2	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

@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 >> \
  inner_join_merge( ( emp >> group_by('job') >> summarize(sal_max = X.sal.max()) ), 
                    left_on=['job','sal'], right_on=['job','sal_max'], suffixes=["_x", "_y"])

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	sal_max
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

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

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

[상관 서브쿼리(Max)] 특정 조건을 만족하는 데이터 추출 - 163 (오라클 SQL, R, Python, SAS)

163. find out the employees who earned the highest Sal in each job typed sort in descending Sal order.

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

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

댓글

티스토리툴바