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

117. Find out the number of employees whose salary is greater than there manager salary?

 

* 담당 관리자의 급여보다 더 많이 받는 직원의 숫자를 출력하시오.

---

• 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
• 94번 문제는 해당 사원 출력
• Cross Join 생성(dummy) : 97번
• fuzzyjoin::fuzzy_left_join : 116번
• [데이터 추출] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리
• 데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql

Oracle : 상관 서브쿼리, Self join 파이썬 Pandas : merge(), filter(), query() R 프로그래밍 : merge(), subset(), count(), nrow() R Dplyr Package : inner_join (), dplyr::summarize() R sqldf Package : 상관 서브쿼리, Self join Python pandasql Package : 상관 서브쿼리, Self join R data.table Package : DT Syntax 방식의 데이터 결합, .N SAS Proc SQL : 상관 서브쿼리, Self join SAS Data Step : DT Syntax의 내부조인, .N, nrow() Python Dfply Package : MERGE구문, IF 조건문, Proc SUMMARY의 N 파이썬 Base 프로그래밍 :

 

---

1. Oracle(오라클)

상관 서브쿼리를 사용하여서 담당 관리자의 급여를 조회 후 해당 직원의 급여가 담당 관리자 급여보다 더 많이 수령하는 직원의 수를 카운트 한다.

 

Oracle Programming

select count(*) emp_cnt
from   emp e 
where  sal > (select sal from emp where empno=e.mgr);

 

---

Self join 을 수행하여서 해당 직원의 급여가 담당 관리자 급여보다 더 많이 수령하는 직원의 수를 출력한다.

 

Oracle Programming

SELECT count(*) emp_cnt
FROM   EMP ,EMP E 
WHERE  EMP.EMPNO = E.MGR 
  AND  EMP.SAL   < E.SAL;

 

---

2. Python Pandas(파이썬)

emp 테이블을 Self join형태의 내부조인을 수행하여서 해당 직원의 급여가 담당 관리자의 급여보다 더 많이 수령하는 직원의 수를 카운트한다.

 

Python Programming

joined_df = emp.merge(emp, how='inner',left_on="mgr", right_on="empno")
joined_df[joined_df.sal_x > joined_df.sal_y].filter(like="_x")['empno_x'].count()

 

Results

2

 

---

emp 테이블을 Self join형태의 내부조인을 수행하여서 해당 직원의 급여가 담당 관리자의 급여보다 더 많이 수령하는 직원의 수를 카운트한다.

 

Python Programming

emp.merge(emp, how='inner',left_on="mgr", right_on="empno").query('sal_x > sal_y').filter(like="_x")['empno_x'].count()

 

Results

2

 

---

Python Programming

emp.merge(emp, how='inner',left_on="mgr", right_on="empno").query('sal_x > sal_y').filter(regex='_x|sal_')['empno_x'].count()

 

Results

2

 

---

3. R Programming (R Package)

merge 함수를 사용하여서 emp 테이블을 Self join형태의 내부조인을 수행한 후 해당 직원의 급여가 담당 관리자의 급여보다 더 많이 수령하는 직원들을 선택 후 count 함수를 사용하여 직원의 수를 카운트한다.

 

R Programming

%%R

withmooc <- subset(merge(emp,
             emp,
             by.x=c("mgr"),
             by.y=c("empno"),all=F), sal.x > sal.y)

count(  withmooc[,grep("sal|.x$", names(withmooc))]  )

 

Results

  n
1 2

 

---

merge 함수를 사용하여서 emp 테이블을 Self join형태의 내부조인을 수행한 후 해당 직원의 급여가 담당 관리자의 급여보다 더 많이 수령하는 직원들을 선택 후 nrow 함수를 사용하여 직원의 수를 카운트한다.

 

R Programming

%%R

withmooc <- subset(merge(emp,
             emp,
             by.x=c("mgr"),
             by.y=c("empno"),all=F), sal.x > sal.y)

nrow(  withmooc[,grep("sal|.x$", names(withmooc))]  )

 

Results

[1] 2

 

---

4. R Dplyr Package

inner_join 함수를 사용하여서 emp 테이블을 Self join형태의 내부조인을 수행한 후 해당 직원의 급여가 담당 관리자의 급여보다 더 많이 수령하는 직원들을 선택하고, summarize 함수를 사용하여 직원의 수를 카운트한다.

 

R Programming

%%R

emp %>% 
  inner_join (emp , by = c("mgr" = "empno") , suffix=c("_emp", "_mgr")) %>%
  dplyr::filter(sal_emp > sal_mgr) %>%
  dplyr::select(ends_with("_emp"), "sal_mgr") %>%
  dplyr::summarize(cnt_count = n())

 

Results

# A tibble: 1 x 1
  cnt_count
      <int>
1         2

 

---

5. R sqldf Package

상관 서브쿼리를 사용하여서 담당 관리자의 급여를 조회 후 해당 직원의 급여가 담당 관리자 급여보다 더 많이 수령하는 직원의 수를 카운트 한다.

 

R Programming

%%R

sqldf(" select count(*) emp_cnt 
        from   emp e 
        where  sal > (select sal from emp where empno=e.mgr);")

 

Results

  emp_cnt
1       2

 

---

Self join 을 수행하여서 해당 직원의 급여가 담당 관리자 급여보다 더 많이 수령하는 직원의 수를 출력한다.

 

R Programming

%%R

sqldf(" SELECT count(*) emp_cnt 
        FROM   emp ,emp E 
        WHERE  EMP.EMPNO = E.MGR 
          AND  EMP.SAL   < E.SAL")

 

Results

  emp_cnt
1       2

 

---

6. Python pandasql Package

상관 서브쿼리를 사용하여서 담당 관리자의 급여를 조회 후 해당 직원의 급여가 담당 관리자 급여보다 더 많이 수령하는 직원의 수를 카운트 한다.

 

Python Programming

ps.sqldf("select count(*) from emp e where sal>(select sal from emp where empno=e.mgr);")

 

Results

	count(*)
0	2

 

---

Self join 을 수행하여서 해당 직원의 급여가 담당 관리자 급여보다 더 많이 수령하는 직원의 수를 출력한다.

 

Python Programming

ps.sqldf("SELECT count(*) FROM emp ,emp E WHERE EMP.EMPNO=E.MGR AND EMP.SAL<E.SAL")

 

Results

	count(*)
0	2

 

---

7. R data.table Package

emp 테이블을 DT Syntax 방식으로 내부조인을 수행한 후 해당 직원의 급여가 담당 관리자의 급여보다 더 많이 수령하는 직원들을 선택하고, .N 함수를 사용하여 직원의 수를 카운트한다.

 

R Programming

%%R

DT          <- data.table(emp)
dept_DT     <- data.table(dept)
salgrade_DT <- data.table(salgrade)

DT[DT, nomatch=NULL, on = .( mgr= empno, sal>sal), ][, .(`cnt_count` = .N)]

 

Results

   cnt_count
1:         2

 

---

emp 테이블을 DT Syntax 방식으로 내부조인을 수행한 후 해당 직원의 급여가 담당 관리자의 급여보다 더 많이 수령하는 직원들을 선택하고, nrow 함수를 사용하여 직원의 수를 카운트한다.

 

R Programming

%%R

DT          <- data.table(emp)
dept_DT     <- data.table(dept)
salgrade_DT <- data.table(salgrade)

nrow(DT[DT, nomatch=NULL, on = .( mgr= empno, sal>sal), ])

 

Results

[1] 2

 

---

8. SAS Proc SQL

상관 서브쿼리를 사용하여서 담당 관리자의 급여를 조회 후 해당 직원의 급여가 담당 관리자 급여보다 더 많이 수령하는 직원의 수를 카운트 한다.

 

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select count(distinct emp.empno) as emp_cnt,
           count(*) as emp_cnt1
    from   emp e 
    where  sal > (select sal from emp where empno=e.mgr);;
QUIT;
PROC PRINT;RUN;

 

Results

OBS	emp_cnt	emp_cnt1
1	2	2

 

---

Self join 을 수행하여서 해당 직원의 급여가 담당 관리자 급여보다 더 많이 수령하는 직원의 수를 출력한다.

 

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_2 AS
    SELECT count(distinct emp.empno) as emp_cnt,
           count(*) as emp_cnt1 
    FROM   emp ,emp E 
    WHERE  EMP.MGR = E.EMPNO
      AND  EMP.SAL   > E.SAL;
QUIT;
PROC PRINT;RUN;

 

Results

OBS	emp_cnt	emp_cnt1
1	2	2

 

---

9. SAS Data Step

 

SAS Programming

%%SAS sas

PROC SORT DATA=EMP OUT=EMP_1(RENAME=(MGR=MGR_EMPNO));
     BY MGR;
RUN;

PROC SORT DATA=EMP OUT=EMP_2(RENAME=(EMPNO=MGR_EMPNO SAL=MGR_SAL) KEEP=EMPNO SAL);
     BY EMPNO;
RUN;

DATA STATSAS_3;
 MERGE EMP_1(IN=A) EMP_2(IN=B);
     BY MGR_EMPNO;
     IF A AND B;
     IF SAL > MGR_SAL THEN OUTPUT;
RUN;

PROC SUMMARY DATA=STATSAS_3;
     VAR EMPNO;
     OUTPUT OUT=STATSAS_4(DROP=_:) N=;
RUN;
PROC PRINT;RUN;

 

Results

OBS	empno
1	2

 

---

10. Python Dfply Package

 

Python Programming

# inner_join()  오류 존재 : 56 번 / 94번 참고

@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 >> \
  inner_join_merge( emp, left_on=['mgr'], right_on=['empno'], suffixes=["_emp", "_mgr"] ) >> \
  filter_by( X.sal_emp > X.sal_mgr) >> \
  select(ends_with("_emp"), "sal_mgr") >> \
  summarize(cnt_count = n(X.empno_emp))

 

Results

	cnt_count
0	2

 

---

Python Programming

emp >> \
  inner_join_merge( emp, left_on=['mgr'], right_on=['empno'], suffixes=["_emp", "_mgr"] ) >> \
  filter_by( X.sal_emp > X.sal_mgr) >> \
  select(ends_with("_emp"), "sal_mgr") >> \
  summarize(cnt_count = X.empno_emp.count() )

 

Results

	cnt_count
0	2

 

---

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