R Notebook

데이터베이스 분석하기 20180908

필요한 패키지

if("rJava" %in% installed.packages("rJava") == FALSE)install.packages("rJava")
library(rJava)
if("DBI" %in% installed.packages("DBI") == FALSE)install.packages("DBI")
library(DBI)
if("RJDBC" %in% installed.packages("RJDBC") == FALSE)install.packages("RJDBC")
library(RJDBC)
if("dplyr" %in% installed.packages("dplyr") == FALSE)install.packages("dplyr")
library(dplyr)
if("data.table" %in% installed.packages("data.table") == FALSE)install.packages("data.table")
library(data.table)

Oracle 데이터를 Rstudio로 가져오는 방법

myJDBC <- JDBC("oracle.jdbc.driver.OracleDriver", 
               "C:\\oraclexe\\app\\oracle\\product\\11.2.0\\server\\jdbc\\lib\\ojdbc6.jar")
conn <- dbConnect(myJDBC,
                  "jdbc:oracle:thin:@localhost:1521:xe",
                  "hr",
                  "oracle"
                  )
tab <- dbGetQuery(conn,"SELECT * FROM TAB")
tab

dplyr

filter, select, arrange, mutate,

summarise, group_by, left_join, bind_rows

tab <- data.frame(tab)
View(tab)
tname <- tab$TNAME
tname <- as.vector(tname)
tname

[1] “COUNTRIES”

[2] “DEPARTMENTS”

[3] “EMPLOYEES” “EMP_DETAILS_VIEW”

[5] “JOBS” “JOB_HISTORY”

[7] “LOCATIONS” “REGIONS”

cnt <- data.frame(dbGetQuery(conn,"SELECT * FROM COUNTRIES"))
head(cnt)
dep <- data.frame(dbGetQuery(conn,"SELECT * FROM DEPARTMENTS"))
emp <- data.frame(dbGetQuery(conn,"SELECT * FROM EMPLOYEES"))
empd <- data.frame(dbGetQuery(conn,"SELECT * FROM EMP_DETAILS_VIEW"))
job <- data.frame(dbGetQuery(conn,"SELECT * FROM JOBS"))
jobh <- data.frame(dbGetQuery(conn,"SELECT * FROM JOB_HISTORY"))
loc <- data.frame(dbGetQuery(conn,"SELECT * FROM LOCATIONS"))
reg <- data.frame(dbGetQuery(conn,"SELECT * FROM REGIONS"))
head(dep)
head(emp)

cnt 의 컬럼명을 한글로 전환하시오.

COUNTRY_ID

COUNTRY_NAME

REGION_ID

cnt <- cnt %>% 
  dplyr::rename(국가아이디 = COUNTRY_ID,
                     국가명 = COUNTRY_NAME,
                     지역아이디 = REGION_ID
                     )
str(cnt)

dep 의 컬럼명 전환

DEPARTMENT_ID 부서아이디

DEPARTMENT_NAME 부서명

MANAGER_ID 매니저아이디

LOCATION_ID 위치아이디

dep <- dep %>% 
  dplyr::rename(부서아이디 = DEPARTMENT_ID,
                     부서명 = DEPARTMENT_NAME,
                     매니저아이디 = MANAGER_ID,
                     위치아이디 = LOCATION_ID)

emp 의 컬럼명을 한글로 전환하시오.

그리고 First Name 과 Last Name 을

붙여서 이름 으로 된 컬럼을 추가하시오

단, 이름 간격은 띄울것. ex) James Dean

직원아이디 = EMPLOYEE_ID

이메일 = EMAIL

전화번호 = PHONE_NUMBER

채용일 = HIRE_DATE

업무아이디 = JOB_ID

연봉 = SALARY

커미션비율 = COMMISSION_PCT

매니저아이디 = MANAGER_ID

부서아이디 = DEPARTMENT_ID

emp <- emp %>% 
  dplyr::rename(직원아이디 = EMPLOYEE_ID,
                     이메일 = EMAIL,
                     전화번호 = PHONE_NUMBER,
                     채용일 = HIRE_DATE,
                     업무아이디 = JOB_ID,
                     연봉 = SALARY,
                     커미션비율 = COMMISSION_PCT,
                     매니저아이디 = MANAGER_ID,
                     부서아이디 = DEPARTMENT_ID) %>%
  mutate(이름 = paste(FIRST_NAME, LAST_NAME))
View(head(emp))

# 필요없는 변수 삭제하기
emp <- subset(emp, select = -c(FIRST_NAME,LAST_NAME))
View(head(emp))

# 매달 지급하는 월급여(연봉 / 12)를 보여주는
# 월급이라는 컬럼(변수)을 추가시키시오. 0단위 이하는 절삭

emp <- emp %>% 
  dplyr::mutate(월급 = 연봉%/%12)
emp %>% View

연봉이 10000불이상인 사원(emp)의 목록을 이름, 직원아이디, 연봉만 구하되,

연봉 내림차순으로 보여주시오.

emp <- emp %>% emp 내용에 할당한다는 말이므로 emp 내용 자체가 변경됌 / 단순히 출력을 위해서는 emp %>% 라고만 입력

emp %>% 
  filter(연봉 >= 10000) %>% 
  select(이름, 직원아이디, 연봉) %>% 
  arrange(desc(연봉)) %>% 
  head

연봉이 3000 미만인 사원에게 보너스로 급여의 1%를 지급하겠다고 합니다. filter

대상자의 목록을 이름, 직원아이디, 연봉, 보너스를 기재하고 select

아이디 오름차순으로 보여주시오 arrange

dplyr:: 를 포함하는 이유는 어떤 패키지를 사용했는지 보여주기위해

단, 보너스는 이번 달만 주는 것이므로 emp DF(data.frame)에

저장하지는 말고 1회용 임시 DF를 따로 생성해서

저장하고, 기한이 지나서는 폐기하라

임시 저장용 data frame을 생성했으며 한글로 생성했으나 되도록 피해야함

보너스지급명세서 <- emp %>% 
  filter(연봉 < 3000) %>% 
  dplyr::mutate(보너스 = 월급*0.01) %>% 
  select(이름, 직원아이디, 연봉, 보너스) %>% 
  arrange(직원아이디)

View(보너스지급명세서)
# remove의 약자로 보너스지급명세서 삭제
rm(보너스지급명세서)
            

연봉이 10000이 넘는 직원의 부서명, 이름, 연봉을

출력하시오

emp %>% ## 기준이 되는 dataframe 지정
  left_join(dep, by="부서아이디") %>%  ##  연산자를 포함한 경우 raw데이터 (raw 데이터는 "", 302(수), )
  filter(연봉 >= 10000) %>% 
  select(부서명, 이름, 연봉)

부서별로 연봉 평균을 구하시오

dep %>% 
  left_join(emp, by="부서아이디") %>%
  group_by(부서명, 부서아이디) %>% 
  dplyr::summarise(연봉평균=mean(연봉,na.rm=T)) %>% 
  arrange(desc(연봉평균)) %>% 
  View

이 회사의 부서의 수를 구하시오

dep %>%
  dplyr::distinct(부서명) %>%
  count

연봉이 12000 이 넘는 직원의 부서명, 이름, 연봉, 직책

을 기재하시오.

job <- job %>% 
  rename(업무아이디 = JOB_ID,
              직책 = JOB_TITLE,
              최소연봉 = MIN_SALARY,
              최대연봉 = MAX_SALARY
              )
job %>% View

emp %>% 
  left_join(dep, by="부서아이디") %>% 
  left_join(job, by="업무아이디") %>% 
  filter(연봉 >= 12000) %>% 
  select(부서명, 이름, 연봉, 직책) %>% 
  View

부서별로 가장 높은 연봉을 받는 부서아이디,

부서명, 최대연봉을 구하시오. 최대연봉 max(연봉)

emp %>% 
  left_join(dep, by="부서아이디") %>%
  left_join(job, by="업무아이디") %>%
  group_by(부서명, 부서아이디) %>%
  summarise(max(연봉)) 

부서아이디를 발급받지 않으면 신입입니다.

신입의 이름과 연봉을 구하시오. is.na(부서아이디) 와의 차이는?

emp %>% 
  filter(is.na(emp %>% select(부서아이디))) %>% 
  select(이름, 연봉)