Traffic Stops

Consider a database of information from Stanford Open Policing Project on over 200 million traffic stops in the US.

Establishing a SQL connection

con_traffic <- DBI::dbConnect(
  RMariaDB::MariaDB(),
  dbname = "traffic",
  host = Sys.getenv("TRAFFIC_HOST"),
  user = Sys.getenv("TRAFFIC_USER"),
  password = Sys.getenv("TRAFFIC_PWD")
)

Parts of a SQL table

• Field is the name of the variable
• Type is the type of the variable (numeric, character, etc.)
• Null indicates whether or not NULL values are acceptable. NULL values are never allowed for a Primary Key.
• Key designates whether the variable is a Primary Key. PRI is a primary key, all unique, no NULL values. UNI is a unique key, but NULL values are allowed. MUL is a non-unique (“Multiple”) key, allowing for duplicates, can speed up queries.
• Default provides the default value of the variable if no value is provided when a row is added to the database.

Order of clauses

Queries in SQL start with the SELECT keyword and consist of several clauses, which must be written in the following order:

The clauses are similar to data wrangling verbs in R, but the order in SQL is super important!

SELECT … FROM

• start with a SELECT, include a corresponding FROM
• columns may be specified or * returns every column

The shortest SQL query is the following SELECT command. Do not run this command!!! The ca_los_angeles_2020_04_01 table has 5 million rows, and we do not want to look at them (print to screen) simultaneously.

DO NOT RUN:  SELECT * FROM ca_los_angeles_2020_04_01;

SELECT … FROM

How do we know how many traffic stops are in the database? Two different ways of counting the rows (that produce the same number):

SELECT COUNT(*), SUM(1) FROM ca_los_angeles_2020_04_01 LIMIT 0, 10;

More than 5.4 million LAPD traffic stops.

SELECT … FROM

Let’s look at New Orleans, LA

DESCRIBE la_new_orleans_2020_04_01;

SELECT … FROM

How old was the car being driven?

Unfortunately, date is saved as a date variable, and vehicle_year is saved as a double. Fortunately, we can convert vehicle_year to a date variable.

SELECT
      date, vehicle_year,
      STR_TO_DATE(CONCAT(vehicle_year, '-01-01'), '%Y-%m-%d') AS vehicle_date
   FROM la_new_orleans_2020_04_01
   LIMIT 0, 10;

SELECT … FROM

Why can’t we find the difference between the two time variables?

SELECT
      date, vehicle_year,
      STR_TO_DATE(CONCAT(vehicle_year, '-01-01'), '%Y-%m-%d') AS vehicle_date
      TIMESTAMPDIFF(YEAR, vehicle_date, date) AS car_age
   FROM la_new_orleans_2020_04_01
   LIMIT 0, 10;

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMPDIFF(YEAR, vehicle_date, date) AS car_age
   FROM la_new_orleans_2020_0' at line 4 [1064]

Important note:

There is a distinction between clauses that operate on the variables of the original table versus those that operate on the variables of the results set.

date and vehicle_year are columns in the original table - they are written to disk on the SQL server.

car_age exist only in the results set, which is passed from the server (SQL server) to the client (e.g., RStudio or DBeaver) in your computer’s memory and is not written to disk.

WHERE

The WHERE clause is analogous to the filter() function in dplyr. However, keep in mind that there are two SQL commands that resemble the dplyr filter() function. WHERE operates on the original data in the table and HAVING operates on the result set.

WHERE

BETWEEN can be used to specify a range of values for a numeric value. BETWEEN is inclusive.

SELECT time, search_conducted, vehicle_year, vehicle_make
FROM la_new_orleans_2020_04_01
WHERE vehicle_year BETWEEN 1970 and 1999
   AND vehicle_make = "Subaru"
LIMIT 0, 10;

WHERE

IN is similar to the dplyr %in% function which specifies distinct values for the variable.

SELECT time, search_conducted, vehicle_year, vehicle_make
FROM la_new_orleans_2020_04_01
WHERE vehicle_make IN ("Subaru", "Dodge")
   AND search_conducted = 1
LIMIT 0, 10;

WHERE

AND takes precedent over OR in the order of operations, when there are no parentheses.

SELECT time, search_conducted, vehicle_year, vehicle_make
FROM la_new_orleans_2020_04_01
WHERE vehicle_year BETWEEN 1970 and 1999 OR
      vehicle_make IN ("Subaru", "Dodge") AND 
      search_conducted = 1
LIMIT 0, 10;

same as:

SELECT time, search_conducted, vehicle_year, vehicle_make
FROM la_new_orleans_2020_04_01
WHERE vehicle_year BETWEEN 1970 and 1999 OR
      (vehicle_make IN ("Subaru", "Dodge") AND 
      search_conducted = 1)
LIMIT 0, 10;

WHERE

Parentheses take precedent over AND and OR.

SELECT time, search_conducted, vehicle_year, vehicle_make
FROM la_new_orleans_2020_04_01
WHERE (vehicle_year BETWEEN 1970 and 1999 OR
      vehicle_make IN ("Subaru", "Dodge") ) AND 
      search_conducted = 1
LIMIT 0, 10;

WHERE

IS NULL not = NULL (because NULL indicates unknown). (Just like how we need to use is.na() in R!)

SELECT time, search_conducted, vehicle_year, vehicle_make
FROM la_new_orleans_2020_04_01
WHERE vehicle_make IS NULL
LIMIT 0, 10;

SELECT time, search_conducted, vehicle_year, vehicle_make
FROM la_new_orleans_2020_04_01
WHERE vehicle_make = NULL
LIMIT 0, 10;

A NULL example

Consider the following table and SQL query to evaluate WHERE age <= 20 OR num_dogs = 3:

SELECT * FROM (
   SELECT 'Ace' AS name, 20 AS age, 4 as num_dogs
   UNION
   SELECT 'Ada' AS name, NULL AS age, 3 as num_dogs   
   UNION
   SELECT 'Ben' AS name, NULL AS age, NULL as num_dogs
   UNION
   SELECT 'Cho' AS name, 27 AS age, NULL as num_dogs
   ) AS temptable;

A NULL example

Where does the WHERE clause do? It tells us that we only want to keep the rows satisfying the age <= 20 OR num_dogs = 3. Let’s consider each row one at a time:

• For Ace, age <= 20 evaluates to TRUE so the claim is satisfied.
• For Ada, age <= 20 evaluates to NULL but num_dogs = 3 evaluates to TRUE so the claim is satisfied.
• For Ben, age <= 20 evaluates to NULL and num_dogs = 3 evaluates to NULL so the overall expression is NULL which has a FALSE value.
• For Cho, age <= 20 evaluates to FALSE and num_dogs = 3 evaluates to NULL so the overall expression evaluates to NULL (because it depends on the value of the NULL).

Thus we keep only Ace and Ada.

A NULL example

SELECT * FROM (
   SELECT 'Ace' AS name, 20 AS age, 4 as num_dogs
   UNION
   SELECT 'Ada' AS name, NULL AS age, 3 as num_dogs   
   UNION
   SELECT 'Ben' AS name, NULL AS age, NULL as num_dogs
   UNION
   SELECT 'Cho' AS name, 27 AS age, NULL as num_dogs
   ) AS temptable
WHERE age <= 20 OR num_dogs = 3;

GROUP BY

For those people whose cars are between 1970 and 1999, how old is the youngest and oldest person for each subject_race?

SELECT subject_race,
       COUNT(*) AS num_stops, 
       MIN(subject_age) AS min_age,
       MAX(subject_age) AS max_age
FROM la_new_orleans_2020_04_01
WHERE vehicle_year BETWEEN 1970 and 1999
GROUP BY subject_race;

ORDER BY

We are able to GROUP BY and ORDER BY on the new variables we had created, wday.

HAVING

Recall that WHERE acts only on the original data. If we are interested in traffic stops that happened on Friday, we need to use the derived variable wday instead of the raw variable date. Fortunately, HAVING works on the results set.

HAVING

SELECT subject_race,
       COUNT(*) AS num_stops, 
       MIN(subject_age) AS min_age,
       MAX(subject_age) AS max_age,
       DAYNAME(date) AS wday
FROM la_new_orleans_2020_04_01
GROUP BY subject_race, wday
HAVING wday = 'Friday';

HAVING

While it worked out quite well for us that HAVING was able to filter the data based on the results set, the use of HAVING was quite onerous because the entire data set was considered before the filter was applied. That is, if the filter can be done on the original data using WHERE, the query will be much faster and more efficient.

Note: HAVING requires a GROUP BY clause. And the variable(s) used in HAVING must also be part of the GROUP BY clause.

Good practice

Always a good idea to terminate the SQL connection when you are done with it.

dbDisconnect(con_traffic, shutdown = TRUE)