포스팅 목차
61. Display the names of employees from department number 10 with salary greater than that of any employee working in other departments.
* 다른 부서에서 근무하는 직원보다 급여를 많이 받는 10번 부서의 직원 정보를 출력하시오.
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- any , subquery(서브쿼리)
- ANY 연산자는 서브쿼리의 여러 결과값 중에서 비교 연산자를 하나의 값이라도 만족하면 해당 기준 행을 반환한다.
- [데이터 추출] 상관 서브쿼리(Correlated Subqueyr) 를 만족하는 데이터 추출 - Any 연산자 & 최소값(Min)
|
1. Oracle(오라클)
- 부서코드 10번(회계부서)에 근무하는 직원 중에서 회계부서를 제외한 다른 부서에서 근무하는 직원의 급여 중 최소 한 사람의 급여보다 크면 해당 직원 정보를 출력한다.
- any 연산자를 통하여 다른 부서에 근무하는 직원의 최소(Min) 연봉과 비교하게 된다.
| Oracle Programming |
select ename,sal,deptno
from emp e
where deptno=10
and sal > any(select sal from emp where e.deptno!=deptno);
2. Python Pandas(파이썬)
회계부서('10')에 근무하는 직원의 급여가 다른 부서에서 근무하는 직원의 최소 연봉보다 큰 경우 해당 직원의 정보를 출력한다.
| Python Programming |
emp[emp['deptno']==10].loc[lambda x:x['sal'] > ( min(emp[emp['deptno']!=10]['sal']) )]
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | |
| 6 | 7782 | CLARK | MANAGER | 7839.0 | 1981/01/09 | 2450 | NaN | 10 |
| 8 | 7839 | KING | PRESIDENT | NaN | 1981/11/17 | 5000 | NaN | 10 |
| 13 | 7934 | MILLER | CLERK | 7782.0 | 1982/01/23 | 1300 | NaN | 10 |
3. R Programming (R Package)
회계부서('10')에 근무하는 직원의 급여가 다른 부서에서 근무하는 직원의 최소 급여보다 큰 경우 해당 직원의 정보를 출력한다. subset 연산자를 사용하여서 해당 조건에 만족하는 직원 정보를 선택한다.
| R Programming |
%%R
subset( emp[emp$deptno == 10, ], sal > min(emp[emp$deptno != 10,]$sal) )
| Results |
# A tibble: 3 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 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10
3 7934 MILLER CLERK 7782 1982-01-23 1300 NA 10
4. R Dplyr Package
회계부서('10')를 선택하여서 해당 부서에 근무하는 직원의 급여가 다른 부서에 근무하는 직원들의 최소 급여보다 큰 경우에 해당 직원의 정보를 출력한다.
| R Programming |
%%R
emp %>%
filter(deptno == 10) %>%
filter(sal > ( emp %>%
filter(deptno != 10) %>%
summarise(min(sal, na.rm = TRUE)) %>%
pull() )
)
| Results |
# A tibble: 3 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 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10
3 7934 MILLER CLERK 7782 1982-01-23 1300 NA 10
회계부서('10')를 선택 후에 다른 부서에 근무하는 직원의 최소 급여를 결합(내부조인-left Join)하여서 다른 부서의 최소 급여 보다 많이 받는 회계부서의 직원 정보를 출력한다.
| R Programming |
%%R
emp %>%
filter(deptno == 10) %>%
mutate(dummy=TRUE) %>%
left_join ( emp %>%
filter(deptno != 10) %>%
summarise(sal_min = min(sal, na.rm = TRUE)) %>%
mutate(dummy=TRUE)
) %>%
filter(sal > sal_min)
| Results |
Joining, by = "dummy"
# A tibble: 3 x 10
empno ename job mgr hiredate sal comm deptno dummy sal_min
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <lgl> <dbl>
1 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 TRUE 800
2 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 TRUE 800
3 7934 MILLER CLERK 7782 1982-01-23 1300 NA 10 TRUE 800
5. R sqldf Package
- 부서코드 10번(회계부서)에 근무하는 직원 중에서 회계부서를 제외한 다른 부서에서 근무하는 직원의 급여 중 최소 한 사람의 급여보다 크면 해당 직원 정보를 출력한다.
- any 연산자를 통하여 다른 부서에 근무하는 직원의 최소(Min) 연봉과 비교하게 된다.
- 현재 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 min(sal) from emp where e.deptno!=deptno)")
| Results |
ename sal deptno
1 CLARK 2450 10
2 KING 5000 10
3 MILLER 1300 10
| R Programming |
%%R
sqldf("select ename,sal,deptno
from emp e
where deptno=10
and EXISTS ( select sal from emp where e.deptno!=deptno AND e.sal > sal)")
| Results |
ename sal deptno
1 CLARK 2450 10
2 KING 5000 10
3 MILLER 1300 10
6. Python pandasql Package
- 부서코드 10번(회계부서)에 근무하는 직원 중에서 회계부서를 제외한 다른 부서에서 근무하는 직원의 급여 중 최소 한 사람의 급여보다 크면 해당 직원 정보를 출력한다.
- any 연산자를 통하여 다른 부서에 근무하는 직원의 최소(Min) 연봉과 비교하게 된다.
| Python Programming |
ps.sqldf("select ename,sal,deptno \
from emp e \
where deptno=10 and sal > (select min(sal) from emp where e.deptno!=deptno)")
| Results |
| ename | sal | deptno | |
| 0 | CLARK | 2450 | 10 |
| 1 | KING | 5000 | 10 |
| 2 | MILLER | 1300 | 10 |
| Python Programming |
ps.sqldf("select ename,sal,deptno \
from emp e \
where deptno = 10 \
and EXISTS ( select e2.sal from emp e2 where e2.deptno!= e.deptno AND e2.sal < e.sal)")
| Results |
| ename | sal | deptno | |
| 0 | CLARK | 2450 | 10 |
| 1 | KING | 5000 | 10 |
| 2 | MILLER | 1300 | 10 |
7. R data.table Package
회계부서('10')를 선택 후에 다른 부서에 근무하는 직원의 최소 급여와 비교하여서 해당 결과값을 불리언(boolean)으로 반환 받은 후 내부조인(DT syntax)하여서 다른 부서의 최소 급여 보다 많이 받는 회계부서의 직원 정보를 출력한다.
| R Programming |
%%R
DT[DT[,deptno == 10 & sal > (DT[deptno != 10, min(sal)]) ]]
| Results |
empno ename job mgr hiredate sal comm deptno
1: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10
2: 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10
3: 7934 MILLER CLERK 7782 1982-01-23 1300 NA 10
8. SAS Proc SQL
- 부서코드 10번(회계부서)에 근무하는 직원 중에서 회계부서를 제외한 다른 부서에서 근무하는 직원의 급여 중 최소 한 사람의 급여보다 크면 해당 직원 정보를 출력한다.
- any 연산자를 통하여 다른 부서에 근무하는 직원의 최소(Min) 연봉과 비교하게 된다.
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_1 AS
select ename,sal,deptno
from emp e
where deptno=10
and sal > any(select sal from emp where e.deptno NE deptno);
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | ename | sal | deptno |
| 1 | CLARK | 2450 | 10 |
| 2 | KING | 5000 | 10 |
| 3 | MILLER | 1300 | 10 |
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_1 AS
select ename,sal,deptno
from emp e
where deptno=10
and sal > (select MIN(sal) from emp where e.deptno NE deptno);
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | ename | sal | deptno |
| 1 | CLARK | 2450 | 10 |
| 2 | KING | 5000 | 10 |
| 3 | MILLER | 1300 | 10 |
9. SAS Data Step
- DATA STEP(CROSS JOIN-카테시안 곱);
| SAS Programming |
%%SAS sas
PROC SUMMARY DATA=EMP NWAY;
VAR SAL;
OUTPUT OUT=SAL_MIN(DROP=_:) MIN=SAL_MIN;
where deptno <> 10;
RUN;
DATA STATSAS_3;
SET SAL_MIN;
DO I=1 TO KOBS;
SET emp NOBS=KOBS POINT=I;
IF deptno = 10 and SAL > SAL_MIN THEN OUTPUT;
END;
RUN;
PROC PRINT;RUN;
| Results |
| OBS | SAL_MIN | empno | ename | job | mgr | hiredate | sal | comm | deptno |
| 1 | 800 | 7782 | CLARK | MANAGER | 7839 | 1981-01-09 | 2450 | . | 10 |
| 2 | 800 | 7839 | KING | PRESIDEN | . | 1981-11-17 | 5000 | . | 10 |
| 3 | 800 | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | . | 10 |
- Macro 변수 할당 방식
| SAS Programming |
%%SAS sas
proc sql noprint;
select min(SAL) into :SAL_min
from EMP
where deptno ne 10;
quit;
%put &SAL_min;
DATA STATSAS_3;
SET EMP;
IF SAL > &SAL_min THEN OUTPUT;
WHERE deptno = 10;
RUN;
PROC PRINT;RUN;
| Results |
| OBS | empno | ename | job | mgr | hiredate | sal | comm | deptno |
| 1 | 7782 | CLARK | MANAGER | 7839 | 1981-01-09 | 2450 | . | 10 |
| 2 | 7839 | KING | PRESIDEN | . | 1981-11-17 | 5000 | . | 10 |
| 3 | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | . | 10 |
10. Python Dfply Package
회계부서('10')를 선택하여서 해당 부서에 근무하는 직원의 급여가 다른 부서에 근무하는 직원들의 최소 급여보다 큰 경우에 해당 직원의 정보를 출력한다.
| Python Programming |
# [참고] 함수 사용. (52번 예제 참고)
@pipe
def pull_fun(df, column=-1):
return df.loc[:, column].item()
emp >> filter_by( X.deptno==10 , \
X.sal > ( emp >> filter_by(X.deptno != 10) >> summarize(sal_min=X.sal.min()) >> pull_fun("sal_min") ) )
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | |
| 6 | 7782 | CLARK | MANAGER | 7839.0 | 1981/01/09 | 2450 | NaN | 10 |
| 8 | 7839 | KING | PRESIDENT | NaN | 1981/11/17 | 5000 | NaN | 10 |
| 13 | 7934 | MILLER | CLERK | 7782.0 | 1982/01/23 | 1300 | NaN | 10 |
회계부서('10')를 선택 후에 다른 부서에 근무하는 직원의 최소 급여를 결합(내부조인-left Join)하여서 다른 부서의 최소 급여 보다 많이 받는 회계부서의 직원 정보를 출력한다.
| Python Programming |
emp >> filter_by(X.deptno==10)>> mutate(dummy=1) >> \
left_join( (emp >> filter_by(X.deptno != 10) >> summarize(sal_min = X.sal.min()) >> mutate(dummy=1)) ) >> \
filter_by( X.sal > X.sal_min )
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | dummy | sal_min | |
| 0 | 7782 | CLARK | MANAGER | 7839.0 | 1981/01/09 | 2450 | NaN | 10 | 1 | 800 |
| 1 | 7839 | KING | PRESIDENT | NaN | 1981/11/17 | 5000 | NaN | 10 | 1 | 800 |
| 2 | 7934 | MILLER | CLERK | 7782.0 | 1982/01/23 | 1300 | NaN | 10 | 1 | 800 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트
반응형
댓글