[비상관 서브쿼리(최대값, 최소값)] 특정 조건을 만족하는 데이터 추출

포스팅 목차

121. Display those earners whose salary is out of the grade available in Sal grade table?

* 직원 급여가 급여 등급 테이블에서 관리하는 급여 범위를 벗어나서 존재하지 않는 직원의 정보를 출력하시오.

파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
참고문제 : 86 번
[데이터 추출] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리(최대값, 최소값)
데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql

Oracle : 비상관 서브쿼리, Min(), Max(), Not Between 연산자
파이썬 Pandas : min(), max(), Or('|'), between(), 지역변Local variables) 할당
R 프로그래밍 : min(), max(), Or('|'), which(), subset()
R Dplyr Package : min(), max(), Or('|'), between()
R sqldf Package : 비상관 서브쿼리, Min(), Max(), Not Between 연산자
Python pandasql Package : 비상관 서브쿼리, Min(), Max(), Not Between 연산자
R data.table Package : min(), max(), NOT('!') 연산자, between(), unlist()
SAS Proc SQL : 비상관 서브쿼리, Min(), Max(), Not Between 연산자
SAS Data Step : PROC SUMMARY의 MIN 과 MAX, CROSS JOIN (카테시안 곱) 구현, , Macro 변수 할당, CALL SYMPUT()
Python Dfply Package : min(), max(), Or('|'), between(), NOT('~') 연산자
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

emp 테이블의 급여가 급여 등급 테이블(‘SALGRADE’)의 최소 급여와 최대 급여를 벗어난 경우 해당 직원의 정보를 출력하시오.

Oracle Programming

select * 
from   emp 
where  sal < (select min(losal) from salgrade) 
   or  sal > (select max(hisal) from salgrade);

not between 연산자를 사용하여 emp 테이블의 급여가 급여 등급 테이블(‘SALGRADE’)의 최소 급여와 최대 급여를 벗어난 경우 해당 직원의 정보를 출력하시오.

Oracle Programming

select * 
from   withmooc 
where  sal not between (select min(losal) from salgrade) and (select max(hisal) from salgrade);

2. Python Pandas(파이썬)

OR(|) 연산자를 사용하여 emp 테이블의 급여가 급여 등급 테이블(‘SALGRADE’)의 최소 급여보다 작거나 최대 급여보다 큰 직원의 정보를 출력하시오.

OR(|) 연산자 양쪽에 괄호 없는 경우 에러
코드 확인을 위하여 MILLER(7934)의 급여를 100으로 변경하였다.

Python Programming

withmooc= emp.copy()

withmooc = ps.sqldf("select empno,ename,job,mgr,hiredate,comm,              \
                            case when empno=7934 then 100 else sal end as sal from withmooc")
# display(withmooc)

withmooc[ (withmooc['sal'] < min(salgrade.losal)) | (withmooc['sal'] > max(salgrade.hisal))]

Results

	empno	ename	job	mgr	hiredate	comm	sal
13	7934	MILLER	CLERK	7782.0	1982/01/23	NaN	100

not(“~”) between 연산자를 사용하여 emp 테이블의 급여가 급여 등급 테이블(‘SALGRADE’)의 최소 급여와 최대 급여를 벗어난 경우 해당 직원의 정보를 출력하시오.

코드 확인을 위하여 MILLER(7934)의 급여를 100으로 변경하였다.

Python Programming

withmooc= emp.copy()

withmooc = ps.sqldf("select empno,ename,job,mgr,hiredate,comm,              \
                            case when empno=7934 then 100 else sal end as sal from withmooc")
# display(withmooc)

withmooc[~ withmooc['sal'].between( min(salgrade.losal), max(salgrade.hisal) )]

Results

	empno	ename	job	mgr	hiredate	comm	sal
13	7934	MILLER	CLERK	7782.0	1982/01/23	NaN	100

급여 등급 테이블(‘SALGRADE’)의 최소 급여와 최대 급여를 Local variables로 할당 후 query() 함수에서 emp 테이블의 급여와 비교하여서 범위를 벗어난 해당 직원의 정보를 출력하시오.

코드 확인을 위하여 MILLER(7934)의 급여를 100으로 변경하였다.

Python Programming

withmooc= emp.copy()

withmooc = ps.sqldf("select empno,ename,job,mgr,hiredate,comm,                             \
                            case when empno=7934 then 100 else sal end as sal from withmooc")
# display(withmooc)

losal_min = min(salgrade.losal)

losal_max = max(salgrade.hisal)

withmooc.query('sal < @losal_min or sal > @losal_max')

Results

	empno	ename	job	mgr	hiredate	comm	sal
13	7934	MILLER	CLERK	7782.0	1982/01/23	NaN	100

3. R Programming (R Package)

OR(|) 연산자를 사용하여 emp 테이블의 급여가 급여 등급 테이블(‘SALGRADE’)의 최소 급여보다 작거나 최대 급여보다 큰 직원의 정보를 출력하시오.

코드 확인을 위하여 MILLER(7934)의 급여를 100으로 변경하였다.

R Programming

%%R
withmooc = emp

withmooc <- sqldf(c("update withmooc set sal = 100 WHERE empno = 7934",
                    "select * from withmooc"))

withmooc[withmooc$sal < min(salgrade$losal) | withmooc$sal > max(salgrade$hisal) ,  ]   ## 논리형(boolean) 연산자로 조건에 맞는 직원 선택

Results

   empno  ename   job  mgr   hiredate sal comm deptno
14  7934 MILLER CLERK 7782 1982-01-23 100   NA     10

OR(|) 연산자를 사용하여 emp 테이블의 급여가 급여 등급 테이블(‘SALGRADE’)의 최소 급여보다 작거나 최대 급여보다 큰 직원의 정보를 출력하시오.

코드 확인을 위하여 MILLER(7934)의 급여를 100으로 변경하였다.

R Programming

%%R
withmooc = emp

withmooc <- sqldf(c("update withmooc set sal = 100 WHERE empno = 7934",
                    "select * from withmooc"))

withmooc[which( withmooc$sal < min(salgrade$losal) | withmooc$sal > max(salgrade$hisal) ) ,  ]   ## 조건에 맞는 관측치의 위치를 선택한다.

Results

   empno  ename   job  mgr   hiredate sal comm deptno
14  7934 MILLER CLERK 7782 1982-01-23 100   NA     10

OR(|) 연산자를 사용하여 emp 테이블의 급여가 급여 등급 테이블(‘SALGRADE’)의 최소 급여보다 작거나 최대 급여보다 큰 급여를 수령하는 직원을 선택을 위한 조건을 subset 함수에 지정하여서 해당 조건에 맞는 직원 정보를 출력한다.

코드 확인을 위하여 MILLER(7934)의 급여를 100으로 변경하였다.

R Programming

%%R
withmooc = emp

withmooc <- sqldf(c("update withmooc set sal = 100 WHERE empno = 7934",
                    "select * from withmooc"))

# subset 함수를 사용하여서 사용자가 지정한 조건을 만족하는 관측치를 선택한다.
subset(withmooc,subset= (sal <  min(salgrade$losal) | sal > max(salgrade$hisal) ) , select=c(empno,ename,job,mgr,hiredate,sal,comm,deptno) )

Results

   empno  ename   job  mgr   hiredate sal comm deptno
14  7934 MILLER CLERK 7782 1982-01-23 100   NA     10

4. R Dplyr Package

not(“!”) between 연산자를 사용하여 emp 테이블의 급여가 급여 등급 테이블(‘SALGRADE’)의 최소 급여보다 작거나 최대 급여보다 큰 급여를 수령하는 직원을 선택을 위한 조건을 filter 함수에 지정하여서 해당 조건에 맞는 직원 정보를 출력한다.

코드 확인을 위하여 MILLER(7934)의 급여를 100으로 변경하였다.

R Programming

%%R
withmooc = emp

withmooc <- sqldf(c("update withmooc set sal = 100 WHERE empno = 7934",
                    "select * from withmooc"))


withmooc %>% filter( ! between(sal, min(salgrade$losal), max(salgrade$hisal) ) )

Results

  empno  ename   job  mgr   hiredate sal comm deptno
1  7934 MILLER CLERK 7782 1982-01-23 100   NA     10

emp 테이블의 급여가 급여 등급 테이블(‘SALGRADE’)의 최소 급여보다 작거나 최대 급여보다 큰 급여를 수령하는 직원을 선택을 위한 조건을 filter 함수에 지정하여서 해당 조건에 맞는 직원 정보를 출력한다.

코드 확인을 위하여 MILLER(7934)의 급여를 100으로 변경하였다.

R Programming

%%R
withmooc = emp

withmooc <- sqldf(c("update withmooc set sal = 100 WHERE empno = 7934",
                    "select * from withmooc"))


withmooc %>% filter( sal < min(salgrade$losal) | sal > max(salgrade$hisal) )

Results

  empno  ename   job  mgr   hiredate sal comm deptno
1  7934 MILLER CLERK 7782 1982-01-23 100   NA     10

5. R sqldf Package

emp 테이블의 급여가 급여 등급 테이블(‘SALGRADE’)의 최소 급여와 최대 급여를 벗어난 경우 해당 직원의 정보를 출력하시오.

Sqlite 의 update / delete 구문 : select / where 구문으로 처리 가능 (98번 / 119번)

R Programming

%%R

withmooc = emp

withmooc <- sqldf(c("update withmooc set sal = 100 WHERE empno = 7934",
                    "select * from withmooc"))

sqldf(" select * 
        from   withmooc 
        where  sal < (select min(losal) from salgrade) 
           or  sal > (select max(hisal) from salgrade);")

Results

  empno  ename   job  mgr   hiredate sal comm deptno
1  7934 MILLER CLERK 7782 1982-01-23 100   NA     10

R Programming

%%R

withmooc = emp
withmooc <- sqldf(c("update withmooc set sal = 100 WHERE empno = 7934",
                    "select * from withmooc"))

sqldf(" select * 
        from   withmooc 
        where  sal not between (select min(losal) from salgrade) 
                           and (select max(hisal) from salgrade);")

Results

  empno  ename   job  mgr   hiredate sal comm deptno
1  7934 MILLER CLERK 7782 1982-01-23 100   NA     10

6. Python pandasql Package

현재 pandasql에서 update 제대로 작동 안 함.

Python Programming

withmooc= emp.copy()

withmooc = ps.sqldf("select empno,ename,job,mgr,hiredate,sal,comm,              \
                     case when empno=7934 then 100 else sal end as sal from withmooc")
# display(withmooc)

ps.sqldf(" select *                                       \
           from   withmooc                                \
           where  sal < (select min(losal) from salgrade) \
               or sal > (select max(hisal) from salgrade);")

Results

	empno	ename	job	mgr	hiredate	sal	comm
0	7934	MILLER	CLERK	7782.0	1982/01/23	100	None

Python Programming

ps.sqldf(" select * 
           from   withmooc 
           where  sal not between (select min(losal) from salgrade) 
                              and (select max(hisal) from salgrade);")

Results

	empno	ename	job	mgr	hiredate	sal	comm
0	7934	MILLER	CLERK	7782.0	1982/01/23	100	None

7. R data.table Package

not(“!”) between 연산자를 사용하여 emp 테이블의 급여가 급여 등급 테이블(‘SALGRADE’)의 최소 급여와 최대 급여 범위 이외의 급여를 수령하는 직원을 선택한다.

코드 확인을 위하여 MILLER(7934)의 급여를 100으로 변경하였다.

R Programming

%%R

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

withmooc = emp
withmooc <- sqldf(c("update withmooc set sal = 100 WHERE empno = 7934",
                    "select * from withmooc"))

DT          <- data.table(withmooc)

DT[!between(sal, unlist (salgrade_DT[ , .(`losal_min` = min(losal, na.rm = TRUE))] ) , 
                 unlist (salgrade_DT[ , .(`hisal_max` = max(hisal, na.rm = TRUE))] ) ), .(empno,ename,job,mgr,hiredate,sal,comm,deptno)]

Results

   empno  ename   job  mgr   hiredate sal comm deptno
1:  7934 MILLER CLERK 7782 1982-01-23 100   NA     10

8. SAS Proc SQL

emp 테이블의 급여가 급여 등급 테이블(‘SALGRADE’)의 최소 급여와 최대 급여를 벗어난 경우 해당 직원의 정보를 출력하시오.

SAS Programming

%%SAS sas

PROC SQL;
  create table emp_copy as
    select *
    from   emp;

  update emp_copy 
    set sal = 100 WHERE empno = 7934;

  create table STATSAS_1 as
    select * 
    from   emp_copy 
    where  sal < (select min(losal) from salgrade) 
       or  sal > (select max(hisal) from salgrade);;
QUIT;
PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7934	MILLER	CLERK	7782	1982-01-23	100	.	10

SAS Programming

%%SAS sas

PROC SQL;
  create table emp_copy as
    select *
    from   emp;

  update emp_copy 
    set sal = 100 WHERE empno = 7934;

  create table STATSAS_2 as
    select * 
    from   emp_copy 
    where  sal not between (select min(losal) from salgrade) and (select max(hisal) from salgrade);
QUIT;
PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7934	MILLER	CLERK	7782	1982-01-23	100	.	10

9. SAS Data Step

SAS Programming

%%SAS sas

DATA emp_copy;
 SET EMP;
     IF EMPNO = 7934 THEN SAL = 100;
RUN;

PROC SUMMARY DATA=SALGRADE;
     VAR LOSAL;
     OUTPUT OUT=SALGRADE_LOW(DROP=_:) MIN=;
QUIT;

PROC SUMMARY DATA=SALGRADE;
     VAR hisal;
     OUTPUT OUT=SALGRADE_HIGH(DROP=_:) MAX=;
QUIT;

DATA STATSAS_2; 
 SET SALGRADE_LOW ; 
 SET SALGRADE_HIGH;
     DO I=1 TO KOBS; 
        SET emp_copy NOBS=KOBS POINT=I;
        IF SAL < LOSAL OR SAL > HISAL THEN output;
     END;
RUN;
PROC PRINT;RUN;

Results

OBS	losal	hisal	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	700	9999	7934	MILLER	CLERK	7782	1982-01-23	100	.	10

SAS Programming

%%SAS sas

DATA emp_copy;
 SET EMP;
     IF EMPNO = 7934 THEN SAL = 100;
RUN;

proc sql noprint;
  select MIN(losal), MAX(hisal) into :losal_MIN, :hisal_MAX
  from   salgrade;
quit;

%put TNote:  losal_MIN  = &losal_MIN;
%put TNote:  hisal_MAX  = &hisal_MAX;

DATA STATSAS_3;
 SET emp_copy;
     IF SAL < &losal_MIN OR SAL > &hisal_MAX THEN OUTPUT;
RUN;
PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7934	MILLER	CLERK	7782	1982-01-23	100	.	10

SAS Programming

%%SAS sas

PROC SUMMARY DATA=SALGRADE;
     VAR LOSAL;
     OUTPUT OUT=SALGRADE_LOW(DROP=_:) MIN=LOSAL_MIN;
QUIT;

PROC SUMMARY DATA=SALGRADE;
     VAR hisal;
     OUTPUT OUT=SALGRADE_HIGH(DROP=_:) MAX=hisal_MAX;
QUIT;

DATA _NULL_;
 SET SALGRADE_LOW;
     CALL SYMPUT('LOSAL_MIN',LOSAL_MIN);
RUN;

DATA _NULL_;
 SET SALGRADE_HIGH;
     CALL SYMPUT('hisal_MAX',hisal_MAX);
RUN;

%put TNote:  LOSAL_MIN  = &LOSAL_MIN;
%put TNote:  hisal_MAX  = &hisal_MAX;

DATA STATSAS_4;
 SET emp_copy;
     IF SAL < &LOSAL_MIN OR SAL > &hisal_MAX THEN OUTPUT;
RUN;
PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7934	MILLER	CLERK	7782	1982-01-23	100	.	10

10. Python Dfply Package

Python Programming

withmooc = emp >> mutate( sal = make_symbolic(np.where)(X.empno == 7934, 100, X.sal) )
withmooc

withmooc >> filter_by( ~ X.sal.between(min(salgrade.losal) , max(salgrade.hisal)) )

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
13	7934	MILLER	CLERK	7782.0	1982/01/23	100	NaN	10

Or(‘|’) 연산자 양쪽 구문에 괄호로 둘러싸야 한다.

TypeError: index returned non-int (type Intention)

Python Programming

withmooc = emp >> mutate( sal = make_symbolic(np.where)(X.empno == 7934, 100, X.sal) )
withmooc

withmooc >> filter_by(  (X.sal < min(salgrade.losal)) | (X.sal > max(salgrade.hisal)) )

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
13	7934	MILLER	CLERK	7782.0	1982/01/23	100	NaN	10

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

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

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

[날짜 함수] 날짜 함수를 사용한 기준월 사이의 기간(Gap) 계산 - 123 (오라클 SQL, R, Python, SAS) (0)	2022.12.13
[비상관 서브쿼리(최대값)] 특정 조건을 만족하는 데이터 추출 - 결측치 처리 - 122 (오라클 SQL, R, Python, SAS) (0)	2022.12.13
[비상관 서브쿼리(Not in 연산자)] 특정 조건에 해당하는 데이터 삭제 - 120 (오라클 SQL, R, Python, SAS) (0)	2022.12.11
[상관 서브쿼리] 특정 조건에 해당하는 데이터 삭제 - 119 (오라클 SQL, R, Python, SAS) (0)	2022.12.11
[비상관 서브쿼리(IN 연산자)] 특정 조건을 만족하는 데이터 추출 - 118 (오라클 SQL, R, Python, SAS) (0)	2022.12.11

[비상관 서브쿼리(최대값, 최소값)] 특정 조건을 만족하는 데이터 추출 - 121 (오라클 SQL, R, Python, SAS)

121. Display those earners whose salary is out of the grade available in Sal grade table?

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)' 카테고리의 다른 글

댓글

티스토리툴바

[비상관 서브쿼리(최대값, 최소값)] 특정 조건을 만족하는 데이터 추출 - 121 (오라클 SQL, R, Python, SAS)

121. Display those earners whose salary is out of the grade available in Sal grade table?

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)' 카테고리의 다른 글

관련글

댓글

티스토리툴바