Para evitar confusiones, a lo largo de esta sección intentaré usar “base de datos” para referirme a RDBMS, “tabla” para hablar de cada uno de los componentes de una base de datos y “matriz” cuando hable del tipo de datos que hasta ahora hemos almacenado en un data.frame. Empecemos, pues por leer dos matrices de datos que utilizaremos a lo largo del ejercicio.

La primera es una versión limpia de una encuesta de opinión política en España. La encuesta está centrada en comportamiento electoral y pregunta a los entrevistados por información sociodemográfica habitual, por su intención de voto, por su recuerdo de voto y por dos variables que son claves en las encuestas electorales: por qué partido siente más simpatía y su autoubicación en una escala ideológica (en donde 1 representa la extrema izquierda y 10, la extrema derecha).

cis <- readRDS('./assets/clean-data.RDS')
dim(cis)

Veamos ahora unos descriptivos de variables que nos pueden interesar en el análisis:

summary(cis[, c("age", "gender", "education", "ideology", "pid", "pastvote")])

Todos los análisis a continuación los podríamos ejecutar directamente en R, pero vamos a ver cómo los haríamos en SQL.

En primer lugar, tenemos que escoger la base de datos que usaremos. Por lo general, esto nos viene dado. En este caso podemos escoger entre alguno de los muchos productos en el mercado (MySQL, PostgreSQL, SQL Server). En este caso, usaremos una base de datos más sencilla y que vive en memoria llamada SQLite.

El proceso para trabajar con SQLite es similar al de otras bases de datos, con la diferencia de que en este caso no necesitamos conectarnos a otra máquina/servidor para acceder a los datos, con lo que evitamos el problema de autenticarnos.

Nos conectaremos a SQLite usando el paquete DBI que nos ofrece formas de conectarnos a bases relacionales y el paquete RSQLite que ofrece la conexión de DBI con SQLite.

library(DBI)
install.packages("RSQLite")
con <- dbConnect(RSQLite::SQLite(), ":memory:")

La parte fundamental de lo que acabamos de hacer es la conexión a la base de datos usando dbConnect. En este caso, hemos utilizado el driver de SQLite y trabajaremos en memoria. Para trabajar con otras bases de datos remotas pasaríamos aquí el driver correspondiente a la base de datos en cuestión así como los parámetros de autenticación necesarios (servidor, base de datos, usuario, clave secreta y puerto de conexión).

Empezaremos por obtener un listado de las tablas disponibles en la base de datos que acabamos de crear:

dbListTables(con)

Por ahora, la base está vacía. Copiemos la matriz de datos cis a la base de datos. Para ello le daremos un nombre a la tabla:

dbWriteTable(con, "cis", cis)
dbListTables(con)

Ahora que los datos están en la base de datos, podemos empezar a interactuar con ella. Para ello usaremos SQL.

Operaciones básicas

SELECT

La estructura de las búsquedas con SQL es siempre la misma. Usaremos la cláusula SELECT y la tabla a la que nos referimos usando FROM. Para enviar nuestras búsquedas usaremos la conexión que hemos creado. En este caso, con y pasaremos la búsqueda a través de la función dbSendQuery. Algo importante a recordar es que una cosa es enviar la búsqueda y otra distinta recuperar los datos que la búsqueda produce.

res <- dbSendQuery(con, "SELECT gender FROM cis")
dbFetch(res)

Podemos seleccionar más de una columna haciendo un listado

res <- dbSendQuery(con, "SELECT gender, age FROM cis")
dbFetch(res)

Y podemos seleccionar también todas las columnas utilizando el carácter especial * (la razón de usar asterisco quedará más clara cuando veamos expresiones regulares).

dbFetch(dbSendQuery(con, "SELECT * FROM cis"))

Podemos también obtener solo un grupo de filas utilizando LIMIT

dbFetch(dbSendQuery(con, "SELECT * FROM cis LIMIT 5"))

COUNT, SUM, AVG, MIN, MAX

Podemos empezar a hacer cosas más interesantes utilizando funciones de agregación. Por ejemplo, podemos contar el número de filas en la tabla:

dbFetch(dbSendQuery(con, "SELECT COUNT(*) FROM cis"))

O podemos contar el número de filas distintas en la tabla. Por ejemplo, ¿cuántos diferentes valores de edad tenemos en la tabla?

dbFetch(dbSendQuery(con, "SELECT COUNT(DISTINCT age) FROM cis"))

Podemos obtener también descriptivos de las variables

dbFetch(dbSendQuery(con, "SELECT MIN(ideology) FROM cis"))
dbFetch(dbSendQuery(con, "SELECT AVG(ideology) FROM cis"))

Algo que será muy útil será poder dar nombres a las columnas a través de la creación de “alias” con la cláusula AS.

dbFetch(dbSendQuery(con, "SELECT AVG(ideology) as mean_ideology FROM cis"))

Podemos establecer un alias para referirnos a operaciones más complejas:

dbFetch(dbSendQuery(con, "SELECT MAX(ideology) - MIN(ideology) AS diff FROM cis;"))

Antes de continuar, vamos a cerrar la conexión a la base de datos

dbDisconnect(con)