परिचय

PostgreSQL Summary Stats और Window Functions

Michel Semaan

Data Scientist

प्रेरणा

2004 से USA के Summer Olympics के स्वर्ण पदक: कुल और रनिंग टोटल

| Year | Medals | Medals_RT |
|------|--------|-----------|
| 2004 | 116    | 116       |
| 2008 | 125    | 241       |
| 2012 | 147    | 388       |

Discus throw का वर्तमान चैंपियन स्टेटस

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

कोर्स रूपरेखा

Window functions का परिचय
Fetching, ranking, और paging
Aggregate window functions और frames
Window functions से आगे

Summer Olympics डेटासेट

हर पंक्ति Summer Olympics में दिए गए एक पदक को दर्शाती है

कॉलम

Year, City
Sport, Discipline, Event
Athlete, Country, Gender
Medal

Window functions

किसी row-set पर ऑपरेशन करें जो current row से संबंधित हो
GROUP BY aggregate functions जैसा, पर सभी पंक्तियाँ आउटपुट में रहती हैं

उपयोग

पिछली/अगली पंक्तियों से मान लाना (जैसे पिछली पंक्ति का मान)
- वर्तमान चैंपियन स्टेटस निकालना
- समय के साथ growth निकालना
sorted सूची में स्थिति के आधार पर पंक्तियों को ordinal रैंक देना (1st, 2nd, आदि)
रनिंग टोटल, मूविंग एवरेज

Row numbers

क्वेरी

SELECT
  Year, Event, Country
FROM Summer_Medals
WHERE
  Medal = 'Gold';

परिणाम

| Year | Event                      | Country |
|------|----------------------------|---------|
| 1896 | 100M Freestyle             | HUN     |
| 1896 | 100M Freestyle For Sailors | GRE     |
| 1896 | 1200M Freestyle            | HUN     |
| ...  | ...                        | ...     |

ROW_NUMBER का उपयोग

क्वेरी

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

परिणाम

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

Window function की संरचना

क्वेरी

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

FUNCTION_NAME() OVER (...)
- ORDER BY
- PARTITION BY
- ROWS/RANGE PRECEDING/FOLLOWING/UNBOUNDED

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

PostgreSQL Summary Stats और Window Functions