The Art of PostgreSQL/

...

/

Materialized Views

Materialized Views

Get an introduction to materialized views in PostgreSQL.

We'll cover the following...

Use case: Computing points per season

Let’s now compute constructor and driver points per season. In the following query, we compute points for the ongoing season and the dataset available:

Press + to interact
\set season 2017
  select drivers.surname as driver,
         constructors.name as constructor,
         sum(points) as points
    
    from results
         join races using(raceid)
         join drivers using(driverid)
         join constructors using(constructorid)
   
   where races.year = :season
group by grouping sets(drivers.surname, constructors.name)
  having sum(points) > 150
order by drivers.surname is not null, points desc;

Here’s the result, which we know is wrong because the season was not over yet at the time of the computation. The having clause has been used only to reduce the number of lines to display; in a real application, we would certainly get all the results at once. Here’s our result set:

   driver   | constructor | points 
------------+-------------+--------
            | Mercedes    |    668
            | Ferrari     |    522
            | Red Bull    |    368
            |
...