पिवटिंग

PostgreSQL Summary Stats और Window Functions

Michel Semaan

Data Scientist

टेबल ट्रांसफॉर्म करना

पहले

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

China, Russia, और USA को दिए गए Gold medals

बाद में

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

Year के आधार पर पिवट किया गया
स्कैन करना आसान, खासकर जब समयानुक्रम में सॉर्टेड कॉलम पर पिवट हो

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

क्वेरीज़

पहले

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;

बाद में

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;

सोर्स क्वेरी

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;

सोर्स परिणाम

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

पिवट क्वेरी

CREATE EXTENSION IF NOT EXISTS tablefunc;

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

ORDER BY Country ASC;

पिवट परिणाम

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

अभ्यास करते हैं!

PostgreSQL Summary Stats और Window Functions