[데이터 필터링] 비상관 서브쿼리를 통한 최대값 추출 - 51 (오라클 SQL, R, Python, SAS)

 

51. Display the name of emp who earns highest sal.

 

* 최대 연봉을 받는 직원의 정보를 출력하시오.

---

• 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
• [데이터 추출] 비상관 서브쿼리(Uncorrelated Subqueyr) 를 만족하는 데이터 추출 - 최대값(MAX)과 비교연산자

Oracle : 서브쿼리(Subquery), max() 함수 파이썬 Pandas : max() 함수 R 프로그래밍 : max() 함수 R Dplyr Package : filter(), max() 함수 R sqldf Package : 서브쿼리(Subquery), max() 함수 Python pandasql Package : 서브쿼리(Subquery), max() 함수 R data.table Package : max() 함수 SAS Proc SQL : 서브쿼리(Subquery), max() 함수 SAS Data Step : PROC RANK, obs Python Dfply Package : filter_by(), max() 함수 파이썬 Base 프로그래밍 :

 

---

1. Oracle(오라클)

• Max() 함수

Oracle Programming

select *
from   emp 
where  sal=(select max(sal) from emp)

 

---

2. Python Pandas(파이썬)

• Max() 함수

Python Programming

emp[emp['sal'] == max(emp.sal)]

 

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
8	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10

 

---

3. R Programming (R Package)

• Max() 함수

R Programming

%%R

emp[emp$sal == max(emp$sal), ]

 

Results

# A tibble: 1 x 8
  empno ename job         mgr hiredate     sal  comm deptno
  <dbl> <chr> <chr>     <dbl> <date>     <dbl> <dbl>  <dbl>
1  7839 KING  PRESIDENT    NA 1981-11-17  5000    NA     10

 

---

4. R Dplyr Package

• Max() 함수

R Programming

%%R

emp %>%
  filter(sal == max(sal))

 

Results

# A tibble: 1 x 8
  empno ename job         mgr hiredate     sal  comm deptno
  <dbl> <chr> <chr>     <dbl> <date>     <dbl> <dbl>  <dbl>
1  7839 KING  PRESIDENT    NA 1981-11-17  5000    NA     10

 

---

5. R sqldf Package

• Max() 함수

R Programming

%%R
sqldf("select * from emp where sal=(select max(sal) from emp)")

 

Results

  empno ename       job mgr   hiredate  sal comm deptno
1  7839  KING PRESIDENT  NA 1981-11-17 5000   NA     10

 

---

6. Python pandasql Package

• Max() 함수

Python Programming

ps.sqldf("select * from emp where sal=(select max(sal) from emp)")

 

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7839	KING	PRESIDENT	None	1981/11/17	5000	None	10

 

---

7. R data.table Package

• Max() 함수

R Programming

%%R

DT <- data.table(emp)

DT[sal == DT[,max(sal, na.rm = TRUE)], ]

 

Results

   empno ename       job mgr   hiredate  sal comm deptno
1:  7839  KING PRESIDENT  NA 1981-11-17 5000   NA     10

 

---

8. SAS Proc SQL

• Max() 함수

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select ename 
    from   emp 
    where  sal=(select max(sal) from emp);
QUIT;
PROC PRINT;RUN;

 

Results

OBS	ename
1	KING

 

---

9. SAS Data Step

 

SAS Programming

%%SAS sas

proc sort data=emp out=emp_1;
     by empno;
run;

PROC SORT DATA=EMP OUT=SAL_MAX;
     BY descending sal;
RUN;

proc sort data=SAL_MAX(OBS=1) out=SAL_MAX_1;
     by empno;
run;

DATA STATSAS_3;
 MERGE EMP_1(IN=A) SAL_MAX_1(IN=B);
     BY EMPNO;
     IF A AND B;   * IF A = 1 AND B = 1 THEN OUTPUT STATSAS_3;
RUN;
PROC PRINT;RUN;

 

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7839	KING	PRESIDEN	.	1981-11-17	5000	.	10

 

---

• PROC RANK 사용;

SAS Programming

%%SAS sas

proc rank data=EMP_1 out= SAL_MAX(where=(SAL_rank=1)) ties=low descending;
      var   SAL;
      ranks SAL_rank;
run;

proc sort data=emp out=emp_1;
     by empno;
run;

DATA STATSAS_3;
 MERGE EMP_1(IN=A) SAL_MAX(IN=B);
     BY EMPNO;
     IF A AND B;   * IF A = 1 AND B = 1 THEN OUTPUT STATSAS_3;
RUN;
PROC PRINT;RUN;

 

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno	SAL_rank
1	7839	KING	PRESIDEN	.	1981-11-17	5000	.	10	1

 

---

10. Python Dfply Package

• Max() 함수

Python Programming

emp >> filter_by( X.sal == X.sal.max() )

 

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
8	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10

 

 

---

 

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