Window function trong SQLite: OVER, PARTITION BY và frame

Window function thêm một cột mà không gộp các hàng

GROUP BY gộp nhiều hàng thành một. Window function (hàm cửa sổ) làm việc khác: nó tính một giá trị trên một tập các hàng liên quan nhưng vẫn giữ mọi hàng đầu vào trong kết quả. Bạn có cả chi tiết từng hàng lẫn giá trị tổng hợp, nằm cạnh nhau.

Cấu trúc luôn giống nhau: một hàm, rồi đến OVER (...).

CREATE TABLE sales (
    employee TEXT,
    region   TEXT,
    amount   INTEGER
);

INSERT INTO sales VALUES
    ('Ada',   'EU', 100),
    ('Boris', 'EU', 250),
    ('Cleo',  'EU', 175),
    ('Dan',   'US', 300),
    ('Eve',   'US', 150);

SELECT employee, region, amount,
       SUM(amount) OVER () AS total_all
FROM sales;

Cột total_all hiển thị tổng của mọi hàng, lặp lại trên từng dòng. Các hàng gốc vẫn giữ nguyên. Hãy so sánh với SELECT SUM(amount) FROM sales: cùng một con số, nhưng chỉ trả về một hàng. Window function cho bạn cả hai góc nhìn cùng lúc.

PARTITION BY: tổng hợp trong từng nhóm

OVER () để trống sẽ tổng hợp trên toàn bảng. Thêm PARTITION BY để tổng hợp trong từng nhóm, khá giống GROUP BY, nhưng một lần nữa, không gộp các hàng lại.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

Mỗi hàng nhận được tổng của khu vực mình và tỷ lệ của nó trong tổng đó. Với GROUP BY thông thường, bạn sẽ mất chi tiết theo từng nhân viên. Đó là lợi thế lớn nhất của window function: có cả chi tiết lẫn tổng hợp trong một truy vấn.

Xếp hạng: ROW_NUMBER, RANK, DENSE_RANK

Nhóm hàm xếp hạng đánh số các hàng theo ORDER BY bên trong OVER. Ba hàm này khác nhau ở cách xử lý các giá trị bằng nhau.

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES
    ('Ada', 90), ('Boris', 85), ('Cleo', 90),
    ('Dan', 70), ('Eve', 85);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dr
FROM scores;

Cách đọc kết quả:

• ROW_NUMBER() luôn duy nhất: các hàng bằng nhau được phân thứ tự tùy ý. Dùng nó khi bạn cần một số ổn định, riêng biệt cho mỗi hàng.
• RANK() cho các hàng bằng nhau cùng một hạng, rồi bỏ qua các số tiếp theo. Hai người chơi cùng hạng 1 thì người tiếp theo là hạng 3.
• DENSE_RANK() cũng cho đồng hạng, nhưng không bỏ số. Hạng tiếp theo là 2.

Để lấy "top N mỗi nhóm", hãy kết hợp xếp hạng với PARTITION BY rồi lọc ở truy vấn bên ngoài, vì WHERE không thể tham chiếu trực tiếp đến window function:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT *
FROM (
    SELECT employee, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
WHERE rn <= 2;

Hai người có doanh số cao nhất ở mỗi khu vực.

LAG và LEAD: xem các hàng lân cận

LAG(col) trả về giá trị của col ở hàng trước đó trong window. LEAD(col) thì nhìn về phía trước. Cả hai rất hợp cho các câu hỏi về thay đổi theo thời gian.

CREATE TABLE temps (day INTEGER, celsius INTEGER);
INSERT INTO temps VALUES (1, 18), (2, 21), (3, 19), (4, 24), (5, 22);

SELECT day, celsius,
       LAG(celsius)  OVER (ORDER BY day) AS yesterday,
       celsius - LAG(celsius) OVER (ORDER BY day) AS delta
FROM temps;

yesterday của hàng đầu tiên là NULL, vì không có gì đứng trước nó. Bạn có thể đặt giá trị mặc định: LAG(celsius, 1, celsius) OVER (ORDER BY day) sẽ dùng giá trị của hôm nay khi không có hàng trước.

LEAD là phiên bản ngược lại. Kết hợp cả hai với PARTITION BY để có chuỗi giá trị theo từng nhóm, chẳng hạn so sánh doanh số tháng này với tháng trước trong từng khu vực.

Tổng lũy kế với window frame

Thêm ORDER BY bên trong OVER, và các hàm tổng hợp như SUM, AVG, COUNT bắt đầu tính lũy kế:

CREATE TABLE deposits (day INTEGER, amount INTEGER);
INSERT INTO deposits VALUES (1, 50), (2, 30), (3, 70), (4, 20), (5, 40);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day
FROM deposits;

Có hai điều cần chú ý:

• SUM(amount) OVER (ORDER BY day) là tổng lũy kế. Khi bạn viết ORDER BY mà không chỉ rõ frame, frame mặc định là "từ đầu window đến hàng hiện tại".
• Cột thứ hai dùng frame tường minh: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Đó là một cửa sổ trượt 3 hàng, tức là trung bình động.

Cách hình dung về frame: mỗi window function được tính trên một frame gồm các hàng, xác định tương đối so với hàng hiện tại. Các frame thường gặp:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: tổng lũy kế (mặc định ngầm định).
• ROWS BETWEEN N PRECEDING AND CURRENT ROW: cửa sổ trượt phía sau.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: toàn bộ partition.

ROWS đếm theo hàng vật lý. Ngoài ra còn có RANGE, nhóm theo giá trị, rất tiện khi cột trong ORDER BY có các giá trị bằng nhau và bạn muốn chúng được tính như một bước.

FIRST_VALUE, LAST_VALUE, NTILE

Thêm vài window function đáng biết:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT employee, region, amount,
       FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY amount DESC) AS half
FROM sales;

• FIRST_VALUE và LAST_VALUE trả về giá trị đầu tiên hoặc cuối cùng trong frame. Với LAST_VALUE, hãy để ý frame: frame mặc định kết thúc ở CURRENT ROW, nên thường bạn cần ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING để lấy đúng giá trị cuối của partition.
• NTILE(n) chia các hàng thành n nhóm có kích thước gần bằng nhau, hữu ích cho tứ phân vị, phân vị hay chia nhóm kiểu A/B.

Đặt tên window với WINDOW

Khi nhiều cột dùng chung một mệnh đề OVER (...), việc lặp lại trở nên nhàm chán. SQLite cho phép bạn đặt tên window một lần rồi dùng lại:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount)   OVER region_window AS region_total,
       AVG(amount)   OVER region_window AS region_avg,
       RANK()        OVER region_window AS region_rank
FROM sales
WINDOW region_window AS (PARTITION BY region ORDER BY amount DESC);

Cùng truy vấn đó, gọn hơn. Mệnh đề WINDOW đứng sau WHERE/GROUP BY/HAVING và trước ORDER BY.

Window function và GROUP BY

Cả hai đều liên quan đến tổng hợp, nhưng trả lời những câu hỏi khác nhau:

• GROUP BY thu gọn. Mỗi nhóm một hàng. Dùng nó khi bạn chỉ cần phần tóm tắt.
• Window function giữ nguyên. Mọi hàng đầu vào đều còn, kèm theo các cột được tính thêm.

Nếu bạn thấy mình đang GROUP BY rồi lại join các giá trị tổng hợp ngược về bảng gốc, đó là dấu hiệu rõ ràng rằng một window function có thể làm việc đó chỉ trong một truy vấn.

Vài lỗi dễ mắc

• WHERE không thể tham chiếu window function. Bộ lọc chạy trước khi window được tính. Bọc truy vấn trong một subquery hoặc CTE rồi lọc ở cấp bên ngoài.
• Frame ngầm định dễ gây bất ngờ. SUM(x) OVER (ORDER BY y) là tổng lũy kế vì frame mặc định là RANGE UNBOUNDED PRECEDING. Nếu bạn muốn tổng của cả partition, hãy viết OVER (PARTITION BY ...) không có ORDER BY, hoặc chỉ rõ frame.
• LAST_VALUE làm ai cũng bất ngờ lần đầu. Với frame mặc định kết thúc ở hàng hiện tại, nó trả về giá trị hiện tại chứ không phải giá trị cuối của partition. Hãy ghi đè frame.
• Window function cần SQLite 3.25 trở lên (phát hành năm 2018). Hầu hết bản cài đặt hiện đại đều có, nhưng một số môi trường nhúng vẫn còn chậm cập nhật.

Tiếp theo: generated column

Window function là tính toán lúc truy vấn. Trang tiếp theo nói về tính toán lúc lưu trữ: generated column, nơi giá trị của cột được định nghĩa bằng một biểu thức và tự động cập nhật khi dữ liệu gốc thay đổi.