ORDER BY

Statistici sumare și funcții de fereastră în PostgreSQL

Michel Semaan

Data Scientist

Numere de rând

Interogare

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

Rezultat*

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

Introducere ORDER BY

ORDER BY din OVER ordonează rândurile asociate rândului curent
- Exemplu: Ordonarea descrescătoare după an în clauza OVER a ROW_NUMBER va atribui valoarea 1 rândurilor din cel mai recent an

Ordonare descrescătoare după an

Interogare

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

Rezultat

| 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   |
| ...  | ...           | ...     | ...   |

Ordonare după mai multe coloane

Interogare

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

Rezultat

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

Ordonare în interiorul și exteriorul OVER

Interogare

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;

Rezultat

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

ORDER BY din OVER se aplică înaintea ORDER BY din afara OVER

Campion în exercițiu

Un campion în exercițiu este cel care a câștigat atât în anul precedent, cât și în cel curent
Campionii din anul precedent și din cel curent trebuie să apară pe aceeași linie (în două coloane diferite)

Introducere LAG

LAG(column, n) OVER (...) returnează valoarea column de la linia cu n rânduri înaintea rândului curent
- LAG(column, 1) OVER (...) returnează valoarea rândului anterior

Campionii actuali

Interogare

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';

Rezultat

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

Campionii actuali și anteriori

Interogare

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;

Rezultat

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

Să exersăm!

Statistici sumare și funcții de fereastră în PostgreSQL