Working with databases in R

library(DBI)
library(RSQLite)
dbConn = dbConnect(SQLite(), "../database.sqlite")

Our database

dbListTables(dbConn)

## [1] "BoardGames"               "bgg.ldaOut.top.documents"
## [3] "bgg.ldaOut.top.terms"     "bgg.ldaOut.topics"       
## [5] "bgg.topics"

SQL Chunks

Use {sql connection=dbConn}!

select 
`details.name`, `attributes.boardgamecategory`
from boardgames 
limit 10

Terminology

• A database is a collection of data
• A schema is a collection of related data
• A table is a collection of data on a specific event or entity
• A column is data on an attribute of the event or entity
• A row is a unique event or entity
• A key identifies a unique event or row

Keys

• A key is a unique identifier of an entity or row
• A business key is one or more columns that uniquely describe an entity e.g. first name + last name + DOB
• A surrogate key is a numeric column that is easier to reference than the business key
• A primary key is typically the surrogate key

Exercise

1. Create a markdown document and connect to the database.
2. Can you use the dbListColumns() function to find out what columns are in the boardgames table?

Answers

dbListFields(dbConn,"boardgames")

##  [1] "row_names"                               
##  [2] "game.id"                                 
##  [3] "game.type"                               
##  [4] "details.description"                     
##  [5] "details.image"                           
##  [6] "details.maxplayers"                      
##  [7] "details.maxplaytime"                     
##  [8] "details.minage"                          
##  [9] "details.minplayers"                      
## [10] "details.minplaytime"                     
## [11] "details.name"                            
## [12] "details.playingtime"                     
## [13] "details.thumbnail"                       
## [14] "details.yearpublished"                   
## [15] "attributes.boardgameartist"              
## [16] "attributes.boardgamecategory"            
## [17] "attributes.boardgamecompilation"         
## [18] "attributes.boardgamedesigner"            
## [19] "attributes.boardgameexpansion"           
## [20] "attributes.boardgamefamily"              
## [21] "attributes.boardgameimplementation"      
## [22] "attributes.boardgameintegration"         
## [23] "attributes.boardgamemechanic"            
## [24] "attributes.boardgamepublisher"           
## [25] "attributes.total"                        
## [26] "stats.average"                           
## [27] "stats.averageweight"                     
## [28] "stats.bayesaverage"                      
## [29] "stats.family.abstracts.bayesaverage"     
## [30] "stats.family.abstracts.pos"              
## [31] "stats.family.cgs.bayesaverage"           
## [32] "stats.family.cgs.pos"                    
## [33] "stats.family.childrensgames.bayesaverage"
## [34] "stats.family.childrensgames.pos"         
## [35] "stats.family.familygames.bayesaverage"   
## [36] "stats.family.familygames.pos"            
## [37] "stats.family.partygames.bayesaverage"    
## [38] "stats.family.partygames.pos"             
## [39] "stats.family.strategygames.bayesaverage" 
## [40] "stats.family.strategygames.pos"          
## [41] "stats.family.thematic.bayesaverage"      
## [42] "stats.family.thematic.pos"               
## [43] "stats.family.wargames.bayesaverage"      
## [44] "stats.family.wargames.pos"               
## [45] "stats.median"                            
## [46] "stats.numcomments"                       
## [47] "stats.numweights"                        
## [48] "stats.owned"                             
## [49] "stats.stddev"                            
## [50] "stats.subtype.boardgame.bayesaverage"    
## [51] "stats.subtype.boardgame.pos"             
## [52] "stats.trading"                           
## [53] "stats.usersrated"                        
## [54] "stats.wanting"                           
## [55] "stats.wishing"                           
## [56] "polls.language_dependence"               
## [57] "polls.suggested_numplayers.1"            
## [58] "polls.suggested_numplayers.10"           
## [59] "polls.suggested_numplayers.2"            
## [60] "polls.suggested_numplayers.3"            
## [61] "polls.suggested_numplayers.4"            
## [62] "polls.suggested_numplayers.5"            
## [63] "polls.suggested_numplayers.6"            
## [64] "polls.suggested_numplayers.7"            
## [65] "polls.suggested_numplayers.8"            
## [66] "polls.suggested_numplayers.9"            
## [67] "polls.suggested_numplayers.Over"         
## [68] "polls.suggested_playerage"               
## [69] "attributes.t.links.concat.2...."         
## [70] "stats.family.amiga.bayesaverage"         
## [71] "stats.family.amiga.pos"                  
## [72] "stats.family.arcade.bayesaverage"        
## [73] "stats.family.arcade.pos"                 
## [74] "stats.family.atarist.bayesaverage"       
## [75] "stats.family.atarist.pos"                
## [76] "stats.family.commodore64.bayesaverage"   
## [77] "stats.family.commodore64.pos"            
## [78] "stats.subtype.rpgitem.bayesaverage"      
## [79] "stats.subtype.rpgitem.pos"               
## [80] "stats.subtype.videogame.bayesaverage"    
## [81] "stats.subtype.videogame.pos"

The core statement

select *
from boardgames
limit 10

Selecting columns

Use delimiters for bad names!

select 
`details.name`, 
`attributes.boardgamecategory`
from boardgames
limit 10

Selecting rows

select 
`details.name`, 
`attributes.boardgamecategory`
from boardgames
where `attributes.boardgamecategory` like '%nautical%'
limit 10

Selecting rows

select 
`details.name`
,`attributes.boardgamecategory`
,`details.maxplayers`
from boardgames
where `attributes.boardgamecategory` like '%nautical%'
and `details.maxplayers` = 2 
limit 10

Identifying missings

select 
`details.name`
,`attributes.boardgameartist`
from boardgames
where `attributes.boardgameartist` is null
limit 10

Exercise

1. Find all games with a details.minage of 16 or greater
2. Find all records where either the attributes.boardgamedesigner is missing or the attributes.boardgameartist is missing

Answers

select 
`details.name`
,`details.minage`
from boardgames
where `details.minage` >= 16

Answers

select 
 `details.name`
,`attributes.boardgamedesigner`
,`attributes.boardgameartist` 
from boardgames
where `attributes.boardgamedesigner` is null 
or `attributes.boardgameartist` is null

Renaming columns

select 
`details.name` as `name`
from boardgames
limit 10

Calculated columns

select 
`details.name` ,substr(`attributes.boardgamecategory`, 1,10) as short_category
, 1.2 as fixed_colum
, coalesce(`attributes.boardgameartist`,"Unknown") as arttist
,case when `attributes.boardgamecategory` like '%nautical%' then 'Boats, boats, boats!'
when `attributes.boardgamecategory` like '%pirate%' then 'R!' 
else 'Who cares?' end as category
from boardgames
where `attributes.boardgameartist` is null
limit 10

Grouping data

select 
`details.maxplayers` as max_players
from boardgames
where `details.maxplayers` is not null
group by `details.maxplayers`

Aggregates

select 
`details.maxplayers` as max_players
,count(*) as n
,avg(`details.playingtime`) as avg_playtime
,avg(`details.playingtime`)/`details.maxplayers` as pp_avg_playtime
from boardgames
where `details.maxplayers` is not null
group by `details.maxplayers`
limit 10

Exercises

1. How many games received ratings of 0,1,2,…,10 etc based on the `stats.average col? (Hint: Use the floor() function)
2. For records with the game.type of ‘boardgameexpansion’ what is the minimum, mean, and maximum of users who have said they want the expansion (stats.wanted)?

Answers

select 
floor(`stats.average`) as rating
, count(*) as games
from boardgames
group by  floor(`stats.average`)

Answers

select avg(`stats.wishing` ) as avg_want
,min(`stats.wishing`) as min_want
,max(`stats.wishing`) as max_want
from boardgames
where `game.type`='boardgameexpansion'

Combination types - inner join

Use when you need only stuff that matches on both sides

Combination types - left join

Use when you you need to get potentially optional values from another table

Combination types - union (all)

Use when you you need to combine two separate datasets with the same structure

Writing joins

select 
b.`details.name`
,t.`topics(bgg.ldaout)`
from boardgames b
inner join `bgg.ldaOut.topics` t on b.`details.name`=t.row_names
limit 10

Exercises

1. Use a join to return any board games without a topic entry, turn the count of records
2. Can you transform the board game name column so that more entries get joined together? (Hint: Use the replace() function)

Answers

select 
count(*)
from boardgames b
left join `bgg.ldaOut.topics` t on b.`details.name`=t.row_names
where t.row_names is null

Answers

select 
count(*)
from boardgames b
left join `bgg.ldaOut.topics` t on replace(b.`details.name`,' ','.')=t.row_names
where t.row_names is null

Other keywords to learn

• order by
• having
• cross apply
• with

Typical execution order of keywords

1. from
2. joins
3. where
4. group by
5. having
6. select
7. order by
8. limit

Ways to learn SQL

1. Writing it
2. Reading other people’s code
3. Using dbplyr and checking the code it’s written
4. The SQL Cookbook geni.us/sqlcookbook

Q&A

Followup

1. Get the slides itsalocke.com/talks
2. Contact me @theStephLocke steph@itsalocke.com