5시간코딩

[그룹함수] employees 테이블에서 department_id와 job_id를 기준으로 salary를 그룹지으세요. 본문

문제풀기/테이블 조작 관련 문제

[그룹함수] employees 테이블에서 department_id와 job_id를 기준으로 salary를 그룹지으세요.

5시간코딩 2019. 4. 11. 00:33

1. oracle

select department_id, job_id, sum(salary)
from employees
group by department_id,job_id
order by department_id,job_id;

 

2. r

1)
emp1<-aggregate(SALARY~DEPARTMENT_ID+JOB_ID,emp,sum)
library(doBy)
emp1<-orderBy(~DEPARTMENT_ID+JOB_ID,emp1)

2)
emp2<-aggregate(SALARY~DEPARTMENT_ID+JOB_ID,emp,sum)
emp2<-emp2[order(emp2$DEPARTMENT_ID,emp2$JOB_ID),]

3)
install.packages('plyr')
library(plyr)
emp3<-ddply(emp,c('DEPARTMENT_ID','JOB_ID'),summarise,sum_sal=sum(SALARY))

4)
library(dplyr)
emp4<-emp%>%
  select(DEPARTMENT_ID,JOB_ID,SALARY)%>%
  group_by(DEPARTMENT_ID,JOB_ID)%>%
  summarise(sum_sal=sum(SALARY),mean_sal=mean(SALARY))


5)
library(sqldf)
sqldf("
select department_id,job_id,salary
from emp
group by department_id,job_id
order by department_id,job_id
")

 

6)

ddply(emp,c('DEPARTMENT_ID','JOB_ID'),summarise,sumsal=sum(SALARY))

 

3. python

 

import pandas as pd
emp=pd.read_csv("c:/data/employees.csv")

emp['SALARY'].groupby([emp['DEPARTMENT_ID'],emp['JOB_ID']]).sum()
emp.groupby([emp['DEPARTMENT_ID'],emp['JOB_ID']])['SALARY'].sum()