Frames

PostgreSQL Summary Stats और Window Functions

Michel Semaan

Data Scientist

प्रेरणा

LAST_VALUE

LAST_VALUE(City) OVER (
 ORDER BY Year ASC
 RANGE BETWEEN
   UNBOUNDED PRECEDING AND
   UNBOUNDED FOLLOWING
) AS Last_City

फ्रेम: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
फ्रेम के बिना, LAST_VALUE City कॉलम की उसी रो का मान लौटाएगा
डिफ़ॉल्ट रूप से, फ्रेम टेबल या पार्टिशन की शुरुआत से शुरू होकर करंट रो पर खत्म होता है

ROWS BETWEEN

ROWS BETWEEN [START] AND [FINISH]
- n PRECEDING: करंट रो से पहले की n रो
- CURRENT ROW: करंट रो
- n FOLLOWING: करंट रो के बाद की n रो

उदाहरण

ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING

स्रोत टेबल

क्वेरी

SELECT
  Year, COUNT(*) AS Medals
FROM Summer_Medals
WHERE
  Country = 'RUS'
  AND Medal = 'Gold'
GROUP BY Year
ORDER BY Year ASC;

परिणाम

| Year | Medals |
|------|--------|
| 1996 | 36     |
| 2000 | 66     |
| 2004 | 47     |
| 2008 | 43     |
| 2012 | 47     |

फ्रेम के बिना MAX

क्वेरी

WITH Russia_Medals AS (...)

SELECT
  Year, Medals,
  MAX(Medals)
    OVER (ORDER BY Year ASC) AS Max_Medals
FROM Russia_Medals
ORDER BY Year ASC;

परिणाम

| Year | Medals | Max_Medals |
|------|--------|------------|
| 1996 | 36     | 36         |
| 2000 | 66     | 66         |
| 2004 | 47     | 66         |
| 2008 | 43     | 66         |
| 2012 | 47     | 66         |

फ्रेम के साथ MAX

क्वेरी

WITH Russia_Medals AS (...)

SELECT
  Year, Medals,
  MAX(Medals)
    OVER (ORDER BY Year ASC) AS Max_Medals,
  MAX(Medals)
    OVER (ORDER BY Year ASC
          ROWS BETWEEN
          1 PRECEDING AND CURRENT ROW)
    AS Max_Medals_Last
FROM Russia_Medals
ORDER BY Year ASC;

परिणाम

| Year | Medals | Max_Medals | Max_Medals_Last |
|------|--------|------------|-----------------|
| 1996 | 36     | 36         | 36              |
| 2000 | 66     | 66         | 66              |
| 2004 | 47     | 66         | 66              |
| 2008 | 43     | 66         | 47              |
| 2012 | 47     | 66         | 47              |

करंट और अगले रो

क्वेरी

WITH Russia_Medals AS (...)

SELECT
  Year, Medals,
  MAX(Medals)
    OVER (ORDER BY Year ASC
          ROWS BETWEEN
          CURRENT ROW AND 1 FOLLOWING)
    AS Max_Medals_Next
FROM Russia_Medals
ORDER BY Year ASC;

परिणाम

| Year | Medals | Max_Medals_Next |
|------|--------|-----------------|
| 1996 | 36     | 66              |
| 2000 | 66     | 66              |
| 2004 | 47     | 47              |
| 2008 | 43     | 47              |
| 2012 | 47     | 47              |

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

PostgreSQL Summary Stats और Window Functions