[날짜함수] employees 테이블에서 짝수달에 입사한 사원의 employee_id, last_name, hire_date, 입사월 정보를 출력하세요. 추가적으로, 입사월을 기준으로 정렬하세요.

1. oracle

explain plan for 
select employee_id, last_name,hire_date, to_number(to_char(hire_date,'mm')) as "hire_month"
from employees
where mod(to_number(to_char(hire_date,'mm')),2)=0
order by 4;
select * from table(dbms_xplan.display(null,null,'typical'));

-------------------------------------------------------------------------------- | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |           |     1 |    20 |     4  (25)| 00:00:01 | |   1 |  SORT ORDER BY     |           |     1 |    20 |     4  (25)| 00:00:01 | |*  2 |   TABLE ACCESS FULL| EMPLOYEES |     1 |    20 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter(MOD(TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("HIRE_DATE"),'mm')               ),2)=0)

 

 

2. r

1) 문자->날짜->문자(월)->숫자

emp <- read.csv("c:/data/employees.csv",header=T,stringsAsFactors = F)
emp_new <-subset(emp,,select=c(EMPLOYEE_ID, LAST_NAME, HIRE_DATE))
emp_new$month <-as.numeric(format(as.Date(emp_new$HIRE_DATE,'%Y-%m-%d'),'%m'))
res=emp_new[emp_new$month%%2==0,]
library(doBy)
orderBy(~month,res) #혹은 res[order(res$month),]

2)

library(dplyr)
emp%>%
  select(EMPLOYEE_ID,LAST_NAME,HIRE_DATE)%>%
  mutate(MONTH = as.numeric(format(as.Date(HIRE_DATE,format=('%Y-%m-%d')),'%m')))%>%
  filter(MONTH%%2==0)%>%
  arrange(desc(MONTH))

 

 

 

3. python

1)

import csv

생략

 

2) pandas.to_datetime 이용

import pandas as pd
from datetime import datetime

emp=pd.read_csv("c:/data/employees.csv")
emp['MONTH']=pd.to_datetime(emp['HIRE_DATE']).apply(lambda x:x.month)
res = emp.loc[emp['MONTH']%2==0,['EMPLOYEE_ID','LAST_NAME','HIRE_DATE','MONTH']]
res.sort_values(by='MONTH',ascending=True)

 

3) datetime.datetime.strptime() 이용

생략

 

4) 인덱싱 이용

import dandas as pd
df=pd.read_csv("c:/data/employees.csv")

df['MONTH']=[int(i[5:7]) for i in df['HIRE_DATE']]
df['bool']=[int(i[5:7])%2==0 for i in df['HIRE_DATE']]

res=df.loc[df['bool'],['EMPLOYEE_ID','LAST_NAME','HIRE_DATE','MONTH']]
res.sort_values(by='MONTH',ascending=False)