Introducción a SQL (y II)

Reabramos la conexión a la base de datos. Ya que está en memoria, y al cerrar la conexión se ha borrado, tendremos que volver a hacer el proceso de antes. En una base de datos habitual los datos son persistentes.

cis <- readRDS('./assets/clean-data.RDS')
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "cis", cis)

WHERE

Armados con lo que hemos visto antes, podemos empezar ahora a utilizar SQL para entender mejor los datos. Por ejemplo, podemos hacer selecciones de los datos para echar un vistazo a la composición de los datos por ideología:

dbFetch(dbSendQuery(con, "SELECT age, gender FROM cis WHERE ideology == 10;"))

Podemos por ejemplo mirar a la distribución en los extremos ideológicos

dbFetch(dbSendQuery(con, "SELECT COUNT(*) FROM cis WHERE ideology == 1;"))
dbFetch(dbSendQuery(con, "SELECT COUNT(*) FROM cis WHERE ideology == 10;"))
dbFetch(dbSendQuery(con, "SELECT COUNT(*) FROM cis WHERE ideology == 1 OR ideology == 10;"))

NULL

Una cosa importante es que RBDMS tiene el concepto de NULL que representa varios tipos de situaciones, pero que en este caso nos ayuda a representar los valores perdidos.

dbFetch(dbSendQuery(con, "SELECT COUNT(*) FROM cis WHERE ideology IS NULL;"))

ORDER BY

Podemos ordenar los valores a lo largo de una variable. Por ejemplo, podemos ver la distribución de los casos usando como orden la ideología:

res <- dbSendQuery(con, "SELECT age, gender, ideology FROM cis ORDER BY ideology;")
dbFetch(res)

Como vemos, los valores NULL aparecen al principio, que quizás es algo que prefiramos no ver en este caso

res <- dbSendQuery(con, "SELECT age, ideology FROM cis ORDER BY ideology WHERE ideology is not NULL;")
dbFetch(res)

SQL es muy flexible pero tiene preferencias sobre el orden de las cláusulas. Lo que es útil es que al evaluar la búsqueda, SQL intenta ser útil dándonos una pista de qué ha ido mal.

res <- dbSendQuery(con, "SELECT age, ideology FROM cis WHERE ideology is not NULL ORDER BY ideology;")
dbFetch(res)

Es útil que podamos también ejecutar el orden inverso:

res <- dbSendQuery(con, "SELECT age, ideology FROM cis ORDER BY ideology DESC;")
dbFetch(res)

Y que podamos ordenar los datos por más de una variable. En este caso, ordenamos ideología y edad usando diferente órdenes.

res <- dbSendQuery(con, "SELECT age, ideology FROM cis ORDER BY ideology DESC, age;")
dbFetch(res)

GROUP BY y HAVING

Ahora podemos empezar a hacer análisis más interesantes. Por ejemplo, podemos intentar contar el número de votantes de cada partido. Para ello queremos agrupar los datos por partido y después aplicar la función COUNT a esos resultados.

dbFetch(dbSendQuery(con, "SELECT COUNT(*) FROM cis GROUP BY (pid);"))

¿Qué nos falta aquí? Saber el partido al que se refiere la búsqueda

dbFetch(dbSendQuery(con, "SELECT pid, COUNT(*) FROM cis GROUP BY (pid);"))

Igual que antes, podemos asociar un resultado (una columna en este caso) a un nombre usando un alias:

dbFetch(dbSendQuery(con, "SELECT pid, COUNT(*) as count FROM cis GROUP BY (pid);"))

Veamos ahora por ejemplo la distribución ideológica de cada partido mirando para ello la ideología declarada de los votantes de ese partido. Lo que queremos hacer es agrupar los datos por pid y después, para cada grupo, calcular la media de la ideología.

dbFetch(dbSendQuery(con, "SELECT pid, AVG(ideology) as mean FROM cis GROUP BY (pid);"))

Podemos también agruppar los resultados usando más de una variable. Por ejemplo, podemos calcular la distribución ideológica usando pid pero también recuerdo de voto, lo que nos permite ver si los que se identifican con un partido pero no han votado por él en el pasado tienen una ideología diferente de los que son consistentes.

dbFetch(dbSendQuery(con, "SELECT pid, pastvote, AVG(ideology) 
                          FROM cis 
                          WHERE pid IS NOT NULL
                          AND pastvote IS NOT NULL
                          GROUP BY pid, pastvote;"))

HAVING

La cláusula HAVING es poco intuitiva al principio pero es muy útil. Imaginemos que nos interesa calcular los resultados pero únicamente para aquellos grupos para los que tenemos suficientes datos como para que la media de ideología sea una estimación “razonable”. En este caso, queremos calcular la tabla anterior y después de calcularla queremos limitarla a las filas para las que el COUNT de ideología sea mayor que, por ejemplo, 30. WHERE no nos será útil en este caso. Y podríamos resolver este problema usando una subquery (que no veremos) pero por lo general la solución más eficiente cuando queremos aplicar transformaciones/funciones a grupos, es usar HAVING.

dbFetch(dbSendQuery(con, "SELECT pid, pastvote, AVG(ideology) 
                          FROM cis 
                          WHERE pid IS NOT NULL
                          AND pastvote IS NOT NULL
                          GROUP BY pid, pastvote
                          HAVING COUNT(ideology) > 30;"))

JOIN

Ya hemos visto que uno de los valores de las bases de datos relacionales es el hecho de que nos permiten establecer relaciones entre tablas. Una de las utilidades más prácticas que SQL nos permite es realizar un JOIN, es decir, juntar variables tablas usando columnas comunes. Es lo mismo que hemos visto usando merge en R. Es un tema que se escapa del contenido básico pero es útil quizás ver un JOIN

Empezaremos por cargar en SQLite otra tabla con la que poder hacer JOIN. Por ejemplo, en los datos CIS que hemos usado hasta ahora tenemos una distribución de quienes han contestado a las preguntas de la entrevista. Pero podemos intentar poner estos datos en el contexto de, por ejemplo, datos contextuales sobre la región de la que los votantes vienen. Para ello podemos poner en la base de datos una tabla que capture información a nivel regional.

frame <- read.csv('./assets/ccaa.csv')
dbWriteTable(con, "frame", frame)
dbListTables(con)

Listemos los datos:

dbFetch(dbSendQuery(con, "SELECT * FROM frame;"))

Ahora haremos un JOIN de las dos tablas de tal forma que, a cada entrevistado, le asignemos los datos de renta de la comunidad autónoma en la que vive usando los datos de 2018.

res <- dbFetch(dbSendQuery(con,
"SELECT *
 FROM cis
 JOIN frame
 ON cis.ccaa = frame.ccaa"))
head(res)

Pero quizás es más interesante ejecutar operaciones directamente en la base de datos y sobre el JOIN que acabamos de hacer

res <- dbFetch(dbSendQuery(con,
"SELECT cis.pid, cis.ccaa, frame.renta, AVG(ideology) as mean_ideology
 FROM cis
 JOIN frame
 ON cis.ccaa = frame.ccaa
 WHERE pid IS NOT NULL
 AND pastvote IS NOT NULL
 GROUP BY pid
 HAVING COUNT(ideology) > 30
 AND renta > AVG(renta);"))
print(res)

dbplyr

Lo que hemos visto hasta ahora es muy parecido a dplyr que es el conjuto de verbos para la gestión de matrices de datos que vimos en la introducción a R. No es una sorpresa. La idea detrás de dplyr es la de incorporar a R un lenguaje similar a SQL. De hecho, una extensión de dplyr nos permite usar los mismos verbos pero con una base de datos como backend. Es decir, podríamos ejecutar la mayoría de las instrucciones anteriores pero usando dbplyr. Para ello, lo único que tenemos hacer es establecer una conexión y declarar en qué tabla queremos trabajar.

library(dbplyr)
cis_db <- tbl(con, "cis")

Podríamos usar la misma query de antes directamente desde R:

res <- cis_db %>%
    group_by(pid, pastvote) %>%
    filter(!is.na(pid) & !is.na(pastvote)) %>%
    summarize(mean_ideology=mean(ideology, na.rm=TRUE))
res

Para ejecutar la búsqueda y crear una copia usaremos collect

res %>% collect()    

Podemos ver el statement en SQL

res %>% show_query()