[비상관 서브쿼리(In)] 특정 조건을 만족하는 데이터 추출 - 최소값(Min) - 158 (오라클 SQL, R, Python, SAS)

158. List out the lowest paid employees working for each manager, exclude any groups where min sal is less than 1000 sort the output by sal.

 

* 관리자 별로 담당하고 있는 직원 중에서 최소 급여를 수령하는 직원을 선택하고, 이 중에서 급여가 1000을 초과하는 직원의 정보를 출력하시오.

---

• 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
• 상호연관 : 56 / 57 /61 /62 / 94 / 95 / 102 / 106 / 112 / 114 / 116 / 117 / 119 / 139 / 149
• [데이터 추출] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리(In), 최소값(Min)
• 데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql

Oracle : 비상관 서브쿼리, IN 연산자, Min() 파이썬 Pandas : pd.merge(), agg()의 np.min, .loc[], lambda 함수 R 프로그래밍 : subset(), merge(), aggregate()와 function(x) 사용자 정의 함수(Min()) R Dplyr Package : inner_join(), is.na(), dplyr::summarise()의 min(), ungroup() R sqldf Package : 비상관 서브쿼리, IN 연산자, Min() Python pandasql Package : 비상관 서브쿼리, IN 연산자, Min() R data.table Package : merge 방식 데이터 결합, DT Syntax 방식 데이터 결합, min(), IF 조건문, anyNA(), is.na() SAS Proc SQL : 비상관 서브쿼리, IN 연산자, Min() SAS Data Step : PROC SUMMARY의 MIN, MERGE 구문, IF 조건문 Python Dfply Package : @pipe & def 사용자 정의 함수(inner_join_merge), Summarize()의 MIN() 파이썬 Base 프로그래밍 :

 

---

1. Oracle(오라클)

상관 서브쿼리를 사용하여서 관리자가 관리하고 있는 부서원 중에서 최소 급여를 수령하는 직원을 선택하고, 이 중에서 급여가 1000을 초과하여 수령하는 직원을 출력한다.

 

Oracle Programming

select e.ename,e.mgr,e.sal 
from   emp e 
where  sal in (select min(sal) from emp where mgr=e.mgr) 
  and  e.sal > 1000 
order  by sal;

 

---

2. Python Pandas(파이썬)

관리자(‘mgr’) 별로 최소 급여를 집계하여 emp 테이블과 관리자 사원번호와 급여(‘sal=amin’)를 기준으로 조인하여 관리자가 담당하고 있는 직원 중 최소 급여를 수령하는 직원 정보를 선택 후 1000 초과 급여를 수령하는 직원을 최종으로 선택한다.

 

Python Programming

pd.merge(emp, 
         (emp['sal'].groupby(emp['mgr']).agg([np.min])), 
         how='inner', 
         left_on=['mgr','sal'], right_on=['mgr','amin']).loc[lambda x:x['sal']  >1000]

 

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	amin
1	7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10	2450
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	7876	ADAMS	CLERK	7788.0	1983/01/12	1100	NaN	20	1100
5	7934	MILLER	CLERK	7782.0	1982/01/23	1300	NaN	10	1300

 

---

3. R Programming (R Package)

관리자(‘mgr’) 별로 최소 급여를 집계하여 emp 테이블과 관리자 사원번호와 급여(‘sal=sal’)를 기준으로 조인하여 관리자가 담당하고 있는 직원 중 최소 급여를 수령하는 직원 정보를 선택 후 subset() 함수를 사용하여 1000 초과 급여를 수령하는 직원을 최종으로 선택한다.

 

R Programming

%%R

subset( merge(emp,
      ( aggregate(sal ~ mgr, data = emp, FUN = function(x) c( sal_min = min(x) )) ),
      by.x=c("mgr","sal"),
      by.y=c("mgr","sal"),all=F) , 
        (sal > 1000) )

 

Results

   mgr  sal empno  ename     job   hiredate comm deptno
1 7566 3000  7788  SCOTT ANALYST 1982-12-09   NA     20
2 7566 3000  7902   FORD ANALYST 1981-12-03   NA     20
4 7782 1300  7934 MILLER   CLERK 1982-01-23   NA     10
5 7788 1100  7876  ADAMS   CLERK 1983-01-12   NA     20
6 7839 2450  7782  CLARK MANAGER 1981-01-09   NA     10

 

---

4. R Dplyr Package

관리자(‘mgr’) 별로 최소 급여를 집계하여 emp 테이블과 관리자 사원번호와 급여(‘sal=sal_min’)를 기준으로 조인하여 관리자가 담당하고 있는 직원 중 최소 급여를 수령하는 직원 정보를 선택 후 dplyr::filter() 함수를 사용하여 1000 초과 급여를 수령하는 직원을 최종으로 선택한다.

 

R Programming

%%R

emp %>% 
  inner_join (emp %>% filter( is.na(mgr) == FALSE) %>% group_by(mgr) %>% dplyr::summarise(sal_min = min(sal)) %>% ungroup(), 
              by = c("mgr","sal" = "sal_min") ) %>%
  dplyr::filter(sal > 1000)

 

Results

`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 5 x 8
  empno ename  job       mgr hiredate     sal  comm deptno
  <dbl> <chr>  <chr>   <dbl> <date>     <dbl> <dbl>  <dbl>
1  7782 CLARK  MANAGER  7839 1981-01-09  2450    NA     10
2  7788 SCOTT  ANALYST  7566 1982-12-09  3000    NA     20
3  7876 ADAMS  CLERK    7788 1983-01-12  1100    NA     20
4  7902 FORD   ANALYST  7566 1981-12-03  3000    NA     20
5  7934 MILLER CLERK    7782 1982-01-23  1300    NA     10

 

---

5. R sqldf Package

상관 서브쿼리를 사용하여서 관리자가 관리하고 있는 부서원 중에서 최소 급여를 수령하는 직원을 선택하고, 이 중에서 급여가 1000을 초과하여 수령하는 직원을 출력한다.

 

R Programming

%%R

sqldf(" select e.ename,e.mgr,e.sal
           from   emp e
           where  sal in (select min(sal) from emp where mgr=e.mgr)
             and  e.sal>1000
           order
              by  sal; ")

 

Results

   ename  mgr  sal
1  ADAMS 7788 1100
2 MILLER 7782 1300
3  CLARK 7839 2450
4  SCOTT 7566 3000
5   FORD 7566 3000

 

---

6. Python pandasql Package

 

Python Programming

ps.sqldf(" select e.ename,e.mgr,e.sal                                  \
           from   emp e                                                \
           where  sal in (select min(sal) from emp where mgr=e.mgr)    \
             and  e.sal>1000                                           \
           order                                                       \
              by  sal; ")

 

Results

	ename	mgr	sal
0	ADAMS	7788.0	1100
1	MILLER	7782.0	1300
2	CLARK	7839.0	2450
3	SCOTT	7566.0	3000
4	FORD	7566.0	3000

 

---

7. R data.table Package

관리자(‘mgr’) 별로 최소 급여를 집계하여 merge 조인 방식을 사용하여서 emp 테이블과 관리자 사원번호와 급여(‘sal=sal_min’)를 기준으로 조인하여 관리자가 담당하고 있는 직원 중 최소 급여를 수령하는 직원 정보를 선택하고, 이 중에서 1000 초과 급여를 수령하는 직원을 최종으로 선택한다.

• 그룹별 최소 급여를 집계 전에 그룹 변수(by)에 존재하는 결측치 값을 제외한다.

R Programming

%%R

DT <- data.table(emp)

merge(DT, DT[!is.na(mgr) , .(sal_min = min(sal)), by = .(mgr)],
      by.x=c("mgr","sal"),
      by.y=c("mgr","sal_min"),all=F)[sal > 1000, ]

 

Results

    mgr  sal empno  ename       job   hiredate comm deptno
1:   NA 5000  7839   KING PRESIDENT 1981-11-17   NA     10
2: 7566 3000  7788  SCOTT   ANALYST 1982-12-09   NA     20
3: 7566 3000  7902   FORD   ANALYST 1981-12-03   NA     20
4: 7782 1300  7934 MILLER     CLERK 1982-01-23   NA     10
5: 7788 1100  7876  ADAMS     CLERK 1983-01-12   NA     20
6: 7839 2450  7782  CLARK   MANAGER 1981-01-09   NA     10

 

---

if() 조건절을 사용하여서 그룹 변수(by)에 존재하는 결측치 값을 제외한다.

 

R Programming

%%R

DT <- data.table(emp)

merge(DT, DT[,if (!anyNA(.BY)) .(sal_min=min(sal)), by = .(mgr)],
      by.x=c("mgr","sal"),
      by.y=c("mgr","sal_min"),all=F)[sal > 1000, ]

 

Results

    mgr  sal empno  ename     job   hiredate comm deptno
1: 7566 3000  7788  SCOTT ANALYST 1982-12-09   NA     20
2: 7566 3000  7902   FORD ANALYST 1981-12-03   NA     20
3: 7782 1300  7934 MILLER   CLERK 1982-01-23   NA     10
4: 7788 1100  7876  ADAMS   CLERK 1983-01-12   NA     20
5: 7839 2450  7782  CLARK MANAGER 1981-01-09   NA     10

 

---

관리자(‘mgr’) 별로 최소 급여를 집계하여 DT 조인 방식을 사용하여서 emp 테이블과 관리자 사원번호와 급여(‘sal=sal_min’)를 기준으로 조인하여 관리자가 담당하고 있는 직원 중 최소 급여를 수령하는 직원 정보를 선택하고, 이 중에서 1000 초과 급여를 수령하는 직원을 최종으로 선택한다.

 

R Programming

%%R

DT <- data.table(emp)

merge(DT, DT[!is.na(mgr) , .(sal_min = min(sal)), by = .(mgr)],
      by.x=c("mgr","sal"),
      by.y=c("mgr","sal_min"),all=F)[sal > 1000, ]

 

Results

    mgr  sal empno  ename     job   hiredate comm deptno
1: 7566 3000  7788  SCOTT ANALYST 1982-12-09   NA     20
2: 7566 3000  7902   FORD ANALYST 1981-12-03   NA     20
3: 7782 1300  7934 MILLER   CLERK 1982-01-23   NA     10
4: 7788 1100  7876  ADAMS   CLERK 1983-01-12   NA     20
5: 7839 2450  7782  CLARK MANAGER 1981-01-09   NA     10

 

---

R Programming

%%R

DT <- data.table(emp)

DT[  DT[!is.na(mgr) , .(sal_min = min(sal)), by = .(mgr)] , on=c("mgr","sal" = "sal_min")][sal > 1000, ]

 

Results

   empno  ename     job  mgr   hiredate  sal comm deptno
1:  7782  CLARK MANAGER 7839 1981-01-09 2450   NA     10
2:  7788  SCOTT ANALYST 7566 1982-12-09 3000   NA     20
3:  7902   FORD ANALYST 7566 1981-12-03 3000   NA     20
4:  7876  ADAMS   CLERK 7788 1983-01-12 1100   NA     20
5:  7934 MILLER   CLERK 7782 1982-01-23 1300   NA     10

 

---

8. SAS Proc SQL

SAS에서는 NULL이라는 개념이 없어서 DB와 차이가 있음. SAS에서는 NULL을 공백으로 처리.

 

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select E.EMPNO,e.ename,e.mgr,e.sal 
    from   emp e 
    where  sal in (select min(sal) from emp where mgr=e.mgr) 
      and  e.sal > 1000
      AND  E.MGR NE .
    order  by sal;
QUIT;
PROC PRINT;RUN;

 

Results

OBS	empno	ename	mgr	sal
1	7876	ADAMS	7788	1100
2	7934	MILLER	7782	1300
3	7782	CLARK	7839	2450
4	7788	SCOTT	7566	3000
5	7902	FORD	7566	3000

 

---

9. SAS Data Step

 

SAS Programming

%%SAS sas

PROC SORT DATA=EMP OUT=EMP_1(RENAME=(MGR=JOIN_KEY1 SAL=JOIN_KEY2));
     BY MGR SAL;
     WHERE SAL > 1000;
RUN;

PROC SUMMARY DATA=EMP NWAY;
     CLASS MGR;
     VAR   SAL;
     OUTPUT OUT=STATSAS_2(DROP=_:) MIN=MIN_SAL;
QUIT;

PROC SORT DATA=STATSAS_2 OUT=STATSAS_3(RENAME=(MGR=JOIN_KEY1 MIN_SAL=JOIN_KEY2));
     BY MGR MIN_SAL;
RUN;

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

PROC PRINT;RUN;

 

Results

OBS	empno	ename	job	JOIN_KEY1	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	7876	ADAMS	CLERK	7788	1983-01-12	1100	.	20
5	7782	CLARK	MANAGER	7839	1981-01-09	2450	.	10

 

---

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('mgr') >> summarize(sal_min = X.sal.min()) ),
                     left_on=['mgr','sal'], right_on=['mgr','sal_min'], suffixes=["_x", "_y"]) >> \
  filter_by(X.sal > 1000)

 

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	sal_min
1	7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10	2450
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	7876	ADAMS	CLERK	7788.0	1983/01/12	1100	NaN	20	1100
6	7934	MILLER	CLERK	7782.0	1982/01/23	1300	NaN	10	1300

 

---

 

 

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