[데이터 전처리- 필터링 예제] 상관 서브쿼리(Correlated Subqueyr) 를 만족하는 데이터 추출 - All 연산자 & 최대값(Max)

포스팅 목차

62. Display the names of employee from department number 10 with salary greater then that of all employee working in other departments.

* 10번 부서 이외의 부서에서 근무하는 사원들의 최대 급여보다 많이 받는 10번(회계부서) 부서의 직원 정보를 출력하시오.

파이썬 & R 패키지 호출 및 예제 데이터 생성 링크

[데이터 추출] 상관 서브쿼리(Correlated Subqueyr) 를 만족하는 데이터 추출 - All 연산자 & 최대값(Max)

Oracle : 상관 서브쿼리, All 연산자
파이썬 Pandas : .loc[], max() 함수, lambda 함수
R 프로그래밍 : subset(), max() 함수
R Dplyr Package : filter(), summarise(), pull(), mutate(), left_join
R sqldf Package : 상관 서브쿼리, max() 함수, NOT EXISTS 연산자
Python pandasql Package : 상관 서브쿼리, max() 함수, NOT EXISTS 연산자
R data.table Package : max() 함수
SAS Proc SQL : 상관 서브쿼리, All 연산자, max() 함수
SAS Data Step : Proc Summary, CROSS JOIN (카테시안 곱) 구현, Macro 변수 할당
Python Dfply Package : summarize(), filter_by(), pipe (사용자정의 함수) - pull_fun
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

부서코드 10번(회계부서)에 근무하는 직원 중에서 회계부서를 제외한 다른 부서에서 근무하는 직원들의 최대 급여보다 많이 받는 직원 정보를 출력한다.

Oracle Programming

select ename, sal, deptno 
from   emp e 
where  deptno=10 
  and  sal > all(select sal from emp where e.deptno != deptno);

Oracle Programming

select ename 
from   emp 
where  deptno=10 
  and  sal > all(select sal from emp where deptno not in 10);

2. Python Pandas(파이썬)

회계부서('10')에 근무하는 직원의 급여가 다른 부서에서 근무하는 직원들의 최대 급여보다 큰 경우 해당 직원의 정보를 출력한다.

Python Programming

emp[emp['deptno']==10].loc[lambda x:x['sal'] > ( max(emp[emp['deptno']!=10]['sal']) )]

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
8	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10

3. R Programming (R Package)

회계부서('10')에 근무하는 직원의 급여가 다른 부서에서 근무하는 직원들의 최대 급여보다 큰 경우 해당 직원의 정보를 출력한다. subset 연산자를 사용하여서 해당 조건에 만족하는 직원 정보를 선택한다.

R Programming

%%R

subset( emp[emp$deptno == 10, ], sal > max(emp[emp$deptno != 10,]$sal) )

Results

# A tibble: 1 x 8
  empno ename job         mgr hiredate     sal  comm deptno
  <dbl> <chr> <chr>     <dbl> <date>     <dbl> <dbl>  <dbl>
1  7839 KING  PRESIDENT    NA 1981-11-17  5000    NA     10

4. R Dplyr Package

회계부서('10')를 선택하여서 해당 부서에 근무하는 직원의 급여가 다른 부서에 근무하는 직원들의 최대 급여보다 큰 경우에 해당 직원의 정보를 출력한다.

R Programming

%%R

emp %>%
  filter(deptno == 10) %>% 
  filter(sal > ( emp %>%
                       filter(deptno != 10) %>%
                       summarise(max(sal, na.rm = TRUE)) %>%
                       pull() )
        )

Results

# A tibble: 1 x 8
  empno ename job         mgr hiredate     sal  comm deptno
  <dbl> <chr> <chr>     <dbl> <date>     <dbl> <dbl>  <dbl>
1  7839 KING  PRESIDENT    NA 1981-11-17  5000    NA     10

회계부서('10')를 선택 후에 다른 부서에 근무하는 직원들의 최대 급여를 결합(내부조인-left Join)하여서 다른 부서에 근무하는 직원들의 최대 급여 보다 많이 받는 회계부서의 직원 정보를 출력한다.

R Programming

%%R

emp %>%
  filter(deptno == 10) %>%
  mutate(dummy=TRUE) %>%
  left_join ( emp %>%
                filter(deptno != 10) %>%
                summarise(sal_max = max(sal, na.rm = TRUE)) %>%
                mutate(dummy=TRUE) 
            ) %>%
  filter(sal > sal_max)

Results

Joining, by = "dummy"
# A tibble: 1 x 10
  empno ename job         mgr hiredate     sal  comm deptno dummy sal_max
  <dbl> <chr> <chr>     <dbl> <date>     <dbl> <dbl>  <dbl> <lgl>   <dbl>
1  7839 KING  PRESIDENT    NA 1981-11-17  5000    NA     10 TRUE     3000

5. R sqldf Package

부서코드 10번(회계부서)에 근무하는 직원 중에서 회계부서를 제외한 다른 부서에서 근무하는 직원들의 급여 중 최대 급여보다 크면 해당 직원 정보를 출력한다.

all 연산자를 통하여 다른 부서에 근무하는 직원의 최대(Max) 연봉과 비교하게 된다.

현재 SQLite에서는 ANY / ALL 연산자를 지원하지 않음. (ANY 연산자는 Min 함수로 대체 가능하고, ALL 연산자는 Max 함수로 대체 가능하다.)

ANY : sal > ANY(SELECT sal FROM emp WHERE job='SALESMAN')
min : sal > (SELECT min(sal) FROM emp WHERE job='SALESMAN')
ALL : sal > ALL(SELECT sal FROM emp WHERE job='SALESMAN')
max : sal > (SELECT max(sal) FROM emp WHERE job='SALESMAN')

R Programming

%%R

sqldf("select ename,sal,deptno 
       from emp e 
       where deptno=10 and sal > (select MAX(sal) from emp where e.deptno!=deptno)")

Results

  ename  sal deptno
1  KING 5000     10

R Programming

%%R

sqldf("select ename,sal,deptno 
       from emp e 
       where deptno=10 
         and NOT EXISTS ( select sal from emp where e.deptno!=deptno AND e.sal < sal)")

Results

  ename  sal deptno
1  KING 5000     10

6. Python pandasql Package

Python Programming

ps.sqldf("select ename,sal,deptno  \
          from emp e \
          where deptno=10 and sal > (select MAX(sal) from emp where e.deptno!=deptno)")

Results

	ename	sal	deptno
0	KING	5000	10

Python Programming

ps.sqldf("select ename,sal,deptno \
       from emp e \
       where deptno=10 \
         and NOT EXISTS ( select sal from emp where e.deptno!=deptno AND e.sal < sal)")

Results

	ename	sal	deptno
0	KING	5000	10

7. R data.table Package

회계부서('10')를 선택 후에 다른 부서에 근무하는 직원들의 최대 급여와 비교하여서 해당 결과값을 불리언(boolean)으로 반환 받은 후 내부조인(DT syntax)하여서 다른 부서에 근무하는 직원들의 최대 급여 보다 많이 받는 회계부서의 직원 정보를 출력한다.

R Programming

%%R
DT <- data.table(emp)

DT[DT[,deptno == 10 & sal > (DT[deptno != 10, max(sal)]) ]]

Results

   empno ename       job mgr   hiredate  sal comm deptno
1:  7839  KING PRESIDENT  NA 1981-11-17 5000   NA     10

8. SAS Proc SQL

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select ename, sal, deptno 
    from   emp e 
    where  deptno = 10 
      and  sal > all(select sal from emp where e.deptno NE deptno);
QUIT;
PROC PRINT;RUN;

Results

OBS	ename	sal	deptno
1	KING	5000	10

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select ename, sal, deptno 
    from   emp e 
    where  deptno = 10 
      and  sal > (select MAX(sal) from emp where e.deptno NE deptno);
QUIT;
PROC PRINT;RUN;

Results

OBS	ename	sal	deptno
1	KING	5000	10

9. SAS Data Step

DATA STEP(CROSS JOIN-카테시안 곱);

SAS Programming

%%SAS sas

PROC SUMMARY DATA=EMP NWAY;
     VAR   SAL;
     OUTPUT OUT=SAL_MAX(DROP=_:) MAX=SAL_MAX;
     where deptno <> 10;
RUN;

DATA STATSAS_3; 
 SET SAL_MAX; 
     DO I=1 TO KOBS; 
        SET emp NOBS=KOBS POINT=I;
        IF deptno = 10 and SAL > SAL_MAX THEN OUTPUT;
     END;
RUN;

PROC PRINT;RUN;

Macro 변수 할당 방식

SAS Programming

%%SAS sas

proc sql noprint;
  select MAX(SAL) into :SAL_MAX
  from   EMP
  where  deptno ne 10;
quit;

%put &SAL_MAX;

DATA STATSAS_3;
 SET EMP;
     IF SAL > &SAL_MAX THEN OUTPUT;
     WHERE deptno = 10;
RUN;
PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7839	KING	PRESIDEN	.	1981-11-17	5000	.	10

10. Python Dfply Package

Python Programming

# [참고] 함수 사용. (52번 예제 참고)
@pipe
def pull_fun(df, column=-1):
    return df.loc[:, column].item()

emp >>                       \
  filter_by(X.deptno==10) >> \
  filter_by(X.sal > ( emp >> filter_by(X.deptno != 10)>>summarize(sal_max=X.sal.max()) >> pull_fun("sal_max") ) )

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
8	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10

Python Programming

emp >> filter_by(X.deptno==10)>> mutate(dummy=1) >> \
  left_join( (emp >> filter_by(X.deptno != 10) >> summarize(sal_max = X.sal.max()) >> mutate(dummy=1)) ) >> \
  filter_by( X.sal > X.sal_max )

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	dummy	sal_max
1	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10	1	3000

[데이터 전처리 방법 비교 목록] SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE

저작자표시

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

[데이터 전처리- 문자함수 예제] 문자 소문자 변환 방식 비교 - 64 (0)	2021.08.24
[데이터 전처리- 문자함수 예제] 문자 대문자 변환 - 63 (0)	2021.08.24
[데이터 필터링 - 상관 서브쿼리(Correlated Subqueyr) 를 만족하는 데이터 추출 - Any 연산자 & 최소값(Min)] 테이블 데이터 전처리 비교 - 61 (0)	2021.08.20
[데이터 필터링] Having 절에 비상관 서브쿼리 사용 - 60 (0)	2021.08.20
[데이터 필터링 - 비상관 서브쿼리(Uncorrelated Subqueyr) 를 만족하는 데이터 추출 - 비교연산자] 테이블 데이터 전처리 비교 - 59 (0)	2021.08.20

[데이터 전처리- 필터링 예제] 상관 서브쿼리(Correlated Subqueyr) 를 만족하는 데이터 추출 - All 연산자 & 최대값(Max) - 62

62. Display the names of employee from department number 10 with salary greater then that of all employee working in other departments.

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

[데이터 전처리 방법 비교 목록] SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE

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

댓글

티스토리툴바

[데이터 전처리- 필터링 예제] 상관 서브쿼리(Correlated Subqueyr) 를 만족하는 데이터 추출 - All 연산자 & 최대값(Max) - 62

62. Display the names of employee from department number 10 with salary greater then that of all employee working in other departments.

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

[데이터 전처리 방법 비교 목록] SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE

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

관련글

댓글

티스토리툴바