Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision Next 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) | ||
| + | </file> | ||
| + | |||
| + | ===== Using the dplyr package===== | ||
| + | |||
| + | <file rsplus> | ||
| + | library(dplyr) | ||
| + | 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> | ||
