SQL과 R과 비교(조인)

■ R로 조인하는 방법

오라클	VS	R
equi join		merge
non equi join
outer join
self join

 

 

문제74. dept.csv 를 내려받아 dept 라는 변수에 로드하고

이름,월급,부서위치를 출력하시오

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

dept

 

merge(emp,dept, by='deptno')
   deptno empno  ename       job  mgr   hiredate  sal comm      dname      loc
1      10  7839   KING PRESIDENT   NA 1981-11-17 5000   NA ACCOUNTING NEW YORK
2      10  7934 MILLER     CLERK 7782 1982-01-23 1300   NA ACCOUNTING NEW YORK
3      10  7782  CLARK   MANAGER 7839 1981-06-09 2450   NA ACCOUNTING NEW YORK
4      20  7369  SMITH     CLERK 7902 1980-12-17  800   NA   RESEARCH   DALLAS
5      20  7566  JONES   MANAGER 7839 1981-04-02 2975   NA   RESEARCH   DALLAS
6      20  7902   FORD   ANALYST 7566 1981-12-03 3000   NA   RESEARCH   DALLAS
7      20  7876  ADAMS     CLERK 7788 1987-05-23 1100   NA   RESEARCH   DALLAS
8      20  7788  SCOTT   ANALYST 7566 1987-04-19 3000   NA   RESEARCH   DALLAS
9      30  7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300      SALES  CHICAGO
10     30  7698  BLAKE   MANAGER 7839 1981-05-01 2850   NA      SALES  CHICAGO
11     30  7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400      SALES  CHICAGO
12     30  7844 TURNER  SALESMAN 7698 1981-09-08 1500    0      SALES  CHICAGO
13     30  7521   WARD  SALESMAN 7698 1981-02-22 1250  500      SALES  CHICAGO
14     30  7900  JAMES     CLERK 7698 1981-12-03  950   NA      SALES  CHICAGO

 

부분만 보고 싶으면

x<-merge(emp,dept, by='deptno')

 

x[ ,c("ename","sal","loc")]

 

 

 

 

문제75.부서위치가 DALLAS 인 사원들의 이름과 월급과 부서위치를 출력하시오

 

x<-merge(emp,dept, by='deptno')

x[ x$loc=="DALLAS" ,c("ename","sal","loc") ]

 ename  sal    loc
4 SMITH  800 DALLAS
5 JONES 2975 DALLAS
6  FORD 3000 DALLAS
7 ADAMS 1100 DALLAS
8 SCOTT 3000 DALLAS

 

 

 

문제76. 커미션이 na인 사원들의 이름, 부서위치, 커미션을 출력하시오

x[is.na(x$comm) ,c("ename","loc", "comm")]
    ename      loc comm
1    KING NEW YORK   NA
2  MILLER NEW YORK   NA
3   CLARK NEW YORK   NA
4   SMITH   DALLAS   NA
5   JONES   DALLAS   NA
6    FORD   DALLAS   NA
7   ADAMS   DALLAS   NA
8   SCOTT   DALLAS   NA
10  BLAKE  CHICAGO   NA
14  JAMES  CHICAGO   NA

 

 

 

 

문제77. 이름,부서위치를 출력하는데 오라클의 outer join과 같은 결과를

출력하시오

merge(emp,dept, by="deptno", all.y=T)
   deptno empno  ename       job  mgr   hiredate  sal comm      dname      loc
1      10  7839   KING PRESIDENT   NA 1981-11-17 5000   NA ACCOUNTING NEW YORK
2      10  7934 MILLER     CLERK 7782 1982-01-23 1300   NA ACCOUNTING NEW YORK
3      10  7782  CLARK   MANAGER 7839 1981-06-09 2450   NA ACCOUNTING NEW YORK
4      20  7369  SMITH     CLERK 7902 1980-12-17  800   NA   RESEARCH   DALLAS
5      20  7566  JONES   MANAGER 7839 1981-04-02 2975   NA   RESEARCH   DALLAS
6      20  7902   FORD   ANALYST 7566 1981-12-03 3000   NA   RESEARCH   DALLAS
7      20  7876  ADAMS     CLERK 7788 1987-05-23 1100   NA   RESEARCH   DALLAS
8      20  7788  SCOTT   ANALYST 7566 1987-04-19 3000   NA   RESEARCH   DALLAS
9      30  7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300      SALES  CHICAGO
10     30  7698  BLAKE   MANAGER 7839 1981-05-01 2850   NA      SALES  CHICAGO
11     30  7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400      SALES  CHICAGO
12     30  7844 TURNER  SALESMAN 7698 1981-09-08 1500    0      SALES  CHICAGO
13     30  7521   WARD  SALESMAN 7698 1981-02-22 1250  500      SALES  CHICAGO
14     30  7900  JAMES     CLERK 7698 1981-12-03  950   NA      SALES  CHICAGO
15     40    NA   <NA>      <NA>   NA       <NA>   NA   NA OPERATIONS   BOSTON

 

그냥 merge 한 것과 다르게

15번째 줄이 출력되었다.

 

merge(emp,dept, by="deptno", all.y=T)

          ↑     ↑                         

         x     y                         

*y에만 있는 값을 가져와라(=sql에서 emp(+) 한것과 같음. )

 

 

 

문제78. 풀아우터 조인을 구현하시오.

merge(emp,dept, by="deptno", all=T)
   deptno empno  ename       job  mgr   hiredate  sal comm      dname      loc
1      10  7839   KING PRESIDENT   NA 1981-11-17 5000   NA ACCOUNTING NEW YORK
2      10  7934 MILLER     CLERK 7782 1982-01-23 1300   NA ACCOUNTING NEW YORK
3      10  7782  CLARK   MANAGER 7839 1981-06-09 2450   NA ACCOUNTING NEW YORK
4      20  7369  SMITH     CLERK 7902 1980-12-17  800   NA   RESEARCH   DALLAS
5      20  7566  JONES   MANAGER 7839 1981-04-02 2975   NA   RESEARCH   DALLAS
6      20  7902   FORD   ANALYST 7566 1981-12-03 3000   NA   RESEARCH   DALLAS
7      20  7876  ADAMS     CLERK 7788 1987-05-23 1100   NA   RESEARCH   DALLAS
8      20  7788  SCOTT   ANALYST 7566 1987-04-19 3000   NA   RESEARCH   DALLAS
9      30  7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300      SALES  CHICAGO
10     30  7698  BLAKE   MANAGER 7839 1981-05-01 2850   NA      SALES  CHICAGO
11     30  7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400      SALES  CHICAGO
12     30  7844 TURNER  SALESMAN 7698 1981-09-08 1500    0      SALES  CHICAGO
13     30  7521   WARD  SALESMAN 7698 1981-02-22 1250  500      SALES  CHICAGO
14     30  7900  JAMES     CLERK 7698 1981-12-03  950   NA      SALES  CHICAGO
15     40    NA   <NA>      <NA>   NA       <NA>   NA   NA OPERATIONS   BOSTON
16     70  9292   JACK     CLERK 7782 1982-01-23 3200   NA       <NA>     <NA>

 

*emp에만 있는 70번과, dept에만 있는 40번이 나온것을 알 수 있다.

 

 

 

 

문제79. 이름과 자기의 직속상사의 이름(관리자)을 출력하시오

 

x<-merge(emp,emp, by.x='mgr', by.y='empno')

x

hiredate.y sal.y comm.y deptno.y
1  1981-04-02  2975     NA       20
2  1981-04-02  2975     NA       20
3  1981-05-01  2850     NA       30
4  1981-05-01  2850     NA       30
5  1981-05-01  2850     NA       30
6  1981-05-01  2850     NA       30
7  1981-05-01  2850     NA       30
8  1981-06-09  2450     NA       10
9  1981-06-09  2450     NA       10
10 1987-04-19  3000     NA       20
11 1981-11-17  5000     NA       10
12 1981-11-17  5000     NA       10
13 1981-11-17  5000     NA       10
14 1981-12-03  3000     NA       20

 

※ emp테이블 2개를 하나는 mgr로, 하나는 empno로 merge 시킨다.

 

 

x[ ,c("ename.x","ename.y")]

 ename.x ename.y
1    SCOTT   JONES
2     FORD   JONES
3    ALLEN   BLAKE
4     WARD   BLAKE
5    JAMES   BLAKE
6   TURNER   BLAKE
7   MARTIN   BLAKE
8   MILLER   CLARK
9     JACK   CLARK
10   ADAMS   SCOTT
11   CLARK    KING
12   JONES    KING
13   BLAKE    KING
14   SMITH    FORD

 

 

 

 

문제80. 위의 결과를 다시 출력하는데 관리자 보다 더 많은 월급을 받는

사원들의 이름, 월급을 출력하시오

x<-merge(emp,emp, by.x='mgr', by.y='empno')

x[x$sal.x> x$sal.y ,c("ename.x","ename.y", "sal.x", "sal.y")]
  ename.x ename.y sal.x sal.y
1   SCOTT   JONES  3000  2975
2    FORD   JONES  3000  2975
9    JACK   CLARK  3200  2450

 

 

 

문제81. 위의 결과 데이터인 사원이름과 직속상사의 이름을 출력하는

데이터를 가지고 아래와 같이 시각화를 하시오

(사원 테이블의 조직도를 그리시오)

 

 

install.packages("igraph")

library(igraph)

x<-merge(emp, emp, by.x="mgr", by.y="empno")

a<-x[ ,c("ename.x", "ename.y")]

b<-graph.data.frame(a, directed=F)

plot(b)

 

 

 

 

 

 

 

문제82. 위의 그래프를 구글의 googleVis를 이용해서 emp 테이블의

관계도를 시각화 하시오

 

 

 

install.packages("googleVis")

library(googleVis)

 

a <- merge(emp,emp, by.x="empno",by.y="mgr", all.y=T)

 

org <- gvisOrgChart(a, idvar="ename.y",parentvar="ename.x",

                    options=list(width=600, height=250, size='middle',allowCollapse=T))

 

plot(org)

 

 

 

 

문제83. 부서위치, 부서위치별 토탈월급을 출력하시오

x<-merge(emp,dept, by='deptno')
> tapply(x$sal, x$loc, sum )
  BOSTON  CHICAGO   DALLAS NEW YORK
      NA     9400    10875     8750

 

 

 

 

 

문제84. 위의 결과를 막대 그래프로 시각화 하시오

x<-merge(emp,dept, by='deptno')

x2<-tapply(x$sal, x$loc, sum )

x2[is.na(x2)] <-0

barplot(x2, col=rainbow(7))

 

 

 

str(dept)
'data.frame':        4 obs. of  3 variables:
 $ deptno: int  10 20 30 40
 $ dname : Factor w/ 4 levels "ACCOUNTING","OPERATIONS",..: 1 3 4 2
 $ loc   : Factor w/ 4 levels "BOSTON","CHICAGO",..: 4 3 2 1

 

 

■ 팩터(factor) 자료형이란?

-범주(값의 목록)(순서)을 갖는 vector

-종류가 2가지

1. nominal  -> level의 순서의 값이 무의미하다.(디폴트 알파벳순서)

2. ordinal   -> level의 순서의 값을 직접 정의해서 원하는 순서로

정의할 수 있다.

 

예제:

a <- c("middle", "low", "high")
> str(a)

 chr [1:3] "middle" "low" "high"

 

> b <- factor(a)
> str(b)
 Factor w/ 3 levels "high","low","middle": 3 2 1

 

> c <- factor(a, order=TRUE, level=c("low", "middle", "high") )

#앞에 order를 선언해서, 레벨에서 순서를 변경시키지 않음.

> str(c)
 Ord.factor w/ 3 levels "low"<"middle"<..: 2 1 3

 

> max(c)

[1] high
Levels: low < middle < high

 

> min(c)
[1] low
Levels: low < middle < high

 

> c[order(c, decreasing=F)]
[1] low    middle high 
Levels: low < middle < high

 

 

 

 

 

문제85. 이름,월급, 부서위치를 출력하시오

 

x<-merge(emp, dept, by='deptno')

data.table(ENAME=x$ename, SAL=x$sal, DEPTNO=x$deptno)
     ENAME  SAL DEPTNO
 1:   KING 5000     10
 2: MILLER 1300     10
 3:  CLARK 2450     10
 4:  SMITH  800     20
 5:  JONES 2975     20
 6:   FORD 3000     20
 7:  ADAMS 1100     20
 8:  SCOTT 3000     20
 9:  ALLEN 1600     30
10:  BLAKE 2850     30
11: MARTIN 1250     30
12: TURNER 1500     30
13:   WARD 1250     30
14:  JAMES  950     30

 

x[, c("ename", "sal", "deptno")]

 

 

 

 

문제86. 부서위치, 부서위치별 토탈월급을 출력하시오

세로aggregate

가로tapply

 

merge 문에 all.y=T 를 추가시킬 것

x<-merge(emp, dept, by='deptno', all.y=T)  #아우터 조인!

 

aggregate(sal~loc,x,sum)
       loc   sal
1  CHICAGO  9400
2   DALLAS 10875
3 NEW YORK  8750

 

tapply(x$sal,x$loc,sum)
  BOSTON  CHICAGO   DALLAS NEW YORK
      NA     9400    10875     8750

 

※ 같은 merge 문을 이용해서 출력시키는데 왜 값이 다른가?

na.action=na.pass 를 사용해보면~

 

aggregate(sal~loc,x,sum, na.action=na.pass)
       loc   sal
1   BOSTON    NA
2  CHICAGO  9400
3   DALLAS 10875
4 NEW YORK  8750

 

 

 

 

문제87. 위의 결과를 구글막대 그래프로 그리시오

x2<-aggregate(sal~loc,x,sum, na.action=na.pass)

x2

x2[is.na(x2)] <-0

x2

install.packages("googleVis")

library(googleVis)

x3<-data.table(x2)

x3

x4<-gvisBarChart(x3)

plot(x4)

 

※ tapply 가져와서 하면 column 없다고 에러뜸

 

 

 

문제88. 아래와 같이 결과를 출력하시오

(입사한 년도별 부서위치별 토탈월급을 출력하시오)

 

x<-merge(emp, dept, by='deptno', all.y=T)

tapply(x$sal,list(x$loc,year(x$hiredate)), sum)
         1980 1981 1982 1987
BOSTON     NA   NA   NA   NA
CHICAGO    NA 9400   NA   NA
DALLAS    800 5975   NA 4100
NEW YORK   NA 7450 1300   NA

 

 

aggregate(sal~loc+ year(hiredate), x, sum)
       loc year(hiredate)  sal
1   DALLAS           1980  800
2  CHICAGO           1981 9400
3   DALLAS           1981 5975
4 NEW YORK           1981 7450
5 NEW YORK           1982 1300
6   DALLAS           1987 4100

 

 

 

 

문제89. 위의 결과 데이터를 일반 막대 그래프로 그리시오

 

x<-merge(emp, dept, by='deptno', all.y=T)

x2<-tapply(x$sal,list(x$loc,year(x$hiredate)), sum)

x2[is.na(x2)] <-0

barplot(x2, col=rainbow(4), beside=T, legend=rownames(x2))

 

 

 

 

 

문제90. 지하철 1~4호선 승하차 승객수.csv 를 R로 로드해서 line no 컬럼과

time 컬럼을 이용해서 구글 모션차트를 그리시오

 

line<-read.csv("1-4호선승하차승객수.csv", header=T)
> head(line)
  line_no time    in.     out
1  line_1  506  88136   35394
2  line_1  607 114628  195028
3  line_1  708 259282  483162
4  line_1  809 384892 1165703
5  line_1  910 315797  791704
6  line_1 1011 340972  585759

 

t1<-gvisMotionChart(line, idvar="line_no", timevar="time")

plot(t1)

 

 

 

 

문제91. 지하철 5-8호선.csv를 내려받고 구글 모션 차트를 그리시오!

 

 line<-read.csv("서울지하철_5-8호선_이용현황_시간대별.csv", header=T)
> head(line)
  호선명 시간 승차인원 하차인원
1  5호선  506     7992     1547
2  5호선  607    18445    10611
3  5호선  708    58566    29023
4  5호선  809    69908    80276
5  5호선  910    35972    38304
6  5호선 1011    28855    24063

 

t1<-gvisMotionChart(line, idvar="호선명", timevar="시간")

 

 

 

 

 

■ 집합연산자

	오라클	vs	R
1	union all		rbind
2	union		rbind + unique
3	intersect		intersect
4	minus		setdiff

 

 

 

문제92. 아래의 SQL 의 결과를 R로 구현하시오

sqp> select ename, sal, deptno

from emp

where deptno in (10,20)

 

union all

 

select ename, sal, deptno

from emp

where deptno=10;

 

rbind(emp[deptno %in% c(10,20), c("ename","sal","deptno")],
+       emp[deptno == 10, c("ename","sal","deptno")])
     ename  sal deptno
1    SMITH  800     20
2    ALLEN 1600     30
5   MARTIN 1250     30
6    BLAKE 2850     30
9     KING 5000     10
10  TURNER 1500     30
13    FORD 3000     20
14  MILLER 1300     10
11   SMITH  800     20
51  MARTIN 1250     30
91    KING 5000     10
131   FORD 3000     20

 

※설명

 rbind : 위아래의 결과를 하나의 결과로 출력

cbind : 양옆으로의 결과를 하나의 결과로 출력

 

 

 

문제93. 부서번호, 부서번호별 토탈월급을 아래와 같이 출력하는 SQL 을

R로 구현하시오

SQL> select deptno, sum(sal)

from emp

group by rollup(deptno);

 

rbind(aggregate(sal~deptno, emp, sum),
+       c("토탈값: ", sum(emp$sal)) )
    deptno   sal
1       10  8750
2       20 10875
3       30  9400
4       70  3200
5 토탈값:  32225

 

 

 

 

문제94. 아래의 SQL 결과를 R로 구현하시오

SQL> select ename, sal, deptno

from emp

where deptno in (10,20)

 

union

 

seelct ename, sal, deptno

from emp

where deptno=10;

※설명: union all 과 union 의 차이점은 중복값 제거 유무. union이 중복제거함

 

unique(rbind(emp[deptno %in% c(10,20), c("ename","sal","deptno")],

            emp[deptno == 10, c("ename","sal","deptno")] ) )

 

 ename  sal deptno
1   SMITH  800     20
2   ALLEN 1600     30
5  MARTIN 1250     30
6   BLAKE 2850     30
9    KING 5000     10
10 TURNER 1500     30
13   FORD 3000     20
14 MILLER 1300     10

※ 문제 92랑 비교하면 중복제거 확인가능

 

※ tapply 로 하면 안되는 이유

aggregate(sal~deptno, emp, sum)
  deptno   sal
1     10  8750
2     20 10875
3     30  9400
4     70  3200

 

tapply(emp$sal, emp$deptno, sum)
   10    20    30    70
 8750 10875  9400  3200

 

※검증:

x<-tapply(emp$sal, emp$deptno, sum)
> x
   10    20    30    70
 8750 10875  9400  3200
> rbind(tapply(sal, deptno, sum), c("1", "TOTAL: ", sal))
Error in tapply(sal, deptno, sum) :
  인자들은 반드시 같은 길이를 가져야 합니다

 

※인자는 3개인데, 결과는 4개의 컬럼으로 나온다.

tapply 같은 경우는 지정인자 수가

결과수랑 일치하지 않으므로

rbind 형태잡기가 어려워서 안된다 !

 

 

 

문제95. 아래의 SQL의 결과를 R 로 구현하시오

SQL> select ename, sal, deptno

from emp

where deptno in (10, 20)

minus

select ename, sal, deptno

from emp

where deptno = 10;

 

 

 x<-setdiff(emp[emp$deptno %in% c(10,20), "ename"],
+            emp[emp$deptno==10, "ename"])
> x
[1] "SMITH" "JONES" "SCOTT" "ADAMS" "FORD"
> emp[emp$ename %in% x, c("ename","sal","deptno")]
   ename  sal deptno
1  SMITH  800     20
4  JONES 2975     20
8  SCOTT 3000     20
11 ADAMS 1100     20
13  FORD 3000     20

 

 

 

 

문제96. 아래의 SQL의 결과를 R로 구현하시오

SQL> select ename, sal, deptno

from emp

where deptno in (10, 20)

intersect  # 교집합

select ename, sal, deptno

from emp

where deptno=10;

 

 x<-intersect(emp[emp$deptno %in% c(10,20), "ename"],
+            emp[emp$deptno==10, "ename"])
> x
[1] "CLARK"  "KING"   "MILLER"
> emp[emp$ename %in% x, c("ename","sal","deptno")]
    ename  sal deptno
7   CLARK 2450     10
9    KING 5000     10
14 MILLER 1300     10