Phân vùng và hàm cửa sổ

Phân tích chuỗi thời gian trong PostgreSQL

Jasmin Ludolf

Content Developer, DataCamp

Phân vùng

Chia bảng lớn thành các tập con nhỏ (phân vùng)
Hữu ích với tập dữ liệu lớn
Dữ liệu chuỗi thời gian:
- dữ liệu mới mỗi ngày
- phân vùng theo khoảng ngày

Minh họa một bảng lớn được chia thành hai tập con

Một khoảng

Ranh giới khoảng:
- bao gồm đầu dưới
- loại trừ đầu trên
Thay cho khoảng: phân vùng theo sự kiện hoặc danh mục

Khoảng 1: 2000-2010

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

Khoảng 2: 2010-2020

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

Phân vùng theo

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);

Phân vùng của

PARITION OF FOR VALUES FROM
timetable là bảng được phân vùng, phân vùng theo date_info bằng phương pháp RANGE

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

Hàm cửa sổ

Đơn giản hóa truy vấn SQL thông thường
Trả về giá trị cho từng hàng (có thể phụ thuộc hàng khác)

Cửa sổ: tập hàng mà hàm áp dụng
Hàm cửa sổ: hàm áp dụng trên cửa sổ đó
Hàm tổng hợp mang tính so sánh, nhưng kết quả không gộp thành một giá trị

Mệnh đề OVER

Mệnh đề OVER cho biết hàm cửa sổ
Ví dụ: regional_timetable có ngày, giờ, id, độ trễ và vùng
Nếu không có PARTITION BY: hàm cửa sổ xem toàn bộ bảng là một phân vùng

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|

Hãy thực hành!

Phân tích chuỗi thời gian trong PostgreSQL