Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
bdlibre [2017/02/20 14:42] glaroc [Introduction à la gestion des bases de données avec des logiciels libres] |
bdlibre [2024/03/11 12:25] (current) qcbs [Ressources utiles] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ======= Introduction à la gestion des bases de données avec des logiciels libres ======= | ======= Introduction à la gestion des bases de données avec des logiciels libres ======= | ||
| - | Atelier offert par [[http://qcbs.ca/fr/ressources/professionnel-de-recherche/professionnels-de-recherche-guillaume-larocque-phd/|Guillaume Larocque]], professionnel de recherche au CSBQ, à l'Université du Québec à Rimouski le 21 février 2017. | + | Atelier offert par [[http://qcbs.ca/fr/ressources/professionnel-de-recherche/professionnels-de-recherche-guillaume-larocque-phd/|Guillaume Larocque]], professionnel de recherche au CSBQ. |
| ===== Presentation ===== | ===== Presentation ===== | ||
| Line 11: | Line 10: | ||
| ===== Ressources utiles ===== | ===== Ressources utiles ===== | ||
| - | * [[http://www.postgresql.org/docs/current/static/reference.html|PostgreSQL 9.6 Reference Manual]] | + | * [[http://www.postgresql.org/docs/current/static/reference.html|PostgreSQL Reference Manual]] |
| * [[http://link.springer.com/book/10.1007%2F978-1-4302-0018-5|Beginning databases with PostgreSQL]] | * [[http://link.springer.com/book/10.1007%2F978-1-4302-0018-5|Beginning databases with PostgreSQL]] | ||
| * [[https://wiki.postgresql.org/wiki/Main_Page|PostgreSQL wiki]] | * [[https://wiki.postgresql.org/wiki/Main_Page|PostgreSQL wiki]] | ||
| Line 523: | Line 522: | ||
| 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 948: | ||
| 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> | ||
