[데이터 추출] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리, 평균

포스팅 목차

111. Display name of those managers name whose salary is more than average salary of company.

* 회사 평균 급여보다 더 많은 급여를 받는 관리자의 정보를 출력하시오.

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

Oracle : 비상관 서브쿼리, avg()
파이썬 Pandas : mean()
R 프로그래밍 : mean()
R Dplyr Package : dplyr::summarise(), mean(), dplyr::pull()
R sqldf Package : 비상관 서브쿼리, avg()
Python pandasql Package : 비상관 서브쿼리, avg()
R data.table Package : DT[], mean()
SAS Proc SQL : 비상관 서브쿼리, avg()
SAS Data Step : Merge 구문, IF 조건문, PROC SUMMARY, CROSS JOIN (카테시안 곱) 구현
Python Dfply Package : @pipe & def 사용자 정의 함수(pull_list), @dfpipe & def 사용자 정의 함수(pull_fun), summarize(), mean()
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

관리자 역할을 수행하는 직원을 선택하기 위하여 서브쿼리를 사용하여 관리자(‘mgr’) 사원번호에 존재하는 사원번호(‘empno’)를 선택하고, 이 중에서 직원 평균 급여보다 더 많은 급여를 수령하는 관리자 정보를 출력한다.

Oracle Programming

select a.empno, a.ename
from   emp a, ( select distinct mgr from emp) e 
WHERE  a.empno = e.MGR 
  AND  a.SAL > (select avg(sal) from emp)

emp 테이블에서 관리자 사원번호의 중복을 제거 후 emp 테이블과 self Join 형식으로 내부조인을 수행하여서 관리자 역할을 수행하는 직원을 선택하고, 이 직원 중에서 직원 평균 급여보다 더 많은 급여를 수령하는 관리자 직원 정보를 출력한다.

Oracle Programming

select a.empno, a.ename
from   emp a, ( select distinct mgr from emp) e 
WHERE  a.empno = e.MGR 
  AND  a.SAL > (select avg(sal) from emp)

2. Python Pandas(파이썬)

emp 테이블에서 사원번호가 관리자 사원번호에 존재하고, 직원 급여가 직원 평균 급여보다 큰 직원의 정보를 출력한다.

Python Programming

emp[(emp.empno.isin(emp.mgr)) & (emp.sal > emp["sal"].mean())]

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
6	7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10
7	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20
8	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10
12	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20

3. R Programming (R Package)

emp 테이블에서 사원번호가 관리자 사원번호에 존재하고, 급여가 직원 평균 급여보다 더 많은 급여를 수령하는 관리자 직원의 정보를 출력한다.

R Programming

%%R

emp[ (emp$empno %in% emp$mgr) & (emp$sal > mean(emp$sal)) , ]

Results

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

4. R Dplyr Package

관리자 역할을 수행하는 직원을 선택하기 위하여 %in% 함수를 사용하여 관리자(‘mgr’) 사원번호에 존재하는 사원번호(‘empno’)를 선택하고, 이 중에서 직원 평균 급여보다 더 많은 급여를 수령하는 관리자 정보를 출력한다.

R Programming

%%R

emp %>% 
  dplyr::filter(empno %in% ( emp%>%
                               dplyr::select(mgr) %>%
                               dplyr::pull()  ) ) %>%
  dplyr::filter(sal > ( emp%>%dplyr::summarise(avg_value = mean(sal)) )  )

Results

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

5. R sqldf Package

R Programming

%%R

sqldf(" select * 
        from   emp 
        where  empno in (select mgr from emp) 
          and  sal > (select avg(sal) from emp);")

Results

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

R Programming

%%R

sqldf(" select a.empno, a.ename
        from   emp a, ( select distinct mgr from emp) e 
        WHERE  a.empno = e.MGR 
          AND  a.SAL > (select avg(sal) from emp)")

Results

  empno ename
1  7566 JONES
2  7698 BLAKE
3  7782 CLARK
4  7788 SCOTT
5  7839  KING
6  7902  FORD

6. Python pandasql Package

Python Programming

ps.sqldf(" select * 
           from   emp 
           where  empno in (select mgr from emp) 
             and  sal > (select avg(sal) from emp);")

Results

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

7. R data.table Package

관리자 역할을 수행하는 직원을 선택하기 위하여 %in% 함수를 지정하여 관리자(‘mgr’) 사원번호에 존재하는 사원번호(‘empno’)를 선택하고, 이 중에서 직원 평균 급여(‘mean_sal’)보다 더 많은 급여를 수령하는 관리자 정보를 출력한다.

R Programming

%%R

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

DT[ DT[,(empno %in% DT[]$mgr) & (sal > DT[, .('mean_sal' = mean(sal, na.rm = TRUE))]$mean_sal) ] ]

Results

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

8. SAS Proc SQL

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select *
    from   emp 
    where  empno in (select mgr from emp) 
      and  sal    > (select avg(sal) from emp);
QUIT;
PROC PRINT;RUN;

Results

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

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_2 AS
    select a.*
    from   emp a, ( select distinct mgr from emp ) e 
    WHERE  a.empno = e.MGR 
      AND  a.SAL > (select avg(sal) from emp);
QUIT;
PROC PRINT;RUN;

Results

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

9. SAS Data Step

SAS Programming

%%SAS sas

PROC SORT DATA=emp OUT=EMP_1(RENAME=( EMPNO = MGR_EMPNO));
     BY EMPNO;
RUN;

PROC SORT DATA=emp OUT=EMP_2(RENAME=( MGR  = MGR_EMPNO)
                                      KEEP = MGR) NODUPKEY;
     BY MGR;
RUN;

DATA STATSAS_2;
 MERGE EMP_1(IN=A) EMP_2(IN=B);
     BY MGR_EMPNO;
     IF A AND B THEN OUTPUT;
     KEEP MGR_EMPNO SAL;
RUN;
PROC PRINT;RUN;

Results

OBS	MGR_EMPNO	sal
1	7566	2975
2	7698	2850
3	7782	2450
4	7788	3000
5	7839	5000
6	7902	3000

SAS Programming

%%SAS sas

PROC SUMMARY DATA=EMP;
     VAR SAL;
     OUTPUT OUT=EMP_1(DROP=_:) MEAN=SAL_MEAN;
RUN;


DATA STATSAS_3; 
 SET EMP_1; 
     DO I=1 TO KOBS; 
        SET STATSAS_2 NOBS=KOBS POINT=I;
        IF SAL > SAL_MEAN THEN OUTPUT;
     END;    
RUN;

PROC PRINT;RUN;

Results

OBS	SAL_MEAN	MGR_EMPNO	sal
1	2073	7566	2975
2	2073	7698	2850
3	2073	7782	2450
4	2073	7788	3000
5	2073	7839	5000
6	2073	7902	3000

10. Python Dfply Package

Python Programming

# [참고] 함수 사용. (52번 예제 참고) : AttributeError: 'DataFrame' object has no attribute 'ix'
@pipe
def pull_list(df, column=-1):
    return df.loc[:, column]

# [참고] 함수 사용. (52번 예제 참고)

@dfpipe
def pull_fun(df, column=-1):
    return df.loc[:, column].item()

emp >> \
  filter_by (X.empno.isin( (emp >> select(X.mgr) >> pull_list("mgr"))) ) >> \
  filter_by(X.sal > ( emp >> summarize( sal_avg = X.sal.mean() ) >> pull_fun("sal_avg") ) )

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
6	7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10
7	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20
8	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10
12	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20

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

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

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

[데이터 추출] 결측치 대체, 카테시안 곱(Cartesian Product) - Full Join, Any 연산자 - 113 (0)	2022.12.07
[데이터 추출] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리, 평균 - 112 (0)	2022.12.07
[데이터 추출] 해당 그룹에 대한 특정 조건을 만족하는 그룹 리스트 출력 - 110 (0)	2022.12.06
[데이터 추출] 그룹 집계 후 특정 조건을 만족하는 그룹의 집계 통계량 출력 - 109 (0)	2022.12.05
[데이터 추출] 최소값과 최대값 계산하기 - 108 (0)	2022.12.05

[데이터 추출] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리, 평균 - 111

111. Display name of those managers name whose salary is more than average salary of company.

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

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

댓글

티스토리툴바

[데이터 추출] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리, 평균 - 111

111. Display name of those managers name whose salary is more than average salary of company.

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

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

관련글

댓글

티스토리툴바