포스팅 목차
139. Display the name of then dept those employees who joined the company on the same date?
* 동일한 일자에 입사한 직원의 정보를 출력하시오.
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- 상관(상호연관)서브쿼리 - Correlated Subquery / co-related subquery : 56 / 94 /112 / 113 등
- [데이터 추출] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리(In), 내부조인
- 데이터 전처리 -SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql
|
1. Oracle(오라클)
서브쿼리에서 입사일자(hiredate)를 선택 후 동일한 입사일자에 입사한 직원들의 정보를 출력한다.
| Oracle Programming |
select empno,ename,hiredate,deptno
from emp e
where hiredate in (select hiredate from emp where empno<>e.empno);
emp 테이블을 self join 형태로 결합하여서 본인 이외의 사원들 중에서 동일한 입사일자에 입사한 직원들의 정보를 출력한다.
| Oracle Programming |
select a.ename,b.ename
from emp a,emp b
where a.hiredate = b.hiredate
and a.empno! = b.empno
2. Python Pandas(파이썬)
emp 테이블을 self join 형식으로 내부조인(Inner join)을 수행하여서 동일한 입사일자에 입사한 직원들을 선택 후 본인의 사원번호를 제거한 후 직원들의 정보를 출력한다.
- 왼쪽 테이블(‘_x’)의 변수만을 선택하여 출력하기 위하여 fliter 함수를 사용한다.
| Python Programming |
joined_df = emp.merge(emp, how='inner',left_on="hiredate", right_on="hiredate")
joined_df[joined_df.empno_x != joined_df.empno_y].filter(like="_x")
| Results |
| empno_x | ename_x | job_x | mgr_x | sal_x | comm_x | deptno_x | |
| 12 | 7900 | JAMES | CLERK | 7698.0 | 950 | NaN | 30 |
| 13 | 7902 | FORD | ANALYST | 7566.0 | 3000 | NaN | 20 |
emp 테이블을 self join 형식으로 내부조인(Inner join)을 수행하여서 동일한 입사일자에 입사한 직원들을 선택 후 query()함수를 사용하여서 본인의 사원번호를 제거한 후 직원들의 정보를 출력한다.
- 왼쪽 테이블(‘_x’)의 변수만을 선택하여 출력하기 위하여 fliter 함수를 사용한다.
| Python Programming |
emp.merge(emp, how='inner',left_on="hiredate", right_on="hiredate").query('empno_x != empno_y').filter(like="_x")
| Results |
| empno_x | ename_x | job_x | mgr_x | sal_x | comm_x | deptno_x | |
| 12 | 7900 | JAMES | CLERK | 7698.0 | 950 | NaN | 30 |
| 13 | 7902 | FORD | ANALYST | 7566.0 | 3000 | NaN | 20 |
3. R Programming (R Package)
emp 테이블을 self join 형식으로 내부조인(Inner join)을 수행하여서 동일한 입사일자에 입사한 직원들을 선택 후 subset()함수를 사용하여서 본인의 사원번호를 제외한 후 직원들의 정보를 출력한다.
- 왼쪽 테이블(‘.x’)의 변수만을 선택하여 출력하기 위하여 grep()로 변수명 리스트에서 해당 변수 이름의 위치를 검색 후 emp테이블에서 해당 변수를 선택한다.
| R Programming |
%%R
withmooc <- subset( merge(emp, emp, by.x=c("hiredate"), by.y=c("hiredate"),all=F),
empno.x != empno.y)
withmooc[,grep(".x$", names(withmooc))]
| Results |
empno.x ename.x job.x mgr.x sal.x comm.x deptno.x
11 7900 JAMES CLERK 7698 950 NA 30
12 7902 FORD ANALYST 7566 3000 NA 20
emp 테이블을 self join 형식으로 내부조인(Inner join)을 수행하여서 동일한 입사일자에 입사한 직원들을 선택 후 subset()함수를 사용하여서 본인의 사원번호를 제외한 후 직원들의 정보를 출력한다.
- 왼쪽 테이블(‘.x’)의 변수만을 선택하여 출력하기 위하여 like() 함수로 변수명 리스트에서 해당 변수 이름을 선택하고, emp 테이블에서 해당 변수를 선택한다.
| R Programming |
%%R
withmooc <- subset(merge(emp,
emp,
by.x=c("hiredate"),
by.y=c("hiredate"),all=F), empno.x != empno.y)
withmooc[,names(withmooc) %like% ".x"]
| Results |
empno.x ename.x job.x mgr.x sal.x comm.x deptno.x
11 7900 JAMES CLERK 7698 950 NA 30
12 7902 FORD ANALYST 7566 3000 NA 20
4. R Dplyr Package
| R Programming |
%%R
emp %>%
inner_join (emp , by = c("hiredate" = "hiredate") , suffix=c("_x", "_y")) %>%
dplyr::filter(empno_x != empno_y) %>%
dplyr::select(ends_with("_x"))
| Results |
# A tibble: 2 x 7
empno_x ename_x job_x mgr_x sal_x comm_x deptno_x
<dbl> <chr> <chr> <dbl> <dbl> <dbl> <dbl>
1 7900 JAMES CLERK 7698 950 NA 30
2 7902 FORD ANALYST 7566 3000 NA 20
5. R sqldf Package
서브쿼리에서 입사일자(hiredate)를 선택 후 본인을 제외한 동일한 입사일자에 입사한 직원들의 정보를 출력한다.
| R Programming |
%%R
sqldf(" select empno,ename,hiredate,deptno
from emp e
where hiredate in (select hiredate from emp where empno<>e.empno);")
| Results |
empno ename hiredate deptno
1 7900 JAMES 1981-12-03 30
2 7902 FORD 1981-12-03 20
emp 테이블을 self join 형태로 결합하여서 본인 이외의 사원들 중에서 동일한 입사일자에 입사한 직원들의 정보를 출력한다.
| R Programming |
%%R
sqldf(" select a.*
from emp a,emp b
where a.hiredate=b.hiredate
and a.empno!=b.empno ")
| Results |
empno ename job mgr hiredate sal comm deptno
1 7900 JAMES CLERK 7698 1981-12-03 950 NA 30
2 7902 FORD ANALYST 7566 1981-12-03 3000 NA 20
6. Python pandasql Package
서브쿼리에서 입사일자(hiredate)를 선택 후 동일한 입사일자에 입사한 직원들의 정보를 출력한다.
| Python Programming |
ps.sqldf(" select empno,ename,hiredate,deptno from emp e \
where hiredate in (select hiredate from emp where empno<>e.empno); ")
| Results |
| empno | ename | hiredate | deptno | |
| 0 | 7900 | JAMES | 1981/12/03 | 30 |
| 1 | 7902 | FORD | 1981/12/03 | 20 |
emp 테이블을 self join 형태로 결합하여서 본인 이외의 사원들 중에서 동일한 입사일자에 입사한 직원들의 정보를 출력한다.
| Python Programming |
ps.sqldf(" select a.ename,b.ename from emp a,emp b \
where a.hiredate = b.hiredate \
and a.empno != b.empno ")
| Results |
| ename | ename | |
| 0 | JAMES | FORD |
| 1 | FORD | JAMES |
7. R data.table Package
emp 테이블을 DT 결합 방식으로 내부조인(Inner join)을 수행하여서 동일한 입사일자에 입사한 직원들을 선택 후 본인의 사원번호를 제외한 후 직원들의 정보를 출력한다.
- DT syntax : DT방식의 on에서 != 는 현재 지원 안함
- cross join 예제 : 113번 예제 참고
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
salgrade_DT <- data.table(salgrade)
DT[DT, nomatch=NULL, on = .( hiredate= hiredate ), ][empno != i.empno, empno:deptno]
| Results |
empno ename job mgr hiredate sal comm deptno
1: 7902 FORD ANALYST 7566 1981-12-03 3000 NA 20
2: 7900 JAMES CLERK 7698 1981-12-03 950 NA 30
8. SAS Proc SQL
서브쿼리에서 입사일자(hiredate)를 선택 후 동일한 입사일자에 입사한 직원들의 정보를 출력한다.
| SAS Programming |
%%SAS sas
PROC SQL;
create table STATSAS_1 as
select e.*
from emp e
where hiredate in (select hiredate from emp where empno<>e.empno);;
QUIT;
PROC PRINT;RUN;| Results |
| OBS | empno | ename | job | mgr | hiredate | sal | comm | deptno |
| 1 | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | . | 30 |
| 2 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | . | 20 |
emp 테이블을 self join 형태로 결합하여서 본인 이외의 사원들 중에서 동일한 입사일자에 입사한 직원들의 정보를 출력한다.
| SAS Programming |
%%SAS sas
PROC SQL;
create table STATSAS_2 as
select a.*
from emp a,emp b
where a.hiredate = b.hiredate
and a.empno ne b.empno;
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | empno | ename | job | mgr | hiredate | sal | comm | deptno |
| 1 | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | . | 30 |
| 2 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | . | 20 |
9. SAS Data Step
- DATA STEP(CROSS JOIN-카테시안 곱);
| SAS Programming |
%%SAS sas
DATA STATSAS_3;
SET EMP(RENAME=(hiredate = hiredate1 empno=empno_1) KEEP=hiredate empno);
DO I=1 TO KOBS;
SET EMP NOBS=KOBS POINT=I;
IF hiredate = hiredate1 AND empno NE empno_1 THEN OUTPUT;
DROP hiredate1 empno_1;
END;
RUN;
PROC PRINT;
RUN;
| Results |
| OBS | empno | ename | job | mgr | hiredate | sal | comm | deptno |
| 1 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | . | 20 |
| 2 | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | . | 30 |
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, left_on=["hiredate"], right_on=["hiredate"], suffixes=["_x", "_y"]) >> \
filter_by(X.empno_x != X.empno_y) >> \
select(ends_with('_x'))
| Results |
| empno_x | ename_x | job_x | mgr_x | sal_x | comm_x | deptno_x | |
| 12 | 7900 | JAMES | CLERK | 7698.0 | 950 | NaN | 30 |
| 13 | 7902 | FORD | ANALYST | 7566.0 | 3000 | NaN | 20 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트
반응형
댓글