Công thức mảng và window functions

Bạn sẽ học được

• Vì sao công thức mảng trong Excel khó bảo trì.
• Cách dùng window function thay cho công thức kéo xuống.
• Tính lũy kế, xếp hạng và phần trăm trên tổng.
• So sánh với kỳ trước bằng LAG.
• Khi nào nên dùng GROUP BY, khi nào dùng window.

Khái niệm cốt lõi

Công thức mảng (array formula) trong Excel như SUMPRODUCT, {=SUM(IF(...))} hoặc SEQUENCE cho phép tính trên cả vùng mà không cần kéo từng dòng. Mạnh nhưng khó đọc, dễ vỡ khi thêm dòng, và không ai khác hiểu vì sao nó đúng.

SQL có window function: tính toán trên một "cửa sổ" dòng liên quan nhưng không gộp các dòng lại. Khác GROUP BY ở chỗ mỗi dòng vẫn giữ nguyên, kèm thêm giá trị tính được.

SELECT ...,
       SUM(x) OVER (PARTITION BY nhom ORDER BY ngay) AS luy_ke
FROM ...

Ba thành phần:

• PARTITION BY: chia nhóm (giống nhóm PivotTable).
• ORDER BY: thứ tự trong nhóm (quan trọng với lũy kế, LAG).
• Khung ROWS BETWEEN ...: phạm vi tính (mặc định là từ đầu đến dòng hiện tại khi có ORDER BY).

Lũy kế theo tháng

WITH thang AS (
  SELECT date_trunc('month', o.order_date)::date AS thang,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status = 'completed'
  GROUP BY 1
)
SELECT thang,
       doanh_thu,
       SUM(doanh_thu) OVER (ORDER BY thang) AS luy_ke
FROM thang
ORDER BY thang;

Mỗi dòng vẫn là một tháng; cột luy_ke cộng dồn từ tháng đầu. Trong Excel, đây là công thức =SUM($B$2:B2) kéo xuống — nhưng nếu chèn dòng giữa, vùng tham chiếu có thể sai. Window function không có vấn đề đó vì thứ tự do ORDER BY quyết định.

Xếp hạng trong nhóm

SELECT c.city,
       p.name,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu,
       RANK() OVER (
         PARTITION BY c.city
         ORDER BY SUM(oi.quantity * oi.unit_price) DESC
       ) AS hang
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'completed'
GROUP BY c.city, p.name
ORDER BY c.city, hang;

RANK cho cùng hạng khi hòa và nhảy số tiếp theo (1, 1, 3). DENSE_RANK không nhảy (1, 1, 2). ROW_NUMBER luôn khác nhau (1, 2, 3) nhưng thứ tự hòa là tùy ý — nếu cần tất định, thêm tiêu chí phụ vào ORDER BY của window.

Phần trăm trên tổng

SELECT c.city,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu,
       ROUND(
         100.0 * SUM(oi.quantity * oi.unit_price)
         / SUM(SUM(oi.quantity * oi.unit_price)) OVER (),
         2
       ) AS phan_tram
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY c.city
ORDER BY doanh_thu DESC;

OVER () không có PARTITION BY nghĩa là cửa sổ toàn bộ kết quả — mẫu số là tổng doanh thu tất cả thành phố. Đây là cách thay công thức =B2/SUM($B$2:$B$10) mà không cần biết trước số dòng.

Điểm dễ sai: không được dùng SUM(doanh_thu) OVER () trực tiếp trong cùng cấp SELECT khi doanh_thu là alias vừa tạo — phải lặp biểu thức hoặc dùng CTE. Cách gọn hơn:

WITH theo_thanh_pho AS (
  SELECT c.city, SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status = 'completed'
  GROUP BY c.city
)
SELECT city,
       doanh_thu,
       ROUND(100.0 * doanh_thu / SUM(doanh_thu) OVER (), 2) AS phan_tram
FROM theo_thanh_pho
ORDER BY doanh_thu DESC;

So sánh với kỳ trước

WITH thang AS (
  SELECT date_trunc('month', o.order_date)::date AS thang,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status = 'completed'
  GROUP BY 1
)
SELECT thang,
       doanh_thu,
       LAG(doanh_thu) OVER (ORDER BY thang) AS thang_truoc,
       doanh_thu - LAG(doanh_thu) OVER (ORDER BY thang) AS chenh_lech
FROM thang
ORDER BY thang;

LAG lấy giá trị dòng trước; LEAD lấy dòng sau. Đây là cách thay công thức =B3-B2 kéo xuống, nhưng tự động đúng khi dữ liệu thiếu tháng — dòng thiếu không có thì LAG lấy tháng gần nhất trước đó, nên hãy kiểm tra lịch tháng nếu báo cáo yêu cầu đủ tháng.

GROUP BY hay window?

Window thường dùng sau GROUP BY, trên kết quả đã tổng hợp. Không dùng window để thay GROUP BY khi chỉ cần một dòng mỗi nhóm.

Thực hành

1. Tính doanh thu lũy kế theo tháng.
2. Xếp hạng sản phẩm theo doanh thu trong từng thành phố.
3. Tính phần trăm doanh thu của mỗi thành phố trên tổng.
4. So sánh doanh thu tháng này với tháng trước bằng LAG.
5. Thử RANK, DENSE_RANK, ROW_NUMBER trên dữ liệu có hòa.

Lỗi thường gặp

Tóm tắt

• Window function tính trên cửa sổ dòng mà không gộp dòng.
• PARTITION BY chia nhóm, ORDER BY quyết định thứ tự.
• Lũy kế, xếp hạng, phần trăm trên tổng, so kỳ trước đều dùng window.
• Dùng GROUP BY để tổng hợp, window để thêm ngữ cảnh cho từng dòng.
• Luôn kiểm tra dữ liệu thiếu kỳ khi dùng LAG.