포스팅 목차
94. Display those employees whose salary greater than his manager salary.
* 해당 관리자의 급여보다 많이 받는 직원의 정보를 출력하시오.
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- 비등가 내부 조인(Inner Join)을 만족하는 데이터 추출
- 유사문제 : 91, 94, 95, 174, 210번
|
1. Oracle(오라클)
emp 테이블을 self join으로 결합하여서 사원의 급여와 해당 관리자의 급여를 비교하여서 관리자의 급여보다 더 많이 받는 사원의 정보를 출력한다.
| Oracle Programming |
select e.*
from emp e,emp p
where e.mgr = p.empno
and e.sal > p.sal;
상호 연관 서브 쿼리를 사용하여서 사원의 급여와 해당 관리자의 급여를 비교하여서 관리자의 급여보다 더 많이 받는 사원의 정보를 출력한다.
| Oracle Programming |
select *
from emp e
where sal > (select sal from emp where empno=e.mgr);
2. Python Pandas(파이썬)
emp 테이블을 self join으로 결합하여서 joined_df 테이블로 저장한 후에 사원의 급여와 해당 관리자의 급여를 비교하여서 관리자의 급여보다 더 많이 받는 사원의 정보를 출력한다.
| 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")
| Results |
| empno_x | ename_x | job_x | mgr_x | hiredate_x | sal_x | comm_x | deptno_x |
| 7788 | SCOTT | ANALYST | 7566.0 | 1982/12/09 | 3000 | NaN | 20 |
| 7902 | FORD | ANALYST | 7566.0 | 1981/12/03 | 3000 | NaN | 20 |
emp 테이블을 self join으로 결합하여서 joined_df 테이블로 저장한 후에 사원의 급여와 해당 관리자의 급여를 비교하여서 관리자의 급여보다 더 많이 받는 사원의 정보를 출력한다.
| Python Programming |
emp.merge(emp, how='inner',left_on="mgr", right_on="empno").query('sal_x > sal_y').filter(like="_x")
| Results |
| empno_x | ename_x | job_x | mgr_x | hiredate_x | sal_x | comm_x | deptno_x |
| 7788 | SCOTT | ANALYST | 7566.0 | 1982/12/09 | 3000 | NaN | 20 |
| 7902 | FORD | ANALYST | 7566.0 | 1981/12/03 | 3000 | NaN | 20 |
emp 테이블을 self join으로 결합하여서 joined_df 테이블로 저장한 후에 사원의 급여와 해당 관리자의 급여를 비교하여서 관리자의 급여보다 더 많이 받는 사원의 정보를 출력한다.
| Python Programming |
emp.merge(emp, how='inner',left_on="mgr", right_on="empno").query('sal_x > sal_y').filter(regex='_x|sal_')
| Results |
| empno_x | ename_x | job_x | mgr_x | hiredate_x | sal_x | comm_x | deptno_x | sal_y |
| 7788 | SCOTT | ANALYST | 7566.0 | 1982/12/09 | 3000 | NaN | 20 | 2975 |
| 7902 | FORD | ANALYST | 7566.0 | 1981/12/03 | 3000 | NaN | 20 | 2975 |
3. R Programming (R Package)
emp 테이블을 self join으로 결합하여서 joined_df 테이블로 저장한 후에 사원의 급여와 해당 관리자의 급여를 비교하여서 관리자의 급여보다 더 많이 받는 사원의 정보를 출력한다.
| R Programming |
%%R
withmooc <- subset(merge( emp,
emp,
by.x=c("mgr"),
by.y=c("empno"),all=F), sal.x > sal.y)
withmooc[,grep("sal|.x$", names(withmooc))]
| Results |
ename.x job.x hiredate.x sal.x comm.x deptno.x sal.y
1 SCOTT ANALYST 1982-12-09 3000 NA 20 2975
2 FORD ANALYST 1981-12-03 3000 NA 20 2975
emp 테이블을 self join으로 결합하여서 joined_df 테이블로 저장한 후에 사원의 급여와 해당 관리자의 급여를 비교하여서 관리자의 급여보다 더 많이 받는 사원의 정보를 출력한다.
| R Programming |
%%R
withmooc <- subset(merge(emp,
emp,
by.x=c("mgr"),
by.y=c("empno"),all=F), sal.x > sal.y)
withmooc[,names(withmooc) %like% ".x"]
| Results |
ename.x job.x hiredate.x sal.x comm.x deptno.x
1 SCOTT ANALYST 1982-12-09 3000 NA 20
2 FORD ANALYST 1981-12-03 3000 NA 20
4. R Dplyr Package
emp 테이블을 self join으로 결합하여서 joined_df 테이블로 저장한 후에 사원의 급여와 해당 관리자의 급여를 비교하여서 관리자의 급여보다 더 많이 받는 사원의 정보를 출력한다. 비등가 조인이 바로 지원하지 않아서 filter로 처리한다.
| 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")
| Results |
# A tibble: 2 x 7
ename_emp job_emp hiredate_emp sal_emp comm_emp deptno_emp sal_mgr
<chr> <chr> <date> <dbl> <dbl> <dbl> <dbl>
1 SCOTT ANALYST 1982-12-09 3000 NA 20 2975
2 FORD ANALYST 1981-12-03 3000 NA 20 2975
5. R sqldf Package
emp 테이블을 self join으로 결합하여서 사원의 급여와 해당 관리자의 급여를 비교하여서 관리자의 급여보다 더 많이 받는 사원의 정보를 출력한다.
| R Programming |
%%R
sqldf(" select e.*,p.sal,p.mgr
from emp e,emp p
where e.mgr = p.empno
and e.sal > p.sal")
| Results |
empno ename job mgr hiredate sal comm deptno sal mgr
1 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 2975 7839
2 7902 FORD ANALYST 7566 1981-12-03 3000 NA 20 2975 7839
상호 연관 서브 쿼리를 사용하여서 사원의 급여와 해당 관리자의 급여를 비교하여서 관리자의 급여보다 더 많이 받는 사원의 정보를 출력한다.
| R Programming |
%%R
sqldf(" select *
from emp e
where sal>(select sal from emp where empno=e.mgr);")
| Results |
empno ename job mgr hiredate sal comm deptno
1 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20
2 7902 FORD ANALYST 7566 1981-12-03 3000 NA 20
6. Python pandasql Package
| Python Programming |
ps.sqldf("select e.*,p.sal,p.mgr from emp e,emp p where e.mgr=p.empno and e.sal > p.sal")
| Python Programming |
ps.sqldf("select * from emp e where sal>(select sal from emp where empno=e.mgr);")
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
| 7788 | SCOTT | ANALYST | 7566.0 | 1982/12/09 | 3000 | None | 20 |
| 7902 | FORD | ANALYST | 7566.0 | 1981/12/03 | 3000 | None | 20 |
7. R data.table Package
emp 테이블을 self join으로 결합하여서 사원의 급여와 해당 관리자의 급여를 비교하여서 관리자의 급여보다 더 많이 받는 사원의 정보를 출력한다.
| 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), ][,empno:deptno]
| Results |
empno ename job mgr hiredate sal comm deptno
1: 7788 SCOTT ANALYST 7566 1982-12-09 2975 NA 20
2: 7902 FORD ANALYST 7566 1981-12-03 2975 NA 20
emp 테이블을 self join으로 결합하여서 사원의 급여와 해당 관리자의 급여를 비교하여서 관리자의 급여보다 더 많이 받는 사원의 정보를 출력한다. 정규식을 사용하여서 self Join의 우측 테이블의 변수에 해당하는 불필요한 변수를 선별하여서 출력을 제어한다.
| R Programming |
%%R
withmooc <- DT[DT, nomatch=NULL, on = .( mgr= empno, sal>sal), ]
mycols <- grep('^i.', colnames(withmooc), value=TRUE) # 불필요한 변수명을 선정
print(mycols)
print("------------------------------------------------------------------------------------------")
withmooc[, !..mycols]
| Results |
[1] "i.ename" "i.job" "i.mgr" "i.hiredate" "i.comm" "i.deptno"
[1] "------------------------------------------------------------------------------------------"
empno ename job mgr hiredate sal comm deptno
1: 7788 SCOTT ANALYST 7566 1982-12-09 2975 NA 20
2: 7902 FORD ANALYST 7566 1981-12-03 2975 NA 20
8. SAS Proc SQL
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_1 AS
select e.*,p.sal as mar_sal
from emp e,emp p
where e.mgr = p.empno
and e.sal > p.sal;
QUIT;
PROC PRINT;RUN;
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | mar_sal |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000 | . | 20 | 2975 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | . | 20 | 2975 |
9. SAS Data Step
| SAS Programming |
%%SAS sas
PROC SORT DATA=emp OUT=EMP_1(RENAME=( mgr = MGR_EMPNO
SAL = EMP_SAL));
BY mgr;
RUN;
PROC SORT DATA=emp OUT=EMP_2(RENAME=( empno = MGR_EMPNO
SAL = MGR_SAL)
KEEP=empno SAL) NODUPKEY;
BY empno;
RUN;
DATA STATSAS_2;
MERGE EMP_1(IN=A) EMP_2(IN=B);
BY MGR_EMPNO;
IF A AND B AND EMP_SAL > MGR_SAL THEN OUTPUT;
RUN;
PROC PRINT;RUN;
| Results |
| empno | ename | job | MGR_EMPNO | hiredate | EMP_SAL | comm | deptno | MGR_SAL |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000 | . | 20 | 2975 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | . | 20 | 2975 |
10. Python Dfply Package
- Key 변수명이 상이한 경우 inner_join : 56번 참고
- Sef_join으로 동일한 테이블을 구별하기 위해서 접미어 추가가 필요
- 현재 dfply 패키지에서 작성된 get_join_parameters(kwargs) 함수 내에 “right_in”으로 입력된 부분 때문에 에러 발생
- 접미어가 get_join_parameters(kwargs) 함수에 (_x,_y)로 기본 세팅 되어 있어서 다른 이름으로 변경이 안됨.
- 조치사항 : 1) 소스 수정 2) 별도 사용자 함수 제작
| 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
emp >> inner_join_fun( emp , \
by =[['mgr'], ['empno'],["_emp","_mgr"]] ) >> \
filter_by(X.sal_x > X.sal_y) >> \
select(ends_with("_x"), X.sal_y)
| Results |
| empno_x | ename_x | job_x | mgr_x | hiredate_x | sal_x | comm_x | deptno_x | sal_y |
| 7788 | SCOTT | ANALYST | 7566.0 | 1982/12/09 | 3000 | NaN | 20 | 2975 |
| 7902 | FORD | ANALYST | 7566.0 | 1981/12/03 | 3000 | NaN | 20 | 2975 |
- 별도 함수 작성 : pd.merge() 로 직접 함수 작성
- 접미어(suffixes) 지정 가능
- inner_join() 함수를 inner_join_fun() 로 편집 후에도 get_join_parameters(join_kwargs) 함수에 접미어(suffixes) 부분이 (‘_x’, ‘_y’)으로 기본 세팅이 되어 변경 불가 및 추가 에러 발생으로 아래 함수 별도 작성
| 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, left_on = ["mgr"], right_on = "empno", suffixes=("_emp","_mgr")) >> \
filter_by(X.sal_emp > X.sal_mgr) >> \
select(ends_with("_emp"), X.sal_mgr)
| Results |
| empno_emp | ename_emp | job_emp | mgr_emp | hiredate_emp | sal_emp | comm_emp | deptno_emp | sal_mgr |
| 7788 | SCOTT | ANALYST | 7566.0 | 1982/12/09 | 3000 | NaN | 20 | 2975 |
| 7902 | FORD | ANALYST | 7566.0 | 1981/12/03 | 3000 | NaN | 20 | 2975 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트
반응형
'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글
| [데이터 추출] 값이 존재하지 않는 NULL 데이터 추출 - 96 (0) | 2022.09.28 |
|---|---|
| [데이터 추출] 내부 조인(Inner Join)을 만족하는 데이터 추출 - 95 (0) | 2022.09.27 |
| [데이터 추출] 비상관 서브쿼리(Uncorrelated Subqueyr) 를 만족하는 데이터 추출 - 비교 연산자(=) 과 Between 연산자 - 93 (0) | 2022.09.27 |
| [데이터 추출] 비상관 서브쿼리(Uncorrelated Subqueyr) 를 만족하는 데이터 추출 - 비교 연산자(=) - 92 (0) | 2022.09.27 |
| [데이터 추출] 내부 조인(Inner Join)을 만족하는 데이터 추출 - 91 (0) | 2022.09.27 |
댓글