[날짜함수] employees 테이블에서 근무년수가 15년이상이면서, 월요일에 입사한 사원들의 employee_id, last_name, 근무년수/월수/일수, 입사요일을 출력하세요. 추가적으로, 입사요일순으로 출력하세요

1. oracle

1)

select employee_id, last_name, hire_date, 
round((sysdate-hire_date)/365,1) "year", 
round(months_between(sysdate,hire_date),1) "month",
round(sysdate-hire_date,1) "month",
to_char(hire_date,'day') "weekday"
from employees
where (sysdate - hire_date)/365 >= 15
order by to_char(hire_date-1,'d');

 

 

2. r

1)

library(doBy)
emp <- read.csv("c:/data/employees.csv",header=T,stringsAsFactors = F)
emp$HIRE_DATE<-as.Date(emp$HIRE_DATE,format("%Y-%m-%d"))
emp<-subset(emp,(Sys.Date()-HIRE_DATE)/365>=15,select=c(EMPLOYEE_ID,LAST_NAME,HIRE_DATE))
emp$YEAR <- round(as.numeric((Sys.Date()-emp$HIRE_DATE)/365),1)
emp$MONTH <- round(as.numeric((Sys.Date()-emp$HIRE_DATE)/30),1) #####수정이 필요
emp$DAY <- as.numeric((Sys.Date()-emp$HIRE_DATE))
emp$WEEKDAY<-factor(format(emp$HIRE_DATE,"%a"),levels=c('월','화','수','목','금','토','일'),ordered=TRUE)
orderBy(~WEEKDAY,emp)

 

2)

library(dplyr)
emp <- read.csv("c:/data/employees.csv",header=T,stringsAsFactors = F)
emp$HIRE_DATE <- as.Date(emp$HIRE_DATE,format='%Y-%m-%d')
emp%>%
  select(EMPLOYEE_ID,LAST_NAME,HIRE_DATE)%>%
  mutate(YEAR=format(HIRE_DATE,"%Y"))%>%
  mutate(MONTH=format(HIRE_DATE,"%m"))%>%
  mutate(DAY=format(HIRE_DATE,"%d"))%>%
  mutate(WEEKDAY=factor(format(HIRE_DATE,"%a"),levels=c('월','화','수','목','금','토','일'),ordered=TRUE))%>%
  arrange(WEEKDAY)

 

 

 

 

3. python

1)

import pandas as pd
import numpy as np
import datetime
from dateutil.relativedelta import relativedelta
emp=pd.read_csv("c:/data/employees.csv")
#df.head(10) #df.info()
emp['HIRE_DATE']=pd.to_datetime(emp['HIRE_DATE'])
emp['DAY'] = [(datetime.datetime.today()-i).days for i in emp['HIRE_DATE']]
emp['MONTH'] = [relativedelta(datetime.datetime.today(),i).years*12 + 
                   relativedelta(datetime.datetime.today(),i).months for i in emp['HIRE_DATE']]
emp['YEAR']=[relativedelta(datetime.datetime.today(),i).years for i in emp['HIRE_DATE']]
emp['SORT']=[i.weekday() for i in emp['HIRE_DATE']]
emp['WEEKDAY']=['월화수목금토일'[i.weekday()] for i in emp['HIRE_DATE']]
emp=emp.sort_values(by='SORT')
res=emp.loc[emp['YEAR']>=15,['EMPLOYEE_ID','LAST_NAME','HIRE_DATE','YEAR','MONTH','DAY','WEEKDAY']]
res

 

 

2)

argsort도 있다!

emp.ix[emp.EMPLOYEE_ID.argsort()[::-1],]