포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ LAST_DAY Oracle Function ]
LAST_DAY 함수는 해당 날짜가 속한 달의 마지막 날짜를 반환한다. 월마다 마지막 날짜가 다르므로 유용하게 사용될 수 있다.
반환된 데이터 형은 인수 date와 상관없이 항상 DATE이다.
- 함수설명 : LAST_DAY 오라클 함수 링크
- 유사문제 : 80
1. Oracle(오라클)
LAST_DAY()
| Oracle Programming |
SELECT SYSDATE,
LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;
| Results |
SYSDATE Last Days Left
--------------------------------------------
06-JAN-24 31-JAN-24 25
| Oracle Programming |
SELECT empno, hiredate,
TO_CHAR(ADD_MONTHS(LAST_DAY(hiredate), 5)) "Eval Date"
FROM emp
ORDER BY
empno, hiredate;
| Results |
EMPNO HIREDATE Eval Date
-----------------------------------
7369 17-DEC-80 31-MAY-81
7499 20-FEB-81 31-JUL-81
7521 22-FEB-81 31-JUL-81
7566 02-APR-81 30-SEP-81
7654 28-SEP-81 28-FEB-82
7698 01-MAY-81 31-OCT-81
7782 09-JUN-81 30-NOV-81
7788 19-APR-87 30-SEP-87
7839 17-NOV-81 30-APR-82
7844 08-SEP-81 28-FEB-82
7876 23-MAY-87 31-OCT-87
7900 03-DEC-81 31-MAY-82
7902 03-DEC-81 31-MAY-82
7934 23-JAN-82 30-JUN-82
2. Python Pandas(파이썬)
| Python Programming |
import datetime
import dateutil
# Get the current date + time
# 현재 날짜와 시간을 반환한다.
now = datetime.datetime.utcnow()
display(now)
now = now.date()
display(now)
# MonthEnd : Get the last date of the month
# 기준월의 마지막 날짜를 반환한다.
from pandas.tseries.offsets import MonthEnd
display( pd.Timestamp.now() + MonthEnd(1) )
# relativedelta : Get the last date of the month
# 기준월의 마지막 날짜를 반환한다.
from dateutil.relativedelta import relativedelta
display( pd.Timestamp.now() + relativedelta(day=31) )
# 현재일자와 당월 말일자 사이의 Gap
(pd.Timestamp.now() + MonthEnd(1) - datetime.datetime.utcnow()).days
| Results |
datetime.datetime(2021, 1, 15, 13, 27, 4, 589726)
datetime.date(2021, 1, 15)
Timestamp('2021-01-31 22:27:04.593726')
Timestamp('2021-01-31 22:27:04.595726')
16
emp테이블에서 입사월 마지막 날짜를 기준으로 5개월 이후 날짜를 반환한다.
| Python Programming |
from pandas.tseries.offsets import MonthEnd
from dateutil.relativedelta import relativedelta
import copy
withmooc =copy.copy(emp)
withmooc['add_Month'] = pd.to_datetime(withmooc['hiredate']) + MonthEnd(1) + pd.DateOffset(months=5)
withmooc.head(10)
| Results |
empno ename job mgr hiredate sal comm deptno add_Month
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 1981-05-31
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 1981-07-28
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 1981-07-28
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 1981-09-30
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 1982-02-28
5 7698 BLAKE MANAGER 7839.0 1981/03/01 2850 NaN 30 1981-08-31
6 7782 CLARK MANAGER 7839.0 1981/01/09 2450 NaN 10 1981-06-30
7 7788 SCOTT ANALYST 7566.0 1982/12/09 3000 NaN 20 1983-05-31
8 7839 KING PRESIDENT NaN 1981/11/17 5000 NaN 10 1982-04-30
9 7844 TURNER SALESMAN 7698.0 1981/09/08 1500 0.0 30 1982-02-28
| Python Programming |
## from dateutil.relativedelta import relativedelta, FR
from pandas.tseries.offsets import MonthEnd
from dateutil.relativedelta import relativedelta
import copy
withmooc =copy.copy(emp)
withmooc['add_Month'] = withmooc.apply(lambda row: pd.to_datetime(row['hiredate'], format='%Y %m %d')+ MonthEnd(1) + relativedelta(months=5),
axis=1 )
withmooc.head(10)
| Results |
empno ename job mgr hiredate sal comm deptno add_Month
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 1981-05-31
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 1981-07-28
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 1981-07-28
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 1981-09-30
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 1982-02-28
5 7698 BLAKE MANAGER 7839.0 1981/03/01 2850 NaN 30 1981-08-31
6 7782 CLARK MANAGER 7839.0 1981/01/09 2450 NaN 10 1981-06-30
7 7788 SCOTT ANALYST 7566.0 1982/12/09 3000 NaN 20 1983-05-31
8 7839 KING PRESIDENT NaN 1981/11/17 5000 NaN 10 1982-04-30
9 7844 TURNER SALESMAN 7698.0 1981/09/08 1500 0.0 30 1982-02-28
| Python Programming |
from dateutil.relativedelta import relativedelta, FR
import copy
withmooc =copy.copy(emp)
withmooc['add_Month'] = [pd.to_datetime(x, format='%Y %m %d') + MonthEnd(1) + relativedelta(months=5)
for x in withmooc['hiredate']]
withmooc.head(10)
| Results |
empno ename job mgr hiredate sal comm deptno add_Month
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 1981-05-31
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 1981-07-28
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 1981-07-28
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 1981-09-30
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 1982-02-28
5 7698 BLAKE MANAGER 7839.0 1981/03/01 2850 NaN 30 1981-08-31
6 7782 CLARK MANAGER 7839.0 1981/01/09 2450 NaN 10 1981-06-30
7 7788 SCOTT ANALYST 7566.0 1982/12/09 3000 NaN 20 1983-05-31
8 7839 KING PRESIDENT NaN 1981/11/17 5000 NaN 10 1982-04-30
9 7844 TURNER SALESMAN 7698.0 1981/09/08 1500 0.0 30 1982-02-28
3. R Programming (R Package)
| R Programming |
%%R
# 날짜를 반환한다.
print( Sys.Date() )
library(lubridate)
# 당월의 마지막 날짜를 반환한다.
print( lubridate::ceiling_date(Sys.Date(),"month") - days(1) )
# 현재일자와 당월 말일자 사이의 Gap
print( (ceiling_date(Sys.Date(),"month") - 1) - Sys.Date() )
difftime((ceiling_date(Sys.Date(),"month") - 1) , Sys.Date() )
| Results |
[1] "2021-01-27"
[1] "2021-01-31"
Time difference of 4 days
Time difference of 4 days
emp테이블에서 입사월 마지막 날짜를 기준으로 5개월 이후 날짜를 반환한다.
| R Programming |
%%R
withmooc <- emp
withmooc['add_date'] <- (lubridate::ceiling_date(as.Date(withmooc$hiredate),"month") - 1) %m+% months(5)
withmooc[1:10, ]
| Results |
# A tibble: 10 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-31
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-07-28
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-07-28
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-09-30
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-31
7 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 1981-06-30
8 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 1983-05-31
9 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 1982-04-30
10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 1982-02-28
4. R Dplyr Package
| R Programming |
%%R
library(lubridate)
library(mondate) # 월단위 날짜 처리
emp %>%
dplyr::mutate(empno,ename,hiredate,
month_end = (lubridate::ceiling_date(as.Date(hiredate),"month") - days(1)), # 기준월의 마지막 날짜
add_date = as.Date(mondate::mondate(month_end) + 5), # 입사월 마지막 날짜를 기준으로 5개월 이후 마지막 날짜를 반환한다.
add_date_1 = month_end %m+% months(5), # 입사월 마지막 날짜를 기준으로 5개월 이후 날짜를 반환한다.
add_date_2 = lubridate::add_with_rollback(month_end, months(5)) # 입사월 마지막 날짜를 기준으로 5개월 이후 날짜를 반환한다.
) %>%
dplyr::select(empno,ename,hiredate,month_end,add_date,add_date_1,add_date_2) %>%
head(10)
| Results |
# A tibble: 10 x 7
empno ename hiredate month_end add_date add_date_1 add_date_2
<dbl> <chr> <date> <date> <date> <date> <date>
1 7369 SMITH 1980-12-17 1980-12-31 1981-05-31 1981-05-31 1981-05-31
2 7499 ALLEN 1981-02-20 1981-02-28 1981-07-31 1981-07-28 1981-07-28
3 7521 WARD 1981-02-22 1981-02-28 1981-07-31 1981-07-28 1981-07-28
4 7566 JONES 1981-04-02 1981-04-30 1981-09-30 1981-09-30 1981-09-30
5 7654 MARTIN 1981-09-28 1981-09-30 1982-02-28 1982-02-28 1982-02-28
6 7698 BLAKE 1981-03-01 1981-03-31 1981-08-31 1981-08-31 1981-08-31
7 7782 CLARK 1981-01-09 1981-01-31 1981-06-30 1981-06-30 1981-06-30
8 7788 SCOTT 1982-12-09 1982-12-31 1983-05-31 1983-05-31 1983-05-31
9 7839 KING 1981-11-17 1981-11-30 1982-04-30 1982-04-30 1982-04-30
10 7844 TURNER 1981-09-08 1981-09-30 1982-02-28 1982-02-28 1982-02-28
5. R sqldf Package
| R Programming |
%%R
# 현재일자 / 현재일자 / 당월 마지막 날짜
# 당월 마지막 일자 / 현재일자와 마지막 일자 GAP (26일과 31일 GAP)
sqldf(" SELECT date('now') now_day_1,
strftime('%d-%m-%Y', 'now') now_day_2,
date('now','start of month','+1 month','-1 day') last_day,
strftime('%d',datetime('now','start of month','+1 month','-1 second')) last_day_1,
strftime('%d',datetime('now','start of month','+1 month','-1 second')) - strftime('%d-%m-%Y', 'now') Days_left")
| Results |
now_day_1 now_day_2 last_day last_day_1 Days_left
1 2021-01-26 26-01-2021 2021-01-31 31 5
| 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'), 'start of month', '+1 months', '-1 day' , '+5 month') last_day_5Mon
FROM emp
ORDER BY empno, hiredate ")[1:10, ]
| Results |
ename ename hiredate last_day last_day_5Mon
1 SMITH SMITH 1980-12-17 1980-12-31 1981-05-31
2 ALLEN ALLEN 1981-02-20 1981-02-28 1981-07-28
3 WARD WARD 1981-02-22 1981-02-28 1981-07-28
4 JONES JONES 1981-04-02 1981-04-30 1981-09-30
5 MARTIN MARTIN 1981-09-28 1981-09-30 1982-03-02
6 BLAKE BLAKE 1981-03-01 1981-03-31 1981-08-31
7 CLARK CLARK 1981-01-09 1981-01-31 1981-07-01
8 SCOTT SCOTT 1982-12-09 1982-12-31 1983-05-31
9 KING KING 1981-11-17 1981-11-30 1982-04-30
10 TURNER TURNER 1981-09-08 1981-09-30 1982-03-02
6. Python pandasql Package
| Python Programming |
ps.sqldf(" SELECT date('now') now_day_1, \
strftime('%d-%m-%Y', 'now') now_day_2, \
date('now','start of month','+1 month','-1 day') last_day, \
strftime('%d',datetime('now','start of month','+1 month','-1 second')) last_day_1, \
strftime('%d',datetime('now','start of month','+1 month','-1 second')) - strftime('%d-%m-%Y', 'now') Days_left ")
| Results |
now_day_1 now_day_2 last_day last_day_1 Days_left
0 2021-01-26 26-01-2021 2021-01-31 31 5
| 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)), 'start of month', '+1 months', '-1 day' , '+5 month') last_day_5Mon \
FROM emp \
ORDER BY empno, hiredate ").head(10)
| Results |
ename hiredate last_day last_day_5Mon
0 SMITH 1980/12/17 1980-12-31 1981-05-31
1 ALLEN 1981/02/20 1981-02-28 1981-07-28
2 WARD 1981/02/22 1981-02-28 1981-07-28
3 JONES 1981/04/02 1981-04-30 1981-09-30
4 MARTIN 1981/09/28 1981-09-30 1982-03-02
5 BLAKE 1981/03/01 1981-03-31 1981-08-31
6 CLARK 1981/01/09 1981-01-31 1981-07-01
7 SCOTT 1982/12/09 1982-12-31 1983-05-31
8 KING 1981/11/17 1981-11-30 1982-04-30
9 TURNER 1981/09/08 1981-09-30 1982-03-02
7. R data.table Package
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
# DT[, .add_date := as.numeric(format( (lubridate::ceiling_date(as.Date(hiredate),"month") - 1) %m+% months(5) , "%Y%m%d")) ]
DT[, .add_date := (lubridate::ceiling_date(as.Date(hiredate),"month") - 1) %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-31
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-07-28
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-07-28
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-09-30
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-31
7: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 1981-06-30
8: 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 1983-05-31
9: 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 1982-04-30
10: 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 1982-02-28
8. Python DuckDB의 SQL
| Python Programming |
%%sql
SELECT CURRENT_DATE,
LAST_DAY(CURRENT_DATE) as Last_1,
LAST_DAY(CURRENT_DATE) - CURRENT_DATE as Days_Left
| Python Programming |
duckdb.sql(" SELECT CURRENT_DATE, \
LAST_DAY(CURRENT_DATE) as Last_1, \
LAST_DAY(CURRENT_DATE) - CURRENT_DATE as Days_Left ").df()
| Results |
CURRENT_DATE Last_1 Days_Left
0 2023-08-16 2023-08-31 15
| Python Programming |
%%sql
SELECT empno, hiredate,
CAST(LAST_DAY(cast(hiredate as date)) + INTERVAL 5 month AS VARCHAR) "Eval Date"
FROM emp
ORDER BY empno, hiredate
LIMIT 6
| Python Programming |
duckdb.sql(" SELECT empno, hiredate, \
CAST(LAST_DAY(cast(hiredate as date)) + INTERVAL 5 month AS VARCHAR) AS Eval_Date \
FROM emp \
ORDER BY empno, hiredate \
LIMIT 6
| Results |
empno hiredate Eval_Date
0 7369 1980/12/17 1981-05-31
1 7499 1981/02/20 1981-07-28
2 7521 1981/02/22 1981-07-28
3 7566 1981/04/02 1981-09-30
4 7654 1981/09/28 1982-02-28
5 7698 1981/03/01 1981-08-31
--------------------------------------------
| [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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크 |
반응형
댓글