22 dicembre 2009

SQL join in R

Consideriamo un classico esempio di 3 tabelle di un database, ognuna con i rispettivi campi:
  1. acquisti (codFornitore, numeroColli, codProdotto, codQualità)
  2. prodotti (codice, qualità, descrizione)
  3. fornitori (codice, nome)
I fornitori hanno un codice numerico ed un nome. Ogni prodotto ha un codice numerico ed uno di qualità e, per ognuna di queste combinazioni, c'è una descrizione. Infine, gli acquisti prevedono un codice di fornitori, uno di prodotto e di qualità, infine il numero colli acquistati.

In una situazione di questo tipo, è comune eseguire le operazioni di "congiunzione" (appunto, join) tra tabelle. Per ogni comando SQL in un db, esiste un analogo in R mediante la funzione merge. Riporto di seguito degli esempi di comparazione dei due linguaggi, ipotizzando di disporre in R dei relativi dataframe.

Visualizzo gli acquisti assieme al nome del fornitore.

Inner join in SQL:

SELECT a.*, b.nome
FROM acquisti AS a INNER JOIN fornitori AS b
ON a.codFornitore = b.codice;

Inner join in R:

>merge(acquisti,fornitori,by.x="codFornitore",by.y="codice")

Visualizzo gli acquisti assieme a tutti i fornitori, compresi quelli dai quali non ho comprato prodotti.

Right join in SQL:

SELECT a.*, b.nome
FROM Acquisti AS a RIGHT JOIN Fornitori AS b
ON b.codice = a.codFornitore;

Right join in R:

>merge(acquisti,fornitori,by.x="codFornitore",by.y="codice",all.y=T)

Visualizzo gli acquisti assieme alla descrizione del prodotto (descrizione relativa al tipo di prodotto ed alla sua qualità), compresi i prodotti che non sono stati acquistati.

Left join in SQL:

SELECT a.*, b.codFornitore, b.numeroColli
FROM Prodotti as a LEFT JOIN Acquisti as b
ON (a.qualità = b.codQualità) AND (a.codice = b.codProdotto);

Left join in R:

>merge(prodotti,acquisti,by.x=c("codice","qualità"),by.y=c("codProdotto","codQualità"),all.x=T)

Infine, solo per completezza, riporto il codice di una full, ma in tal caso il risultato è analogo alla left.

Full join in SQL:

SELECT a.*, b.codFornitore, b.numeroColli
FROM Prodotti as a FULL JOIN Acquisti as b
ON (a.qualità = b.codQualità) AND (a.codice = b.codProdotto);

Full join in R:

>merge(prodotti,acquisti,by.x=c("codice","qualità"),by.y=c("codProdotto","codQualità"),all.x=T,all.y=T)

Riassumendo, quindi, la logica in R è molto intuitiva, alla pari dell'SQL.
Con by.x e by.y, si determinano i campi su qui eseguire la join, ossia i campi da "legare". Mediante all.x e all.y si stabilisce se left, right o full join.
Per tutte le varianti della funzione, si rimanda all'help di R.