Correspondances entre SQL et R

Je ne sais pas si vous avez remarqué, mais le langage R commence à apparaître vraiment partout dans l’écosystème Microsoft depuis le rachat de Revolution Analytics : Power BI Desktop, SQL Server 2016, Azure ML…

Si vous êtes consultant BI, Data Analyste… plutôt à l’aise avec le SQL, et que vous vous intéressez au R, je vous propose une petite table de correspondances de syntaxe entre ces 2 langages pour quelques actions élémentaires.

Prérequis pour mieux comprendre ce tableau, la colonne “R” du tableau traite des objets de type “dataframe”. Le “dataframe” est un des formats de stockage de données dans R. Si vous ne savez pas du tout ce que c’est, je vous conseille l’article suivant : http://ww2.coastal.edu/kingw/statistics/R-tutorials/dataframes.html

Voici le tableau de correspondances :

Action

SQL

R

Afficher toutes les lignes et colonnes de la table

select * from tb

tb

Affectation, stocker un résultat dans un objet

select * into tb2 from tb1

tb2 <- tb1

Filtrer des lignes en fonction de valeurs de colonnes

select * from tb where col1 = 1 and col2 is not null or col3 <> 3

tb[tb$col1 == 1 & !is.na(tb$col2) | tb$col3 != 3,]

Filtrer les 5 premières lignes

select top 5 * from tb

tb[1:5,]

Afficher certaines colonnes de la table

select col1, col2 from tb

tb[,c('col1','col2')]

Regrouper des lignes en agrégeant une colonne

select AVG(col1) from tb group by col2

aggregate(col1, by=list(col2), FUN=mean)

Compter le nombre de lignes par regroupement

select count(*) from tb group by col

data.frame(table(tb$col))

Faire un union de 2 tables (ajouter les lignes des 2 tables ensemble)

select col1, col2 from tb1 union all select col1, col2 from tb2

rbind(tb1,tb2)

Faire une jointure interne

select * from tb1 inner join tb2 on tb1.pktb1 = tb2.fktb2

merge(tb1, tb2, by.x = "pktb1", by.y = "fktb2")

Faire une jointure externe gauche

select * from tb1 left join tb2 on tb1.pktb1 = tb2.fktb2

merge(tb1, tb2, by.x = "pktb1", by.y = "fktb2",all.x = TRUE)

Concaténer des chaines de caractères

select 'hello' + ' world'

paste0("hello", " world")

Conversion de texte en date

select convert(date,'2015-03-27', 120)

as.Date("2015-03-27")

Conversion de texte en datetime

select convert(datetime,'2015-03-27 01:30:00', 120)

as.POSIXct(strptime("2015-03-27 01:30:00", "%Y-%m-%d %H:%M:%S"))

Supprimer une table

drop table tb

rm('tb')

Une autre solution est d’utiliser le package R sqldf qui permet de requêter des objets R en SQL, par exemple :

install.packages("sqldf")
library(sqldf)
sqldf("select * from iris")

https://cran.r-project.org/web/packages/sqldf/index.html

http://www.r-bloggers.com/make-r-speak-sql-with-sqldf/

Il existe bien sûr plusieurs façons de faire toutes ces actions en R ou en SQL, et je vous conseille de pousser vos recherches en regardant par exemple les liens suivants :

http://www.statmethods.net/

https://stat.ethz.ch/R-manual/

FADATA

Fabien Adato est Consultant Data et BI chez AZEO. Après avoir intégré la société CGI Business Consulting où il rejoint une équipe dédiée à la Business Intelligence, il fait ses premières armes sur la solution Microsoft SQL Server. Pendant plus de 4 ans, il acquiert des compétences techniques et fonctionnelles sur toute la chaîne de valeur de la BI (ETL, Base de Données, Cube Olap et Rapport). Il a pu se spécialiser également sur de nouvelles technologies de la Data et notamment Hadoop, Pig et Hive, ainsi que des technologies self-service BI, le No-SQL comme la base de données MongoDB et le moteur d’indexation ElasticSearch. Il entre chez AZEO en 2014 pour y assurer le poste de Consultant DATA / BI, autour des technologies Microsoft SQL Server, Power BI et Azure Cortana Intelligence.

R, SQL, SQLDF

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *