This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
Last revision Both sides next revision
bdlibre [2017/02/20 14:42]
glaroc [Introduction à la gestion des bases de données avec des logiciels libres]
bdlibre [2017/02/21 16:21]
glaroc [Exercice 5 - Travailler avec plusieurs tables]
Line 523: Line 523:
 WHERE lakes.lake_id=lakes_species.lake_id ​ WHERE lakes.lake_id=lakes_species.lake_id ​
 GROUP BY lakes.lake_id GROUP BY lakes.lake_id
-ORDER BY Num_Species ​DESC LIMIT 20;+ORDER BY num_species ​DESC LIMIT 20;
 </​file>​ </​file>​
Line 949: Line 949:
 lakesqc <- dbGetQuery(con,"​SELECT * FROM lakes WHERE province='​QUEBEC'"​) lakesqc <- dbGetQuery(con,"​SELECT * FROM lakes WHERE province='​QUEBEC'"​)
 hist(lakes$tmean_an) hist(lakes$tmean_an)
 +===== Using the dplyr package=====
 +<file rsplus>
 +src<​-src_postgres(dbname="​workshop",​host="​localhost",​ port="​5432",​user="​your_username",​password="​your_password"​)
 +lakes <- tbl(src, "​lakes"​) # Define lakes table
 +lakes_qc<​-filter(lakes,​ province ​ %=% '​QUEBEC'​) # Select lakes in Quebec
 +prov_tmean<​-summarise(group_by(lakes,​ province), mean(tmean_an)) # Mean annual temperature per province
 +prov_tmean=collect(prov_tmean) # Transfer result to standard R data frame
 +lakes_qc2<​-tbl(src,​ sql("​SELECT * FROM lakes WHERE province='​QUEBEC'"​)) #Perform any SQL statement
 </​file>​ </​file>​