MySQL:n integroiminen R:ään

Tässä tekstissä esittelen, kuinka R:n ja MySQL:n saa yhdistettyä dbConnect-pakkauksen avulla. Yhdistämisen jälkeen voimme hakea tietoa R:llä suoraan MySQL:sta SQL-komennoin. Ensimmäinen vaihe on MySQL:n asentaminen koneelle, jonka jälkeen voidaan R:stä käsin ottaa yhteys ohjelmaan.

Alla on scripti, jolla yhteys muodostetaan. Huom. sqltk-kohdan käyttäjätiedot luodaan MySQL:n asennusvaiheessa.

R-koodi:

install.packages("dbConnect")  # Asennetaan tarvittava pakkaus

library(dbConnect)

library(RMySQL)

sqltk = dbConnect(MySQL(), user='käyttäjänimi', password='salasana', dbname='tietokannan nimi', host='host-nimi') # Haetaan MySQL:sta haluttu tietokanta

dbListTables(sqltk) # Näyttää haetussa tietokannassa olevat taulut

Seuraavaksi luon MySQL:ssa pienen tietokannan, josta voidaan hakea tietoa luodun yhteyden avulla. 

MySQL-koodi: 

create database datamyynnit;

use datamyynnit;

create table myyja(MyyjaID int(10) unsigned auto_increment primary key not null, 

Kaupunki varchar(30) not null, Puhelinnumero varchar(20) not null);

create table myynnit(MyyjaID int(10) not NULL, tilausnumero int(10) unsigned primary key not null, 

maara int(10) not null, tuote varchar(30) not null,hinta int(10) not null, pvm date not null);

insert into myyja (Kaupunki,Puhelinnumero)  values("Espoo","034343434");

insert into myyja (Kaupunki,Puhelinnumero)  values("Helsinki","042004332");

insert into myyja (Kaupunki, Puhelinnumero) values("Espoo","034363555");

insert into myyja (Kaupunki, Puhelinnumero) values("Helsinki","045545555");

insert into myynnit (MyyjaID,tilausnumero,maara,tuote,hinta,pvm) values("2","15","1","tuote1","900","2015-10-10");

insert into myynnit (MyyjaID,tilausnumero,maara,tuote,hinta,pvm) values("1","16","1","tuote2","800","2015-10-15");

insert into myynnit (MyyjaID,tilausnumero,maara,tuote,hinta,pvm) values("1","17","1","tuote1","900","2015-10-15");

insert into myynnit (MyyjaID,tilausnumero,maara,tuote,hinta,pvm) values("3","18","1","tuote2","800","2015-10-28");

insert into myynnit (MyyjaID,tilausnumero,maara,tuote,hinta,pvm) values("3","19","2","tuote1","900","2015-10-26");

insert into myynnit (MyyjaID,tilausnumero,maara,tuote,hinta,pvm) values("4","20","2","tuote1","900","2015-10-25");

Nyt otetaan yhteys tähän tietokantaan R:llä ja haetaan dataa SQL-komennolla.

R-koodi: 

sqltk = dbConnect(MySQL(), user='root', password='1234', dbname='datamyynnit', host='host')

myynnit <- dbGetQuery(sqltk,"select myyja.myyjaID,myyja.kaupunki,myynnit.maara,myynnit.tuote,myynnit.hinta,myynnit.pvm from myyja,myynnit where myyja.myyjaid=myynnit.myyjaid order by myyja.myyjaid")

print(myynnit)

myyjaID kaupunki maara  tuote hinta  pvm

       1      Espoo     1 tuote1   900 2015-10-15

       1      Espoo     1 tuote2   800 2015-10-15

       2  Helsinki     1 tuote1   900 2015-10-10

       3      Espoo     2 tuote1   900 2015-10-26

       3      Espoo     1 tuote2   800 2015-10-28

       4  Helsinki     2 tuote1   900 2015-10-25

Yllä on siis poimittu data. Tätä dataa voi nyt käsitellä normaalisti R:ssä, koska se on perinteisessä dataframe-muodossa. Tarkastellaan seuraavaksi dataa hieman: Luodaan uusi sarake, jossa lasketaan kullekin riville määrä*hinta. Tämä  sarake on siis myyjä-/päivä-/ ja tuotekohtainen kokonaismyynti. Esim. myyjä 1 on myynyt 15.10.2015 yhden kappaleen tuotetta 1, joten hänen sarakearvonsa on 900. 

myynnit$kokonaismyynti <-myynnit$maara*myynnit$hinta

Ratkaistaan  myyjäkohtaiset kokonaismyynnit käyttäen äsken luotua saraketta.

tapply(myynnit$kokonaismyynti,myynnit$myyjaID,sum)

  1    2    3    4 

1700  900 2600 1800 

Ratkaistaan myyjäkohtaiset kokonaismyynnit tuotteelle 1

tapply(myynnit$kokonaismyynti[myynnit$tuote=="tuote1"],myynnit$myyjaID[myynnit$tuote=="tuote1"],sum)

  1    2    3    4 

 900  900 1800 1800 

Ratkaistaan myyjäkohtaiset tuotteen 1 kokonaismyynnit Espoossa ennen 28.10.2015

myynnit$pvm <- as.Date(myynnit$pvm,format="%Y-%m-%d")

tapply(myynnit$kokonaismyynti[myynnit$tuote=="tuote1" & myynnit$kaupunki=="Espoo"& myynnit$pvm <= "2015-10-28" ],myynnit$myyjaID[myynnit$tuote=="tuote1" & myynnit$kaupunki=="Espoo "& myynnit$pvm <= "2015-10-28" ],sum)

 1    3 

 900 1800 

Katsotaan lopuksi vielä myyjien kokonaismyynnit jokaiselle datassa esiintyvälle päivälle

for (i in factor(unique(myynnit$pvm))){

print(paste(c("Myyjä1","Myyjä2","Myyjä3","Myyjä4"),"pvm",i,tapply(myynnit$kokonaismyynti[myynnit$pvm==i],myynnit$myyja[myynnit$pvm==i],sum),"€"

))

}

"Myyjä1 pvm 2015-10-10 NA €"  "Myyjä2 pvm 2015-10-10 900 €"
[3] "Myyjä3 pvm 2015-10-10 NA €"  "Myyjä4 pvm 2015-10-10 NA €" 
[1] "Myyjä1 pvm 2015-10-15 1700 €" "Myyjä2 pvm 2015-10-15 NA €"  
[3] "Myyjä3 pvm 2015-10-15 NA €"   "Myyjä4 pvm 2015-10-15 NA €"  
[1] "Myyjä1 pvm 2015-10-25 NA €"   "Myyjä2 pvm 2015-10-25 NA €"  
[3] "Myyjä3 pvm 2015-10-25 NA €"   "Myyjä4 pvm 2015-10-25 1800 €"
[1] "Myyjä1 pvm 2015-10-26 NA €"   "Myyjä2 pvm 2015-10-26 NA €"  
[3] "Myyjä3 pvm 2015-10-26 1800 €" "Myyjä4 pvm 2015-10-26 NA €"  
[1] "Myyjä1 pvm 2015-10-28 NA €"  "Myyjä2 pvm 2015-10-28 NA €" 
[3] "Myyjä3 pvm 2015-10-28 800 €" "Myyjä4 pvm 2015-10-28 NA €"