Window Functions ① — OVER và PARTITION BY

Dữ liệu dùng trong bài — sales và employee

Một window function tính những thứ như tổng theo nhóm, xếp hạng và tổng lũy kế theo từng dòng, rồi thêm một cột duy nhất vào kết quả của bạn.

Trong khi GROUP BY gom nhiều dòng lại thành một dòng tóm tắt, window function giữ nguyên mọi dòng chi tiết và chỉ thêm giá trị tính được kế bên.

Trước khi bắt đầu các bài tập, hãy xem định nghĩa cột và dữ liệu mẫu của hai bảng sẽ dùng — sales và employee.

① Chạy PRAGMA table_info(sales); và PRAGMA table_info(employee); để kiểm tra định nghĩa cột của cả hai bảng.

② Chạy SELECT * FROM sales LIMIT 5; và SELECT * FROM employee LIMIT 5; để xem nhanh 5 dòng đầu của mỗi bảng. Chú ý rằng cột dept_id trong employee là NULL với một vài nhân viên.

OVER () — tính trên mọi dòng và thêm một cột

Hãy bắt đầu với dạng đơn giản nhất: aggregate(column) OVER ().

Với cặp ngoặc rỗng, window bao trùm toàn bộ tập kết quả (mọi dòng đã được select).

Thêm SUM(amount) OVER () như cột thứ 4 sẽ cho bạn một cột grand_total mới mà mọi dòng đều có cùng giá trị (tổng tất cả).

Số dòng không thay đổi — bạn chỉ có thêm một cột nữa.

-- OVER () với ngoặc rỗng bao trùm toàn bộ tập kết quả
-- Cả 5 dòng chi tiết giữ nguyên, và grand_total mang cùng giá trị trên mọi dòng
SELECT sale_id, emp_id, amount,
  SUM(amount) OVER ()   AS grand_total,
  COUNT(*)    OVER ()   AS row_count,
  AVG(amount) OVER ()   AS grand_avg
FROM sales;

Hãy hình dung bạn muốn một danh sách giao dịch riêng lẻ, với tổng chung và tỷ phần của từng giao dịch trong tổng đó hiển thị kế bên. (Nếu truy vấn của bạn chạy đúng, phần giải thích sẽ xuất hiện.)

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

② Thêm SUM(amount) OVER () làm cột thứ 4 với alias là grand_total. Mọi dòng sẽ mang cùng tổng chung.

③ Thêm *amount 100.0 / SUM(amount) OVER ()** làm cột thứ 5 với alias pct_of_total — tỷ phần của mỗi dòng trong tổng chung.

④ 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.

So sánh với GROUP BY — gom lại hay giữ nguyên các dòng

Đặt GROUP BY và window function cạnh nhau làm sự khác biệt hiện rõ.

Với GROUP BY emp_id + SUM(amount), bạn gom các dòng của mỗi nhân viên thành một dòng duy nhất chỉ trả về tổng.

Với SUM(amount) OVER (), mọi dòng chi tiết được giữ nguyên, và bạn chỉ có thêm một cột chứa giá trị tổng hợp.

Chọn dạng nào tùy vào việc bạn muốn: chỉ tổng, hay vừa tổng vừa chi tiết trong cùng một bảng.

-- GROUP BY: gom thành 1 dòng cho mỗi nhân viên — chi tiết biến mất
SELECT emp_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM sales
WHERE emp_id = 2
GROUP BY emp_id;
-- kết quả: 1 dòng (emp_id=2, cnt=5, total=2150000)

-- Window function: giữ lại cả 5 dòng chi tiết, với tổng chung kèm bên
SELECT sale_id, emp_id, amount,
  SUM(amount) OVER () AS grand_total
FROM sales
WHERE emp_id = 2;
-- kết quả: 5 dòng, grand_total = 2150000 trên mọi dòng

Hãy viết cùng một phép tính "lấy tổng" theo hai cách — một lần với GROUP BY và một lần với OVER () — rồi so sánh kết quả cạnh nhau.

① Bắt đầu với bản GROUP BY: lọc sales theo emp_id = 2, sau đó select emp_id, COUNT(*) AS cnt, và SUM(amount) AS total, rồi gộp với GROUP BY emp_id. Xem nó gom thành 1 dòng ra sao.

② Sau đó viết bản window function: giữ các dòng chi tiết của emp_id = 2 và thêm SUM(amount) OVER () AS grand_total. Select sale_id, emp_id, amount, grand_total. Bạn sẽ nhận được 5 dòng, mỗi dòng đều có cùng tổng kèm theo.

③ Chạy cả hai truy vấn trong cùng một console và so sánh "1 dòng" với "5 dòng" bằng mắt mình. Bộ chấm điểm xem kết quả của window function, nên hãy kết thúc ② với ORDER BY sale_id để cố định thứ tự.

PARTITION BY — chia window thành các nhóm

Trong khi OVER () coi toàn bộ kết quả là một window duy nhất (phạm vi các dòng để tổng hợp), OVER (PARTITION BY column) chia window theo các giá trị của cột đó.

Với PARTITION BY emp_id, window được chia theo từng nhân viên, và mỗi dòng nhận tổng của chính nhân viên đó kế bên.

Trong sơ đồ bên dưới, cả 4 dòng của emp_id=1 nhận cùng giá trị "tổng của emp_id=1 = 970,000", và cả 5 dòng của emp_id=2 nhận "tổng của emp_id=2 = 2,150,000".

-- Thêm tổng, trung bình, và số lượng theo nhân viên vào mọi dòng chi tiết
SELECT sale_id, emp_id, amount,
  SUM(amount)   OVER (PARTITION BY emp_id) AS emp_total,
  AVG(amount)   OVER (PARTITION BY emp_id) AS emp_avg,
  COUNT(*)      OVER (PARTITION BY emp_id) AS emp_count
FROM sales
ORDER BY emp_id, sale_id;

-- Phần trăm trong tổng của nhân viên: giao dịch này chiếm bao nhiêu phần trong tổng của nhân viên?
SELECT sale_id, emp_id, amount,
  amount * 100.0 / SUM(amount) OVER (PARTITION BY emp_id) AS pct_of_emp
FROM sales
ORDER BY emp_id, sale_id;

Hãy hình dung bạn muốn hiển thị từng giao dịch kèm với tổng, trung bình và số lượng giao dịch của nhân viên đó.

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

② Dùng window phân vùng theo nhân viên, thêm ba cột với alias emp_total (tổng), emp_avg (trung bình), và emp_count (số lượng).

③ 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_id (tăng dần theo emp_id, rồi theo sale_id trong mỗi nhân viên).

Kết hợp với JOIN để lấy tổng hợp theo phòng ban

Window function cũng làm việc trên kết quả của JOIN.

JOIN sales với employee để lấy dept_id (ID phòng ban), sau đó PARTITION BY e.dept_id cho phép bạn gắn tổng doanh số của phòng ban của nhân viên đó vào mọi dòng giao dịch.

Cột PARTITION BY có thể là cột bạn lấy về từ bảng khác qua JOIN.

Window function được đánh giá sau FROM / JOIN / WHERE, khi tập dòng đã được xác lập — nên luồng đi là "JOIN để lắp ráp các dòng cần thiết → phân phối tổng hợp window trên tập dòng đó".

-- JOIN sales với employee và thêm tổng cùng số lượng theo phòng ban vào mọi dòng chi tiết
SELECT s.sale_id, e.name, e.dept_id, s.amount,
  SUM(s.amount) OVER (PARTITION BY e.dept_id) AS dept_total,
  COUNT(*)      OVER (PARTITION BY e.dept_id) AS dept_sales
FROM sales AS s
JOIN employee AS e ON s.emp_id = e.emp_id
ORDER BY e.dept_id, s.sale_id;

Hãy hình dung bạn muốn một báo cáo liệt kê từng giao dịch kèm tên nhân viên và phòng ban, cùng tổng doanh số của phòng ban đó kế bên.

① INNER JOIN sales (alias s) với employee (alias e) trên s.emp_id = e.emp_id.

② Select s.sale_id, e.name, e.dept_id, và s.amount.

③ Với window phân vùng theo phòng ban (PARTITION BY e.dept_id), thêm dept_total làm cột thứ 5 hiển thị tổng doanh số của phòng ban.

④ Bộ chấm điểm kiểm tra thứ tự dòng, nên hãy kết thúc với ORDER BY e.dept_id, s.sale_id (tăng dần theo dept_id, rồi theo sale_id trong mỗi phòng ban).