บทนำ

PostgreSQL Summary Stats and Window Functions

Michel Semaan

Data Scientist

แรงบันดาลใจ

จำนวนเหรียญทองโอลิมปิกฤดูร้อนของสหรัฐฯ และยอดสะสมตั้งแต่ปี 2004

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

สถานะแชมป์เปี้ยนปัจจุบันในการขว้างจักร

| 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             |
PostgreSQL Summary Stats and Window Functions

โครงร่างคอร์ส

  1. บทนำสู่ window functions
  2. การดึงข้อมูล การจัดอันดับ และการแบ่งหน้า
  3. Aggregate window functions และ frames
  4. เทคนิคขั้นสูงของ window functions
PostgreSQL Summary Stats and Window Functions

ชุดข้อมูลโอลิมปิกฤดูร้อน

  • แต่ละแถวแทนเหรียญรางวัลที่มอบในโอลิมปิกฤดูร้อน

คอลัมน์

  • Year, City
  • Sport, Discipline, Event
  • Athlete, Country, Gender
  • Medal
PostgreSQL Summary Stats and Window Functions

Window functions

  • ดำเนินการกับชุดแถวที่เกี่ยวข้องกับแถวปัจจุบัน
  • คล้ายกับ aggregate functions ของ GROUP BY แต่ทุกแถวยังคงอยู่ในผลลัพธ์

การใช้งาน

  • ดึงค่าจากแถวก่อนหน้าหรือถัดไป (เช่น ดึงค่าของแถวก่อนหน้า)
    • ตรวจสอบสถานะแชมป์เปี้ยนปัจจุบัน
    • คำนวณการเติบโตตามช่วงเวลา
  • กำหนดอันดับ (ที่ 1, ที่ 2, เป็นต้น) ให้แถวตามตำแหน่งค่าในรายการที่เรียงลำดับแล้ว
  • ยอดสะสม, ค่าเฉลี่ยเคลื่อนที่
PostgreSQL Summary Stats and Window Functions

หมายเลขแถว

คิวรี

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     |
| ...  | ...                        | ...     |
PostgreSQL Summary Stats and Window Functions

รู้จักกับ 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     |
| ...  | ...                        | ...     | ...   |
PostgreSQL Summary Stats and Window Functions

โครงสร้างของ 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 and Window Functions

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

PostgreSQL Summary Stats and Window Functions

Preparing Video For Download...