[그룹함수] employees 테이블에서 입사일이 12년이 넘은 사원에 대하여, 입사요일별로 그룹짓고, 요일순으로 정렬하세요.

1. oracle

1)

select day,sumsal
from(
select to_char(hire_date,'d') d, to_char(hire_date,'day') day, sum(salary) sumsal
from employees
where sysdate - hire_date >= 12*365
group by to_char(hire_date,'day'), to_char(hire_date,'d')
order by to_char(hire_date,'d')
);

 

2)

pivot 후 unpivot 하는 방법도 있음

 

 

 

 

2. r

1)

emp<-read.csv("c:/data/employees.csv",header=T,stringsAsFactors=F)
emp$HIRE_DATE<-as.Date(emp$HIRE_DATE,format="%Y-%m-%d")
emp$WEEKDAYS<-factor(format(as.Date(emp$HIRE_DATE,format="%Y-%m-%d"),"%a"),
                    levels=c('월','화','수','목','금','토','일'),ordered=T)
emp<-emp[Sys.Date()-emp$HIRE_DATE>=12*365,]
emp<-emp[order(emp$WEEKDAYS),]
library(plyr)
result<-ddply(emp,'WEEKDAYS',summarise,sumsal=sum(SALARY)) 

2)
result<-aggregate(SALARY~WEEKDAYS,emp,sum)

 

 

3)

library(dplyr)
emp%>%
  filter((Sys.Date()-as.Date(HIRE_DATE,format="%Y-%m-%d"))>=12)%>%
  mutate(WEEKDAYS = factor(format(HIRE_DATE,'%a'),
                           levels=c('월','화','수','목','금','토','일'),ordered=T))%>%
  group_by(WEEKDAYS)%>%
  summarise(sumsal = sum(SALARY))%>%
  arrange(WEEKDAYS)

 

 

3. python

1)

import pandas as pd
from datetime import datetime, date, time
emp=pd.read_csv("c:/data/employees.csv")
emp['HIRE_DATE']=pd.to_datetime(emp['HIRE_DATE'])
emp['WEEKDAY']=emp['HIRE_DATE'].dt.dayofweek #혹은 weekdays
emp=emp.loc[[(datetime.today() - i).days > 12*365 for i in emp['HIRE_DATE']],]
emp.info()
result = emp['SALARY'].groupby(emp['WEEKDAY']).sum()
result.index = ['월','화','수','목','금','토','일']
result

 

2)

import pandas as pd
from datetime import datetime, date, time

emp = pd.read_csv("c:/data/employees.csv")
emp['HIRE_DATE']=[datetime.strptime(i,'%Y-%m-%d') for i in emp['HIRE_DATE']]
emp = emp.loc[[(datetime.today()-i).days>=12*365 for i in emp['HIRE_DATE']],]
emp['WEEKDAY']=[i.weekday() for i in emp['HIRE_DATE']]
emp=emp['SALARY'].groupby(emp['WEEKDAY']).sum()
emp.index = ['일','월','화','수','목','금','토']
emp['이사']=emp['일']
emp=emp.drop(['일'],axis=0)
emp.index=['월','화','수','목','금','토','일'] 

 

#datetime.today().weekday() 일(0),월(1)...