SQL Part 2 - Counting and More

In the previous section, I only showed how to pull user-level data from the tables in question, which people usually aren't interested in because there are generally too many users for you to care about their individual actions. Suppose we wanted to know how many logins to WoW there were on January 1st. The query for this would be

SELECT
     count(*)
FROM logins
WHERE
     game_id=1 AND login_time::date='2013-1-1'1;

The count(*)2 function gives the number of rows that meet the criterion, and therefore (in this case) return one record with one column, with the value there being the count. You don't have to put a * in the parenthesis, you can also put a field in there. If we had put count(user_id) it would have counted the number of rows where user_id isn't NULL. NULL is a special value a field may have that means that no information was supplied. In this case, it would return the same result, since every login record should contain a user_id.

The above example is a bit silly, since if someone logged in multiple times on January 1st, they would have more than one record in logins on that date. Chances are we want a count of the distinct users that logged in on that particular date, which we would code as

SELECT
     count(distinct user_id)
FROM logins
WHERE
     game_id=1 AND login_time::date='2013-1-1';

Adding the distinct modifier inside the count() will have it count how many different user_ids appear in the selection. Combining all of this, we can figuring how many users logged into WoW on January 1st and how many times they logged in on that day, on average, as

SELECT
     count(distinct user_id) as "users",
     count(*)/count(distinct user_id) as "logins/user"
FROM logins
WHERE
     game_id=1 AND login_time::date='2013-1-1';

The first column will tell us how many people logged in on January 1st and the second column tells us the average number of times those users logged in on that date. It does so by taking the total number of logins and dividing by how many people did those logins. The 'as "X"' parts give our calculated columns names. This is something you don't have to do with a calculated column, since the DBMS will supply some generic name for it, but you should do. You can also do it for non-calculated columns. The double-quotes aren't necessarily except in some situations, like if you want a space in the name (eg: "logins per user").

This was a bit of a potpourri section to cover a couple topics before we dig into something more complicated next time.

Question: Given the char_info table outlined here, calculate the average number of quests that have been completed by a level 90 character. Hint: remember that an average is calculated as the sum of the values divided by how many values there are. Where count() counts how many non-NULL entries are in a selection, the sum() function will add up the values.

Answer: here.

---

¹ The "::date" is necessary because login_time is a timestamp and we want to see if it's equal to a date. In short, the database considers  '2013-1-1 12:31:56' and '2013-1-1' to be different. '2013-1-1' is considered to be equal to '2013-1-1 00:00:00' (the midnight joining 2012-12-31 and 2013-1-1).
² count() and many other similar functions that summarize data are called "aggregate functions".

SQL Training - Part 1: Intro and Basic SELECT Statements

A typical table

SQL is the standard language for extracting and manipulating information from/in databases. While it comes in many varieties generally specific to the database in question, there is a significant shared portion and the differences tend to be more nuanced/less used functions.

The most common statement you'll use is the SELECT statement, which (surprise, surprise) selects data from a table or tables. The basic format of the select statement is

SELECT
     [columns]
FROM [table];

Where [columns] is a comma separated list of which columns you want to pull. For the sake of example, I'm going to be using a hypothetical set of tables that Blizzard might user for their games, since that will make the things we're talking about as common as possible to the typical audience of this blog. The first table we'll talk about will be logins, which contains data about which users logged into which Blizzard games at what time. So it might have 3 columns

date	| game_id	| user_id
1/1/2013	| 1	| 1234567
1/5/2013	| 3	| 3456789
1/7/2013	| 2	| 7636857

This table has three columns, recording who (user_id), logged into which game (game_id) on which date (date). To select all the data in this table, you would code

SELECT
     date,
     game_id,
     user_id
FROM logins;

But you'll probably never need to select all the data in a table. You might only be concerned with all users who logged into WoW (let's call that game_id=1) on 1/1/2013. To do this, you would code

SELECT
     user_id
FROM logins
WHERE
     game_id=1 AND date='1/1/2013';

I omitted the date and game_id columns since we already know what they are based on how we selected the data. They could be left in (and in some cases should).

I lied about how I would set up the logins table. I would instead set up the columns as such:

• logins - tracks players logging into games
• login_time - not just the date but also the time
• game_id - id for the game
• user_id - id for the user
• acct_id - id for the acct they log into

By making login_time have the date and the time, we can better track when people log in and how many times a day they log in. By adding acct_id you can keep track of which account for that game they logged in to. That query would become

SELECT
     user_id
FROM logins
WHERE
     game_id=1 AND login_time::date='1/1/2013';


Question: How would you select when and what games user 2435649 logged into during November?
Answer: here

If you have any questions, feel free to leave them in the comments or to email me. My email can be found on my About page.

NOTES

Note: login_time::date takes the timestamp from the login_time field and removes the time portion, giving you just the date so that the database can accurately tell if it's equal to '1/1/2013'. The way this is done will vary from database to database, but a similar solution should exist for all. Not all databases will necessarily read '1/1/2013' and accept it as a date and may have you do it differently, but for the sake of this, and all further examples, I'll keep doing things this way.

Note: If you're going to select all of the columns in a table you can use * instead of typing all of the column's names. To select all logins for a particular user (1234567) in December you would code

SELECT
     *
FROM logins
WHERE
     user_id=1234567 
     AND login_time::date BETWEEN '12/1/2012' AND '12/31/2012';

Note: The way I've done the spacing and decided where new lines go isn't mandatory and I just do it for easy reading.

How I failed and how I learn.

There are a few reasons why I do math.

• I suck at memorization.  This makes science, history, and literature studies hard for me.
• I'm very good at learning by doing.  I can remember a procedure very well after just doing it a few times.
• I'm clever. I have been using my wits to work my way through math classes for the longest time. Why study when you can re-derive all of the things that you would need to know?

All of this concluded in the Differential Equations comprehensive exam (which I failed, again). The way that the professor that I took Advanced DE from tests is strictly about wrote memorization.  The test format is at follows:

• define X out of these Y terms,
• state what X out of these Y theorems are,
• and prove X out of these Y theorems (findable in the book).

There are no 'problems' to work.  There are no original theorems to prove which would require the use of the student's intuition. This is the only math class that I have ever taken that has ever been this way, and it's very hard for me. Compound that with the fact that I hardly understood the material when I took the class 1.5 years ago, because of the teacher's accent and the natural abstruseness of the material, and you might be able to understand why I did poorly.