Window Function — Learn and Master SQL Training

In this chapter I invite you to discover the “windowing” which allows you to use the aggregation functions on several lines.

For the rest of this article we will use a database listing the sales of a company in different countries.

CREATE TABLE IF NOT EXISTS sales (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    year INTEGER,
    country VARCHAR(255),
    product VARCHAR(255),
    profit INTEGER
);

INSERT INTO sales (year, country, product, profit)
VALUES
  (2000,'Finland','Computer'  ,  1500),
  (2000,'Finland','Phone'     ,   100),
  (2001,'Finland','Phone'     ,    10),
  (2000,'India'  ,'Calculator',    75),
  (2000,'India'  ,'Calculator',    75),
  (2000,'India'  ,'Computer'  ,  1200),
  (2000,'USA'    ,'Calculator',    75),
  (2000,'USA'    ,'Computer'  ,  1500),
  (2001,'USA'    ,'Calculator',    50),
  (2001,'USA'    ,'Computer'  ,  1500),
  (2001,'USA'    ,'Computer'  ,  1200),
  (2001,'USA'    ,'TV'        ,   150),
  (2001,'USA'    ,'TV'        ,   100);

By default, functions like SUM() aggregates the data by generating only one row corresponding to the result of the operation.

SELECT *, SUM(profit) as total FROM sales

With the use of windowing we will be able to retrieve all the rows with the result of the aggregation in addition. For this we will use the syntax <fonction> OVER (PARTITION BY <champs>)

SELECT 
  *,
  SUM(profit) OVER () as total,
  SUM(profit) OVER (PARTITION BY country) as total_country
FROM sales

You can also use window functions to retrieve information about the partition.

SELECT 
  *,
  SUM(profit) OVER () as total,
  SUM(profit) OVER w as total_country,
  RANK() OVER w as rank
  ROW_NUMBER() OVER w as idx
FROM sales
WINDOW w (PARTITION BY country ORDER BY profit DESC)

You can also use the keyword WINDOW to avoid repeating a score.

Concrete case

We want to retrieve the last 5 comments of the last 5 articles.

SELECT * FROM(
    SELECT 
        post_id,
        ROW_NUMBER() OVER (PARTITION BY post_id) as row_number,
        *
    FROM comments
    WHERE post_id IN (
        SELECT id FROM posts ORDER BY id DESC LIMIT 5
    )
) as t
WHERE t.row_number < 6;