포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ LNNVL Oracle Function ]
LNNVL함수는 조건의 한쪽 또는 양쪽 연산자가 NULL이 존재할 경우에, 조건문을 쉽게 평가하기 위한 방법을 제공한다. 이 함수는 WHERE 구문에서만 사용 가능하다. 이 함수는 조건문을 인수로 가지고, 만약 조건문에 FALSE이거나 UNKNOWN이 존재하면 TRUE를 반환하고, 조건문이 모두 TRUE라면 FALSE를 반환한다. LNNVL함수는 스칼라 표현이 사용될 수 있는 곳이면 이용할 수 있다. IS [NOT] NULL, AND, OR, OR조건문이 있는 문장에서는 유효하지 않으나, 발생 가능한 NULL 값들을 처리하기 위해 사용 가능하다.
- 함수설명 : LNNVL 오라클 함수 링크
1. Oracle(오라클)
DEPTNO가 10 이 아닌 부서를 출력하시오. (WHERE DEPTNO NOT IN (10) 과 동일)
| Oracle Programming |
select *
from emp
where lnnvl(deptno=10)
| Results |
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------------------------------------------------------------------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 - 30
7566 JONES MANAGER 7839 02-APR-81 2975 - 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 - 20
7902 FORD ANALYST 7566 03-DEC-81 3000 - 20
7369 SMITH CLERK 7902 17-DEC-80 800 - 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 - 20
7900 JAMES CLERK 7698 03-DEC-81 950 - 30
2. Python Pandas(파이썬)
| Python Programming |
emp[emp['deptno'] != 10].head()
| Results |
empno ename job mgr hiredate sal comm deptno
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30
3. R Programming (R Package)
| R Programming |
%%R
emp[emp$deptno != 10,][1:10, ]
| Results |
# A tibble: 10 x 8
empno ename job mgr hiredate sal comm deptno
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30
7 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20
8 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
9 7876 ADAMS CLERK 7788 1983-01-12 1100 NA 20
10 7900 JAMES CLERK 7698 1981-12-03 950 NA 30
4. R Dplyr Package
| R Programming |
%%R
emp %>%
dplyr::filter(deptno != 10) %>%
head()
| Results |
# A tibble: 6 x 8
empno ename job mgr hiredate sal comm deptno
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30
5. R sqldf Package
| R Programming |
%%R
sqldf(" select * from emp where deptno <> 10 ")
| Results |
empno ename job mgr hiredate sal comm deptno
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30
7 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20
8 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
9 7876 ADAMS CLERK 7788 1983-01-12 1100 NA 20
10 7900 JAMES CLERK 7698 1981-12-03 950 NA 30
11 7902 FORD ANALYST 7566 1981-12-03 3000 NA 20
6. Python pandasql Package
| Python Programming |
ps.sqldf(" select * from emp where deptno <> 10 ")
| Results |
empno ename job mgr hiredate sal comm deptno
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30
5 7698 BLAKE MANAGER 7839.0 1981/03/01 2850 NaN 30
6 7788 SCOTT ANALYST 7566.0 1982/12/09 3000 NaN 20
7 7844 TURNER SALESMAN 7698.0 1981/09/08 1500 0.0 30
8 7876 ADAMS CLERK 7788.0 1983/01/12 1100 NaN 20
9 7900 JAMES CLERK 7698.0 1981/12/03 950 NaN 30
10 7902 FORD ANALYST 7566.0 1981/12/03 3000 NaN 20
7. R data.table Package
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
DT[deptno != 10,][1:10, ]
| Results |
empno ename job mgr hiredate sal comm deptno
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
6: 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30
7: 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20
8: 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
9: 7876 ADAMS CLERK 7788 1983-01-12 1100 NA 20
10: 7900 JAMES CLERK 7698 1981-12-03 950 NA 30
8. Python DuckDB의 SQL
| Python Programming |
%%sql
select *
from emp
WHERE DEPTNO NOT IN (10)
LIMIT 6
| Python Programming |
duckdb.sql(" select * \
from emp \
WHERE DEPTNO NOT IN (10) \
LIMIT 6 ").df()
| Results |
empno ename job mgr hiredate sal comm deptno
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30
5 7698 BLAKE MANAGER 7839.0 1981/03/01 2850 NaN 30
--------------------------------------------
| [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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크 |
반응형
댓글