포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ DECODE Oracle Function ]
DECODE는 일반적인 프로그래밍 언어의 IF문을 SQL 또는 PL/SQL 에서 사용하도록 만들어진 오라클 함수이다. 따라서 일반 프로그래밍 언어의 IF문이 수행할 수 있는 기능을 포함하고 있다. select시의 decode 사용은 from 절만 빼고 어디에서나 사용할 수 있다. 단. 비교 연산은 ‘=’만 가능하다.
- 함수 설명 : DECODE 오라클 함수 링크
- 조건문(Case when) : 73 / 145
1. Oracle(오라클)
| Oracle Programming |
SELECT EMPNO,
JOB,
DECODE (JOB, 'CLERK' , 'G1',
'SALESMAN' , 'G2',
'MANAGER' , 'G3',
'ANALYST' , 'G4',
'ETC') working_type_1 ,
case when job = 'CLERK' then 'G1' /* 사무직 */
when job = 'SALESMAN' then 'G2' /* 영업직 */
when job = 'MANAGER' then 'G3' /* 관리직 */
when job = 'ANALYST' then 'G4' /* 분석직 */
else 'ETC' end working_type_2
FROM EMP
| Results |
EMPNO JOB WORKING_TYPE_1 WORKING_TYPE_2
------------------------------------------------
7839 PRESIDENT ETC ETC
7698 MANAGER G3 G3
7782 MANAGER G3 G3
7566 MANAGER G3 G3
7788 ANALYST G4 G4
7902 ANALYST G4 G4
7369 CLERK G1 G1
7499 SALESMAN G2 G2
7521 SALESMAN G2 G2
7654 SALESMAN G2 G2
7844 SALESMAN G2 G2
7876 CLERK G1 G1
7900 CLERK G1 G1
7934 CLERK G1 G1
2. Python Pandas(파이썬)
np.select
| Python Programming |
import copy
withmooc = copy.copy(emp)
withmooc
withmooc['working_type'] = np.select( [ withmooc['job'] == 'CLERK',
withmooc['job'] == 'SALESMAN',
withmooc['job'] == 'MANAGER',
withmooc['job'] == 'ANALYST'],
['G1','G2','G3','G4'] ,
default= 'ETC' )
withmooc.head()
| Results |
empno ename job mgr hiredate sal comm deptno working_type
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 G1
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 G2
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 G2
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 G3
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 G2
사용자 정의 함수 IF 구문
| Python Programming |
import copy
withmooc = copy.copy(emp)
withmooc
def get_category(job):
if job == 'CLERK' : type = 'G1'
elif job == 'SALESMAN' : type = 'G2'
elif job == 'MANAGER' : type = 'G3'
elif job == 'ANALYST' : type = 'G4'
else : type = 'Etc'
return type
withmooc['working_type'] = withmooc['job'].apply(lambda x : get_category(x))
withmooc.head()
| Results |
empno ename job mgr hiredate sal comm deptno working_type
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 G1
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 G2
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 G2
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 G3
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 G2
IF ~ else 구문
| Python Programming |
withmooc['working_type'] = withmooc['job'].apply(lambda x : 'G1' if x == 'CLERK' else ('G2' if x == 'SALESMAN' else ('G3' if x == 'MANAGER' else ('G4' if x == 'ANALYST' else 'ETC'))) )
withmooc.head()
| Results |
empno ename job mgr hiredate sal comm deptno working_type
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 G1
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 G2
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 G2
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 G3
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 G2
3. R Programming (R Package)
case_when 구문
| R Programming |
%%R
withmooc <- emp
withmooc['working_type'] = lapply(withmooc['job'], function(x) case_when( x == 'CLERK' ~ "G1",
x == 'SALESMAN' ~ "G2",
x == 'MANAGER' ~ "G3",
x == 'ANALYST' ~ "G4",
TRUE ~ "ETC") )
withmooc[1:5, ]
| Results |
# A tibble: 5 x 9
empno ename job mgr hiredate sal comm deptno working_type
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <chr>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 G1
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 G2
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 G2
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 G3
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 G2
Ifelse 구문
| R Programming |
%%R
withmooc <- emp
withmooc['working_type'] = ifelse(withmooc$job == 'CLERK' ,'G1',
ifelse(withmooc$job == 'SALESMAN', 'G2',
ifelse(withmooc$job == 'MANAGER' , 'G3',
ifelse(withmooc$job == 'ANALYST' , 'G4',
'ETC'))))
withmooc[1:5, ]
| Results |
# A tibble: 5 x 9
empno ename job mgr hiredate sal comm deptno working_type
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <chr>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 G1
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 G2
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 G2
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 G3
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 G2
4. R Dplyr Package
RPYT2에서 한글 반환 문제
| R Programming |
%%R
emp %>%
dplyr::mutate(working_type = dplyr::case_when( job == 'CLERK' ~ "G1",
job == 'SALESMAN' ~ "G2",
job == 'MANAGER' ~ "G3",
job == 'ANALYST' ~ "G4",
TRUE ~ "ETC"
)
) %>%
head()
| Results |
# A tibble: 6 x 9
empno ename job mgr hiredate sal comm deptno working_type
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <chr>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 G1
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 G2
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 G2
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 G3
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 G2
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 G3
5. R sqldf Package
| R Programming |
%%R
sqldf(" SELECT *,
case when job = 'CLERK' then 'G1' /* 사무직 */
when job = 'SALESMAN' then 'G2' /* 영업직 */
when job = 'MANAGER' then 'G3' /* 관리직 */
when job = 'ANALYST' then 'G4' /* 분석직 */
else 'ETC' end working_type
FROM emp ")[1:5, ]
| Results |
empno ename job mgr hiredate sal comm deptno working_type
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 G1
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 G2
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 G2
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 G3
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 G2
6. Python pandasql Package
| Python Programming |
ps.sqldf(" SELECT *, \
case when job = 'CLERK' then '사무직' \
when job = 'SALESMAN' then '판매직' \
when job = 'MANAGER' then '관리직' \
when job = 'ANALYST' then '분석직' \
else '기타' end 직무 \
FROM emp ").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 판매직
7. R data.table Package
dplyr::case_when
| R Programming |
%%R
DT <- data.table(emp)
DT[,working_type := dplyr::case_when( job == 'CLERK' ~ "G1",
job == 'SALESMAN' ~ "G2",
job == 'MANAGER' ~ "G3",
job == 'ANALYST' ~ "G4",
TRUE ~ "ETC"
)][1:5, ]
| Results |
empno ename job mgr hiredate sal comm deptno working_type
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 G1
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 G2
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 G2
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 G3
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 G2
8. Python DuckDB의 SQL
| Python Programming |
%%sql
SELECT *,
case when job = 'CLERK' then '사무직'
when job = 'SALESMAN' then '판매직'
when job = 'MANAGER' then '관리직'
when job = 'ANALYST' then '분석직'
else '기타' end 직무
FROM emp
LIMIT 6
| Python Programming |
duckdb.sql(" SELECT *, \
case when job = 'CLERK' then '사무직' \
when job = 'SALESMAN' then '판매직' \
when job = 'MANAGER' then '관리직' \
when job = 'ANALYST' then '분석직' \
else '기타' end 직무 \
FROM emp \
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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크 |
반응형
댓글