포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ NUMTOYMINTERVAL Oracle Function ]
NUMTOYMINTERVAL함수는 입력된 인수 n을 INTERVAL YEAR TO MONTH문자(년도-달에 대한 구간 형식)로 변경한다. 인수 n은 Number값 또는 암묵적으로 NUMBER값으로 변환 가능한 식을 사용할 수 있다. 인수 interval_unit는 CHAR, VARCHAR2, NCHAR, NVARCHAR2 데이터형이다. Interval_unit에 값은 n의 단위를 지정하고, 다음 문자 값을 지정해야 한다.
- 함수 설명 : NUMTOYMINTERVAL 오라클 함수 링크
- LAST_DAY / NUMTODSINTERVAL 참조
1. Oracle(오라클)
NUMTOYMINTERVAL()
| Oracle Programming |
SELECT ENAME, HIREDATE, SAL,
SUM(SAL) OVER (ORDER BY HIREDATE RANGE NUMTOYMINTERVAL(1,'year') PRECEDING) AS t_sal
FROM EMP
ORDER BY
ENAME, HIREDATE;
| Results |
ENAME HIREDATE SAL T_SAL
----------------------------------------------
ADAMS 23-MAY-1987 00:00:00 1100 4100
ALLEN 20-FEB-1981 00:00:00 1600 2400
BLAKE 01-MAY-1981 00:00:00 2850 9475
CLARK 09-JUN-1981 00:00:00 2450 11925
FORD 03-DEC-1981 00:00:00 3000 23625
JAMES 03-DEC-1981 00:00:00 950 23625
JONES 02-APR-1981 00:00:00 2975 6625
KING 17-NOV-1981 00:00:00 5000 19675
MARTIN 28-SEP-1981 00:00:00 1250 14675
MILLER 23-JAN-1982 00:00:00 1300 24125
SCOTT 19-APR-1987 00:00:00 3000 3000
SMITH 17-DEC-1980 00:00:00 800 800
TURNER 08-SEP-1981 00:00:00 1500 13425
WARD 22-FEB-1981 00:00:00 1250 3650
| Oracle Programming |
SELECT SYSDATE + NUMTOYMINTERVAL (10, 'YEAR') "NUMTOYMINTERVAL"
FROM DUAL
| Results |
NUMTOYMINTERVAL
-----------------------
06-JAN-2034 08:26:18
2. Python Pandas(파이썬)
pd.DateOffset(months=5)
| Python Programming |
pd.to_datetime(emp['hiredate']) + pd.DateOffset(months=5)
| Results |
0 1981-05-17
1 1981-07-20
2 1981-07-22
3 1981-09-02
4 1982-02-28
5 1981-08-01
6 1981-06-09
7 1983-05-09
8 1982-04-17
9 1982-02-08
10 1983-06-12
11 1982-05-03
12 1982-05-03
13 1982-06-23
Name: hiredate, dtype: datetime64[ns]
3. R Programming (R Package)
%m+% months()
| R Programming |
%%R
as.Date(emp$hiredate) %m+% months(5)
| Results |
[1] "1981-05-17" "1981-07-20" "1981-07-22" "1981-09-02" "1982-02-28"
[6] "1981-08-01" "1981-06-09" "1983-05-09" "1982-04-17" "1982-02-08"
[11] "1983-06-12" "1982-05-03" "1982-05-03" "1982-06-23"
4. R Dplyr Package
%m+% months()
| R Programming |
%%R
emp %>%
dplyr::mutate(add_date = as.Date(hiredate) %m+% months(5)) %>%
head()
| Results |
# A tibble: 6 x 9
empno ename job mgr hiredate sal comm deptno add_date
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <date>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1981-05-17
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-07-20
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-07-22
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-09-02
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1982-02-28
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1981-08-01
5. R sqldf Package
date() 함수
| R Programming |
%%R
sqldf(" SELECT ename,ename, hiredate,
DATE(strftime('%Y-%m-%d', hiredate * 3600 * 24, 'unixepoch'), 'start of month', '+1 months', '-1 day') last_day,
DATE(strftime('%Y-%m-%d', hiredate * 3600 * 24, 'unixepoch'), '+5 month') last_day
FROM emp
ORDER BY empno, hiredate ")[1:10, ]
| Results |
ename ename hiredate last_day last_day
1 SMITH SMITH 1980-12-17 1980-12-31 1981-05-17
2 ALLEN ALLEN 1981-02-20 1981-02-28 1981-07-20
3 WARD WARD 1981-02-22 1981-02-28 1981-07-22
4 JONES JONES 1981-04-02 1981-04-30 1981-09-02
5 MARTIN MARTIN 1981-09-28 1981-09-30 1982-02-28
6 BLAKE BLAKE 1981-03-01 1981-03-31 1981-08-01
7 CLARK CLARK 1981-01-09 1981-01-31 1981-06-09
8 SCOTT SCOTT 1982-12-09 1982-12-31 1983-05-09
9 KING KING 1981-11-17 1981-11-30 1982-04-17
10 TURNER TURNER 1981-09-08 1981-09-30 1982-02-08
6. Python pandasql Package
date() 함수
| Python Programming |
ps.sqldf(" SELECT ename, hiredate, \
DATE(date(substr(hiredate,1,4)||'-'||substr(hiredate,6,2)||'-'||substr(hiredate,9,2)), 'start of month', '+1 months', '-1 day') last_day, \
DATE(date(substr(hiredate,1,4)||'-'||substr(hiredate,6,2)||'-'||substr(hiredate,9,2)), '+5 month') last_day \
FROM emp \
ORDER BY empno, hiredate ").head()
| Results |
ename hiredate last_day last_day
0 SMITH 1980/12/17 1980-12-31 1981-05-17
1 ALLEN 1981/02/20 1981-02-28 1981-07-20
2 WARD 1981/02/22 1981-02-28 1981-07-22
3 JONES 1981/04/02 1981-04-30 1981-09-02
4 MARTIN 1981/09/28 1981-09-30 1982-02-28
7. R data.table Package
%m+% month()
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
DT[, add_date := as.Date(hiredate) %m+% months(5) ][1:10, ]
| Results |
empno ename job mgr hiredate sal comm deptno add_date
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1981-05-17
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-07-20
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-07-22
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-09-02
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1982-02-28
6: 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1981-08-01
7: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 1981-06-09
8: 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 1983-05-09
9: 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 1982-04-17
10: 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 1982-02-08
8. Python Duckdb의 SQL
| Python Programming |
%%sql
SELECT ename,
hiredate,
date_trunc ('month', cast(hiredate as date) ) + INTERVAL 1 month - interval 1 day as last_day_0,
cast(hiredate as date) + INTERVAL 5 month as NEXT_DAT
FROM emp
ORDER BY empno, hiredate
LIMIT 6
| Python Programming |
duckdb.sql(" SELECT ename, \
hiredate, \
date_trunc ('month', cast(hiredate as date) ) + INTERVAL 1 month - interval 1 day as last_day_0, \
cast(hiredate as date) + INTERVAL 5 month as NEXT_DAT \
FROM emp \
ORDER BY empno, hiredate \
LIMIT 6 ").df()
| Results |
ename hiredate last_day_0 NEXT_DAT
0 SMITH 1980/12/17 1980-12-31 1981-05-17
1 ALLEN 1981/02/20 1981-02-28 1981-07-20
2 WARD 1981/02/22 1981-02-28 1981-07-22
3 JONES 1981/04/02 1981-04-30 1981-09-02
4 MARTIN 1981/09/28 1981-09-30 1982-02-28
5 BLAKE 1981/03/01 1981-03-31 1981-08-01
--------------------------------------------
| [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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 |
반응형
댓글