ORDER BY

PostgreSQL – Sammanfattande statistik och fönsterfunktioner

Michel Semaan

Data Scientist

Radnummer

Fråga

SELECT
  Year, Event, Country,
  ROW_NUMBER() OVER () AS Row_N
FROM Summer_Medals
WHERE
  Medal = 'Gold';

Resultat*

| Year | Event                      | Country | Row_N |
|------|----------------------------|---------|-------|
| 1896 | 100M Freestyle             | HUN     | 1     |
| 1896 | 100M Freestyle For Sailors | GRE     | 2     |
| 1896 | 1200M Freestyle            | HUN     | 3     |
| ...  | ...                        | ...     | ...   |

Introduktion till ORDER BY

ORDER BY i OVER sorterar raderna relativt den aktuella raden
- Exempel: Att sortera på år i fallande ordning i ROW_NUMBERs OVER-sats tilldelar 1 till raderna med det senaste året

Sortering på år i fallande ordning

Fråga

SELECT
  Year, Event, Country,
  ROW_NUMBER() OVER (ORDER BY Year DESC) AS Row_N
FROM Summer_Medals
WHERE
  Medal = 'Gold';

Resultat

| Year | Event         | Country | Row_N |
|------|---------------|---------|-------|
| 2012 | Wg 96 KG      | IRI     | 1     |
| 2012 | 4X100M Medley | USA     | 2     |
| 2012 | Wg 84 KG      | RUS     | 3     |
| ...  | ...           | ...     | ...   |
| 2008 | 50M Freestyle | BRA     | 637   |
| 2008 | 96 - 120KG    | CUB     | 638   |
| ...  | ...           | ...     | ...   |

Sortering på flera kolumner

Fråga

SELECT
  Year, Event, Country,
  ROW_NUMBER() OVER
    (ORDER BY Year DESC, Event ASC) AS Row_N
FROM Summer_Medals
WHERE
  Medal = 'Gold';

Resultat

| Year | Event   | Country | Row_N |
|------|---------|---------|-------|
| 2012 | + 100KG | FRA     | 1     |
| 2012 | + 67 KG | SRB     | 2     |
| 2012 | + 78KG  | CUB     | 3     |
| ...  | ...     | ...     | ...   |

Sortering inuti och utanför OVER

Fråga

SELECT
  Year, Event, Country,
  ROW_NUMBER() OVER
    (ORDER BY Year DESC, Event ASC) AS Row_N
FROM Summer_Medals
WHERE
  Medal = 'Gold'
ORDER BY Country ASC, Row_N ASC;

Resultat

| Year | Event   | Country | Row_N |
|------|---------|---------|-------|
| 2012 | 1500M   | ALG     | 36    |
| 2000 | 1500M   | ALG     | 1998  |
| 1996 | 1500M   | ALG     | 2662  |
| ...  | ...     | ...     | ...   |

ORDER BY inuti OVER tillämpas före ORDER BY utanför OVER

Regerande mästare

En regerande mästare är en mästare som vunnit både föregående och innevarande års tävling
Föregående och innevarande års mästare behöver finnas på samma rad (i två olika kolumner)

Introduktion till LAG

LAG(column, n) OVER (...) returnerar columns värde på raden n rader före den aktuella raden
- LAG(column, 1) OVER (...) returnerar föregående rads värde

Aktuella mästare

Fråga

SELECT
  Year, Country AS Champion
FROM Summer_Medals
WHERE
  Year IN (1996, 2000, 2004, 2008, 2012)
  AND Gender = 'Men' AND Medal = 'Gold'
  AND Event = 'Discus Throw';

Resultat

| Year | Champion |
|------|----------|
| 1996 | GER      |
| 2000 | LTU      |
| 2004 | LTU      |
| 2008 | EST      |
| 2012 | GER      |

Aktuell och föregående mästare

Fråga

WITH Discus_Gold AS (
  SELECT
    Year, Country AS Champion
  FROM Summer_Medals
  WHERE
    Year IN (1996, 2000, 2004, 2008, 2012)
    AND Gender = 'Men' AND Medal = 'Gold'
    AND Event = 'Discus Throw')

SELECT
  Year, Champion,
  LAG(Champion, 1) OVER
    (ORDER BY Year ASC) AS Last_Champion
FROM Discus_Gold
ORDER BY Year ASC;

Resultat

| Year | Champion | Last_Champion |
|------|----------|---------------|
| 1996 | GER      | null          |
| 2000 | LTU      | GER           |
| 2004 | LTU      | LTU           |
| 2008 | EST      | LTU           |
| 2012 | GER      | EST           |

Nu kör vi en övning!

PostgreSQL – Sammanfattande statistik och fönsterfunktioner