포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ NVL2 함수 Oracle Function ]
NVL2 함수는 expr1이 NULL이 아니라면 expr2를 반환하고 NULL이면 expr3을 반환하는 함수이다. NVL2함수를 사용하면, 지정한 표현이 NULL인지 판단 여부에 근거하여 쿼리에 반환하는 값(expr2 or expr3)을 판단할 수 있다. 만약 expr1이 NULL이 아니라면, NVL2는 expr2를 반환한다. 만약 expr1인 NULL이라면, NVL2는 expr3을 반환한다.
- 함수 설명 : NVL2 오라클 함수 링크
- NVL함수 참조
- 좀 더 다양한 방식은 NULLIF 함수 참조
1. Oracle(오라클)
NVL2 함수
| Oracle Programming |
SELECT ENAME, SAL,
NVL2(COMM, SAL + (SAL * COMM), SAL) income
FROM EMP
WHERE ENAME like 'B%'
ORDER BY ENAME;
| Results |
ENAME SAL INCOME
-------------------------
BLAKE 2850 2850
2. Python Pandas(파이썬)
np.where 구문
수수료(comm)의 값이 존재하지 않으면 급여를 반환하고, 수수료(comm)의 값이 존재하는 경우에는 급여+수수료의 값을 반환한다.
| Python Programming |
withmooc = copy.copy(emp)
withmooc['comm_nvl2'] = np.where(withmooc['comm'].isnull(), withmooc['sal'], withmooc['sal']+withmooc['comm'])
withmooc.head()
| Results |
empno ename job mgr hiredate sal comm deptno comm_nvl2
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 800.0
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 1900.0
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 1750.0
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 2975.0
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 2650.0
3. R Programming (R Package)
ifelse 구문
| R Programming |
%%R
withmooc<-emp
withmooc['comm_nvl2'] = ifelse(is.na(withmooc$comm),withmooc$sal,withmooc$sal + withmooc$comm)
withmooc[1:10, ]
| Results |
# A tibble: 10 x 9
empno ename job mgr hiredate sal comm deptno comm_nvl2
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <dbl>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 800
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1900
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1750
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 2975
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 2650
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 2850
7 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 2450
8 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 3000
9 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 5000
10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 1500
4. R Dplyr Package
ifelse()
| R Programming |
%%R
emp %>%
dplyr::mutate(comm_nvl2 = ifelse(is.na(comm),sal,sal + comm)) %>%
head()
| Results |
# A tibble: 6 x 9
empno ename job mgr hiredate sal comm deptno comm_nvl2
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <dbl>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 800
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1900
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1750
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 2975
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 2650
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 2850
5. R sqldf Package
case when 구문
| R Programming |
%%R
sqldf(" SELECT ename,comm,
sal + comm tot_sal,
case when comm is null then sal else sal + comm end comm_nvl2
FROM emp ")
| Results |
ename comm tot_sal comm_nvl2
1 SMITH NA NA 800
2 ALLEN 300 1900 1900
3 WARD 500 1750 1750
4 JONES NA NA 2975
5 MARTIN 1400 2650 2650
6 BLAKE NA NA 2850
7 CLARK NA NA 2450
8 SCOTT NA NA 3000
9 KING NA NA 5000
10 TURNER 0 1500 1500
11 ADAMS NA NA 1100
12 JAMES NA NA 950
13 FORD NA NA 3000
14 MILLER NA NA 1300
6. Python pandasql Package
case when 구문
| Python Programming |
ps.sqldf(" SELECT ename,comm, \
sal + comm tot_sal, \
case when comm is null then sal else sal + comm end comm_nvl2 \
FROM emp ").head()
| Results |
ename comm tot_sal comm_nvl2
0 SMITH NaN NaN 800.0
1 ALLEN 300.0 1900.0 1900.0
2 WARD 500.0 1750.0 1750.0
3 JONES NaN NaN 2975.0
4 MARTIN 1400.0 2650.0 2650.0
7. R data.table Package
ifelse()
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
DT[, comm_nvl2 := ifelse(is.na(comm),sal,sal + comm)][1:10, ]
| Results |
empno ename job mgr hiredate sal comm deptno comm_nvl2
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 800
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1900
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1750
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 2975
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 2650
6: 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 2850
7: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 2450
8: 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 3000
9: 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 5000
10: 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 1500
8. Python Duckdb의 SQL
CASE WHEN 구문
| Python Programming |
%%sql
SELECT ename,comm,
sal + comm tot_sal,
case when comm is null then sal else sal + comm end comm_nvl2
FROM emp
LIMIT 6
| Python Programming |
duckdb.sql(" SELECT ename,comm, \
sal + comm tot_sal, \
case when comm is null then sal else sal + comm end comm_nvl2 \
FROM emp \
LIMIT 6 ").df()
| Results |
ename comm tot_sal comm_nvl2
0 SMITH NaN NaN 800.0
1 ALLEN 300.0 1900.0 1900.0
2 WARD 500.0 1750.0 1750.0
3 JONES NaN NaN 2975.0
4 MARTIN 1400.0 2650.0 2650.0
5 BLAKE NaN NaN 2850.0
--------------------------------------------
| [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table] 오라클 함수와 R & Python 비교 사전 목록 링크 |
| 오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크 |
| [SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크 |
반응형
댓글