[데이터 필터링] 상관 서브쿼리(Correlated Subqueyr) 를 만족하는 데이터 추출 - 최대값(MAX)

포스팅 목차

56. Display the names of the employees who earn highest salary in their respective departments.

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

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

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(오라클)

상관 서브쿼리(Correlated Subqueyr)

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

Oracle Programming

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

2. Python Pandas(파이썬)

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

Python Programming

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

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	amax
0	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30	2850
1	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20	3000
2	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20	3000
3	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10	5000

3. R Programming (R Package)

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

R Programming

%%R

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

Results

  deptno  sal empno ename       job  mgr   hiredate comm
1     10 5000  7839  KING PRESIDENT   NA 1981-11-17   NA
2     20 3000  7902  FORD   ANALYST 7566 1981-12-03   NA
3     20 3000  7788 SCOTT   ANALYST 7566 1982-12-09   NA
4     30 2850  7698 BLAKE   MANAGER 7839 1981-03-01   NA

R Programming

%%R

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

Results

  deptno  sal empno ename       job  mgr   hiredate comm
1     10 5000  7839  KING PRESIDENT   NA 1981-11-17   NA
2     20 3000  7902  FORD   ANALYST 7566 1981-12-03   NA
3     20 3000  7788 SCOTT   ANALYST 7566 1982-12-09   NA
4     30 2850  7698 BLAKE   MANAGER 7839 1981-03-01   NA

R Programming

%%R

class(ave(emp$sal, emp$deptno, FUN = function(x) x == max(x)))

Results

[1] "numeric"

ave 함수를 사용하여서 부서별 최대 연봉 수령자를 선택 후 subset 함수를 사용하여서 해당 정보를 선택한다.
ave 함수는 최대 연봉 수령자에 해당하는 관측치에 numeric("1")을 반환하고, 해당하지 않는 관측치에 "0"을 반환한다. 이 값을 불리언(boolean) 값으로 변환하기 위하여 !! 연산자를 사용한다.
- Not(!) 연산자는 True/False 연산자를 반대로 False / Ture로 변환하다. ( Numeric 값으로 표시된 참("1")/거짓("0") 값을 !! 연산자를 통하여 불리언 연산자 True/False 로 변환한다. )

R Programming

%%R

# ! ave(emp$sal, emp$deptno, FUN = function(x) x == max(x))
subset(emp, !! ave(sal, deptno, FUN = function(x) x == max(x)))

Results

# A tibble: 4 x 8
  empno ename job         mgr hiredate     sal  comm deptno
  <dbl> <chr> <chr>     <dbl> <date>     <dbl> <dbl>  <dbl>
1  7698 BLAKE MANAGER    7839 1981-03-01  2850    NA     30
2  7788 SCOTT ANALYST    7566 1982-12-09  3000    NA     20
3  7839 KING  PRESIDENT    NA 1981-11-17  5000    NA     10
4  7902 FORD  ANALYST    7566 1981-12-03  3000    NA     20

4. R Dplyr Package

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

R Programming

%%R

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

Results

`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 4 x 8
  empno ename job         mgr hiredate     sal  comm deptno
  <dbl> <chr> <chr>     <dbl> <date>     <dbl> <dbl>  <dbl>
1  7698 BLAKE MANAGER    7839 1981-03-01  2850    NA     30
2  7788 SCOTT ANALYST    7566 1982-12-09  3000    NA     20
3  7839 KING  PRESIDENT    NA 1981-11-17  5000    NA     10
4  7902 FORD  ANALYST    7566 1981-12-03  3000    NA     20

5. R sqldf Package

상관 서브쿼리(Correlated Subqueyr)

상관 서브쿼리를 사용하여서 개별 사원의 부서 번호를 서브 쿼리에 전달하여서 해당 부서의 최고 급여를 반환받은 후 메인쿼리에서 최고 급여에 해당하는 사원 정보를 선택한다. 부서 20의 경우 부서 내 최고 급여 30를 받는 사원이 2명이 존재한다.

R Programming

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

Results

  empno ename       job  mgr   hiredate  sal comm deptno
1  7698 BLAKE   MANAGER 7839 1981-03-01 2850   NA     30
2  7788 SCOTT   ANALYST 7566 1982-12-09 3000   NA     20
3  7839  KING PRESIDENT   NA 1981-11-17 5000   NA     10
4  7902  FORD   ANALYST 7566 1981-12-03 3000   NA     20

6. Python pandasql Package

상관 서브쿼리(Correlated Subqueyr)

Python Programming

ps.sqldf("select * from emp e \
          where sal = (select max(sal) from emp where deptno=e.deptno)")

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	None	30
1	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	None	20
2	7839	KING	PRESIDENT	NaN	1981/11/17	5000	None	10
3	7902	FORD	ANALYST	7566.0	1981/12/03	3000	None	20

7. R data.table Package

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

R Programming

%%R

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

Results

   deptno  sal empno ename       job  mgr   hiredate comm
1:     10 5000  7839  KING PRESIDENT   NA 1981-11-17   NA
2:     20 3000  7788 SCOTT   ANALYST 7566 1982-12-09   NA
3:     20 3000  7902  FORD   ANALYST 7566 1981-12-03   NA
4:     30 2850  7698 BLAKE   MANAGER 7839 1981-03-01   NA

8. SAS Proc SQL

상관 서브쿼리(Correlated Subqueyr)

SAS Programming

%%SAS sas

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

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7698	BLAKE	MANAGER	7839	1981-03-01	2850	.	30
2	7788	SCOTT	ANALYST	7566	1982-12-09	3000	.	20
3	7839	KING	PRESIDEN	.	1981-11-17	5000	.	10
4	7902	FORD	ANALYST	7566	1981-12-03	3000	.	20

9. SAS Data Step

데이터 결합(Join)

SAS Programming

%%SAS sas

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

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

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

PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno	SAL_MAX
1	7839	KING	PRESIDEN	.	1981-11-17	5000	.	10	5000
2	7788	SCOTT	ANALYST	7566	1982-12-09	3000	.	20	3000
3	7902	FORD	ANALYST	7566	1981-12-03	3000	.	20	3000
4	7698	BLAKE	MANAGER	7839	1981-03-01	2850	.	30	2850

PROC RANK 사용;

SAS Programming

%%SAS sas

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

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

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno	SAL_rank
1	7839	KING	PRESIDEN	.	1981-11-17	5000	.	10	1
2	7788	SCOTT	ANALYST	7566	1982-12-09	3000	.	20	1
3	7902	FORD	ANALYST	7566	1981-12-03	3000	.	20	1
4	7698	BLAKE	MANAGER	7839	1981-03-01	2850	.	30	1

SAS Programming

%%SAS sas

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

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

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

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

PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7839	KING	PRESIDEN	.	1981-11-17	5000	.	10
2	7788	SCOTT	ANALYST	7566	1982-12-09	3000	.	20
3	7902	FORD	ANALYST	7566	1981-12-03	3000	.	20
4	7698	BLAKE	MANAGER	7839	1981-03-01	2850	.	30

10. Python Dfply Package

복수의 변수(Key)를 기준으로 데이터 결합 : 동일한 변수명으로 자동 결합 수행

Python Programming

emp >> \
  inner_join( ( emp >> group_by('deptno') >> summarize(sal = X.sal.max()) ) )

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
1	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20
2	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20
3	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10

Python Programming

emp >> \
  inner_join( ( emp >> group_by('deptno') >> summarize(sal = X.sal.max()) ), by = ['deptno', 'sal'] )

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
1	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20
2	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20
3	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10

일반적은 merger() 결합 방식

Python Programming

emp.merge( ( emp >> group_by('deptno') >> summarize(max_amount = X.sal.max()) ), \
           how='inner', left_on=["deptno","sal"],right_on=["deptno","max_amount"])

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	max_amount
0	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30	2850
1	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20	3000
2	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20	3000
3	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10	5000

정확한 원인을 아직 모르지만 inner_join 함수와 by= 옵션을 사용하여 내부 조인 수행 시 복수의 Join Key 변수 사용 시 에러가 발생하여서 아래와 같이 프로그램 내의 inne_join에 대한 부분을 별도로 inner_join_fun()으로 함수 이름 변경 후 사용하였음.
파일 위치 : C:\Users\사용자계정\anaconda3\Lib\site-packages\dfply

Python Programming

def get_join_parameters(join_kwargs):
    """
    Convenience function to determine the columns to join the right and
    left DataFrames on, as well as any suffixes for the columns.
    """

    by = join_kwargs.get('by', None)
    suffixes = join_kwargs.get('suffixes', ('_x', '_y'))
    if by is None:
        left_on, right_on = None, None
    else:
        if isinstance(by, str):
            left_on, right_on = by, by
        else:
            if not isinstance(by[0], str):
                left_on  = by[0]
                right_on = by[1]                         # 소스에는 right_in으로 되어 있음.
            else:
                left_on, right_on = by, by
    return left_on, right_on, suffixes

@pipe
def inner_join_fun(df, other, **kwargs):

    left_on, right_on, suffixes = get_join_parameters(kwargs)
    joined = df.merge(other, how='inner', left_on=left_on,
                      right_on=right_on, suffixes=suffixes)
    return joined

Python Programming

emp >> inner_join_fun(( emp >> group_by('deptno') >> summarize(sal_max = X.sal.max()) ), by =[['deptno', 'sal'], ['deptno', 'sal_max'],["_x","_y"]] )

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	sal_max
0	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30	2850
1	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20	3000
2	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20	3000
3	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10	5000

[에러] by= 인수를 지정하여 내부 조인 수행 시 복수의 Key 변수 지정한 경우 에러 발생
- 조치 사항 : 1) 파이썬의 pd.merge()로 별도로 처리하는 방법. 2) inner_join() 함수에 대한 부분을 inner_join_fun() 으로 함수명을 변경하여서 사용

Python Programming

emp >> \
  inner_join( ( emp >> group_by('deptno') >> summarize(sal_max = X.sal.max()) ), by = {'left_on1': ['deptno', 'sal'], 'right_on1': ['deptno', 'sal_max']} )

---------------------------------------------------------------------------

KeyError                                  Traceback (most recent call last)

<ipython-input-52-2ec0b62c1c58> in <module>
----> 1 emp >> \
      2   inner_join( ( emp >> group_by('deptno') >> summarize(sal_max = X.sal.max()) ), by = {'left_on1': ['deptno', 'sal'], 'right_on1': ['deptno', 'sal_max']} )


~\anaconda3\lib\site-packages\dfply\base.py in __rrshift__(self, other)
    140             other_copy._grouped_by = getattr(other, '_grouped_by', None)
    141 
--> 142         result = self.function(other_copy)
    143 
    144         for p in self.chained_pipes:


~\anaconda3\lib\site-packages\dfply\base.py in <lambda>(x)
    147 
    148     def __call__(self, *args, **kwargs):
--> 149         return pipe(lambda x: self.function(x, *args, **kwargs))
    150 
    151 


~\anaconda3\lib\site-packages\dfply\join.py in inner_join(df, other, **kwargs)
     52     """
     53 
---> 54     left_on, right_on, suffixes = get_join_parameters(kwargs)
     55     joined = df.merge(other, how='inner', left_on=left_on,
     56                       right_on=right_on, suffixes=suffixes)


~\anaconda3\lib\site-packages\dfply\join.py in get_join_parameters(join_kwargs)
     20             left_on, right_on = by, by
     21         else:
---> 22             if not isinstance(by[0], str):
     23                 left_on = by[0]
     24                 right_in = by[1]


KeyError: 0

[에러] Key 변수의 이름이 서로 틀린 경우 Inner Join이 제대로 수행 안되는 에러 발생
- 조치사항 : 1) 파이썬의 pd.merge()로 별도로 처리하는 방법. 2) 내부 조인을 수행하기 위한 양쪽 테이블의 Join Key 변수의 이름을 사전에 동일하게 변경 처리.

Python Programming

emp >> \
  inner_join( ( emp >> group_by('deptno') >> summarize(sal_max = X.sal.max())  ), \
              left_on  = ["deptno","sal"], right_on = ["deptno","sal_max"])

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	sal_max
0	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20	3000
1	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20	3000
2	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20	3000
3	7876	ADAMS	CLERK	7788.0	1983/01/12	1100	NaN	20	3000
4	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20	3000
5	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	2850
6	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	2850
7	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	2850
8	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30	2850
9	7844	TURNER	SALESMAN	7698.0	1981/09/08	1500	0.0	30	2850
10	7900	JAMES	CLERK	7698.0	1981/12/03	950	NaN	30	2850
11	7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10	5000
12	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10	5000
13	7934	MILLER	CLERK	7782.0	1982/01/23	1300	NaN	10	5000

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

저작자표시

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

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

[데이터 필터링] 상관 서브쿼리(Correlated Subqueyr) 를 만족하는 데이터 추출 - 최대값(MAX) - 56

56. Display the names of the employees who earn highest salary in their respective departments.

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

상관 서브쿼리(Correlated Subqueyr)

6. Python pandasql Package

상관 서브쿼리(Correlated Subqueyr)

7. R data.table Package

8. SAS Proc SQL

상관 서브쿼리(Correlated Subqueyr)

9. SAS Data Step

10. Python Dfply Package

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

댓글

티스토리툴바

[데이터 필터링] 상관 서브쿼리(Correlated Subqueyr) 를 만족하는 데이터 추출 - 최대값(MAX) - 56

56. Display the names of the employees who earn highest salary in their respective departments.

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

상관 서브쿼리(Correlated Subqueyr)

6. Python pandasql Package

상관 서브쿼리(Correlated Subqueyr)

7. R data.table Package

8. SAS Proc SQL

상관 서브쿼리(Correlated Subqueyr)

9. SAS Data Step

10. Python Dfply Package

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

관련글

댓글

티스토리툴바