포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ NVL Oracle Function ]
NVL함수는 expr1이 NULL이 아니면 expr1을 반환하고, expr1이 NULL이면 expr2를 반환하는 함수이다. 쿼리의 결과에서 NULL(공백으로 반환) 값을 사용자가 지정한 값으로 치환한다. 만약 expr1이 NULL이면, NVL함수는 expr2를 반환한다. 만약 expr1이 NULL이 아니면, NVL함수는 expr1을 반환한다.
- 함수 설명 : NVL 오라클 함수 링크
- NVL 함수는 다음과 같이 CASE WHEN 조건문으로 대체할 수 있다.
- CASE WHEN expr1 is not null THEN expr1 ELSE expr2 END
1. Oracle(오라클)
NVL() 함수
| Oracle Programming |
SELECT ENAME,
COMM,
NVL(TO_CHAR(COMM), 'Not Applicable') "COMMISSION"
FROM EMP
| Results |
ENAME COMM COMMISSION
-------------------------------
KING - Not Applicable
BLAKE - Not Applicable
CLARK - Not Applicable
JONES - Not Applicable
SCOTT - Not Applicable
FORD - Not Applicable
SMITH - Not Applicable
ALLEN 300 300
WARD 500 500
MARTIN 1400 1400
TURNER 0 0
ADAMS - Not Applicable
JAMES - Not Applicable
MILLER - Not Applicable
2. Python Pandas(파이썬)
replace() 함수
수수료(comm)의 값이 Null(NaN)인 경우 ‘Not Applicable’으로 변환하여 반환한다.
| Python Programming |
withmooc = copy.copy(emp)
withmooc['com_nvl'] = withmooc["comm"].replace(np.nan,'Not Applicable')
withmooc.head()
| Results |
empno ename job mgr hiredate sal comm deptno com_nvl
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 Not Applicable
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 300
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 500
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 Not Applicable
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 1400
3. R Programming (R Package)
ifelse 구문(조건문)
| R Programming |
%%R
withmooc <- emp
withmooc['com_nvl']= ifelse(is.na(withmooc$comm),'Not Applicable',withmooc$comm)
withmooc[1:10, ]
| Results |
# A tibble: 10 x 9
empno ename job mgr hiredate sal comm deptno com_nvl
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <chr>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 Not Applicable
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 300
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 500
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 Not Applicable
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1400
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 Not Applicable
7 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 Not Applicable
8 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 Not Applicable
9 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 Not Applicable
10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 0
replace_na 구문
| R Programming |
%%R
replace_na(emp$comm,'Not Applicable')
| Results |
[1] "Not Applicable" "300" "500" "Not Applicable"
[5] "1400" "Not Applicable" "Not Applicable" "Not Applicable"
[9] "Not Applicable" "0" "Not Applicable" "Not Applicable"
[13] "Not Applicable" "Not Applicable"
4. R Dplyr Package
ifelse()
| R Programming |
%%R
emp %>%
dplyr::mutate(comm_nvl = ifelse(is.na(comm),'Not Applicable',comm)) %>%
head()
| Results |
# A tibble: 6 x 9
empno ename job mgr hiredate sal comm deptno comm_nvl
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <chr>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 Not Applicable
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 300
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 500
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 Not Applicable
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1400
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 Not Applicable
5. R sqldf Package
ifnull()
| R Programming |
%%R
sqldf(" SELECT ename,comm,
ifnull(comm,'Not Applicable') comm_nvl,
case when comm is not null then comm else 'Not Applicable' end comm_nvl_1
FROM emp ")
| Results |
ename comm comm_nvl comm_nvl_1
1 SMITH NA Not Applicable Not Applicable
2 ALLEN 300 300.0 300.0
3 WARD 500 500.0 500.0
4 JONES NA Not Applicable Not Applicable
5 MARTIN 1400 1400.0 1400.0
6 BLAKE NA Not Applicable Not Applicable
7 CLARK NA Not Applicable Not Applicable
8 SCOTT NA Not Applicable Not Applicable
9 KING NA Not Applicable Not Applicable
10 TURNER 0 0.0 0.0
11 ADAMS NA Not Applicable Not Applicable
12 JAMES NA Not Applicable Not Applicable
13 FORD NA Not Applicable Not Applicable
14 MILLER NA Not Applicable Not Applicable
6. Python pandasql Package
ifnull()
| Python Programming |
ps.sqldf(" SELECT ename,comm,ifnull(comm,'Not Applicable') comm_nvl FROM emp ").head()
| Results |
ename comm comm_nvl
0 SMITH NaN Not Applicable
1 ALLEN 300.0 300
2 WARD 500.0 500
3 JONES NaN Not Applicable
4 MARTIN 1400.0 1400
7. R data.table Package
ifelse()
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
DT[, comm_nvl := ifelse(is.na(comm),'Not Applicable',comm)][1:10, ]
| Results |
empno ename job mgr hiredate sal comm deptno comm_nvl
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 Not Applicable
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 300
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 500
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 Not Applicable
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1400
6: 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 Not Applicable
7: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 Not Applicable
8: 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 Not Applicable
9: 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 Not Applicable
10: 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 0
8. Python Duckdb의 SQL
COALESCE 함수
| Python Programming |
%%sql
SELECT ename,comm,
coalesce(cast(comm as varchar), 'Not Applicable') "COMMISSION"
FROM emp
LIMIT 6
| Python Programming |
duckdb.sql(" SELECT ename,comm, \
coalesce(cast(comm as varchar), 'Not Applicable') AS COMMISSION \
FROM emp \
LIMIT 6 ").df()
| Results |
ename comm COMMISSION
0 SMITH NaN Not Applicable
1 ALLEN 300.0 300.0
2 WARD 500.0 500.0
3 JONES NaN Not Applicable
4 MARTIN 1400.0 1400.0
5 BLAKE NaN Not Applicable
--------------------------------------------
| [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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 |
반응형
댓글