SQL과 R과 비교(함수)

■R에서의 함수

1. 문자함수

2. 숫자 함수

3. 날짜 함수

4. 변환함수

5. 일반 함수

■ 문자함수

	SQL	VS	R
	upper		toupper
	lower		tolower
	substr		substr
	replace		gsub

 

months_between=function (last,first){

  # 년

  x=12*(as.integer(format(last,'%Y'))-as.integer(format(as.Date(first),'%Y')))

 

  # 달

  y=as.integer(format(last,'%m'))-as.integer(format(as.Date(first),'%m'))

 

  # 일

  if (as.integer(format(last,'%d'))<as.integer(format(as.Date(first),'%d')) ) z=-1

  else z=0

  print(x+y+z)

}

 

 

 

 

문제23. 이름과 직업을 출력하는데 소문자로 출력하시오

library(data.table)

data.table(이름=tolower(emp$ename), 직업=tolower(emp$job))

      이름      직업
 1:  smith     clerk
 2:  allen  salesman
 3:   ward  salesman
 4:  jones   manager
 5: martin  salesman
 6:  blake   manager
 7:  clark   manager
 8:  scott   analyst
 9:   king president
10: turner  salesman
11:  adams     clerk
12:  james     clerk
13:   ford   analyst
14: miller     clerk
15:   jack     clerk

 

 

데이터 테이블 사용안하고 하는 방법

cbind  (* sql의 유니온과 같은 역할)

cbind(tolower(emp$ename), tolower(emp$job))

 

 

 

 

문제24. (R로 함수 생성) 아래와 같이 이름을 물어보게 하고

이름을 입력하면 해당 사원의 이름,월급이 출력되는

R코드를 작성하시오

 

find_sal <- function() {

              response <- readline(prompt = 'put in name')

              x <- emp[emp$ename==resposnse, c("ename","sal")]

              print(x)

                      }

 

설명:

find_sal 함수 이름

선언 function()

그리고 중괄호 열기 { 

response  <-  readline 이 파이썬의 input

x <- emp[ 조건 ==(위에적은)response, 나머지 똑같이 combine]

print(x) 까지. (<-이게 리턴)

끝으로 중괄호 닫기 }

 

 

 

 

 

문제25. 위의 문제를 다시 해결하는데 이름을 소문자로 입력해도

출력되게 하시오

 

find_sal <- function() {

              response <- readline(prompt = 'put in name')

              x <- emp[emp$ename==toupper(resposnse), c("ename","sal")]

              print(x)

}

find_sal()

 

 

 

문제26. (substr 함수) 이름의 두번째 철자가 M 인 사원들의 이름,월급을

출력하는데 substr 함수를 사용해서 출력하시오

 

emp[substr(emp$ename, 2,2) =='M', c('ename','sal') ]

                          행                             열

 

※ 설명: substr(x, start, stop)

   substr(emp$ename,2,2)     S  M  I   T  H

이름의 두번째 자리부터       1   2  3  4   5

두번째 자리까지

 

 

 

 

 

문제27. substr 함수를 이용해서 이름, 이름의 앞부터 철자3개까지인

두개의 이름 컬럼을 출력하시오

data.table(이름=emp$ename, 이름=substr(ename,1,3))

이름 이름
 1:  SMITH  SMI
 2:  ALLEN  ALL
 3:   WARD  WAR
 4:  JONES  JON
 5: MARTIN  MAR
 6:  BLAKE  BLA
 7:  CLARK  CLA
 8:  SCOTT  SCO
 9:   KING  KIN
10: TURNER  TUR
11:  ADAMS  ADA
12:  JAMES  JAM
13:   FORD  FOR
14: MILLER  MIL
15:   JACK  JAC

 

 

 

 

■ gsub

설명: gsub('h', 'H', text)

특정 text에서 소문자 h를 대문자 H로 변경해라

최종적으로 뽑고자 하는 값은 대문자H

(오라클의 replace)

 

 

문제28. 이름,월급을 출력하는데 월급을 출력할 때에 숫자 0을

*로 출력하시오

 

emp[gsub('0','*',sal ),c('ename','sal')]

 

data.table(emp$ename, gsub(0,'*',sal))

*0을 굳이 문자처리 안해줘도 됨

 

 

 

문제29. 이름,월급을 출력하는데 월급을 출력할 때에

숫자 0,1,2 를 *로 출력하시오

data.table(ename, sal, gsub('[0-2]','*',sal))

*범위지정은 문자화해야함(프로그램 형식)

 

 

 

※이 사이트는

cifar10 으로 훈련시킨것으로, 사진넣으면 맞춘다 !

https://aidencahn.shinyapps.io/cifar10_densenet/

 

 

 

 

 

■ 숫자 함수

	SQL	vs	R
1.	round		round
2.	trunc		trunc
3.	mod		%%
4.	power		^(예: 2^3)

 

 

 

문제30. 6의 9승을 출력하시오

6^9
[1] 10077696

 

문제31. 10을 3으로 나눈 나머지값이 무엇인가?

 10%%3
[1] 1

 

 

 

문제32. 이름,연봉을 출력하는데 연봉은 월급에 12를 곱해서

출력하고 컬럼명을 한글로 연봉으로 출력되게 하시오

data.table(ENAME=ename, SALARY=sal*12)

 

 

 

문제33. 이름,연봉을 출력하는데 연봉이 높은것부터 출력하시오

 

orderBy(~-SAL, data.table(ENAME=ename, SAL=sal*12) )

 

 

 

 

문제34. 위의 결과를 다시 출력하는데 round 함수를 이용해서

아래와 같이 백단위에서 반올림되게 하시오

 

4: JONES 35700 ------> 360000

 

orderBy(~-SAL, data.table(ENAME=ename, SAL=round(sal*12 , -3) ) )

※ 설명:

-3 은

예를들어 3  5  7  0  0

    -5 -4 -3 -2 -1

해당 자릿수에서 반올림시킨다

 

orderBy(~-SAL, data.table(ENAME=ename, S=sal*12,SAL=round(sal*12 , -3) ) )
     ENAME     S   SAL
 1:   KING 60000 60000
 2:   JACK 38400 38000
 3:  JONES 35700 36000
 4:  SCOTT 36000 36000
 5:   FORD 36000 36000
 6:  BLAKE 34200 34000
 7:  CLARK 29400 29000
 8:  ALLEN 19200 19000
 9: TURNER 18000 18000
10: MILLER 15600 16000
11:   WARD 15000 15000
12: MARTIN 15000 15000
13:  ADAMS 13200 13000
14:  JAMES 11400 11000
15:  SMITH  9600 10000

 

 

※ R의 round 의 특징

round(122.5)
[1] 122
> round(123.5)
[1] 124

 

R은 짝수를 좋아한다.

R뿐만 아니라 파이썬도 그렇다.

 

 

 

문제35. 문제34번 결과를 다시 출력하는데 백자리를 포함해서

그 이후를 다 버려서 출력하시오

 

orderBy(~-SAL, data.table(ENAME=ename, S=sal*12,SAL=trunc(sal*12 , -3) ) )

 

에러는 안뜨는데 안된다.

※ 설명:

왜냐하면 trunc 는 소수점 이후만 가능하다.

 

3   5   6   .   5   6   7

-3 -2  -1  0  1   2   3

 

 

 

 

■ 날짜 함수

 

	오라클	vs	R
1.	sysdate		Sys.Date()
2.	months_between		사용자 정의 함수
3.	last_day		사용자 정의 함수
4.	next_day		사용자 정의 함수

 

 

 

문제36. 오늘날짜를 출력하시오

Sys.Date()
[1] "2019-02-14"

 

 

 

문제37. 이름,입사한 날짜부터 오늘까지 총 몇일 근무했는지 출력하시오

 

emp의 hiredate 는 날짜가 아니라 factor 다.

 

※날짜로 변환하는 함수?

오라클	VS	R
to_date()		as.Date()

data.table(ENAME=ename, DATES=Sys.Date()-as.Date(hiredate))
     ENAME      DATES
 1:  SMITH 13938 days
 2:  ALLEN 13873 days
 3:   WARD 13871 days
 4:  JONES 13832 days
 5: MARTIN 13653 days
 6:  BLAKE 13803 days
 7:  CLARK 13764 days
 8:  SCOTT 11624 days
 9:   KING 13603 days
10: TURNER 13673 days
11:  ADAMS 11590 days
12:  JAMES 13587 days
13:   FORD 13587 days
14: MILLER 13536 days
15:   JACK 13536 days

 

 

 

 

문제38. 오늘날짜의 달의 마지막 날짜를 출력하시오

SQL> select last_day(sysdate) from dual;

 

install.packages("lubridate")

library(lubridate)

 

 last_day <- function(x){
+   ceiling_date(x, "month")- days(1)
+ }
> last_day(Sys.Date())
[1] "2019-02-28"

 

 

> ceiling_date(Sys.Date(), "month")
[1] "2019-03-01"

 

*ceiling_date는 해당조건(ex: month)을 반올림 해주고 이하 조건(day)은

1로 맞춘다.

그래서 2018.02.14 에 ceiling_date를 해주면 2018.03.01 이 된다.

 

 

 

 

 

문제39(오늘의 마지막 문제)

last_day 함수처럼 first_day 함수도 생성하시오

first_day(Sys.Date() )

 

 

first_day<-function(x){
+   floor_date(x, "month")+ days(0)
+ }
> first_day(Sys.Date())
[1] "2019-02-01"

 

 

*floor_date는 바닥, 즉 해당달의 가장낮은, 1일을 뽑아오는 명령어

floor_date(Sys.Date(), "month")
[1] "2019-02-01"

 

 

 

 

 

 

 

문제40. 오라클에 접속해서 오늘부터 돌아오는 월요일의 날짜를

SQL로 출력하시오

 

***오라클 11로 접속

ORACLE_SID=xe

sqlplus scott/tiger

 

 

select next_day(sysdate,'월요일') from dual;

 

 

 

답:

next_day <- function(x,day){

            for(y in 1:7) {

                    check_date = as.Date(x+days(y) )  #우리가 입력한 날짜에 y숫자를 더함!

                      if(format(check_date, '%A')==day) {

                        print(check_date)             }

            }

}

 

 

library(lubridate)

 next_day(Sys.date(), '월요일')
Error in Sys.date() : could not find function "Sys.date"
> next_day(Sys.Date(), '월요일')
[1] "2019-02-18"

 

 

 

# as_Date  가 숫자를 날짜로 변환해주는 함수

# %A 는 check_date의 요일을 뽑는다. if 절에 A가 우리가 입력한 '월요일'과

일치할 때 프린트 시키는 것이다.

 

 

 

 

■ 변환함수

오라클	vs	R
to_char		as.character
to_number		as.integer
to_date		as.Date
		as.Factor --> factor 형으로 변환하는 함수
		format 함수 %Y  ---> 년도 %m ---> 달 %d ---> 일 %A ---> 요일

 

 

 

문제41. 이름, 입사 요일을 출력하시오

#이거 매번 해줘야 한다 !!

setwd("d:\\data")

emp<- read.csv("emp.csv", header=T)

emp

 

library(data.table)

data.table(emp$ename, format(as.Date(emp$hiredate), '%A') )                     

 

V1     V2
 1:  SMITH 수요일
 2:  ALLEN 금요일
 3:   WARD 일요일
 4:  JONES 목요일
 5: MARTIN 월요일
 6:  BLAKE 금요일
 7:  CLARK 화요일
 8:  SCOTT 일요일
 9:   KING 화요일
10: TURNER 화요일
11:  ADAMS 토요일
12:  JAMES 목요일
13:   FORD 목요일
14: MILLER 토요일
15:   JACK 토요일

 

 

 

 

 

문제42. 내가 무슨 요일에 태어났는지 출력하시오

 

date <- '1988-04-01'           

data.table(format(as.Date(date), '%A'))

 

#바로숫자 쓰는것도 가능

data.table(format(as.Date('1988-04-01'),'%A'))

 

 

 

 

 

 

문제43. add_months 함수를 R로 생성하시오

add_months<-function(sysdate,x){
+             check_date=sysdate+months(x)
+             print(check_date)}
> add_months(Sys.Date(), 100)
[1] "2027-06-15"

 

 

 

 

■ 매번 패키지를 라이브러리 하기 번거롭고

매번 아래의 명령어로 emp 데이터 프레임을 만들기 번거롭다면

아래 해당경로에 있는 파일이 저장! ,R실행할때 마다 자동 실행된다.

 

C:\Program Files\R\R-3.5.2\etc 들어가서

Rprofile.site 워드패드로 열어서

필요 라이브러리 저장

(*setwd read.csv 는 안됨)

 

*나중에 다 에러떠서 그냥 지움

 

 

 

■ 일반 함수

오라클	vs	R
nvl함수		is.na
decode함수		ifelse
case 함수		ifelse

 

 

 

 

문제44. 이름,월급,등급을 출력하는데 월급이 1500 이상이면

등급을 A로 출력하고 아니면 B로 출력하시오

 

SQL> select ename, sal,

case when sal >= 1500 then 'A'

else 'B' end as '등급'

from emp;

 

R답:

data.table(emp$ename, emp$sal, ifelse(emp$sal >= 1500, 'A', 'B'))
        V1   V2 V3
 1:  SMITH  800  B
 2:  ALLEN 1600  A
 3:   WARD 1250  B
 4:  JONES 2975  A
 5: MARTIN 1250  B
 6:  BLAKE 2850  A
 7:  CLARK 2450  A
 8:  SCOTT 3000  A
 9:   KING 5000  A
10: TURNER 1500  A
11:  ADAMS 1100  B
12:  JAMES  950  B
13:   FORD 3000  A
14: MILLER 1300  B
15:   JACK 3200  A

 

 

 

 

문제45. 이름,월급,등급을 출력하는데 월급이 3000 이상이면

A를 출력하고 월급이 1500 이상이고 3000 보다 작으면 B를

출력하고 나머지 사원들은 C를 출력하시오 !

 

 

data.table(ename, sal,
+            ifelse(sal >= 3000, 'A',
+                   ifelse(sal >= 1500, 'B', 'C' )))
     ename  sal V3
 1:  SMITH  800  C
 2:  ALLEN 1600  B
 3:   WARD 1250  C
 4:  JONES 2975  B
 5: MARTIN 1250  C
 6:  BLAKE 2850  B
 7:  CLARK 2450  B
 8:  SCOTT 3000  A
 9:   KING 5000  A
10: TURNER 1500  B
11:  ADAMS 1100  C
12:  JAMES  950  C
13:   FORD 3000  A
14: MILLER 1300  C
15:   JACK 3200  A

 

 

 

 

문제46. is.na 함수를 이용해서 커미션이 NA 인 사원들의 이름, 커미션을

출력하시오 !

 

is.na 는 NA값을 찾기만 하는 함수 !

(*오라클처럼 nvl 해서 변환까지는 안됨)

 

문제9 답이랑 일치

 

 

 

문제47. 이름,커미션을 출력하는데 커미션이 NA인 사원들은 no comm 으로

출력되게 하시오 !

 

SQL> select ename, nvl(to_char(comm), 'no comm' )

from emp;

 

> data.table(ENAME='ename', COMM=comm, ifelse(is.na(comm), 'no comm', comm) )
    ENAME COMM      V3
 1: ename   NA no comm
 2: ename  300     300
 3: ename  500     500
 4: ename   NA no comm
 5: ename 1400    1400
 6: ename   NA no comm
 7: ename   NA no comm
 8: ename   NA no comm
 9: ename   NA no comm
10: ename    0       0
11: ename   NA no comm
12: ename   NA no comm
13: ename   NA no comm
14: ename   NA no comm
15: ename   NA no comm