[날짜함수] employees 테이블에서 2005년도에 입사한 사원의 employee_id, last_name, hire_date를 출력하세요. 추가적으로, 날짜순으로 정렬하세요.

1. oracle

1) 가장 좋은 방법
select employee_id, last_name, hire_date
from employees
where hire_date >= to_date('2005-01-01','yyyy-mm-dd')  #데이터가 날짜형으로 되어 있으므로 !
and   hire_date <  to_date('2006-01-01','yyyy-mm-dd')
order by hire_date;

-------------------------------------------------------------------------------- | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |           |    29 |   580 |     4  (25)| 00:00:01 | |   1 |  SORT ORDER BY     |           |    29 |   580 |     4  (25)| 00:00:01 | |*  2 |   TABLE ACCESS FULL| EMPLOYEES |    29 |   580 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter("HIRE_DATE"<TO_DATE(' 2006-01-01 00:00:00', 'syyyy-mm-dd                hh24:mi:ss') AND "HIRE_DATE">=TO_DATE(' 2005-01-01 00:00:00',                'syyyy-mm-dd hh24:mi:ss'))

2) 좋지 못한 방법
select employee_id, last_name, hire_date
from employees
where extract(year from hire_date) =2005
order by hire_date;

3) 좋지 못한 방법
select employee_id, last_name, hire_date
from employees
where to_char(hire_date,'yyyy') ='2005'
order by hire_date;

 

 

 

 

2. r

emp <- read.csv("c:/data/employees.csv",header=T,stringsAsFactors = F)
1)날짜로 풀기
ed <- as.Date(emp$HIRE_DATE,format='%Y-%m-%d')
res<-emp[ed>=as.Date('2005-01-01','%Y-%m-%d')&ed<as.Date('2006-01-01','%Y-%m-%d'),c('EMPLOYEE_ID','LAST_NAME','HIRE_DATE')]
res<-res[order(res$HIRE_DATE, decreasing = T),]

2)문자로 풀기
2-1)
res<-emp[substr(emp$HIRE_DATE,1,4)=='2005',c('EMPLOYEE_ID','LAST_NAME','HIRE_DATE')]
res[order(res$HIRE_DATE,decreasing = T),]
2-2)
res<-emp[format(as.Date(emp$HIRE_DATE,format='%Y-%m-%d'),format='%Y')=='2005', c('EMPLOYEE_ID','LAST_NAME','HIRE_DATE')]
res[order(res$HIRE_DATE,decreasing = F),]
2-3)
res<-subset(emp,substr(emp$HIRE_DATE,1,4)=='2005',select=c(EMPLOYEE_ID,LAST_NAME,HIRE_DATE))
res[order(res$HIRE_DATE),]
2-4)
library(dplyr)
emp%>%
  filter(substr(emp$HIRE_DATE,1,4)=='2005')%>%
  select(EMPLOYEE_ID, LAST_NAME, HIRE_DATE)%>%
  arrange(HIRE_DATE)

 

 

 

3. python

import pandas as pd

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

1) datetime 함수 이용

from datetime import datetime

1-1) list내장객체 사용
temp = [datetime.strptime(i,'%Y-%m-%d').year==2005 for i in df['HIRE_DATE']]
type(temp) #list
res=df.loc[temp,['EMPLOYEE_ID','LAST_NAME','HIRE_DATE']]
res.sort_values(by='HIRE_DATE',ascending=False)

1-2) 사용자 함수 사용(+apply)
def todate(arg1):
    return datetime.strptime(arg1,'%Y-%m-%d').year==2005
temp = df['HIRE_DATE'].apply(todate)
type(temp) #series
res=df.loc[temp,['EMPLOYEE_ID','LAST_NAME','HIRE_DATE']]
res.sort_values(by='HIRE_DATE',ascending=True)

 

1-3) lambda 사용(+apply)

import pandas as pd
df=pd.read_csv("c:/data/employees.csv")
from datetime import datetime
hdate=pd.to_datetime(df['HIRE_DATE'])
hdate_year=hdate.apply(lambda x:x.year==2005)
res = df.loc[hdate_year,['EMPLOYEE_ID','LAST_NAME','HIRE_DATE']]
res.sort_values(by='HIRE_DATE',ascending=True)

 

 

 

2) pandas 함수 이용

2-1)리스트 내장객체

df['HIRE_DATE']
hdate=pd.to_datetime(df['HIRE_DATE'])
res=df.loc[[i.year==2005 for i in hdate],['EMPLOYEE_ID','LAST_NAME','HIRE_DATE']]
res.sort_values(by='HIRE_DATE',ascending=True)

2-2) 사용자 함수 사용(+apply)

생략

2-3) lambda 사용(+apply)

생략