[R] sqldf

sqldf

: SQL을 이용해서 데이터를 처리

install.packages("sqldf") 
library(sqldf)

→ 테이블 이름은 데이터프레임 이름과 동일하게 대소문자가 구분된다.

 

→ sql문("")이랑 initcap('') 겹치지 않게 써야한다.

사용 가능한 sql 함수

주의! sql 함수가 거의 사용가능하지만 안되는 함수도 있음(trunc,round 등)

 

join

left outer join

right outer join

존재하지 않음 (위치 바꿔서 left outer join 사용할 것)

full outer join

존재하지 않음(union 사용)

 

집합

부서가 없는 사원 정보 출력

union 합집합

intersect 교집합

except 차집합

 

case문 사용 가능

sqldf("select last_name, salary,
	case
		when salary < 5000 then 'low'
		when salary < 10000 then 'medium'
		when salary < 20000 then 'good'
		else 'excellent'
	end qualified_salary
from employees")

rank() over() 사용 가능

sqldf("select last_name, salary, rank() over(order by salary desc)
from employees")

sqldf("select last_name, salary, dense_rank() over(order by salary desc)
from employees")

---

[문제146]fruits_sales.csv file 읽어 들인 후 과일 이름별 판매량, 판매합계를 구하세요.(sqldf를 이용하세요)

sqldf("select name, sum(qty), sum(price)
	from sales
	group by name")

[문제146]fruits_sales.csv file 읽어 들인 후 과일 이름별 판매량, 판매합계를 구하세요.(sqldf를 이용하세요)

sqldf("select year
       from sales
       group by year
       having sum(qty) = (select max(sumqty)
                          from(select sum(qty) sumqty
                               from sales
                               group by year))")

x<-plyr::ddply(sales,'year',summarise,qty=sum(qty))
x[x$qty==max(x$qty),'year']

[문제148]부서별 급여를 기준으로 내림차순 순위를 구하세요.(연이은 순위).(sqldf를 이용하세요)

sqldf("select last_name,salary,department_id,
              dense_rank() over(partition by department_id order by salary desc)
       from employees")

[문제149]부서별 최소 급여자들의 정보를 출력해주세요. (sqldf를 이용하세요)

sqldf("select * from(select employee_id,last_name,salary,department_id,
                            min(salary) over(partition by department_id) min_sal,
                case 
                  when salary = min(salary) over(partition by department_id) then 1 
                  end case_min
       from employees)
       where case_min=1")

sqldf("select * from (select last_name, salary, department_id,
                             dense_rank() over(partition by department_id order by salary) 순위
                      from employees)
        where 순위=1")

[문제150]JOB_ID별 급여를 많이 받는 사원 1등만 추출해 주세요.(sqldf를 이용하세요)

sqldf("select * 
       from (select last_name, salary, job_id,
                dense_rank() over(partition by job_id order by salary desc) 순위
             from employees)
        where 순위=1")

[문제151]동일한 날짜에 입사한 사원들의 정보를 출력해주세요.(sqldf를 이용하세요)

sqldf("select * from employees e
       where exists (select 'x'
                     from employees
                     where hire_date = e.hire_date
                     and employee_id != e.employee_id)
       order by 6")

sqldf사용 안하고 구하기

df <- data.frame()
for(i in 1:nrow(employees)){
  new <- employees[employees$EMPLOYEE_ID != 
                   as.integer(employees[,c('EMPLOYEE_ID','HIRE_DATE')][i,][1]) & 
         #사원번호가 다른 사람(자기자신 빼기)
    employees$HIRE_DATE == as.character(employees[,c('EMPLOYEE_ID','HIRE_DATE')][i,][2]),] 
    #입사일이 같은 사람
df <- rbind(df,new)
unique(df)

중복성 없이 df 데이터 프레임에 데이터 입력하기

df <- data.frame()
for(i in 1:nrow(employees)){
 new <- employees[!employees$EMPLOYEE_ID %in% employees[,c('EMPLOYEE_ID','HIRE_DATE')][i,][1] &
 employees$HIRE_DATE %in% employees[,c('EMPLOYEE_ID','HIRE_DATE')][i,][2],]
 for(j in new$EMPLOYEE_ID){
   if(nrow(df[df$EMPLOYEE_ID==j,])==0){
     df <- rbind(df,new[new$EMPLOYEE_ID==j,])
}
}
}