Window Functions ② — ORDER BY và Frame (ROWS / RANGE)

Window với ORDER BY — tạo tổng lũy kế

Thêm ORDER BY vào trong OVER và mục tiêu tổng hợp chuyển thành "từ đầu window đến dòng hiện tại" — giá trị tăng dần theo từng dòng, tạo nên tổng lũy kế (tổng từ đầu đến dòng hiện tại).

Dạng là SUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date).

PARTITION BY emp_id chia window theo từng nhân viên, ORDER BY sale_date sắp xếp giao dịch của mỗi nhân viên theo ngày, sau đó tổng lũy kế được tính theo từng nhân viên.

Sơ đồ bên dưới theo dấu cách tổng lũy kế tăng dần qua 5 giao dịch của emp_id=29 theo thứ tự ngày.

-- Không có ORDER BY: mỗi dòng nhận tổng không đổi của nhân viên
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY emp_id) AS emp_total
FROM sales
ORDER BY emp_id, sale_date;

-- Với ORDER BY: tổng lũy kế theo nhân viên từ đầu đến dòng hiện tại
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS running_total
FROM sales
ORDER BY emp_id, sale_date;

Hãy hình dung bạn muốn tổng lũy kế doanh số theo từng nhân viên, sắp theo ngày. (Nếu truy vấn của bạn chạy đúng, phần giải thích sẽ xuất hiện.)

① Select emp_id, sale_date, và amount từ sales.

② Tính SUM(amount) trên window phân vùng theo từng nhân viên và sắp theo ngày, rồi thêm làm cột thứ 4 với alias running_total.

③ Bộ chấm điểm kiểm tra thứ tự dòng, nên hãy kết thúc với ORDER BY emp_id, sale_date.

Frame — các dòng thực sự được tổng hợp trong window

Khi bạn thêm ORDER BY vào window function, phạm vi các dòng mà mỗi dòng thực sự tổng hợp được gọi là frame.

Nếu bạn không viết frame một cách tường minh, mặc định là RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (từ đầu window đến dòng hiện tại).

Đó chính là lý do thêm ORDER BY ở bài tập trước cho bạn tổng lũy kế — frame mặc định này đang làm việc.

-- A: bỏ frame (mặc định = RANGE UNBOUNDED PRECEDING ... CURRENT ROW)
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS run_default
FROM sales WHERE emp_id = 14
ORDER BY sale_date;

-- B: frame mặc định viết tường minh (cùng kết quả với A)
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (
    PARTITION BY emp_id ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS run_explicit
FROM sales WHERE emp_id = 14
ORDER BY sale_date;

Frame được xây từ 5 từ khóa sau, dùng để nói "từ đâu" và "đến đâu" so với dòng hiện tại.

Hãy xác nhận hành vi tổng lũy kế bằng cách viết frame một cách tường minh.

① Select emp_id, sale_date, và amount từ sales.

② Trên window phân vùng theo từng nhân viên và sắp theo sale_date, tính SUM(amount) với frame tường minh ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, và thêm làm cột thứ 4 với alias running_total.

③ Bộ chấm điểm kiểm tra thứ tự dòng, nên hãy kết thúc với ORDER BY emp_id, sale_date.

ROWS BETWEEN n PRECEDING — tổng di động và trung bình di động

Đặt frame thành ROWS BETWEEN n PRECEDING AND CURRENT ROW và phép tổng hợp thu hẹp thành "n dòng gần nhất + dòng hiện tại".

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW bao trùm 3 dòng gần nhất (bao gồm dòng hiện tại), nên SUM trở thành tổng di động và AVG trở thành trung bình di động.

Đây là dạng dùng phổ biến để làm mịn xu hướng gần đây.

Gần đầu window thì frame có ít dòng để lấy (dòng 1 có 1 dòng, dòng 2 có 2 dòng, dòng 3 trở đi có 3 dòng).

Sơ đồ bên dưới đi qua 5 giao dịch của emp_id=14 theo từng dòng, hiển thị nội dung của frame tại mỗi "dòng hiện tại".

-- Trung bình di động của 3 giao dịch gần nhất (2 dòng trước + hiện tại)
SELECT emp_id, sale_date, amount,
  AVG(amount) OVER (
    PARTITION BY emp_id ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg3
FROM sales WHERE emp_id = 2
ORDER BY sale_date;

-- Tổng di động của 2 giao dịch gần nhất (1 dòng trước + hiện tại) dùng cùng dạng
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (
    PARTITION BY emp_id ORDER BY sale_date
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_sum2
FROM sales WHERE emp_id = 2
ORDER BY sale_date;

Hãy hình dung bạn muốn tổng di động theo từng nhân viên cho 3 giao dịch gần nhất (2 dòng cuối + hiện tại), sắp theo ngày.

① Select emp_id, sale_date, và amount từ sales.

② Trên window phân vùng theo từng nhân viên và sắp theo sale_date, tính SUM(amount) với frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, và thêm làm cột thứ 4 với alias moving_sum3.

③ Bộ chấm điểm kiểm tra thứ tự dòng, nên hãy kết thúc với ORDER BY emp_id, sale_date.

ROWS vs. RANGE — cách xử lý các dòng trùng giá trị

Frame có hai chế độ đếm: ROWS và RANGE.

ROWS đếm dòng vật lý ("2 dòng trước" nghĩa là đúng 2 dòng). Ngay cả khi có giá trị trùng, mỗi dòng vẫn được đếm riêng, nên tổng lũy kế tăng theo từng dòng.

RANGE gom các dòng có cùng giá trị ORDER BY (trùng) vào một nhóm. Các dòng trùng nhau dùng chung frame và kết thúc với cùng tổng lũy kế.

Hai sơ đồ bên dưới cho thấy ROWS và RANGE xử lý ra sao với 3 dòng có amount = 100000.

-- ORDER BY amount có 3 dòng trùng tại 100000, nên ROWS và RANGE khác nhau
SELECT sale_id, amount,
  SUM(amount) OVER (ORDER BY amount ROWS  UNBOUNDED PRECEDING) AS rows_run,
  SUM(amount) OVER (ORDER BY amount RANGE UNBOUNDED PRECEDING) AS range_run
FROM sales
WHERE amount = 100000
ORDER BY sale_id;
-- ROWS đếm từng dòng một; RANGE gói 3 dòng trùng 100000 vào một frame

Hãy hình dung bạn muốn thấy, trong một kết quả duy nhất, tổng lũy kế thay đổi thế nào giữa ROWS và RANGE khi nhiều dòng có cùng giá trị. Có 3 giao dịch với amount = 100000.

① Select sale_id và amount từ sales.

② Trên window sắp theo amount, thêm SUM(amount) với frame ROWS UNBOUNDED PRECEDING alias rows_run, và một SUM(amount) khác với frame RANGE UNBOUNDED PRECEDING alias range_run.

③ Lọc chỉ các dòng có amount = 100000.

④ Bộ chấm điểm kiểm tra thứ tự dòng, nên hãy kết thúc với ORDER BY sale_id.