[날짜 경과일] 특정 조건을 해당하는 데이터 삭제 - 기준일자 사이의 기간(Gap) 계산

포스팅 목차

135. Delete those employees who joined the company 38 years back from today?

* 오늘 날짜를 기준으로 입사년차가 40년이 경과한 직원들의 정보를 삭제하시오.

파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
유사문제 : 12 / 74번
Delete 구문 : 99 / 119 / 120 / 134
[데이터 추출] 특정 조건을 해당하는 데이터 삭제 - 기준일자 사이의 기간(Gap) 계산
데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql

Oracle : Delete 구문, sysdate, to_char()
파이썬 Pandas : pd.Timestamp.now(), pd.to_datetime(), .dt.days
R 프로그래밍 : Sys.Date(), NOT('!') 연산자
R Dplyr Package : Sys.Date(), NOT('!') 연산자
R sqldf Package : julianday(), date(), typeof(), cast(), datetime(), gsubfn::fn$sqldf() 외부 변수 참조, Sys.Date(), Delete 구문
Python pandasql Package : julianday(), strftime()
R data.table Package : Sys.Date(), NOT('!') 연산자
SAS Proc SQL : round(), TODAY(), YRDIF()
SAS Data Step : round(), TODAY(), YRDIF()
Python Dfply Package : pd.Timestamp.now(), make_symbolic(), pd.to_datetime(), .dt.days
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

현재일자와 입사일차의 차이가 40년이 경과된 직원을 선택한다.

Oracle Programming

select * 
from   emp 
where  (sysdate-hiredate) >= 40*365;

올해의 년도와 입사년도의 차이가 40년이 경과한 직원을 선택한다.

Oracle Programming

select * 
from   emp 
where  (to_char (sysdate, 'yyyy')-to_char (hiredate ,'yyyy') ) >= 40;

서브쿼리로 올해의 년도와 입사년도의 차이가 40년 미만인 직원을 선택하여서 직원테이블(EMP)에서 선택된 대상을 삭제한다.

Oracle Programming

create table emp_copy as
  select *
  from   emp;

delete from emp_copy 
where  empno in ( select empno from emp_copy
                  where to_char(sysdate,'yyyy')- to_char(hiredate,'yyyy') < 40);

select *
from   emp_copy;

2. Python Pandas(파이썬)

입사일자를 기준으로 오늘 현재까지 경과 기간을 계산한 후 NOT 연산자(‘~’)를 사용하여서 40년 미만인 직원을 제외한 근속년수가 40년 이상인 직원들을 선택한다.

Python Programming

emp[~ ( (pd.Timestamp.now() - pd.to_datetime(emp['hiredate'])).dt.days < 40*365 )]

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
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
6	7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10

입사일자를 기준으로 오늘 현재까지 경과 기간을 계산한 후 근속년수가 40년 이상인 직원들을 선택한다.

Python Programming

emp[ (pd.Timestamp.now() - pd.to_datetime(emp['hiredate'])).dt.days >= 40*365 ]

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
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
6	7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10

3. R Programming (R Package)

입사일자를 기준으로 오늘 현재까지 경과 기간을 계산한 후 NOT 연산자(‘!’)를 사용하여서 40년 미만인 직원을 제외한 근속년수가 40년 이상인 직원들을 선택한다.

R Programming

%%R

emp[! (Sys.Date() - emp$hiredate < 40*365), ]

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  7698 BLAKE MANAGER   7839 1981-03-01  2850    NA     30
6  7782 CLARK MANAGER   7839 1981-01-09  2450    NA     10

입사일자를 기준으로 오늘 현재까지 경과 기간을 계산한 후 근속년수가 40년 이상인 직원들을 선택한다.

R Programming

%%R

emp[ (Sys.Date() - emp$hiredate >= 40*365), ]

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  7698 BLAKE MANAGER   7839 1981-03-01  2850    NA     30
6  7782 CLARK MANAGER   7839 1981-01-09  2450    NA     10

4. R Dplyr Package

R Programming

%%R
emp %>% filter(! (Sys.Date() - hiredate) < 40*365 )

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  7698 BLAKE MANAGER   7839 1981-03-01  2850    NA     30
6  7782 CLARK MANAGER   7839 1981-01-09  2450    NA     10

입사일자를 기준으로 오늘 현재까지 경과 기간을 계산한 후 근속년수가 40년 이상인 직원들을 선택한다.

R Programming

%%R
emp %>% filter( (Sys.Date() - hiredate) >= 40*365 )

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  7698 BLAKE MANAGER   7839 1981-03-01  2850    NA     30
6  7782 CLARK MANAGER   7839 1981-01-09  2450    NA     10

5. R sqldf Package

https://stackoverflow.com/questions/289680/difference-between-2-dates-in-sqlite
[참고] sqldf(SQLite) 상에서 날짜 데이터 처리
- gap2_int 변수를 조건절에 사용 시 년도를 기준으로 근속년수를 산정하여서 일부 차이가 발생한다.
- 유닉스(UNIX) 시간 형식은 1970년 1월 1일(1970-01-01)을 기준으로 날짜를 계산.
- 율리우스일은 기원전 4713년 1월 1일(B.C 4713.1.1) 그리니치 정오로부터 날짜를 0부터 시작하여 계산
- julianday : 74번(http://statwith.com/emp-example-sql-r-python-74/)
- NUMTODSINTERVAL Oracle Function [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table] : http://statwith.com/numtodsinterval-oracle-function-comparison/

R Programming

%%R

sqldf("select (julianday('now','localtime') - julianday(datetime(hiredate * 3600 * 24,'unixepoch'))) / 365 gap_float,
              (date('now','localtime') - datetime(hiredate * 3600 * 24,'unixepoch')) gap2_int,
              hiredate,
              date(0, 'unixepoch')              base_date,  /* 1970-01-01을 시작으로 경과된 날짜를 계산하여 처리한다. */
              julianday('-4713-11-24 12:00:00') julian_base,
              julianday('1970-01-01')           ex_dt1,
              julianday('1980-12-17')           ex_dt2,
              julianday('now','localtime')      today,
              julianday(datetime('now'))        today_dt,
              typeof(hiredate)                  type1,
              typeof(julianday('now'))          type2,

              JulianDay(hiredate,'localtime')                       hire_dt1,
              cast(hiredate AS NUMERIC)                             hire_num,
              julianday(datetime(hiredate * 3600 * 24,'unixepoch')) hire_dt2
       from   emp
       where  (date('now','localtime') - datetime(hiredate * 3600 * 24,'unixepoch')) >= 40")

Results

   gap_float gap2_int   hiredate  base_date julian_base  ex_dt1  ex_dt2   today today_dt type1 type2 hire_dt1 hire_num hire_dt2
1   40.30519       41 1980-12-17 1970-01-01           0 2440588 2444591 2459302  2459302  real  real 4003.375     4003  2444591
2   40.12710       40 1981-02-20 1970-01-01           0 2440588 2444591 2459302  2459302  real  real 4068.375     4068  2444656
3   40.12162       40 1981-02-22 1970-01-01           0 2440588 2444591 2459302  2459302  real  real 4070.375     4070  2444658
4   40.01477       40 1981-04-02 1970-01-01           0 2440588 2444591 2459302  2459302  real  real 4109.375     4109  2444697
5   39.52436       40 1981-09-28 1970-01-01           0 2440588 2444591 2459302  2459302  real  real 4288.375     4288  2444876
6   40.10245       40 1981-03-01 1970-01-01           0 2440588 2444591 2459302  2459302  real  real 4077.375     4077  2444665
7   40.24217       40 1981-01-09 1970-01-01           0 2440588 2444591 2459302  2459302  real  real 4026.375     4026  2444614
8   39.38738       40 1981-11-17 1970-01-01           0 2440588 2444591 2459302  2459302  real  real 4338.375     4338  2444926
9   39.57916       40 1981-09-08 1970-01-01           0 2440588 2444591 2459302  2459302  real  real 4268.375     4268  2444856
10  39.34354       40 1981-12-03 1970-01-01           0 2440588 2444591 2459302  2459302  real  real 4354.375     4354  2444942
11  39.34354       40 1981-12-03 1970-01-01           0 2440588 2444591 2459302  2459302  real  real 4354.375     4354  2444942

입사일자를 기준으로 오늘 현재까지 경과 기간을 계산한 후 근속년수가 40년 이상인 직원들을 선택한다.

정확한 날짜 계산을 위하여 julianday 함수를 사용하여 일자 사이의 차이를 계산
74번 문제 참조

R Programming

%%R

sqldf("select empno,
              datetime('now','localtime')  now_date, 
              strftime('%d-%m-%Y', 'now')  now_char,
              julianday('now','localtime') now_julian,
              (julianday('now','localtime') - julianday(datetime(hiredate * 3600 * 24,'unixepoch'))) / 365 hire_gap,
              (date('now','localtime') - datetime(hiredate * 3600 * 24,'unixepoch'))                       hire_gap2
       from   emp
       where  (julianday('now','localtime') - julianday(datetime(hiredate * 3600 * 24,'unixepoch'))) / 365 >= 40")

Results

  empno            now_date   now_char now_julian hire_gap hire_gap2
1  7369 2021-03-28 23:08:55 28-03-2021    2459302 40.30675        41
2  7499 2021-03-28 23:08:55 28-03-2021    2459302 40.12867        40
3  7521 2021-03-28 23:08:55 28-03-2021    2459302 40.12319        40
4  7566 2021-03-28 23:08:55 28-03-2021    2459302 40.01634        40
5  7698 2021-03-28 23:08:55 28-03-2021    2459302 40.10401        40
6  7782 2021-03-28 23:08:55 28-03-2021    2459302 40.24374        40

입사일자를 기준으로 오늘 현재까지 경과 일자를 계산한 후 근속년수가 40년 이상인 직원들을 선택한다.

R에서 현재시간을 반환하는 Sys.Date() 함수를 fn$sqldf 기능으로 함께 사용하여서 sqldf에 내에서 사용한다.

R Programming

%%R
print(Sys.Date())

gsubfn::fn$sqldf(" select empno,ename,
                          (`Sys.Date()` - hiredate) hire_gap
                   from   emp 
                   where  (`Sys.Date()` - hiredate) >= 40 * 365  ")

Results

[1] "2021-03-28"
  empno ename hire_gap
1  7369 SMITH    14711
2  7499 ALLEN    14646
3  7521  WARD    14644
4  7566 JONES    14605
5  7698 BLAKE    14637
6  7782 CLARK    14688

서브쿼리로 올해의 년도와 입사년도의 차이가 40년 미만인 직원을 선택한 후 delete 구문을 사용하여서 직원테이블(EMP)에서 선택된 대상을 삭제한다.

R Programming

%%R

withmooc <- emp

withmooc <- sqldf(c("delete from withmooc 
                     where  (julianday('now','localtime') - julianday(datetime(hiredate * 3600 * 24,'unixepoch'))) / 365 < 40",
                     "select * from main.withmooc"))
withmooc

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  7698 BLAKE  MANAGER 7839 1981-03-01 2850   NA     30
6  7782 CLARK  MANAGER 7839 1981-01-09 2450   NA     10

6. Python pandasql Package

현재 pandasql에서 update/delete 지원 안 함.

Python Programming

import copy

withmooc_1 = ps.sqldf(" select empno,ename,                                                                                                       \
                               ( julianday(date('now')) -                                                                                         \
                                 julianday(substr(hiredate,1,4)||'-'||substr(hiredate,6,2)||'-'||substr(hiredate,9,2)   ) )/365  gap_float,       \
                               (strftime('%s','now') -                                                                                            \
                                strftime('%s',(substr(hiredate,1,4)||'-'||substr(hiredate,6,2)||'-'||substr(hiredate,9,2))))/60/60/24/365 gap_int \
                        from withmooc                                                                                                             \
                        where ( julianday(date('now')) -                                                                                          \
                                julianday(substr(hiredate,1,4)||'-'||substr(hiredate,6,2)||'-'||substr(hiredate,9,2)   ) )/365 <= 40") 
withmooc_1

7. R data.table Package

R Programming

%%R

DT          <- data.table(emp)

DT[! (Sys.Date() - hiredate) < 40*365,]

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:  7698 BLAKE  MANAGER 7839 1981-03-01 2850   NA     30
6:  7782 CLARK  MANAGER 7839 1981-01-09 2450   NA     10

입사일자를 기준으로 오늘 현재까지 경과 기간을 계산한 후 근속년수가 40년 이상인 직원들을 선택한다.

R Programming

%%R

DT          <- data.table(emp)

DT[ (Sys.Date() - hiredate) >= 40*365,]

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:  7698 BLAKE  MANAGER 7839 1981-03-01 2850   NA     30
6:  7782 CLARK  MANAGER 7839 1981-01-09 2450   NA     10

8. SAS Proc SQL

현재일자와 입사일차의 차이가 40년이 경과된 직원의 데이터를 선택한다.

SAS Programming

%%SAS sas

PROC SQL;
  create table STATSAS_1 as
    select EMPNO,
           round((TODAY()-hiredate)/365) AS YEAR_GAP,
           ROUND(YRDIF(hiredate, TODAY(), 'act/act')) as YEAR_gap_1
    from   emp 
    where  (TODAY()-hiredate) >= 40*365;  
QUIT;
PROC PRINT;RUN;

Results

OBS	empno	YEAR_GAP	YEAR_GAP
1	7369	40	40
2	7499	40	40
3	7521	40	40
4	7566	40	40
5	7698	40	40
6	7782	40	40

9. SAS Data Step

현재일자와 입사일차의 차이가 40년이 경과된 직원의 데이터를 선택한다.

SAS Programming

%%SAS sas

DATA STATSAS_2; 
 SET EMP;
     YEAR_GAP_1 = round((TODAY()-hiredate)/365);
     YEAR_gap_2 = ROUND(YRDIF(hiredate, TODAY(), 'act/act'));
     WHERE  (TODAY()-hiredate) >= 40*365;
     KEEP EMPNO YEAR_:;
RUN;
PROC PRINT;RUN;

Results

OBS	empno	YEAR_GAP_1	YEAR_gap_2
1	7369	40	40
2	7499	40	40
3	7521	40	40
4	7566	40	40
5	7698	40	40
6	7782	40	40

10. Python Dfply Package

현재일자와 입사일차의 차이가 40년이 경과된 직원의 데이터를 선택한다.

Python Programming

emp >> filter_by( ~ (pd.Timestamp.now() - make_symbolic(pd.to_datetime)(X.hiredate)).dt.days < 40*365 )

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
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
6	7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10

Python Programming

emp >> filter_by( (pd.Timestamp.now() - make_symbolic(pd.to_datetime)(X.hiredate)).dt.days >= 40*365 )

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
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
6	7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트

저작자표시 비영리 변경금지

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

[중복 제거(Distinct)] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리(In) - 137 (오라클 SQL, R, Python, SAS) (0)	2022.12.20
[문자길이(length)] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리(In) - 136 (오라클 SQL, R, Python, SAS) (0)	2022.12.20
[데이터 추출] 특정 조건을 해당하는 데이터 삭제 - 비상관 서브쿼리(In), Having count() - 134 (오라클 SQL, R, Python, SAS) (0)	2022.12.19
[일자 추출] 특정 날짜에 해당하는 데이터 추출 - 133 (오라클 SQL, R, Python, SAS) (0)	2022.12.18
[비등가 조인] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리(부등호) - 132 (오라클 SQL, R, Python, SAS) (0)	2022.12.18

[날짜 경과일] 특정 조건을 해당하는 데이터 삭제 - 기준일자 사이의 기간(Gap) 계산 - 135 (오라클 SQL, R, Python, SAS)

135. Delete those employees who joined the company 38 years back from today?

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

6. Python pandasql Package

7. R data.table Package

8. SAS Proc SQL

9. SAS Data Step

10. Python Dfply Package

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

댓글

티스토리툴바

[날짜 경과일] 특정 조건을 해당하는 데이터 삭제 - 기준일자 사이의 기간(Gap) 계산 - 135 (오라클 SQL, R, Python, SAS)

135. Delete those employees who joined the company 38 years back from today?

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

6. Python pandasql Package

7. R data.table Package

8. SAS Proc SQL

9. SAS Data Step

10. Python Dfply Package

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

관련글

댓글

티스토리툴바