PARTITION BY

PostgreSQL Summary Stats and Window Functions

Michel Semaan

Data Scientist

แรงจูงใจ

คิวรี

WITH Discus_Gold AS (
  SELECT
    Year, Event, Country AS Champion
  FROM Summer_Medals
  WHERE
    Year IN (2004, 2008, 2012)
    AND Gender = 'Men' AND Medal = 'Gold'
    AND Event IN ('Discus Throw', 'Triple Jump')
    AND Gender = 'Men')

SELECT
  Year, Event, Champion,
  LAG(Champion) OVER
    (ORDER BY Event ASC, Year ASC) AS Last_Champion
FROM Discus_Gold
ORDER BY Event ASC, Year ASC;

ผลลัพธ์

| Year | Event        | Champion | Last_Champion |
|------|--------------|----------|---------------|
| 2004 | Discus Throw | LTU      | null          |
| 2008 | Discus Throw | EST      | LTU           |
| 2012 | Discus Throw | GER      | EST           |
| 2004 | Triple Jump  | SWE      | GER           |
| 2008 | Triple Jump  | POR      | SWE           |
| 2012 | Triple Jump  | USA      | POR           |

เมื่อ Event เปลี่ยนจาก Discus Throw เป็น Triple Jump ฟังก์ชัน LAG ดึงแชมป์ล่าสุดของ Discus Throw แทนที่จะคืนค่า null

รู้จัก PARTITION BY

PARTITION BY แบ่งตารางออกเป็น partition ตามค่าที่ไม่ซ้ำกันของคอลัมน์
- ผลลัพธ์จะไม่ถูกรวมเข้าเป็นคอลัมน์เดียว
window function จะทำงานบน partition แต่ละส่วนแยกจากกัน
- ROW_NUMBER จะเริ่มนับใหม่ในแต่ละ partition
- LAG จะดึงค่าแถวก่อนหน้าเฉพาะเมื่อแถวนั้นอยู่ใน partition เดียวกัน

การแบ่ง partition ด้วยคอลัมน์เดียว

คิวรี

WITH Discus_Gold AS (...)

SELECT
  Year, Event, Champion,
  LAG(Champion) OVER
    (PARTITION BY Event
     ORDER BY Event ASC, Year ASC) AS Last_Champion
FROM Discus_Gold
ORDER BY Event ASC, Year ASC;

ผลลัพธ์

| Year | Event        | Champion | Last_Champion |
|------|--------------|----------|---------------|
| 2004 | Discus Throw | LTU      | null          |
| 2008 | Discus Throw | EST      | LTU           |
| 2012 | Discus Throw | GER      | EST           |
| 2004 | Triple Jump  | SWE      | null          |
| 2008 | Triple Jump  | POR      | SWE           |
| 2012 | Triple Jump  | USA      | POR           |

การแบ่ง partition ที่ซับซ้อนขึ้น

| Year | Country | Event                | Row_N |
|------|---------|----------------------|-------|
| 2008 | CHN     | + 78KG (Heavyweight) | 1     |
| 2008 | CHN     | - 49 KG              | 2     |
| ...  | ...     | ...                  | ...   |
| 2008 | JPN     | 48 - 55KG            | 27    |
| 2008 | JPN     | 48 - 55KG            | 28    |
| ...  | ...     | ...                  | ...   |
| 2012 | CHN     | +75KG                | 32    |
| 2012 | CHN     | - 49 KG              | 33    |
| ...  | ...     | ...                  | ...   |
| 2012 | JPN     | +75KG                | 51    |
| 2012 | JPN     | - 49 KG              | 52    |
| ...  | ...     | ...                  | ...   |

หมายเลขแถวควรเริ่มนับใหม่ตาม Year และ Country

การแบ่ง partition ด้วยหลายคอลัมน์

คิวรี

WITH Country_Gold AS (
  SELECT
    DISTINCT Year, Country, Event
  FROM Summer_Medals
  WHERE
    Year IN (2008, 2012)
    AND Country IN ('CHN', 'JPN')
    AND Gender = 'Women' AND Medal = 'Gold')

SELECT
  Year, Country, Event,
  ROW_NUMBER() OVER (PARTITION BY Year, Country)
FROM Country_Gold;

ผลลัพธ์

| Year | Country | Event                | Row_N |
|------|---------|----------------------|-------|
| 2008 | CHN     | + 78KG (Heavyweight) | 1     |
| 2008 | CHN     | - 49 KG              | 2     |
| ...  | ...     | ...                  | ...   |
| 2008 | JPN     | 48 - 55KG            | 1     |
| 2008 | JPN     | 48 - 55KG            | 2     |
| ...  | ...     | ...                  | ...   |
| 2012 | CHN     | +75KG                | 1     |
| 2012 | CHN     | - 49 KG              | 2     |
| ...  | ...     | ...                  | ...   |
| 2012 | JPN     | +75KG                | 1     |
| 2012 | JPN     | - 49 KG              | 2     |
| ...  | ...     | ...                  | ...   |

มาฝึกกันเถอะ!

PostgreSQL Summary Stats and Window Functions