การแบ่งพาร์ติชันและ window functions

การวิเคราะห์อนุกรมเวลาใน PostgreSQL

Jasmin Ludolf

Content Developer, DataCamp

การแบ่งพาร์ติชัน

แบ่งตารางขนาดใหญ่ออกเป็นส่วนย่อย หรือพาร์ติชัน
มีประโยชน์เมื่อต้องจัดการกับชุดข้อมูลขนาดใหญ่
ข้อมูลอนุกรมเวลา:
- มีข้อมูลใหม่เข้ามาทุกวัน
- แบ่งพาร์ติชันตามช่วงวันที่

ภาพแสดงการแบ่งตารางขนาดใหญ่ออกเป็นสองส่วนย่อย

Range

ขอบเขตของ range:
- รวมขอบเขตล่าง
- ไม่รวมขอบเขตบน
ทางเลือกแทน range: แบ่งพาร์ติชันตามเหตุการณ์หรือหมวดหมู่

Range 1 : 2000-2010

2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009

Range 2 : 2010-2020

2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020

Partition by

PARTITION BY RANGE

CREATE TABLE timetable (
    date_info DATE,
    train_id INTEGER,
    departure_time TIMESTAMP,
    arrival_time TIMESTAMP,
    delay INTEGER
) PARTITION BY RANGE (date_info);

Partition of

PARITION OF FOR VALUES FROM
timetable คือตารางที่แบ่งพาร์ติชันแล้ว โดยใช้คอลัมน์ date_info ด้วยวิธี RANGE

CREATE TABLE timetable_y2020 PARTITION OF timetable
    FOR VALUES FROM ('2020-01-01') to ('2020-12-31');

Window functions

ช่วยให้คิวรี SQL ทั่วไปง่ายขึ้น
คืนค่าหนึ่งค่าต่อแต่ละแถว (อาจขึ้นอยู่กับแถวอื่นด้วย)

Window : กลุ่มแถวที่ฟังก์ชันทำงานด้วย
Window function : ฟังก์ชันที่ใช้กับแถวเหล่านั้น หรือ window
ฟังก์ชัน aggregate ใช้เปรียบเทียบ แต่ผลลัพธ์จะไม่ถูกรวมเป็นค่าเดียว

Over clause

clause OVER บ่งบอกว่าเป็น window function
ตัวอย่าง: regional_timetable มีข้อมูลวันที่ เวลา รหัส ความล่าช้า และภูมิภาค
หากไม่มี PARTITION BY: window function จะถือว่าตารางทั้งหมดเป็นพาร์ติชันเดียว

SELECT region, train_id, delay, AVG(delay) OVER (PARTITION BY region)
FROM regional_timetable;

|region|train_id|   delay|     avg|
|------|--------|--------|--------|
| north|       6|00:00:05|00:00:05|
| north|       2|00:00:03|00:00:05|
| north|       5|00:00:07|00:00:05|
|  east|       1|00:00:10|00:00:21|
|  east|       3|00:00:32|00:00:21|
| south|       8|00:01:00|00:01:00|

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

การวิเคราะห์อนุกรมเวลาใน PostgreSQL