The Northwind database has some very interesting time-sensitive data. For that reason, it is worth learning to use dates.
Julianday turns a date into the number of days since noon on Monday, January 1, 4713 BC. The explanation why is sort of complicated…but being able to do it sure makes things easier! This query calculates the ages of the company’s employees (in years). Notice how I divide the calculated julianday by 365.
SELECT Lastname, FirstName, (julianday(CURRENT_DATE) - julianday(Birthdate))/365 as age
FROM employees
ORDER BY age| LastName | FirstName | age |
|---|---|---|
| Dodsworth | Anne | 40.53151 |
| Leverling | Janet | 42.94521 |
| Suyama | Michael | 43.10685 |
| King | Robert | 46.20000 |
| Callahan | Laura | 48.58630 |
| Buchanan | Steven | 51.44110 |
| Fuller | Andrew | 54.47945 |
| Davolio | Nancy | 57.67945 |
| Peacock | Margaret | 68.90685 |
You can manipulate the data type of a field in the Database using the CAST function. The age by default is a “double”, or long decimal number. We can turn it into an integer using CAST if we don’t want the decimals.
SELECT Lastname, FirstName, Birthdate,
CAST((julianday(CURRENT_DATE) - julianday(Birthdate))/365 as int) as age
FROM employees
ORDER BY age| LastName | FirstName | BirthDate | age |
|---|---|---|---|
| Dodsworth | Anne | 1986-01-27 | 40 |
| Leverling | Janet | 1983-08-30 | 42 |
| Suyama | Michael | 1983-07-02 | 43 |
| King | Robert | 1980-05-29 | 46 |
| Callahan | Laura | 1978-01-09 | 48 |
| Buchanan | Steven | 1975-03-04 | 51 |
| Fuller | Andrew | 1972-02-19 | 54 |
| Davolio | Nancy | 1968-12-08 | 57 |
| Peacock | Margaret | 1957-09-19 | 68 |
CAST() is also very useful if you ever get a number that for whatever reason has been stored as text. You may have encountered this in Excel in the past.
You can also parse out pieces of the date with the strftime() function. strfttime() has little codes (eg. %d) that it uses to identify parts of a “well-formed” date. One nice thing about dates in most databases is that they are usually quite “well-formed”, meaning they have a YYYY-MM-DD format with a four digit year, 2 digit month and 2 digit day. This makes them easy to work with once you declare them to be a date. This query takes the Birthdate and tells SQL that it is a date variable, then breaks it up into its component time units.
SELECT Lastname, FirstName, DATE(Birthdate),
strftime("%d",Birthdate) as day,
strftime("%m",Birthdate) as month,
strftime("%Y",Birthdate) as year
FROM Employees| LastName | FirstName | DATE(Birthdate) | day | month | year |
|---|---|---|---|---|---|
| Davolio | Nancy | 1968-12-08 | 08 | 12 | 1968 |
| Fuller | Andrew | 1972-02-19 | 19 | 02 | 1972 |
| Leverling | Janet | 1983-08-30 | 30 | 08 | 1983 |
| Peacock | Margaret | 1957-09-19 | 19 | 09 | 1957 |
| Buchanan | Steven | 1975-03-04 | 04 | 03 | 1975 |
| Suyama | Michael | 1983-07-02 | 02 | 07 | 1983 |
| King | Robert | 1980-05-29 | 29 | 05 | 1980 |
| Callahan | Laura | 1978-01-09 | 09 | 01 | 1978 |
| Dodsworth | Anne | 1986-01-27 | 27 | 01 | 1986 |
Parsing out pieces of dates, when combined with grouping, allows you to Look at time trends. Here I’m looking at how many orders were shipped with, grouped by year.
SELECT CompanyName, COUNT(*) as order_count,
strftime("%Y",ShippedDate) as year
FROM Orders
INNER JOIN Shippers
ON orders.shipvia = shippers.ShipperID
WHERE CompanyName = "Speedy Express"
GROUP BY year
ORDER BY order_count| CompanyName | order_count | year |
|---|---|---|
| Speedy Express | 4 | NA |
| Speedy Express | 62 | 2025 |
| Speedy Express | 188 | 2012 |
| Speedy Express | 388 | 2014 |
| Speedy Express | 389 | 2015 |
| Speedy Express | 394 | 2024 |
| Speedy Express | 408 | 2013 |
| Speedy Express | 409 | 2023 |
| Speedy Express | 412 | 2022 |
| Speedy Express | 415 | 2021 |
Time trends look great on a geom_line. Here is a line showing the orders from the query above.
SELECT CompanyName, COUNT(*) as order_count,
strftime("%Y",ShippedDate) as year
FROM Orders
INNER JOIN Shippers
ON orders.shipvia = shippers.ShipperID
WHERE CompanyName = "Speedy Express" AND year != "NA"
GROUP BY year
ORDER BY order_countMake sure you understand and can accurately interpret the variables used on a geom_line(). Since this data isn’t filtered to any specific year, this is tracking the stay_length across months for ALL years. So like, when they enter the hospital in october in general, how long do they stay…for example.
#Make special note of the "group = 1" in the aes. You must specify how many groups exist in your data with geom_line().
ggplot(data = myquery,
aes(x = year, y = order_count, group = 1))+
geom_line()
If you have multiple groups, you can show a different line for each group.
SELECT CompanyName, COUNT(*) as order_count,
strftime("%Y",ShippedDate) as year
FROM Orders
INNER JOIN Shippers
ON orders.shipvia = shippers.ShipperID
WHERE CompanyName IN("Speedy Express","United Package") AND year != "NA"
GROUP BY year, CompanyName
ORDER BY order_count#Notice how I use the color aesthetic with CompanyName to get two different lines.
ggplot(data = myquery1,
aes(x = year, y = order_count, group = CompanyName, color = CompanyName)) +
geom_line()
In the first line graph we examined, it looked like there was something interesting that happened from 2017 - 2018. Let’s “drill down” and look at 2017 by month.
SELECT CompanyName, COUNT(*) as order_count,
strftime("%m",ShippedDate) as month,
strftime("%Y",ShippedDate) as year
FROM Orders
INNER JOIN Shippers
ON orders.shipvia = shippers.ShipperID
WHERE CompanyName = "Speedy Express" AND year != "NA"
AND year = "2017"
GROUP BY month
ORDER BY order_count#Make special note of the "group = 1" in the aes. You must specify how many groups exist in your data with geom_line().
ggplot(data = myquery2,
aes(x = month, y = order_count, group = 1))+
geom_line()
Now that’s interesting! Looks like we saw a major spike in orders in the month of June. Let’s drill down one more time, and add a different line for each product category. This is the most advanced query we have done so far. It joins all the way from Shippers to Categories.
SELECT CategoryName, COUNT(*) as order_count,
strftime("%m",ShippedDate) as month,
strftime("%Y",ShippedDate) as year
FROM Orders
INNER JOIN Shippers
ON orders.shipvia = shippers.ShipperID
INNER JOIN "Order Details" od
on orders.orderid = od.OrderID
INNER JOIN Products
ON od.ProductID = Products.ProductID
INNER JOIN Categories
ON products.CategoryID = categories.CategoryID
WHERE CompanyName = "Speedy Express"
AND year != "NA"
AND year = "2017"
GROUP BY month, CategoryName
ORDER BY order_countggplot(data = myquery3,
aes(x = month, y = order_count, group = CategoryName, color = CategoryName))+
geom_line()
Hmmm. There is no obvious clear category that dominates. It looks like it is more of an overall spike in orders that may be related to something else. We’ll have to keep digging! There are lots of interesting ways you can look at trends using the Orders, Order Details and Customers tables.