Introducción
Vamos a realizar una breve comparativa entre SQL y R. Para ello nos centraremos en los aspectos básicos del acceso a datos, mostrando ejemplos de las mismas operaciones en ambos lenguajes.
En junio de 2015 Oracle publicó el documento SQL – the natural language for analysis en el que se describen los fundamentos del lenguaje SQL y las ventajas que ofrece en el campo del acceso y análisis de datos:
Éste documento será nuestra guía en la pequeña comparativa que sigue a continucaión.
Preparación del entorno
El juego de datos que se utiliza en los ejemplos del documento son las conocidas tablas EMP y DEPT, que tradicionalmente han acompañado a Oracle. Desde R se puede acceder a una gran variedad de fuentes de datos, entre las que se incluyen bases de datos, ficheros, recursos web, etc.
En este caso, para acceder a los datos de las tablas Oracle desde R, primero los volcaremos a un fichero con estructura CSV y los cargaremos utilizando la librería de R «DPLYR».
Las siguientes sentencias SQL nos pueden servir para generar la estructura CVS:
select '"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"' from dual union all select EMPNO||',"'||ENAME||'","'||JOB||'",'||MGR||','||HIREDATE||','||SAL||','||COMM||','||DEPTNO from emp;
select '"DEPTNO","DNAME","LOC"' from dual union all select DEPTNO||',"'||DNAME||'","'||LOC||'"' from dept;
Después de guardar los datos de EMP y DEPT en disco con formato CSV será necesario copiar ambos ficheros en una ruta a la que se tenga acceso desde el entorno de ejecución de R (por ejemplo, un subdirectorio «./data» dentro del espacionde trabajo) y ejecutar los siguientes comandos:
library(dplyr)
emp <- tbl_dt(read.csv("./data/emp.csv",stringsAsFactors = FALSE))
dept <- tbl_dt(read.csv("./data/dept.csv",stringsAsFactors = FALSE))
Principios de SQL
El lenguaje SQL se compone de cuatro operadores básicos:
- Projection
- Filter
- Join
- Aggregate
Projection
El operador projection permite indicar las columnas que queremos recuperar de una tabla. En su forma básica, se indican las columnas existentes por su nombre o posición en la tabla.
SELECT ename, job, hiredate FROM emp;
ENAME JOB HIREDATE ----- --------- -------------------- KING PRESIDENT 17-NOV-1981 00:00:00 BLAKE MANAGER 01-MAY-1981 00:00:00 CLARK MANAGER 09-JUN-1981 00:00:00 JONES MANAGER 02-APR-1981 00:00:00 -- Next lines were deleted
emp %>% select(ENAME, JOB, HIREDATE)
Source: local data table [14 x 3]
ENAME JOB HIREDATE
(chr) (chr) (chr)
1 KING PRESIDENT 17-NOV-1981 00:00:00
2 BLAKE MANAGER 01-MAY-1981 00:00:00
3 CLARK MANAGER 09-JUN-1981 00:00:00
4 JONES MANAGER 02-APR-1981 00:00:00
# Next lines were deleted
La proyección, en su forma extendida, permite generar columnas dinámicas aplicando operaciones que son calculadas en tiempo de ejecución.
SELECT ename, sal, comm, comm/sal*100 as COMM_RATE FROM emp;
ENAME SAL COMM COMM_RATE ----- ---- ---- --------- KING 5000 - - BLAKE 2850 - - CLARK 2450 - - JONES 2975 - - SCOTT 3000 - - FORD 3000 - - SMITH 800 - - ALLEN 1600 300 18.75 WARD 1250 500 40 MARTIN 1250 1400 112 TURNER 1500 0 0 ADAMS 1100 - - JAMES 950 - - MILLER 1300 - -
emp %>% mutate(COMM_RATE = COMM/SAL*100) %>% select(ENAME,SAL,COMM,COMM_RATE)
Source: local data table [14 x 4]
ENAME SAL COMM COMM_RATE
(chr) (int) (int) (dbl)
1 KING 5000 NA NA
2 BLAKE 2850 NA NA
3 CLARK 2450 NA NA
4 JONES 2975 NA NA
5 SCOTT 3000 NA NA
6 FORD 3000 NA NA
7 SMITH 800 NA NA
8 ALLEN 1600 300 18.75
9 WARD 1250 500 40.00
10 MARTIN 1250 1400 112.00
11 TURNER 1500 0 0.00
12 ADAMS 1100 NA NA
13 JAMES 950 NA NA
14 MILLER 1300 NA NA
Filter
El operador filter permite indicar una serie de reglas que deben cumplir los datos almacenados para ser recuperados en la consulta. En su forma más sencilla comparan los valores de la tabla con una expresión.
SELECT ename, job, hiredate FROM emp WHERE job = 'CLERK';
ENAME JOB HIREDATE ------ ----- -------------------- SMITH CLERK 17-DEC-1980 00:00:00 ADAMS CLERK 12-JAN-1983 00:00:00 JAMES CLERK 03-DEC-1981 00:00:00 MILLER CLERK 23-JAN-1982 00:00:00
emp %>% filter(JOB == "CLERK") %>% select(ENAME, JOB, HIREDATE)
Source: local data table [4 x 3] ENAME JOB HIREDATE (chr) (chr) (chr) 1 SMITH CLERK 17-DEC-1980 00:00:00 2 ADAMS CLERK 12-JAN-1983 00:00:00 3 JAMES CLERK 03-DEC-1981 00:00:00 4 MILLER CLERK 23-JAN-1982 00:00:00
Join
El operador join relaciona distintas tablas a través de la comparación de uno o varios de sus campos. Existen varios tipos de join dependiendo de la forma en la que va a realizar el cruzado de los datos: inner, full outer, outer left, outer right y cross.
SELECT d.deptno, d.dname, e.empno, e.ename FROM dept d INNER JOIN emp e ON (e.deptno = d.deptno);
DEPTNO DNAME EMPNO ENAME ------ ---------- ----- ------ 10 ACCOUNTING 7782 CLARK 10 ACCOUNTING 7934 MILLER 10 ACCOUNTING 7839 KING 20 RESEARCH 7902 FORD 20 RESEARCH 7788 SCOTT -- Next lines were deleted
emp %>%
inner_join(dept, by = c("DEPTNO"="DEPTNO")) %>%
select(DEPTNO,DNAME,EMPNO,ENAME)
Source: local data table [14 x 4]
DEPTNO DNAME EMPNO ENAME
(int) (chr) (int) (chr)
1 10 ACCOUNTING 7839 KING
2 10 ACCOUNTING 7782 CLARK
3 10 ACCOUNTING 7934 MILLER
4 20 RESEARCH 7566 JONES
5 20 RESEARCH 7788 SCOTT
# Next lines were deleted
Aggregate
El operador aggregate ejecuta operaciones sobre grupos, o sets, de datos.
SELECT SUM(sal) AS total_salary FROM emp;
TOTAL_SALARY ------------ 29025
emp %>% summarise(total_salary = sum(SAL))
Source: local data table [1 x 1]
total_salary
(int)
1 29025
Este operador es especialmente importante en el análisis de datos porque permite acotar las operaciones a rangos o agrupaciones de los datos sobre los que estamos trabajando.
SELECT deptno,
COUNT(empno) AS no_of_employees,
SUM(sal) AS total_salary,
AVG(sal) AS average_salary
FROM emp
GROUP BY deptno;
DEPTNO NO_OF_EMPLOYEES TOTAL_SALARY AVERAGE_SALARY ------ --------------- ------------ ---------------------------------------- 30 6 9400 1566.66666666666666666666666666666666667 20 5 10875 2175 10 3 8750 2916.66666666666666666666666666666666667
emp %>% group_by(DEPTNO) %>% summarise(no_of_employees = length(EMPNO),total_salary = sum(SAL), average_salary = mean(SAL))
Source: local data table [3 x 4] DEPTNO no_of_employees total_salary average_salary (int) (int) (int) (dbl) 1 10 3 8750 2916.667 2 30 6 9400 1566.667 3 20 5 10875 2175.000
desc EMP 