Nested select, window functions (česky analytické funkce)

Window functions allow to perform calculations across a set of table rows that are somehow related to the current row.

Most often used for ranking, running totals, moving averages, and other cumulative or comparative calculations.

they allow to add summary information obtained from other rows to regular rows
they are used in the select clause
unlike regular functions, they operate on multiple rows (hence "window")
they are similar to group by aggregates, but do not reduce the number of rows
some can be emulated using nested queries within select clauses

See:

Practical Example: Mordor Marathon

Tables:

sqldrop table if exists mormar;
drop table if exists kategorie;

create table kategorie (
    id      integer  primary key,
    jméno   text     not null unique
);

create table mormar (
    startovní_číslo integer   primary key,
    závodník        text      not null,
    kategorie       integer   references kategorie(id),
    čas             interval  not null check ('00:00:00'::interval < čas)
);

create index idx_čas on mormar(čas);
create index idx_kat on mormar(kategorie);

insert into kategorie values
    (0, 'elf'),
    (1, 'člověk'),
    (2, 'trpaslík'),
    (3, 'skřet'),
    (4, 'maia'),
    (5, 'hobit');

insert into mormar values
    ( 3, 'Azog',    3, '03:56:11'),
    ( 4, 'Gandalf', 4, '01:34:23'),
    ( 5, 'Saruman', 4, '01:56:00'),
    ( 6, 'Sauron',  4, '00:02:00'),
    ( 7, 'Gimli',   2, '2 days 03:14:00'),
    ( 8, 'Aragorn', 1, '02:42:42'),
    ( 9, 'Eowyn',   1, '02:45:12'),
    ( 2, 'Samvěd',  5, '04:12:00'),
    (10, 'Bilbo',   5, '04:11:59');

Nested `select`:

Various queries:

sql-- účastníci
select závodník, čas from mormar;

-- poslední, který měl lepší než zadaný čas
select závodník, čas from mormar
  where čas < '01:56:00'
  order by čas desc
  limit 1;

-- závodník a kdo doběhl těsně před ním
select závodník, čas, (select závodník from mormar
                  where čas < m.čas
                  order by čas desc limit 1)
  from mormar m;

-- kdo doběhl těsně před ním ve stejné kategorii
select závodník, čas, (select závodník from mormar
                  where kategorie=m.kategorie and čas<m.čas
                  order by čas desc limit 1)
  from mormar m;

-- kolik každý ztratil na vítěze
select závodník,
        čas - (select čas from mormar
               order by čas asc limit 1)
  from mormar;

-- kolik každý ztratil na vítěze ve své kategorii
select závodník,
        čas - (select čas from mormar
               where kategorie=m.kategorie
               order by čas asc limit 1)
  from mormar m;

-- kolikátý doběhl
select závodník, 1 + (select count(*) from mormar where čas < m.čas)
  from mormar m;

select závodník, čas, 1 + (select count(*) from mormar where čas < m.čas)
  from mormar m order by čas;

-- kolikátý doběhl ve své kategorii
select závodník, 1 + (select count(*) from mormar
  where kategorie=m.kategorie and čas < m.čas)
  from mormar m;

select kategorie, závodník, čas, 1 + (select count(*) from mormar
  where kategorie=m.kategorie and čas < m.čas)
  from mormar m order by kategorie, čas;

-- kolik závodníků doběhlo za ním
select závodník, (select count(*) from mormar
                  where m.čas < čas)
  from mormar m;

Window functions:

Syntax:

  function_name() over ([partition by column_name] [order by column_name])

How they work:

sql-- select
select kategorie, čas from mormar order by kategorie;

-- agregace
select kategorie, avg(čas) from mormar
  group by kategorie order by kategorie;

select kategorie, avg(čas) from mormar
  group by kategorie order by avg(čas);

-- analytické funkce
select kategorie, čas, avg(čas) over ()
  from mormar order by kategorie;

select kategorie, čas, avg(čas) over (partition by kategorie)
  from mormar order by kategorie;

select kategorie, čas,
       rank() over (order by čas)
  from mormar order by kategorie; -- čas

Various queries, nested select / window function:

sql-- kdo doběhl těsně před ním
select závodník,
       (select závodník from mormar
        where čas < m.čas order by čas desc limit 1)
  from mormar m order by čas;

select závodník, lag(závodník) over (order by čas)
  from mormar order by čas;

select závodník, lag(závodník, 1, '') over (order by čas)
  from mormar order by čas;

-- kdo doběhl těsně před ním ve stejné kategorii
select kategorie, závodník,
       (select závodník from mormar
        where kategorie=m.kategorie and čas<m.čas
        order by čas desc limit 1)
  from mormar m order by kategorie, čas;

select kategorie, závodník,
       lag(závodník) over (partition by kategorie
                           order by čas) as závodník -- jméno sloupce
  from mormar order by kategorie, čas;

-- kolik každý ztratil na vítěze
select závodník,
        čas - (select čas from mormar
               order by čas asc limit 1)
  from mormar order by čas;

select závodník, čas - first_value(čas) over (order by čas)
  from mormar order by čas;

-- kolik každý ztratil na vítěze ve své kategorii
select kategorie, závodník,
        čas - (select čas from mormar
               where kategorie=m.kategorie
               order by čas asc limit 1)
  from mormar m order by kategorie, čas;

select kategorie, závodník,
       čas - first_value(čas) over (partition by kategorie
                                    order by čas)
  from mormar order by kategorie, čas;

-- kolikátý doběhl
select závodník, 1 + (select count(*) from mormar
                      where čas < m.čas)
  from mormar m order by čas;

select závodník, rank() over (order by čas)
  from mormar m order by čas;

-- kolikátý doběhl ve své kategorii
select kategorie, závodník, 1 + (select count(*) from mormar
        where kategorie=m.kategorie and čas < m.čas)
  from mormar m order by kategorie, čas;

select kategorie, závodník,
       rank() over (partition by kategorie order by čas)
  from mormar m order by kategorie, čas;

-- kolik závodníků doběhlo za ním
select závodník, (select count(*) from mormar
                  where m.čas < čas)
  from mormar m order by čas;

-- cf. rank / dense_rank / row_number
select závodník, rank() over (order by čas desc) - 1
  from mormar m order by čas;

Nested select, window functions (česky analytické funkce)

Practical Example: Mordor Marathon

Nested select:

Window functions:

Nested `select`: