포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ EXTRACT (datetime) Oracle Function ]
EXTRACT함수는 특정 날짜,시간 값이나 날짜 값 표현식으로부터 지정된 날짜 영역의 값을 추출하여 반환한다. TIMEZONE_REGION 또는 TIMEZONE_ABBR(생략형)을 추출하는 경우, 반환값은 적절한 time zone명 또는 생략형을 포함한 문자열이다.
- 함수설명 : EXTRACT(datetime) 오라클 함수 링크
- to_char(hiredate,’DD’) : 77 / 126 / 130 / 133
1. Oracle(오라클)
- 다음 예제는 날짜 데이터에서 년도 부분을(1998년) 반환한다.
| Oracle Programming |
SELECT EXTRACT(YEAR FROM DATE '1998-03-07') EXTRACT_FUNC
FROM DUAL;
| Results |
EXTRACT_FUNC
--------------
1998
- 다음 예제는 EMP 테이블에서 1982년 이후에 고용된 모든 종업원을 선택한다.
| Oracle Programming |
SELECT ENAME, EMPNO, HIREDATE,
EXTRACT(YEAR FROM TO_DATE(HIREDATE, 'DD-MON-RR')) HIRE_YEAR_1,
TO_CHAR(HIREDATE, 'YYYY') HIRE_YEAR_2
FROM EMP
WHERE EXTRACT(YEAR FROM TO_DATE(HIREDATE, 'DD-MON-RR')) >= 1982
ORDER
BY HIREDATE;
| Results |
ENAME EMPNO HIREDATE HIRE_YEAR_1 HIRE_YEAR_2
-----------------------------------------------------
MILLER 7934 23-JAN-82 1982 1982
SCOTT 7788 19-APR-87 1987 1987
ADAMS 7876 23-MAY-87 1987 1987
2. Python Pandas(파이썬)
| Python Programming |
from datetime import datetime
datetime.strptime('1998-03-07',"%Y-%m-%d").date()
| Results |
datetime.date(1998, 3, 7)
| Python Programming |
from datetime import datetime
datetime.strptime('1998-03-07',"%Y-%m-%d").strftime("%Y")
| Results |
'1998'
- emp 테이블의 입사일 데이터에서 입사년도를 추출한다.
| Python Programming |
withmooc = emp.copy()
withmooc['hire_year'] = pd.to_datetime(withmooc['hiredate']).dt.strftime('%Y')
withmooc.head()
| Results |
empno ename job mgr hiredate sal comm deptno hire_year
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 1980
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 1981
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 1981
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 1981
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 1981
3. R Programming (R Package)
| R Programming |
%%R
year(as.Date('1998-03-07'))
| Results |
[1] 1998
- emp 테이블의 입사일 데이터에서 입사년도를 추출한다.
| R Programming |
%%R
withmooc <- emp
withmooc['hire_year'] = year(as.Date(withmooc$hiredate))
withmooc[1:5, ]
| Results |
# A tibble: 5 x 9
empno ename job mgr hiredate sal comm deptno hire_year
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <int>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1980
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1981
4. R Dplyr Package
- emp 테이블의 입사일 데이터에서 입사년도를 추출한다.
| R Programming |
%%R
emp %>%
dplyr::mutate(hire_year = year(as.Date(hiredate))) %>%
head()
| Results |
# A tibble: 6 x 9
empno ename job mgr hiredate sal comm deptno hire_year
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <int>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1980
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1981
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1981
5. R sqldf Package
R 함수 사용
| R Programming |
%%R
gsubfn::fn$sqldf("select `year(as.Date('1981-01-09'))` Year ")
| Results |
Year
1 1981
Sqlite 함수 사용
| R Programming |
%%R
sqldf(" SELECT date('1981-01-09') Date_1,
strftime('%Y-%m-%d', date('1981-01-09')) Date_2,
strftime('%Y' , date('1981-01-09')) Year,
cast( strftime('%Y', date('1981-01-09')) as integer) as int_Year
")
| Results |
Date_1 Date_2 Year int_Year
1 1981-01-09 1981-01-09 1981 1981
| R Programming |
%%R
sqldf(" SELECT *,
strftime('%Y', hiredate * 3600 * 24, 'unixepoch') Year
from emp
")[1:5, ]
| Results |
empno ename job mgr hiredate sal comm deptno Year
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1980
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1981
6. Python pandasql Package
| Python Programming |
ps.sqldf(" SELECT date('1981-01-09') Date_1, \
strftime('%Y-%m-%d', date('1981-01-09')) Date_2, \
strftime('%Y' , date('1981-01-09')) Year, \
cast( strftime('%Y', date('1981-01-09')) as integer) as int_Year ")
| Results |
Date_1 Date_2 Year int_Year
0 1981-01-09 1981-01-09 1981 1981
| Python Programming |
ps.sqldf(" SELECT *,strftime('%Y', hiredate * 3600 * 24, 'unixepoch') Year \
from emp ").head()
| Results |
empno ename job mgr hiredate sal comm deptno Year
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 1975
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 1975
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 1975
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 1975
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 1975
7. R data.table Package
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
DT[,hire_year := year(as.Date(hiredate))][1:5, ]
| Results |
empno ename job mgr hiredate sal comm deptno hire_year
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1980
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1981
8. Python DuckDB의 SQL
| Python Programming |
%%sql
SELECT EXTRACT(YEAR FROM DATE '1998-03-07')| Python Programming |
duckdb.sql(" SELECT EXTRACT(YEAR FROM DATE '1998-03-07') as Extract_1 ").df()
| Results |
Extract_1
0 1998
| Python Programming |
%%sql
SELECT date '1981-01-09' Date_1,
strftime('%Y-%m-%d', date '1981-01-09') Date_2,
strftime( date '1981-01-09', '%Y' ) as Year_1, -- Year 변수명 사용 금지
cast( strftime('%Y', date '1981-01-09' ) as integer) as int_Year
| Python Programming |
duckdb.sql(" SELECT date '1981-01-09' Date_1, \
strftime('%Y-%m-%d', date '1981-01-09') Date_2, \
strftime( date '1981-01-09', '%Y' ) as Year_1, \
cast( strftime('%Y', date '1981-01-09' ) as integer) as int_Year ").df()
| Results |
Date_1 Date_2 Year_1 int_Year
0 1981-01-09 1981-01-09 1981 1981
| Python Programming |
%%sql
SELECT *,
typeof(hiredate) hiredate_type,
strftime('%Y', cast(hiredate as date) ) as year_1
from emp
LIMIT 6| Python Programming |
duckdb.sql(" SELECT EMPNO,ENAME, JOB, SAL, HIREDATE, \
typeof(hiredate) hiredate_type, \
strftime('%Y', cast(hiredate as date) ) as year_1 \
from emp \
LIMIT 6 ").df()| Results |
empno ename job sal hiredate hiredate_type year_1
0 7369 SMITH CLERK 800 1980/12/17 VARCHAR 1980
1 7499 ALLEN SALESMAN 1600 1981/02/20 VARCHAR 1981
2 7521 WARD SALESMAN 1250 1981/02/22 VARCHAR 1981
3 7566 JONES MANAGER 2975 1981/04/02 VARCHAR 1981
4 7654 MARTIN SALESMAN 1250 1981/09/28 VARCHAR 1981
5 7698 BLAKE MANAGER 2850 1981/03/01 VARCHAR 1981
[참고]
| Python Programming |
%%sql
SELECT strftime(DATE '1992-03-02', '%d/%m/%Y') as date_1;
| Python Programming |
duckdb.sql(" SELECT strftime(DATE '1992-03-02', '%d/%m/%Y') as date_1 ").df()
| Results |
date_1
0 02/03/1992
--------------------------------------------
| [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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크 |
반응형
댓글