R / 서브쿼리

18 R 에서의 서브쿼리

 

ㅇSQL의 서브쿼리 종류 3가지

1. single row subquery(단일행 서브쿼리) : 서브쿼리에서 메인쿼리로 하나의 값이 리턴되는 경우

2. multiple row subquery(다중행 서브쿼리) : 서브쿼리에서 메인쿼리로 여러개의 값이 리턴되는 경우

3. multiple column subquery(다중컬럼 서브쿼리) : 서브쿼리에서 메인쿼리로 여러개의 컬럼들이 값이 리턴되는 경우

 

ㅇ single row subquery

문제117. 아래의 SQL을 R로 구현하시오

-- SQL
select ename, sal
 from emp
 where sal > ( select sal
                 from emp
                 where ename = 'JONES');

# R
emp <- read.csv("c:\\data\\emp2.csv")
a <- emp[emp$ename == 'JONES', c('sal')]
emp[emp$sal > a, c("ename", "sal")]

문제118. 위의 결과를 파이썬으로 구현하시오.

# python
import pandas as pd

emp = pd.read_csv("c:\\data\\emp2.csv")
a = emp["sal"][emp.ename == 'JONES'].values[0]   # Series에서 값을 뽑아내기 위해 values[0]을 붙여야함
emp[['ename', 'sal']][emp.sal > a]

+) Series는 연산을 할 수 없으므로 numpy로 변환해서 사용해야함

 

 

문제119. 아래의 SQL을 R로 구현하시오.

-- SQL
select ename, sal
 from emp
 where sal = (select max(sal)
                from emp);

# R
max_sal <- max(emp$sal)
emp[emp$sal == max_sal, c("ename", "sal")]

문제120. 위의 SQL을 파이썬으로 구현하시오

# python
a = emp.sal.max()
emp[['ename','sal']][emp.sal == a]

 

문제121. 전국에서 등록금이 가장 비싼 학교이름과 등록금을 R로 출력하시오

(데이터 : 대학별평균등록금.csv)

# R
uni <- read.csv("c:\\data\\한국장학재단_대학별 평균등록금_20210430.csv")
colnames(uni)  # 컬럼명 확인
max_pay <- max(uni$평균등록금.원.)
uni[uni$평균등록금.원. == max_pay, c("대학명", "평균등록금.원.")]

문제122. 위의 결과를 파이썬으로 구현하시오.

# python
uni = pd.read_csv("c:\\data\\한국장학재단_대학별 평균등록금_20210430.csv", encoding = 'euckr')
max_pay = uni['평균등록금(원)'].max()
uni[['대학명', '평균등록금(원)']][uni['평균등록금(원)'] == max_pay]

문제123. 아래의 SQL을 R로 구현하시오.

-- SQL
select ename, sal, deptno
 from emp
 where deptno in (select deptno
                    from emp
                    where job = 'SALESMAN');

# R
a <- emp[emp$job == 'SALESMAN', c("deptno")]
emp[emp$deptno %in% a, c("ename", "sal", "deptno")]

문제124. 위의 결과를 파이썬으로 수행하시오.

# python
import pandas as pd

emp = pd.read_csv("c:\\data\\emp3.csv")
a = emp['deptno'][emp.job == 'SALESMAN']
emp[['ename', 'sal', 'deptno']][emp.deptno.isin(a)]

문제125. 관리자인 사원들의 이름을 출력하시오. (자기 밑에 직속부하가 한 명이라고 있는 사원들)

-- SQL
select ename
 from emp
 where empno in (select mgr
                  from emp);

# R
emp[emp$empno %in% emp$mgr, c("ename")]

문제126. 위의 결과를 파이썬으로 구현하시오.

# python
emp[['ename']][emp.empno.isin(emp.mgr)]

문제127. 지하철에서 가장 많이 발생하는 범죄유형이 무엇인지 출력하시오.

# R
crime <- read.csv("c:\\data\\crime_loc.csv")
x <- crime[(crime$장소 == '지하철') , c("범죄","건수") )
max(x)

# python
crime = pd.read_csv("c:\\data\\crime_loc.csv", encoding = 'euckr')
x = crime[['범죄','건수']][crime.장소 == '지하철']
x[:][x.건수 == x.건수.max()]

문제129. 강력범죄가 가장 많이 발생하는 요일은 언제인가.

(데이터 : crime_day.csv)

# R
c_day <- read.csv("c:\\data\\crime_day.csv")
x <- c_day[trimws(c_day$C_C) == '강력범죄', c("C_C","C_T","DAY","CNT")]
x[x$CNT == max(x$CNT), c("DAY", "CNT")]

trimws() : 양쪽에 공백을 제거

 

문제130. 위의 결과를 파이썬으로 구현하시오.

# python
c_day = pd.read_csv("c:\\data\\crime_day.csv", encoding = 'euckr')

x = c_day[:][c_day['C_C'].str.strip() == '강력범죄']
x[:][x['CNT'] == x['CNT'].max()]

문제131. 살인기수가 많이 발생하는 요일을 1위부터 3위까지 출력하시오.

# R
x <- c_day[trimws(c_day$C_T) == '살인기수', ]
library(doBy)
x2 <- orderBy(~ -CNT, x)
head(x2,3)

head(데이터테이블, 가져올 갯수)

 

문제132. 위의 결과를 파이썬으로 구현하시오.

# python
c_day = pd.read_csv("c:\\data\\crime_day.csv", encoding = 'euckr')

x = c_day[:][c_day['C_T'].str.strip() == '살인기수']
x.sort_values(by = "CNT", ascending = False).head(3)

 

 

19 R에서의 순위출력

SQL	R	파이썬
rank	rank	rank

문제133. 이름, 월급, 월급에 대한 순위를 출력하시오.

-- SQL
select ename, sal, rank() over (order by sal desc) as 순위
 from emp;

# R
library(data.table)
x <- data.table(이름 = emp$ename, 월급 = emp$sal, 순위 = rank(-emp$sal, ties.method = "min"))
library(doBy)
orderBy(~ 순위, x)

rank( -emp$sal, ties.method = "min")

rank 함수에서 마이너스(-)를 사용하면 값이 높은 것부터 순위가 부여됨

 

ㅇties.method의 옵션

- min : oracle의 rank와 같음

- first : oracle의 rank와 같은데 순위가 같은 데이터가 있으면 인덱스 순서가 먼저 나온 데이터를 높은 순위로 부여함.

- max : 만약 2등이 2명이면 둘 다 3등으로 출력함

 

> 오라클의 dense_rank() 와 같은 함수는 무엇인가?

-- SQL
select ename, sal, dense_rank() over (order by sal desc) as 순위
 from emp;

# R
library(dplyr)
x <- data.table(이름 = emp$ename, 월급 = emp$sal, 순위 = dense_rank(-emp$sal))
library(doBy)
orderBy(~ 순위, x)

dplyr패키지에 dense_rank()함수가 있음. 이 함수를 이용해서 dense_rank()기능 사용하면 됨.

 

 

문제134. (점심시간 문제) 아래의 SQL을 R로 구현하시오.

-- SQL
select ename, sal, rank() over (order by sal desc) as 순위
 from emp
 where job = 'SALESMAN';

# R
library(data.table)
x <- emp[emp$job == 'SALESMAN', ]
x2 <- data.table(이름 = x$ename, 월급 = x$sal, 순위 = rank( -x$sal, ties.method = 'min'))
library(doBy)
orderBy(~ 순위,x2 )

 

문제135. 문제133번을 파이썬으로 수행하시오(dense_rank)

# python
emp['sal_rank'] = emp['sal'].rank( ascending = False)
emp['sal_dense'] = emp['sal'].rank( method = 'dense', ascending = False)
emp['sal_min'] = emp['sal'].rank( method = 'min', ascending = False)
emp['sal_max'] = emp['sal'].rank( method = 'max', ascending = False)

emp.sort_values(by = 'sal_rank', ascending = True)

rank(ascending = False)는 오라클의 rank와 다른데 다른점이 있다면 2등이 두명 있으면 2.5등으로 출력함

rank(method = 'dense', ascending = False)는 2등이 두명 있으면 다음 등수를 3등으로 출력함

rank(method = 'min', ascending = False)는 2등이 두명 있으면 그냥 2등으로 둘 다 출력함

rank(method = 'max', ascending = False)는 2등이 두명 있으면 둘 다 3등으로 출력함

 

문제136. 월요일에 많이 발생하는 범죄, 건수, 순위를 출력하시오.

# R
day <- read.csv("c:\\data\\crime_day.csv")
x <- day[trimws(day$DAY) == 'MON',]
x2 <- data.table(범죄 = x$C_T, 건수 = x$CNT, 순위 = dense_rank(- x$CNT) )
orderBy(~ 순위, x2)

문제137. 위의 결과를 파이썬으로 구현하시오.

# python
day = pd.read_csv("c:\\data\\crime_day.csv", encoding = 'euckr')
a = day[:][day['DAY'] == 'MON']
a['rank'] = a['CNT'].rank(method = 'dense', ascending = False)
a.sort_values('rank', ascending = True)

문제138. 위의 결과에서 순위를 1.0, 2.0이 아닌 1, 2로 출력되게 하세요

a['rank'] = a['CNT'].rank(method = 'dense', ascending = False).astype(int)

astype()를 사용해서 자료형을 int로 변환함

 

 

ㅁ 오라클의 서브쿼리 3종류

1. 단일행 서브쿼리

2. 다중행 서브쿼리

3. 다중 컬럼 서브쿼리

 

문제139. 아래의 SQL을 R로 구현하시오. (다중컬럼 서브쿼리)

-- SQL
select ename, sal, job
 from emp
 where deptno in (select deptno
                    from emp
                    where comm is not null)
    and job in (select job
                   from emp
                   where comm is not null );

# R
sub1 <- emp[!is.na(emp$comm), c("deptno")]
sub2 <- emp[!is.na(emp$comm), c("job")]
emp[(emp$deptno %in% sub1) &(emp$job %in% sub2), c("ename", "sal","job")]

문제140. 아래의 SQL을 파이썬으로 수행하시오.

-- SQL
select ename, sal, job
 from emp
 where deptno in (select deptno
                    from emp
                    where comm is not null)
    and job in (select job
                   from emp
                   where comm is not null );

# python
sub1 = emp['deptno'][~ emp.comm.isnull()].values  # Series가 아닌 값을 가져오기 위함
sub2 = emp['job'][~ emp.comm.isnull()].values
emp[['ename', 'sal','job']][(emp.deptno.isin(sub1)&(emp.job.isin(sub2)))]