Organisation

Statistiques récapitulatives et fonctions de fenêtrage PostgreSQL

Michel Semaan

Data Scientist

Transformation des tableaux

Avant

| Country | Year | Awards |
|---------|------|--------|
| CHN     | 2008 | 74     |
| CHN     | 2012 | 56     |
| RUS     | 2008 | 43     |
| RUS     | 2012 | 47     |
| USA     | 2008 | 125    |
| USA     | 2012 | 147    |

Médailles d'or décernées à la Chine, à la Russie et aux États-Unis

Après

| Country | 2008 | 2012 |
|---------|------|------|
| CHN     | 74   | 56   |
| RUS     | 43   | 47   |
| USA     | 125  | 147  |

Organisation par Year
Plus facile à parcourir, en particulier si les informations sont organisées chronologiquement dans une colonne

CROSSTAB

CREATE EXTENSION IF NOT EXISTS tablefunc;


SELECT * FROM CROSSTAB($$
  source_sql TEXT
$$) AS ct (column_1 DATA_TYPE_1,
           column_2 DATA_TYPE_2,
           ...,
           column_n DATA_TYPE_N);

Requêtes

Avant

SELECT
  Country, Year, COUNT(*) AS Awards
FROM Summer_Medals
WHERE
  Country IN ('CHN', 'RUS', 'USA')
  AND Year IN (2008, 2012)
  AND Medal = 'Gold'
GROUP BY Country, Year
ORDER BY Country ASC, Year ASC;

Après

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM CROSSTAB($$
  SELECT
    Country, Year, COUNT(*) :: INTEGER AS Awards
  FROM Summer_Medals
  WHERE
    Country IN ('CHN', 'RUS', 'USA')
    AND Year IN (2008, 2012)
    AND Medal = 'Gold'
  GROUP BY Country, Year
  ORDER BY Country ASC, Year ASC;
$$) AS ct (Country VARCHAR, "2008" INTEGER, "2012" INTEGER)

ORDER BY Country ASC;

Requête source

WITH Country_Awards AS (
  SELECT
    Country, Year, COUNT(*) AS Awards
  FROM Summer_Medals
  WHERE
    Country IN ('CHN', 'RUS', 'USA')
    AND Year IN (2004, 2008, 2012)
    AND Medal = 'Gold' AND Sport = 'Gymnastics'
  GROUP BY Country, Year
  ORDER BY Country ASC, Year ASC)


SELECT
  Country, Year,
  RANK() OVER
    (PARTITION BY Year ORDER BY Awards DESC) :: INTEGER
    AS rank
FROM Country_Awards
ORDER BY Country ASC, Year ASC;

Résultat source

| Country | Year |Rank |
|---------|------|-----|
| CHN     | 2004 | 3   |
| CHN     | 2008 | 1   |
| CHN     | 2012 | 1   |
| RUS     | 2004 | 1   |
| RUS     | 2008 | 2   |
| RUS     | 2012 | 2   |
| USA     | 2004 | 2   |
| USA     | 2008 | 3   |
| USA     | 2012 | 3   |

Organisation de requête

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM CROSSTAB($$
  ...
$$) AS ct (Country VARCHAR,
           "2004" INTEGER,
           "2008" INTEGER,
           "2012" INTEGER)

ORDER BY Country ASC;

Organisation du résultat

| Country | 2004 | 2008 | 2012 |
|---------|------|------|------|
| CHN     | 3    | 1    | 1    |
| RUS     | 1    | 2    | 2    |
| USA     | 2    | 3    | 3    |

Passons à la pratique !

Statistiques récapitulatives et fonctions de fenêtrage PostgreSQL