ORDER BY

PostgreSQL Summary Stats and Window Functions

Michel Semaan

Data Scientist

หมายเลขแถว

คิวรี

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

เพิ่ม ORDER BY

ORDER BY ใน OVER จะจัดเรียงแถวที่เกี่ยวข้องกับแถวปัจจุบัน
- ตัวอย่าง: การเรียงตามปีจากมากไปน้อยใน OVER ของ ROW_NUMBER จะกำหนดเลข 1 ให้แถวของปีล่าสุด

เรียงตามปีจากมากไปน้อย

คิวรี

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

ผลลัพธ์

| Year | Event         | Country | Row_N |
|------|---------------|---------|-------|
| 2012 | Wg 96 KG      | IRI     | 1     |
| 2012 | 4X100M Medley | USA     | 2     |
| 2012 | Wg 84 KG      | RUS     | 3     |
| ...  | ...           | ...     | ...   |
| 2008 | 50M Freestyle | BRA     | 637   |
| 2008 | 96 - 120KG    | CUB     | 638   |
| ...  | ...           | ...     | ...   |

เรียงตามหลายคอลัมน์

คิวรี

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

ผลลัพธ์

| Year | Event   | Country | Row_N |
|------|---------|---------|-------|
| 2012 | + 100KG | FRA     | 1     |
| 2012 | + 67 KG | SRB     | 2     |
| 2012 | + 78KG  | CUB     | 3     |
| ...  | ...     | ...     | ...   |

การเรียงลำดับภายในและภายนอก OVER

คิวรี

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

ผลลัพธ์

| Year | Event   | Country | Row_N |
|------|---------|---------|-------|
| 2012 | 1500M   | ALG     | 36    |
| 2000 | 1500M   | ALG     | 1998  |
| 1996 | 1500M   | ALG     | 2662  |
| ...  | ...     | ...     | ...   |

ORDER BY ภายใน OVER จะมีผลก่อน ORDER BY ที่อยู่นอก OVER

แชมป์ครองตำแหน่ง

แชมป์ครองตำแหน่ง คือผู้ที่ชนะทั้งในปีก่อนหน้าและปีปัจจุบัน
ข้อมูลแชมป์ปีก่อนและปีปัจจุบันต้องอยู่ในแถวเดียวกัน (คนละคอลัมน์)

เพิ่ม LAG

LAG(column, n) OVER (...) คืนค่าของ column จากแถวที่อยู่ก่อนหน้า n แถว
- LAG(column, 1) OVER (...) คืนค่าของแถวก่อนหน้า

แชมป์ปัจจุบัน

คิวรี

SELECT
  Year, Country AS Champion
FROM Summer_Medals
WHERE
  Year IN (1996, 2000, 2004, 2008, 2012)
  AND Gender = 'Men' AND Medal = 'Gold'
  AND Event = 'Discus Throw';

ผลลัพธ์

| Year | Champion |
|------|----------|
| 1996 | GER      |
| 2000 | LTU      |
| 2004 | LTU      |
| 2008 | EST      |
| 2012 | GER      |

แชมป์ปัจจุบันและแชมป์ครั้งก่อน

คิวรี

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

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

ผลลัพธ์

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

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

PostgreSQL Summary Stats and Window Functions