[크로스테이블] 년도/분기 별 크로스 테이블을 만드세요.

1. oracle

1)

select to_char(hire_date,'yyyy'),
sum(case when to_char(hire_date,'q')=1 then salary end) "1분기",
sum(case when to_char(hire_date,'q')=2 then salary end) "2분기",
sum(case when to_char(hire_date,'q')=3 then salary end) "3분기",
sum(case when to_char(hire_date,'q')=4 then salary end) "4분기"
from employees
group by to_char(hire_date,'yyyy')
order by 1;

 

2) pivot

select *
from (select to_char(hire_date,'yyyy') year,to_char(hire_date,'q') quarter,salary
      from employees)
pivot(sum(salary) for year in (2001,2002,2003,2004,2005,2006,2007,2008));

select *
from (select to_char(hire_date,'q') quarter, to_char(hire_date,'yyyy') year, salary
      from employees)
pivot(sum(salary) for quarter in (1,2,3,4));

 

 

 

 

 

2. r

1)

emp<-read.csv("c:/data/employees.csv",header=T,stringsAsFactor=F)
library(plyr)
library(lubridate)
emp$year <- format(as.Date(emp$HIRE_DATE,format='%Y-%m-%d'),'%Y')
emp$quarter<-quarter(as.Date(emp$HIRE_DATE,format='%Y-%m-%d'))
help(as.Date)
tapply(emp$SALARY,list(emp$year,emp$quarter),sum)

 

2)

table(emp$DEPARTMENT_ID)
x=table(emp$JOB_ID,emp$DEPARTMENT_ID)
xtabs(~DEPARTMENT_ID,emp)
xtabs(~JOB_ID+DEPARTMENT_ID,emp)      
margin.table(x)
margin.table(x,1)
margin.table(x,2)
prop.table(x)
prop.table(x,1)
prop.table(x,2)

 

 

3. python

#참고 : emp.groupby([emp.DEPARTMENT_ID.fillna(0),emp['JOB_ID']])['SALARY'].sum()

#참고2 : emp.groupby([emp.DEPARTMENT_ID.fillna(0),emp['JOB_ID']])['SALARY'].sum().unstack()

 

from datetime import datetime, date, time
emp['year'] = pd.to_datetime(emp['HIRE_DATE']).dt.year
emp['quarter'] = pd.to_datetime(emp['HIRE_DATE']).dt.quarter
emp.groupby([emp['year'],emp['quarter']])['SALARY'].sum().unstack().fillna(0)