Joins, aggregate functions, views

• PostgreSQL Chapter 2: The SQL Language^🐘

• PostgreSQL Chapter 3: Advanced Features^🐘

Database model

We will use the constrained database model.

Examples of select

sql-- počasí, vše
select * from weather;

-- vše, vyjmenované sloupce (atributy)
select id_place, date, temp_lo, temp_hi, precip from weather;

-- vypočítané sloupce
select id_place, date, (temp_hi+temp_lo)/2 as "avg.temp." from weather;

-- s očekávaným počtem desetinných míst
select id_place, date, round((temp_hi+temp_lo)/2, 1) as "avg.temp." from weather;
-- lépe: přetypování (cast)
select id_place, date, ((temp_hi+temp_lo)/2)::d_temp as "avg.temp." from weather;

sql-- místa
select * from place;

-- jména míst připojená k počasí, místo id místa
select name, date, temp_lo from weather join place on id_place=id; -- chyba
-- id kvalifikované pomocí jména tabulky
select name, date, temp_lo from weather join place on id_place=place.id;
-- aliasy a plná kvalifikace jmen polí
select p.name, w.date, w.temp_lo from weather w join place p on w.id_place=p.id;

-- výběr výsledných řádků podle podmínky
select p.name, w.date, w.temp_lo from weather w join place p on w.id_place=p.id
where p.name='Praha' and 0<w.temp_lo;

sql-- alias jména sloupce, lze použit v setřídění
select p.name as místo, w.date, w.temp_lo from weather w
join place p on w.id_place=p.id order by místo;
-- setřídění podle více sloupců
select p.name as místo, w.date, w.temp_lo from weather w join place p on w.id_place=p.id order by místo, w.temp_lo;

-- jména míst, všechny záznamy
select p.name as místo from weather w join place p on w.id_place=p.id;
-- také všechny záznamy
select all p.name as místo from weather w join place p on w.id_place=p.id;
-- každé jméno (hodnotu) jen jednou
select distinct p.name as místo from weather w join place p on w.id_place=p.id;

-- každé jméno jen jednou, vybráno podle šablony
select distinct p.name as místo from weather w join place p on w.id_place=p.id
where místo like '%a%'; -- místo: chyba
-- takto lze (co se to děje?)
select distinct p.name as místo from weather w join place p on w.id_place=p.id
where p.name like '%a%' order by místo desc;

Table joins

Outer joins

Cartesian product of relations:

sqlselect * from place;
select count(*) from place;

select * from weather;
select count(*) from weather;

select * from place, weather;
select count(*) from place, weather;

-- stejné jako: from place, weather
select * from place cross join weather;

Inner joins

Originally implemented in SQL as a selection from the Cartesian product:

sqlselect * from place, weather where place.id = id_place;

In modern SQL, the join keyword is used:

sqlselect * from place inner join weather on place.id = id_place;
-- 'inner' is implied
select * from place join weather on place.id = id_place;

Left and right outer joins

Inner join returns only places that have a weather record. Places without a weather record are omitted from the result set.

sqlselect p.name, w.date, w.temp_hi from place p join weather w on p.id = w.id_place;

Left outer join preserves places without weather records:

sqlselect p.name, w.date, w.temp_hi from place p left outer join weather w on p.id = w.id_place;

Right outer join preserves weather records without places. In our model, this is not needed, because the foreign key constraint ensures that every weather record has a corresponding place.

sqlselect p.name, w.date, w.temp_hi from place p right outer join weather w on p.id = w.id_place;

If we swap the order of the tables, however, an outer join is needed:

sqlselect p.name, w.date, w.temp_hi from weather w right outer join place p on p.id = w.id_place;

The outer keyword is optional; the following query is equivalent:

sqlselect p.name, w.date, w.temp_hi from place p left join weather w on p.id = w.id_place;

Reflexive (self-) join

A table can be joined with itself. This is typically used for hierarchical or tree-like structures.

sqldrop table if exists employee;
create table employee (
  id serial primary key,
  name text,
  id_boss integer default null references employee(id)
);

-- bosses
insert into employee(name) values
  ('Hugo'), ('Donald'), ('Sauron');

-- not bosses
insert into employee(id_boss, name) values
  (1, 'Charles'), (1, 'Jenny'),
  (2, 'Mario'), (2, 'Robert'),
  (7, 'Sean');

select
    b.name as boss,
    u.name as underling
from
    employee b
join -- cross left right
    employee u
on
    b.id = u.id_boss;

Another example: select pairs of places where one place had a smaller temperature range than another place:

sqlselect w1.id_place, w1.date, w1.temp_lo as low, w1.temp_hi as high,
       w2.id_place, w2.date, w2.temp_lo as low, w2.temp_hi as high
  from weather w1 join weather w2
  on w1.temp_lo < w2.temp_lo and w2.temp_hi < w1.temp_hi;
  -- and w1.date=w2.date

Aggregate functions

• count, min, max, avg, sum, and more…^🐘

The number of records can be counted:

sqlselect count(*) from weather;
select count(id_place) from weather;
select count(distinct id_place) from weather;

Aggregate functions can be combined with calculations:

sqlselect count(*), min(temp_lo), max(temp_hi), avg(temp_hi-temp_lo), sum(precip) from weather;

Aggregate functions cannot be used in the where clause:

sqlselect id_place from weather where temp_lo = max(temp_lo); -- X

This can be done with a nested select:

sql-- select id_place, temp_lo from weather;
-- select max(temp_lo) from weather;
select id_place from weather where temp_lo = (select max(temp_lo) from weather);

Grouping rows – group by

If aggregate functions are used, all non-aggregated columns must be listed in the group by clause:

sqlselect id_place, temp_lo from weather;
select count(*) from weather;
select max(temp_lo) from weather;
select count(*), max(temp_lo) from weather;
select id_place, count(*), max(temp_lo) from weather; -- chyba
select id_place, count(*), max(temp_lo) from weather group by id_place;

Records used for the calculation of aggregated values can be selected (filtered) using the filter clause:

sqlselect id_place, count(*), max(temp_lo) from weather group by id_place;
select id_place, count(*) filter (where temp_lo < 20), max(temp_lo) from weather group by id_place;

Aggregated rows can be filtered using the having clause:

sqlselect id_place, count(*), max(temp_lo) from weather group by id_place;
select id_place, count(*), max(temp_lo) from weather group by id_place having max(temp_hi) < 20;

Additionally, place names can be joined and filtered:

sqlselect name, count(*), max(temp_lo) from weather
join place on id_place = place.id where name like '%a%' group by name;

-- two filters: where and having
select name, count(*), max(temp_lo) from weather
join place on id_place = place.id where name like '%a%' group by name having max(temp_hi) < 20;

Views

A view is a virtual, computed table:

sqlcreate or replace view place_weather as
  select name, date, temp_lo, temp_hi, precip from weather join place on id_place = place.id;

select * from place_weather;

Alternatively (better?) with aliases:

sqlcreate or replace view place_weather as
  select p.name, w.date, w.temp_lo, w.temp_hi, w.precip
  from weather w join place p on w.id_place = p.id;

The view can be used in the same way as a regular table:

sqlselect name, count(*), max(temp_lo) from place_weather
where name like '%a%' group by name;

Hierarchy of objects in PostgreSQL:

• Databases

  • postgres (default)

    • Schemas

      • public (default)

        • Tables

          • continent

          • country

          • …

The system catalog/catalogue is a set of tables and views that contain metadata about the database objects. It is stored in the pg_catalog schema.

Databases:

sqlselect * from pg_database;
select datname from pg_database;

Tables:

sqlselect * from pg_catalog.pg_tables;
select schemaname as schema, tablename as table from pg_catalog.pg_tables;

More about System Catalogs: PostgreSQL Chapter 52. System Catalogs^🐘