R and SQL (Part 2)

Example 1.1a:

Say we want to list the name of every employee from the employees table who has one or more customers recorded in the customers table.

This is an example of an inner join, as we only want records that have a match in both tables. In this case our keys - the columns that we will be matching - are the employeeNumber in the employees table and the salesRepEmployeeNumber in the customers table.

We would like the code to return the following information:

• employee number
• employee first name
• employee second name
• customer name(s)

With SQL:

To do this in SQL we would use INNER JOIN, followed by the name of the table we are joining to. We then use ON to specify the names of the columns (keys) that are being used to match the data in the two tables:

SELECT 
  employees.employeeNumber, 
  employees.lastName, 
  employees.firstName, 
  customers.customerName
FROM   
  employees 
  INNER JOIN 
  customers 
    ON employees.employeeNumber = customers.salesRepEmployeeNumber
ORDER BY 
  lastName, customerName

Note that we’ve specified the table from which each column is taken; for example, using employees.employeeNumber rather than just employeeNumber. This is necessary in cases where the same column name appears in more than one table. In this particular example, we could have written the code without the table prefixes, but they have been included for illustrative purposes.

With R:

The same join could be carried out in R using the inner_join function. This includes the by argument (equivalent to SQL’s ON), in which we list the names of the keys:

employees %>%   #left-hand table
  #after 'by' we list the key from the left-hand table first:
  inner_join(customers, by = c("employeeNumber" = "salesRepEmployeeNumber")) %>%
  select(employeeNumber, lastName, firstName, customerName) %>% 
  arrange(lastName, customerName)

Both the SQL and R code above returns the following 100 records. Note that each employee’s name appears more than once if they have more than one customer.

Example 1.1b:

Say that we wanted to list every customer from the customers table who has one or more orders in the orders table.

This is another example of an inner join, this time matching records using the ‘customerNumber’ column as our key, which is the same in both tables.

We want the query to return the following information:

• customer number
• customer name
• order number
• current order status

With SQL:

The fact that the same column name is used in both tables means we can simplify our code with USING instead of ON, as follows:

SELECT 
  c.customerNumber, 
  c.customerName, 
  o.orderNumber, 
  o.status
FROM
  customers AS c 
  INNER JOIN 
  orders AS o
    USING (customerNumber)
ORDER BY
  customerName

This simplifies the code as we only need to list one column after USING.

In the example above, we’ve also used aliases in place of the full table names, which further simplifies the code. This has assigned the short name ‘c’ to the ‘customers’ table and ‘o’ to the ‘orders’ table. This is done with the keyword AS, though even this could be excluded. Aliases makes the code shorter and easier to read.

With R:

The equivalent R code is shown below. Note that we now only need to specify the one column name in the by argument.

customers %>% 
  inner_join(orders, by = "customerNumber") %>%
  select(customerNumber, customerName, orderNumber, status) %>% 
  arrange(customerName)

This code returns 326 rows, the first 50 of which are shown below: (Top)

---

Example 1.2:

In the example above we wanted to see the names customers in the customers table who had a corresponding order in the orders table. But what if we wanted to see names of all customers and their order information, even if they don’t have any orders?

This is an example of a left join. We can do this using LEFT JOIN in SQL and left_join in R, as follows:

With SQL:

SELECT 
  c.customerNumber, 
  c.customerName, 
  o.orderNumber, 
  o.status
FROM  
  customers AS c
  LEFT JOIN 
  orders AS o
    ON c.customerNumber = o.customerNumber
ORDER BY customerName

With R:

customers %>% 
  left_join(orders, by = "customerNumber") %>% 
  select(customerNumber, customerName, orderNumber, status) %>% 
  arrange(customerName)

This returns 350 records, the first 50 of which are shown in the table below. If you scroll down, you will see that some customers have ‘NA’ for orderNumber and status (this will appear as ‘NULL’ in MySQL). These are customers who had no corresponding orders in the orders table. Note that each customer will appear more than once if they have more than one order.

(Top)

---

Example 1.4:

Say we want to list the name of every employee from the employees table, and every customer from the customers table. Where possible, we want to match the name of each employee to his/her customers. However we also want to return the names of all employees without customers, and the names of all customers without an associated employee. This is an example of a full join.

Assume that we want our query to return:

• each employee’s last name
• each employee’s first name
• each customer’s name

With SQL:

To simulate a full join in SQL, we are going to use a LEFT JOIN, a RIGHT JOIN and the UNION of the two, as follows:

SELECT 
  e.lastName, e.firstName, c.customerName 
FROM 
  employees AS e
  LEFT JOIN 
  customers AS c 
    ON e.employeeNumber = c.salesRepEmployeeNumber

UNION

SELECT 
  e.lastName, e.firstName, c.customerName 
FROM 
  employees AS e
  RIGHT JOIN 
  customers AS c 
    ON e.employeeNumber = c.salesRepEmployeeNumber

With R:

In R, the syntax is much simpler as we can use full_join:

employees %>% 
  full_join(customers, by = c('employeeNumber' = 'salesRepEmployeeNumber')) %>% 
  select(lastName, firstName, customerName)

The two sets of code above return the following 130 records. As expected, this shows the name of each employee matched to their customer(s), where such a match exists, but also the names of each employee and customer even if there is no match:

(Top)

---

Example A

Say we want to find the total value (in $) of each order in our sample data set.

To do this, we need to (1) calculate the value of each product in every order, i.e. the quantity times the price; and (2) group each product sold by its order number and add these values together.

With SQL:

In SQL we group the data using the GROUP BY clause, and specify our aggregate calculation in the SELECT clause, as follows:

SELECT 
  orderNumber,
  SUM(quantityOrdered * priceEach) AS value
FROM 
  orderdetails
GROUP BY 
  orderNumber

With R:

In R, we would use group_by() to group the data, and summarize() to apply the aggregate function to each group:

orderdetails %>% 
  group_by(orderNumber) %>% 
  summarize(value = sum(quantityOrdered * priceEach)) %>% 
  select(orderNumber, value)

The first 10 records (out of 326) are shown in the table below:

## `summarise()` ungrouping output (override with `.groups` argument)

orderNumber	value
10100	10223.83
10101	10549.01
10102	5494.78
10103	50218.95
10104	40206.20
10105	53959.21
10106	52151.81
10107	22292.62
10108	51001.22
10109	25833.14

Example B

We can also group using an expression rather than values that already exist in a column.

For example, say we wanted to find the total value of orders in each year, where the order has been shipped.

To do this, we need to do a combination of the following:

• Join the orders table, which has the date of each order, with the orderdetails table, which contains information on the quantity and price of the products ordered
• Use the YEAR() function in SQL, and year() in R (using the lubridate package), which the returns the year corresponding to each order date. This is will be used to group the data
• Filter the data to only include orders whose status is ‘shipped’.

This can be done in the following code:

With SQL:

SELECT 
  YEAR(orderDate) AS year,
  SUM(quantityOrdered * priceEach) AS value
FROM
  orders
  INNER JOIN
  orderdetails 
    USING (orderNumber)
WHERE
  status = 'Shipped'
GROUP BY 
  YEAR(orderDate)

With R:

orders %>% 
  inner_join(orderdetails, by = 'orderNumber') %>%
  filter(status == 'Shipped') %>% 
  group_by(year = lubridate::year(orderDate)) %>%
  summarize(value = sum(quantityOrdered * priceEach))

Which gives the total value of shipped orders for the three years:

## `summarise()` ungrouping output (override with `.groups` argument)

year	value
2003	3223096
2004	4300603
2005	1341396

(Top)

---

Example A

Say we want to show all order numbers in which more than 650 individual items were ordered. This requires us to group by order number (orderNumber) from the orderdetails table, and to filter the results based on the total quantity of products in each order.

With SQL:

In SQL we apply our filter using the HAVING clause, as follows:

SELECT 
  orderNumber, 
  sum(quantityOrdered) as quantity
FROM
  orderdetails
GROUP BY 
  orderNumber
HAVING 
  sum(quantityOrdered) > 650
ORDER BY 
  quantity DESC

With R:

With R, we use the filter() function:

orderdetails %>% 
  group_by(orderNumber) %>% 
  summarize(quantity = sum(quantityOrdered)) %>% 
  filter(quantity > 650) %>% 
  arrange(desc(quantity)) 

This gives the following three orders with more than 650 items:

## `summarise()` ungrouping output (override with `.groups` argument)

orderNumber	quantity
10222	717
10106	675
10165	670

Example B

Assume we want to find customers with an average order value over $3,800, for orders that have been shipped.

This example is slightly more complex as we need to join three tables, as follows:

With SQL:

SELECT 
  a.customerName,
  b.status,
  avg(c.quantityOrdered * c.priceEach) as avg_value
FROM
  customers a
  INNER JOIN
  orders b 
    USING (customerNumber)
  INNER JOIN
  orderdetails c 
    USING (orderNumber)
GROUP BY 
  customerName, status
HAVING 
  avg_value > 3800 AND status = 'Shipped'
ORDER BY 
  avg_value DESC

Note that in MySQL we can use the alias for average value (avg_value) in the HAVING clause, which was defined in the SELECT clause. Apparently not all versions of SQL support the use of aliases in the HAVING clause.

With R:

customers %>% 
  left_join(orders, by = 'customerNumber') %>% 
  left_join(orderdetails, by = 'orderNumber') %>% 
  group_by(customerName, status) %>% 
  summarize(avg_value = mean(quantityOrdered * priceEach)) %>% 
  filter(avg_value > 3800 & status == 'Shipped') %>% 
  arrange(desc(avg_value))

We find that the following five customers have average shipped order sizes that are over $3,800:

## `summarise()` regrouping output by 'customerName' (override with `.groups` argument)

customerName	status	avg_value
Tekni Collectables Inc.	Shipped	4253.501
Super Scale Inc.	Shipped	4139.921
UK Collectables, Ltd.	Shipped	4077.812
Mini Caravy	Shipped	3992.596
Gift Depot Inc.	Shipped	3816.985

(Top)

---