Phân tích dữ liệu lớn - Giới thiệu về SQL

SQL là viết tắt của ngôn ngữ truy vấn có cấu trúc. Nó là một trong những ngôn ngữ được sử dụng rộng rãi nhất để trích xuất dữ liệu từ cơ sở dữ liệu trong kho dữ liệu truyền thống và công nghệ dữ liệu lớn. Để chứng minh những điều cơ bản của SQL, chúng tôi sẽ làm việc với các ví dụ. Để tập trung vào chính ngôn ngữ, chúng ta sẽ sử dụng SQL bên trong R. Về mặt viết mã SQL, điều này chính xác như được thực hiện trong cơ sở dữ liệu.

Cốt lõi của SQL là ba câu lệnh: SELECT, FROM và WHERE. Các ví dụ sau sử dụng các trường hợp sử dụng phổ biến nhất của SQL. Điều hướng đến thư mụcbda/part2/SQL_introduction và mở SQL_introduction.Rprojtập tin. Sau đó, mở tập lệnh 01_select.R. Để viết mã SQL trong R, chúng ta cần cài đặtsqldf gói như được minh họa trong đoạn mã sau.

# 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 ...

Câu lệnh select được sử dụng để lấy các cột từ bảng và thực hiện các phép tính trên chúng. Câu lệnh SELECT đơn giản nhất được minh họa trongej1. Chúng tôi cũng có thể tạo các biến mới như được hiển thị trongej2.

### 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

Một trong những tính năng được sử dụng phổ biến nhất của SQL là nhóm theo câu lệnh. Điều này cho phép tính toán một giá trị số cho các nhóm khác nhau của một biến khác. Mở tập lệnh 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

Tính năng hữu ích nhất của SQL là các phép nối. Phép nối có nghĩa là chúng ta muốn kết hợp bảng A và bảng B trong một bảng bằng cách sử dụng một cột để khớp các giá trị của cả hai bảng. Có nhiều loại liên kết khác nhau, về mặt thực tế, để bắt đầu, chúng sẽ là những loại hữu ích nhất: liên kết bên trong và liên kết bên ngoài bên trái.

# 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