---
title: "SQL: Computations"
author: "Statistical Computing, 36-350"
date: "Wednesday November 16, 2016"
---

Databases versus data frames
===

Data frames in R are tables in database lingo

R jargon | Database jargon
--------|----------------
column  | field
row     | record
dataframe | table
types of the columns | table schema
collection of data frames | database
conditional indexing | `SELECT`, `FROM`, `WHERE`, `HAVING`
`d*ply()` | `GROUP BY`
`order()` | `ORDER BY`

`SELECT`
===

Main tool in the SQL language: `SELECT`, which allows you to perform queries on a particular table in a database. It has the form:

```
SELECT columns or computations
  FROM table
  WHERE condition
  GROUP BY columns
  HAVING condition
  ORDER BY column [ASC | DESC]
  LIMIT offset,count;
```

`WHERE`, `GROUP BY`, `HAVING`, `ORDER BY`, `LIMIT` are all optional. Importantly, as we'll see today, in the first line of `SELECT` we can directly specify computations that we want performed

Examples
===

First, we set up our connection to the baseball database:

```{r}
library(DBI)
library(RSQLite)
drv = dbDriver("SQLite")
con = dbConnect(drv, dbname="baseball.db")
```

(Continued)
===

To calculate the average number of homeruns, and average number of hits:

```{r}
dbGetQuery(con, paste("SELECT AVG(HR), AVG(H)",
                      "FROM Batting"))
```

We can replicate this simple command on an imported data frame:

```{r}
batting = dbReadTable(con, "Batting") 
mean(batting$HR, na.rm=TRUE)
mean(batting$H, na.rm=TRUE)
```

`GROUP BY`
===

We can use the `GROUP BY` option in `SELECT` to define aggregation groups

(Note: the order of commands here matters; try switching the order of `GROUP BY` and `ORDER BY` below, and you'll get an error)

```{r}
dbGetQuery(con, paste("SELECT playerID, AVG(HR)",
                      "FROM Batting",
                      "GROUP BY playerID",
                      "ORDER BY AVG(HR) DESC",
                      "LIMIT 5"))
```

`AS`
===

We can use `AS` in the first line of `SELECT` to rename computed columns 

```{r}
dbGetQuery(con, paste("SELECT yearID, AVG(HR) as avgHR",
                      "FROM Batting",
                      "GROUP BY yearID",
                      "ORDER BY avgHR DESC",
                      "LIMIT 5"))
```

`WHERE`
===

We can use the `WHERE` option in `SELECT` to specify a subset of the rows to use (pre-aggregation/pre-calculation)

```{r}
dbGetQuery(con, paste("SELECT yearID, AVG(HR) as avgHR",
                      "FROM Batting",
                      "WHERE yearID >= 1990",
                      "GROUP BY yearID",
                      "ORDER BY avgHR DESC",
                      "LIMIT 5"))
```

`HAVING`
===

We can use the `HAVING` option in `SELECT` to specify a subset of the rows to display (post-aggregation/post-calculation)

```{r}
dbGetQuery(con, paste("SELECT yearID, AVG(HR) as avgHR",
                      "FROM Batting",
                      "WHERE yearID >= 1990",
                      "GROUP BY yearID",
                      "HAVING avgHR >= 4",
                      "ORDER BY avgHR DESC"))
```