Tiêu chí ROWS và RANGE

Hiện tại, chúng ta chưa thể linh hoạt khi chọn số hàng ở trước hoặc sau để tính đến. Giờ đây, bạn có thể làm điều đó với tiêu chí ROWS và RANGE. Để sử dụng chúng, ta viết:

OVER (ROWS BETWEEN --START-- AND --END--)

OVER (RANGE BETWEEN --START-- AND --END--)

Và ta có thể chỉ định các tùy chọn sau:

• CURRENT ROW: hàng hiện tại
• n PRECEDING: các hàng trước hàng hiện tại
• n FOLLOWING: các hàng sau hàng hiện tại 

Điểm khác biệt giữa ROWS & RANGE là tiêu chí ROWS không xét đến các giá trị, mà chỉ xét vị trí, còn RANGE xác định cửa sổ theo khoảng giá trị thay vì vị trí hàng.

Với RANGE, ta phải chỉ định ORDER BY --column_name--, vì nếu không, nó sẽ không biết cách chọn cửa sổ.

Ví dụ:

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Ở đây, câu lệnh tạo một cửa sổ gồm hàng hiện tại, hàng trước đó và hàng sau đó.

ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

Ở đây, câu lệnh tạo một cửa sổ gồm cấp độ hiện tại, một cấp độ trước đó và một cấp độ sau đó cho mỗi cấp độ (được sắp xếp theo thứ tự tăng dần). Nếu cấp độ hiện tại là 5 thì cửa sổ sẽ gồm các cấp độ 4, 5 và 6.

Lưu ý: Việc dùng RANGE BETWEEN có thể khiến cửa sổ của bạn bao gồm nhiều hàng hơn, vì nó bao gồm tất cả các hàng có cùng giá trị với những hàng trong phạm vi, trong khi ROWS BETWEEN luôn bao gồm cùng một số hàng (nếu có đủ hàng trong tập dữ liệu). 

Ngoài ra, với ngày tháng được lưu dưới dạng văn bản (chẳng hạn '2024-01-05'), độ lệch RANGE không thực hiện phép tính ngày tháng: với 1 PRECEDING, cửa sổ chỉ chứa các hàng có cùng ngày. Để đếm số ngày, hãy sắp xếp theo một con số, chẳng hạn julianday(date).

Đây là một ví dụ đơn giản để minh họa sự khác nhau giữa ROWS và RANGE:

Khi dùng ROWS:

SELECT employee_name, salary,
    AVG(salary) OVER (
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as avg_salary_rows

Cách dùng RANGE:

SELECT employee_name, salary,
    AVG(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
    ) as avg_salary_range