Introduction to Window Functions
Discover the window functions available in MySQL.
We'll cover the following...
SQL allows us to read a table using TABLE, select rows with a suitable WHERE clause, or groups of rows based on common criteria with GROUP BY. By those means, we can get only so far, though. For example, let us consider a scenario where an SQL table records the timestamps of a user’s session on a website:
-- Generate a table that records user sessions.DROP TABLE IF EXISTS Log;CREATE TABLE Log(id INT NOT NULL AUTO_INCREMENT,user_id INT NOT NULL,log_in TIMESTAMP NOT NULL,log_out TIMESTAMP NOT NULL,PRIMARY KEY (id));
It is fairly easy for us to answer the following questions using one of the three approaches mentioned above:
-- Inspect the recorded logs.TABLE Log;-- Insert empty lines in the output for better readability.SELECT "";-- How many unique users have created a session in the past?SELECT DISTINCT COUNT(user_id) FROM Log;-- Insert empty lines in the output for better readability.SELECT "";-- How many sessions belong to the user with `user_id = 2`?SELECT COUNT(*) FROM Log WHERE user_id = 2;-- Insert empty lines in the output for better readability.SELECT "";-- What is the average length of a session (in seconds) for the user with `user_id = 3`?-- Note that the average length varies depending on the unit selected for the difference (e.g., seconds are cut off using `MINUTE`).SELECT AVG(TIMESTAMPDIFF(SECOND, log_in, log_out)) FROM Log WHERE user_id = 3;
However, the following question is surely more difficult to answer: what is the average time between the sessions of the user with user_id = 2? That is because we are looking for information that is not explicitly represented in Log, i.e., for the difference between log_out and log_in for each pair of adjacent sessions. While we can handle implicit information about groups of rows with the GROUP BY clause, there is no criterion in Log that would allow us to form pairs of adjacent sessions since they are not ordered within the table. So, how do we ...