빅 데이터 분석-SQL 소개

SQL은 구조화 된 쿼리 언어를 나타냅니다. 기존 데이터웨어 하우스 및 빅 데이터 기술의 데이터베이스에서 데이터를 추출하는 데 가장 널리 사용되는 언어 중 하나입니다. SQL의 기본을 설명하기 위해 예제로 작업 할 것입니다. 언어 자체에 초점을 맞추기 위해 R 내부에서 SQL을 사용할 것입니다. SQL 코드 작성 측면에서 이것은 데이터베이스에서 수행되는 것과 똑같습니다.

SQL의 핵심은 SELECT, FROM 및 WHERE의 세 가지 문입니다. 다음 예제는 SQL의 가장 일반적인 사용 사례를 사용합니다. 폴더로 이동bda/part2/SQL_introduction 열다 SQL_introduction.Rproj파일. 그런 다음 01_select.R 스크립트를 엽니 다. R에서 SQL 코드를 작성하려면 다음을 설치해야합니다.sqldf 다음 코드에 설명 된대로 패키지.

# Install the sqldf package
install.packages('sqldf')  

# load the library 
library('sqldf') 
library(nycflights13)  

# We will be working with the fligths dataset in order to introduce SQL  

# Let’s take a look at the table 
str(flights) 
# Classes 'tbl_d', 'tbl' and 'data.frame': 336776 obs. of  16 variables: 

# $ year     : int  2013 2013 2013 2013 2013 2013 2013 2013 2013 2013 ... 
# $ month    : int  1 1 1 1 1 1 1 1 1 1 ... 
# $ day      : int  1 1 1 1 1 1 1 1 1 1 ... 
# $ dep_time : int  517 533 542 544 554 554 555 557 557 558 ... 
# $ dep_delay: num  2 4 2 -1 -6 -4 -5 -3 -3 -2 ... 
# $ arr_time : int  830 850 923 1004 812 740 913 709 838 753 ... 
# $ arr_delay: num  11 20 33 -18 -25 12 19 -14 -8 8 ...
# $ carrier  : chr  "UA" "UA" "AA" "B6" ... 

# $ tailnum  : chr  "N14228" "N24211" "N619AA" "N804JB" ... 
# $ flight   : int  1545 1714 1141 725 461 1696 507 5708 79 301 ... 
# $ origin   : chr  "EWR" "LGA" "JFK" "JFK" ... 
# $ dest     : chr  "IAH" "IAH" "MIA" "BQN" ... 
# $ air_time : num  227 227 160 183 116 150 158 53 140 138 ... 
# $ distance : num  1400 1416 1089 1576 762 ... 
# $ hour     : num  5 5 5 5 5 5 5 5 5 5 ... 
# $ minute   : num  17 33 42 44 54 54 55 57 57 58 ...

select 문은 테이블에서 열을 검색하고 이에 대한 계산을 수행하는 데 사용됩니다. 가장 간단한 SELECT 문은ej1. 다음과 같이 새 변수를 만들 수도 있습니다.ej2.

### SELECT statement
ej1 = sqldf(" 
   SELECT  
   dep_time 
   ,dep_delay 
   ,arr_time 
   ,carrier 
   ,tailnum 
   FROM 
   flights
")  

head(ej1) 
#    dep_time   dep_delay  arr_time  carrier  tailnum 
# 1      517         2      830      UA       N14228 
# 2      533         4      850      UA       N24211 
# 3      542         2      923      AA       N619AA 
# 4      544        -1     1004      B6       N804JB 
# 5      554        -6      812      DL       N668DN 
# 6      554        -4      740      UA       N39463  

# In R we can use SQL with the sqldf function. It works exactly the same as in 
a database 

# The data.frame (in this case flights) represents the table we are querying 
and goes in the FROM statement  
# We can also compute new variables in the select statement using the syntax: 

# old_variables as new_variable 
ej2 = sqldf(" 
   SELECT 
   arr_delay - dep_delay as gain, 
   carrier 
   FROM 
   flights
")  

ej2[1:5, ] 
#    gain   carrier 
# 1    9      UA 
# 2   16      UA 
# 3   31      AA 
# 4  -17      B6 
# 5  -19      DL

SQL에서 가장 일반적으로 사용되는 기능 중 하나는 문별 그룹입니다. 이를 통해 다른 변수의 다른 그룹에 대한 숫자 값을 계산할 수 있습니다. 02_group_by.R 스크립트를 엽니 다.

### GROUP BY      

# Computing the average 
ej3 = sqldf(" 
  SELECT 
   avg(arr_delay) as mean_arr_delay, 
   avg(dep_delay) as mean_dep_delay, 
   carrier 
   FROM 
   flights 
   GROUP BY 
   carrier 
")  

#    mean_arr_delay   mean_dep_delay carrier 
# 1       7.3796692      16.725769      9E 
# 2       0.3642909       8.586016      AA 
# 3      -9.9308886       5.804775      AS 
# 4       9.4579733      13.022522      B6 
# 5       1.6443409       9.264505      DL 
# 6      15.7964311      19.955390      EV 
# 7      21.9207048      20.215543      F9 
# 8      20.1159055      18.726075      FL 
# 9      -6.9152047       4.900585      HA 
# 10     10.7747334      10.552041      MQ
# 11     11.9310345      12.586207      OO 
# 12      3.5580111      12.106073      UA 
# 13      2.1295951       3.782418      US 
# 14      1.7644644      12.869421      VX 
# 15      9.6491199      17.711744      WN 
# 16     15.5569853      18.996330      YV  

# Other aggregations 
ej4 = sqldf(" 
   SELECT 
   avg(arr_delay) as mean_arr_delay, 
   min(dep_delay) as min_dep_delay, 
   max(dep_delay) as max_dep_delay, 
   carrier 
   FROM  
   flights 
   GROUP BY 
   carrier 
")  

# We can compute the minimun, mean, and maximum values of a numeric value 
ej4 
#      mean_arr_delay    min_dep_delay   max_dep_delay   carrier 
# 1       7.3796692           -24           747          9E 
# 2       0.3642909           -24          1014          AA 
# 3      -9.9308886           -21           225          AS 
# 4       9.4579733           -43           502          B6
# 5       1.6443409           -33           960         DL 
# 6      15.7964311           -32           548         EV 
# 7      21.9207048           -27           853         F9 
# 8      20.1159055           -22           602         FL 
# 9      -6.9152047           -16          1301         HA 
# 10     10.7747334           -26          1137         MQ 
# 11     11.9310345           -14           154         OO 
# 12      3.5580111           -20           483         UA 
# 13      2.1295951           -19           500         US 
# 14      1.7644644           -20           653         VX 
# 15      9.6491199           -13           471         WN 
# 16     15.5569853           -16           387         YV  

### We could be also interested in knowing how many observations each carrier has  
ej5 = sqldf(" 
   SELECT 
   carrier, count(*) as count 
   FROM  
   flights 
   GROUP BY 
   carrier 
")  

ej5 
#      carrier  count 
# 1       9E    18460
# 2       AA   32729 
# 3       AS   714 
# 4       B6   54635 
# 5       DL   48110 
# 6       EV   54173 
# 7       F9   685 
# 8       FL   3260 
# 9       HA   342 
# 10      MQ   26397 
# 11      OO   32 
# 12      UA   58665 
# 13      US   20536 
# 14      VX   5162 
# 15      WN   12275 
# 16      YV   601

SQL의 가장 유용한 기능은 조인입니다. 조인은 두 테이블의 값을 일치시키기 위해 하나의 열을 사용하여 하나의 테이블에서 테이블 A와 테이블 B를 결합한다는 것을 의미합니다. 실제로 시작하기위한 다양한 유형의 조인이 있습니다. 내부 조인과 왼쪽 외부 조인이 가장 유용한 조인입니다.

# Let’s create two tables: A and B to demonstrate joins.
A = data.frame(c1 = 1:4, c2 = letters[1:4]) 
B = data.frame(c1 = c(2,4,5,6), c2 = letters[c(2:5)])  

A 
# c1 c2 
# 1  a 
# 2  b 
# 3  c 
# 4  d  

B 
# c1 c2 
# 2  b 
# 4  c 
# 5  d 
# 6  e  

### INNER JOIN 
# This means to match the observations of the column we would join the tables by.   
inner = sqldf(" 
   SELECT 
   A.c1, B.c2 
   FROM 
   A INNER JOIN B 
   ON A.c1 = B.c1 
")  

# Only the rows that match c1 in both A and B are returned 
inner 
# c1 c2 
#  2  b 
#  4  c  

### LEFT OUTER JOIN
# the left outer join, sometimes just called left join will return the  
# first all the values of the column used from the A table  
left = sqldf(" 
  SELECT 
   A.c1, B.c2 
  FROM 
   A LEFT OUTER JOIN B 
   ON A.c1 = B.c1 
")  

# Only the rows that match c1 in both A and B are returned 
left 
#   c1    c2 
#    1  <NA> 
#    2    b 
#    3  <NA> 
#    4    c