SQL từ cơ bản đến nâng cao
Trang chủ / Module 8
🪟 Module 8 / 16 · 7-8 giờ

Window function: phân tích không cần gom dòng

Xếp hạng, top-N mỗi nhóm, tổng luỹ kế, trung bình trượt, so sánh kỳ trước, tỉ trọng — tất cả trong một câu truy vấn mà vẫn giữ nguyên từng dòng chi tiết.

OVERPARTITION BYLAG/LEADRunning total
📌 Vì sao module này quan trọng

Window function là bước nhảy lớn nhất từ "SQL cơ bản" sang "SQL phân tích". Nhiều bài toán trước đây cần self-join phức tạp hoặc kéo dữ liệu về code xử lý (xếp hạng, so với kỳ trước, tổng luỹ kế, loại trùng giữ bản mới nhất) giờ chỉ là vài dòng. Đây cũng là chủ đề được hỏi nhiều nhất trong phỏng vấn SQL.

🎯 Mục tiêu học tập

  • Giải thích khác biệt giữa GROUP BY (gộp dòng) và window function (giữ dòng)
  • Dùng PARTITION BY, ORDER BY và hiểu frame (ROWS/RANGE) — tránh bẫy LAST_VALUE và tổng luỹ kế khi có giá trị trùng
  • Giải thành thạo: top-N mỗi nhóm, loại trùng, LAG/LEAD, tăng trưởng kỳ trước, tỉ trọng, trung bình trượt
  • Biết rằng window function không dùng được trong WHERE và cách lọc kết quả của nó

Ý tưởng: gom mà không mất dòng

GROUP BY biến nhiều dòng thành một. Window function tính toán trên một "cửa sổ" các dòng liên quan nhưng giữ nguyên từng dòng — thêm một cột kết quả vào mỗi dòng. Cú pháp: hàm(...) OVER (PARTITION BY nhóm ORDER BY thứ_tự khung_cửa_sổ).

  • PARTITION BY — chia dòng thành các nhóm độc lập (như GROUP BY nhưng không gộp). Bỏ qua = cả bảng là một nhóm.
  • ORDER BY trong OVER — thứ tự bên trong nhóm (cần cho xếp hạng, LAG, luỹ kế).
  • Frame — tập dòng cụ thể quanh dòng hiện tại để hàm tính trên đó (ROWS BETWEEN ...).
-- Mỗi sản phẩm kèm giá TB và chênh lệch so với TB của danh mục — vẫn giữ từng sản phẩm
SELECT category_id, name, price,
       round(avg(price) OVER (PARTITION BY category_id)) AS gia_tb_danh_muc,
       price - round(avg(price) OVER (PARTITION BY category_id)) AS chenh_lech
FROM products
ORDER BY category_id, price DESC
LIMIT 8;
Kết quả
 category_id |           name           |    price    | gia_tb_danh_muc |  chenh_lech
-------------+--------------------------+-------------+-----------------+--------------
           3 | Robot hút bụi            |  7490000.00 |         3970000 |   3520000.00
           3 | Quạt đứng cũ (ngừng bán) |   450000.00 |         3970000 |  -3520000.00
           4 | Điện thoại Nova Pro      | 18990000.00 |        12823333 |   6166667.00
           4 | Điện thoại Aurora X1     | 12990000.00 |        12823333 |    166667.00
           4 | Điện thoại Aurora Lite   |  6490000.00 |        12823333 |  -6333333.00
           5 | Laptop Zenbook 16 Pro    | 34900000.00 |        23100000 |  11800000.00
           5 | Laptop Zenbook 14        | 22500000.00 |        23100000 |   -600000.00
           5 | Laptop Student 15        | 11900000.00 |        23100000 | -11200000.00
(8 rows)
-- Window function được tính ở bước SELECT: sau WHERE/GROUP BY, nên không dùng được trong WHERE
SELECT name, price FROM products WHERE rank() OVER (ORDER BY price) = 1;
Lỗi PostgreSQL trả về
ERROR:  window functions are not allowed in WHERE
LINE 1: SELECT name, price FROM products WHERE rank() OVER (ORDER BY...
                                               ^
❌ Lỗi thường gặp — Muốn lọc theo kết quả window function ngay trong WHERE

Vì window chạy sau WHERE (thứ tự thực thi, module 1), phải bọc bằng CTE/subquery rồi lọc ở truy vấn ngoài: WITH x AS (SELECT ..., row_number() OVER (...) AS rn FROM ...) SELECT * FROM x WHERE rn = 1. (Một số DBMS như Snowflake/BigQuery có QUALIFY; PostgreSQL thì chưa.)

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

HàmKhi có giá trị bằng nhauVí dụ cho điểm 90, 90, 80
ROW_NUMBER()Vẫn đánh số khác nhau (tuỳ ý giữa các dòng hoà)1, 2, 3
RANK()Hạng bằng nhau, nhảy cách1, 1, 3
DENSE_RANK()Hạng bằng nhau, không nhảy1, 1, 2
NTILE(n)Chia thành n nhóm đều nhauPhân vị, nhóm 25%
-- Phòng Kỹ thuật có hai người cùng lương 28 triệu: xem ba hàm xử lý "hoà" khác nhau thế nào
SELECT full_name, salary,
       row_number() OVER w AS rn,
       rank()       OVER w AS rk,
       dense_rank() OVER w AS drk
FROM employees
WHERE dept = 'Kỹ thuật'
WINDOW w AS (ORDER BY salary DESC)          -- cửa sổ đặt tên, dùng lại được
ORDER BY salary DESC, id;
Kết quả
   full_name    |   salary    | rn | rk | drk
----------------+-------------+----+----+-----
 Lê Thu Hương   | 60000000.00 |  1 |  1 |   1
 Nguyễn Hải Yến | 42000000.00 |  2 |  2 |   2
 Vũ Gia Khang   | 38000000.00 |  3 |  3 |   3
 Đặng Ngọc Mai  | 28000000.00 |  4 |  4 |   4
 Bùi Anh Tuấn   | 28000000.00 |  5 |  4 |   4
 Phạm Đình Hiếu | 25000000.00 |  6 |  6 |   5
(6 rows)
-- TOP-N mỗi nhóm: 2 sản phẩm doanh thu cao nhất mỗi danh mục
WITH dt AS (
  SELECT p.category_id, p.name, sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100) AS doanh_thu
  FROM order_items oi
  JOIN orders o   ON o.id = oi.order_id AND o.status <> 'cancelled'
  JOIN products p ON p.id = oi.product_id
  GROUP BY p.category_id, p.id, p.name
), xep AS (
  SELECT *, row_number() OVER (PARTITION BY category_id ORDER BY doanh_thu DESC) AS hang
  FROM dt
)
SELECT category_id, hang, name, round(doanh_thu) AS doanh_thu
FROM xep WHERE hang <= 2
ORDER BY category_id, hang;
Kết quả
 category_id | hang |           name           | doanh_thu
-------------+------+--------------------------+------------
           3 |    1 | Robot hút bụi            |  295106000
           3 |    2 | Quạt đứng cũ (ngừng bán) |   52290000
           4 |    1 | Điện thoại Aurora X1     | 1551006000
           4 |    2 | Điện thoại Nova Pro      |  729216000
           5 |    1 | Laptop Zenbook 16 Pro    | 4125180000
           5 |    2 | Laptop Zenbook 14        | 3591000000
           6 |    1 | Sạc nhanh 65W            |   78008000
           6 |    2 | Tai nghe không dây Echo  |   76356000
           7 |    1 | Áo sơ mi công sở         |   62084400
           7 |    2 | Áo khoác gió             |   55062000
           8 |    1 | Giày chạy bộ Sprint      |  100749000
           8 |    2 | Giày da nam              |   64236000
           9 |    1 | Nồi chiên không dầu 5L   |  275258000
           9 |    2 | Máy xay sinh tố          |  103418000
(14 rows)
-- Loại trùng, giữ bản mới nhất: ở mỗi khách giữ đơn gần nhất
SELECT customer_id, id AS order_id, ordered_at::date AS ngay
FROM (SELECT o.*, row_number() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC, id DESC) AS rn FROM orders o) t
WHERE rn = 1 ORDER BY customer_id LIMIT 4;
Kết quả
 customer_id | order_id |    ngay
-------------+----------+------------
           1 |      108 | 2024-06-11
           2 |      247 | 2024-05-29
           3 |      332 | 2024-05-17
           4 |       39 | 2024-06-18
(4 rows)
-- Chia khách thành 4 nhóm chi tiêu (quartile)
SELECT customer_id, tong_chi, ntile(4) OVER (ORDER BY tong_chi DESC) AS nhom
FROM (SELECT o.customer_id, sum(p.amount) AS tong_chi FROM payments p JOIN orders o ON o.id = p.order_id GROUP BY 1) t
ORDER BY tong_chi DESC LIMIT 4;
Kết quả
 customer_id |   tong_chi   | nhom
-------------+--------------+------
          38 | 388803000.00 |    1
          50 | 362434000.00 |    1
          26 | 357144600.00 |    1
           8 | 335643100.00 |    1
(4 rows)
❌ Lỗi thường gặp — ROW_NUMBER với thứ tự không xác định

row_number() OVER (PARTITION BY x ORDER BY ngay) khi hai dòng cùng ngay → thứ tự giữa chúng tuỳ ý và có thể đổi giữa hai lần chạy. Luôn thêm tie-breaker duy nhất (, id). Chọn hàm theo nghiệp vụ: "top 3 sản phẩm" nếu hoà giá có chấp nhận lấy hơn 3? → dùng RANK/DENSE_RANK; đúng 3 dòng → ROW_NUMBER.

LAG / LEAD: so với kỳ trước, kỳ sau

-- Doanh thu theo tháng, so với tháng trước và tăng trưởng %
WITH thang AS (
  SELECT date_trunc('month', o.ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS thang,
         sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100) AS doanh_thu
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY 1
)
SELECT thang,
       round(doanh_thu / 1e6)                                         AS doanh_thu_trieu,
       round(lag(doanh_thu) OVER (ORDER BY thang) / 1e6)              AS thang_truoc_trieu,
       round(100.0 * (doanh_thu - lag(doanh_thu) OVER (ORDER BY thang))
             / NULLIF(lag(doanh_thu) OVER (ORDER BY thang), 0), 1)    AS tang_truong_pct
FROM thang
ORDER BY thang;
Kết quả
   thang    | doanh_thu_trieu | thang_truoc_trieu | tang_truong_pct
------------+-----------------+-------------------+-----------------
 2024-01-01 |              30 |              NULL |            NULL
 2024-02-01 |            1008 |                30 |          3212.8
 2024-03-01 |             645 |              1008 |           -36.0
 2024-04-01 |            1062 |               645 |            64.7
 2024-05-01 |            2017 |              1062 |            90.0
 2024-06-01 |            2353 |              2017 |            16.7
 2024-07-01 |            2129 |              2353 |            -9.5
 2024-08-01 |            1283 |              2129 |           -39.7
 2024-09-01 |             805 |              1283 |           -37.3
 2024-10-01 |             769 |               805 |            -4.6
 2024-11-01 |             123 |               769 |           -84.0
(11 rows)
-- Số giờ giữa hai đơn liên tiếp của cùng khách
SELECT customer_id, id AS order_id, ordered_at,
       round(extract(epoch FROM ordered_at - lag(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at)) / 3600) AS gio_tu_don_truoc
FROM orders
WHERE customer_id IN (1, 2)
ORDER BY customer_id, ordered_at
LIMIT 6;
Kết quả
 customer_id | order_id |       ordered_at       | gio_tu_don_truoc
-------------+----------+------------------------+------------------
           1 |      162 | 2024-02-13 03:00:00+07 |             NULL
           1 |      324 | 2024-02-27 13:00:00+07 |              346
           1 |      270 | 2024-03-14 23:00:00+07 |              394
           1 |      378 | 2024-03-16 11:00:00+07 |               36
           1 |      216 | 2024-05-04 17:00:00+07 |             1182
           1 |       54 | 2024-05-29 15:00:00+07 |              598
(6 rows)
💡 Giá trị mặc định của LAG/LEAD

lag(x, 1, 0): tham số thứ hai là bước nhảy (mặc định 1), thứ ba là giá trị thay thế khi không có dòng trước (mặc định NULL). Dòng đầu của mỗi nhóm luôn không có "trước đó" → NULL nên tính tăng trưởng nhớ xử lý NULL/chia 0.

❌ Lỗi thường gặp — Tháng thiếu dữ liệu làm LAG sai

lag() lấy "dòng trước trong tập dữ liệu", không phải "tháng trước theo lịch". Nếu tháng 5 không có đơn nào, tháng 6 sẽ so sánh với tháng 4. Muốn đúng lịch, hãy sinh đủ dãy tháng bằng generate_series và LEFT JOIN trước khi dùng LAG (module 3, 5).

Tổng luỹ kế, trung bình trượt và frame

Khi có ORDER BY trong OVER mà không ghi frame, mặc định là RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — từ dòng đầu của nhóm đến dòng hiện tại và mọi dòng hoà (peer) với nó. Điều này gây ra hai bẫy lớn; khuyến nghị: luôn ghi frame tường minh bằng ROWS khi tính luỹ kế/trượt.

WITH thang AS (
  SELECT date_trunc('month', o.ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS thang,
         sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100) AS dt
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled' GROUP BY 1
)
SELECT thang,
       round(dt / 1e6) AS dt_trieu,
       round(sum(dt) OVER (ORDER BY thang ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / 1e6) AS luy_ke_trieu,
       round(avg(dt) OVER (ORDER BY thang ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) / 1e6)         AS tb_truot_3_thang,
       round(100.0 * dt / sum(dt) OVER (), 1) AS ti_trong_nam_pct
FROM thang ORDER BY thang;
Kết quả
   thang    | dt_trieu | luy_ke_trieu | tb_truot_3_thang | ti_trong_nam_pct
------------+----------+--------------+------------------+------------------
 2024-01-01 |       30 |           30 |               30 |              0.2
 2024-02-01 |     1008 |         1038 |              519 |              8.2
 2024-03-01 |      645 |         1683 |              561 |              5.3
 2024-04-01 |     1062 |         2745 |              905 |              8.7
 2024-05-01 |     2017 |         4762 |             1241 |             16.5
 2024-06-01 |     2353 |         7116 |             1811 |             19.3
 2024-07-01 |     2129 |         9245 |             2167 |             17.4
 2024-08-01 |     1283 |        10528 |             1922 |             10.5
 2024-09-01 |      805 |        11333 |             1406 |              6.6
 2024-10-01 |      769 |        12102 |              952 |              6.3
 2024-11-01 |      123 |        12225 |              566 |              1.0
(11 rows)

Bẫy 1 — RANGE với giá trị trùng: khi nhiều dòng có cùng giá trị ở cột ORDER BY, frame mặc định coi chúng cùng "peer" nên luỹ kế nhảy một lần qua cả nhóm thay vì từng dòng:

WITH t(ngay, so_tien) AS (VALUES (1, 100), (2, 50), (2, 70), (3, 30))
SELECT ngay, so_tien,
       sum(so_tien) OVER (ORDER BY ngay)                                       AS mac_dinh_range,
       sum(so_tien) OVER (ORDER BY ngay ROWS UNBOUNDED PRECEDING)             AS rows_tung_dong
FROM t ORDER BY ngay, so_tien;
Kết quả
 ngay | so_tien | mac_dinh_range | rows_tung_dong
------+---------+----------------+----------------
    1 |     100 |            100 |            100
    2 |      50 |            220 |            150
    2 |      70 |            220 |            220
    3 |      30 |            250 |            250
(4 rows)

Hai dòng ngày 2 cùng nhận 220 ở cột mặc định (RANGE gộp peer); ở ROWS mới là 150 rồi 220. Bẫy 2 — LAST_VALUE: vì frame mặc định kết thúc ở dòng hiện tại nên last_value luôn trả chính nó, không phải dòng cuối nhóm:

SELECT name, price,
       last_value(name) OVER (ORDER BY price)                                                    AS sai_chinh_no,
       last_value(name) OVER (ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS dat_nhat_nhom
FROM products WHERE category_id = 6 ORDER BY price;
Kết quả
          name           |   price    |      sai_chinh_no       |      dat_nhat_nhom
-------------------------+------------+-------------------------+-------------------------
 Cáp USB-C 1m            |  120000.00 | Cáp USB-C 1m            | Tai nghe không dây Echo
 Chuột không dây         |  350000.00 | Chuột không dây         | Tai nghe không dây Echo
 Sạc nhanh 65W           |  490000.00 | Sạc nhanh 65W           | Tai nghe không dây Echo
 Tai nghe không dây Echo | 1890000.00 | Tai nghe không dây Echo | Tai nghe không dây Echo
(4 rows)
🧠 Cách chuyên gia — Quy tắc frame
  • Luỹ kế / trượt: ROWS BETWEEN ... AND CURRENT ROW — rõ ràng, nhanh hơn RANGE.
  • Giá trị cuối/đầu cả nhóm: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING hoặc dùng first_value với ORDER BY đảo chiều.
  • Chỉ dùng RANGE khi thật sự muốn gộp các dòng cùng giá trị, hoặc với khoảng theo giá trị (RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW — trung bình trượt 7 ngày thật).

Tỉ trọng, phân vị và các mẫu hay gặp khác

-- Tỉ trọng doanh thu của từng danh mục trong tổng, và luỹ kế (Pareto)
WITH dm AS (
  SELECT c.name, sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100) AS dt
  FROM order_items oi JOIN orders o ON o.id = oi.order_id AND o.status <> 'cancelled'
  JOIN products p ON p.id = oi.product_id JOIN categories c ON c.id = p.category_id
  GROUP BY c.name
)
SELECT name, round(dt / 1e6) AS dt_trieu,
       round(100.0 * dt / sum(dt) OVER (), 1) AS ti_trong_pct,
       round(100.0 * sum(dt) OVER (ORDER BY dt DESC ROWS UNBOUNDED PRECEDING) / sum(dt) OVER (), 1) AS luy_ke_pct
FROM dm ORDER BY dt DESC;
Kết quả
    name    | dt_trieu | ti_trong_pct | luy_ke_pct
------------+----------+--------------+------------
 Laptop     |     8173 |         66.9 |       66.9
 Điện thoại |     2792 |         22.8 |       89.7
 Nhà bếp    |      426 |          3.5 |       93.2
 Gia dụng   |      347 |          2.8 |       96.0
 Phụ kiện   |      196 |          1.6 |       97.6
 Giày       |      165 |          1.3 |       99.0
 Áo         |      125 |          1.0 |      100.0
(7 rows)
-- So với trung bình nhóm và xếp phần trăm trong nhóm (lương theo phòng ban)
SELECT dept, full_name, salary,
       round(avg(salary) OVER (PARTITION BY dept)) AS luong_tb_phong,
       round(100 * percent_rank() OVER (PARTITION BY dept ORDER BY salary)) AS vi_tri_pct,
       salary - min(salary) OVER (PARTITION BY dept) AS hon_nguoi_thap_nhat
FROM employees ORDER BY dept, salary DESC;
Kết quả
     dept     |   full_name    |   salary    | luong_tb_phong | vi_tri_pct | hon_nguoi_thap_nhat
--------------+----------------+-------------+----------------+------------+---------------------
 Ban giám đốc | Trần Quốc Bảo  | 90000000.00 |       90000000 |          0 |                0.00
 Kinh doanh   | Phạm Minh Đức  | 55000000.00 |       33000000 |        100 |         35000000.00
 Kinh doanh   | Hoàng Thị Lan  | 35000000.00 |       33000000 |         67 |         15000000.00
 Kinh doanh   | Lê Văn Phong   | 22000000.00 |       33000000 |         33 |          2000000.00
 Kinh doanh   | Trần Bích Ngọc | 20000000.00 |       33000000 |          0 |                0.00
 Kỹ thuật     | Lê Thu Hương   | 60000000.00 |       36833333 |        100 |         35000000.00
 Kỹ thuật     | Nguyễn Hải Yến | 42000000.00 |       36833333 |         80 |         17000000.00
 Kỹ thuật     | Vũ Gia Khang   | 38000000.00 |       36833333 |         60 |         13000000.00
 Kỹ thuật     | Bùi Anh Tuấn   | 28000000.00 |       36833333 |         20 |          3000000.00
 Kỹ thuật     | Đặng Ngọc Mai  | 28000000.00 |       36833333 |         20 |          3000000.00
 Kỹ thuật     | Phạm Đình Hiếu | 25000000.00 |       36833333 |          0 |                0.00
 Nhân sự      | Nguyễn Thảo Vy | 30000000.00 |       30000000 |          0 |                0.00
... (cắt bớt, chạy trên máy bạn để xem đủ)
-- Sự kiện đầu tiên và gần nhất của mỗi khách trên web (không cần GROUP BY + join lại)
SELECT DISTINCT customer_id,
       first_value(event_type) OVER w AS su_kien_dau,
       last_value(event_type)  OVER w AS su_kien_cuoi,
       count(*) OVER (PARTITION BY customer_id) AS tong_su_kien
FROM events
WINDOW w AS (PARTITION BY customer_id ORDER BY occurred_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY customer_id LIMIT 4;
Kết quả
 customer_id | su_kien_dau | su_kien_cuoi | tong_su_kien
-------------+-------------+--------------+--------------
           1 | view        | view         |           50
           2 | view        | view         |           50
           3 | view        | view         |           50
           4 | view        | view         |           50
(4 rows)
❌ Lỗi thường gặp — Nhầm window function và GROUP BY trong cùng truy vấn

Window function được tính sau GROUP BY, nên chỉ nhìn thấy các cột/hàm tổng hợp đã có: sum(sum(x)) OVER (...) hợp lệ (tổng hợp của tổng hợp), còn sum(x) OVER (...) khi x chưa được gom sẽ báo lỗi. Mẹo: viết bước GROUP BY trong CTE, rồi áp window ở truy vấn sau — rõ ràng hơn nhiều.

💡 Hiệu năng

Mỗi cửa sổ khác nhau (PARTITION/ORDER khác) cần một lần sắp xếp. Gom các hàm dùng cùng cửa sổ (đặt tên bằng WINDOW w AS (...)) để planner dùng chung một lần sort. Index trên (partition_cols, order_cols) giúp bỏ bước sort.

🏋️ Bài tập thực hành

Xếp hạng nhân viên theo lương trong phòng banDễ

Với mỗi phòng ban, xếp hạng nhân viên theo lương giảm dần (dùng DENSE_RANK) và chỉ giữ người đứng top 2.

Xem lời giải & giải thích
SELECT dept, full_name, salary, hang FROM (
  SELECT dept, full_name, salary, dense_rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS hang FROM employees
) t WHERE hang <= 2 ORDER BY dept, hang;
Kết quả
     dept     |   full_name    |   salary    | hang
--------------+----------------+-------------+------
 Ban giám đốc | Trần Quốc Bảo  | 90000000.00 |    1
 Kinh doanh   | Phạm Minh Đức  | 55000000.00 |    1
 Kinh doanh   | Hoàng Thị Lan  | 35000000.00 |    2
 Kỹ thuật     | Lê Thu Hương   | 60000000.00 |    1
 Kỹ thuật     | Nguyễn Hải Yến | 42000000.00 |    2
 Nhân sự      | Nguyễn Thảo Vy | 30000000.00 |    1
(6 rows)

Hai nhân viên lương bằng nhau (28 triệu) cùng hạng và cùng được giữ — khác với ROW_NUMBER sẽ cắt một người một cách tuỳ ý.

Đơn tiếp theo của mỗi kháchTrung bình

Với khách hàng số 1, hiển thị từng đơn cùng ngày của đơn tiếp theo của chính khách đó và số ngày chờ giữa hai đơn.

Xem lời giải & giải thích
SELECT id AS order_id, ordered_at::date AS ngay,
       lead(ordered_at::date) OVER (ORDER BY ordered_at) AS ngay_don_ke_tiep,
       lead(ordered_at::date) OVER (ORDER BY ordered_at) - ordered_at::date AS so_ngay_cho
FROM orders WHERE customer_id = 1 ORDER BY ordered_at;
Kết quả
 order_id |    ngay    | ngay_don_ke_tiep | so_ngay_cho
----------+------------+------------------+-------------
      162 | 2024-02-13 | 2024-02-27       |          14
      324 | 2024-02-27 | 2024-03-14       |          16
      270 | 2024-03-14 | 2024-03-16       |           2
      378 | 2024-03-16 | 2024-05-04       |          49
      216 | 2024-05-04 | 2024-05-29       |          25
       54 | 2024-05-29 | 2024-06-11       |          13
... (cắt bớt, chạy trên máy bạn để xem đủ)

Đơn cuối cùng có lead NULL (chưa có đơn kế). Trừ hai date cho số nguyên ngày.

Khách thuộc nhóm chi tiêu top 20%Trung bình

Tính tổng tiền thanh toán theo khách, dùng NTILE(5) chia 5 nhóm, và đếm số khách ở mỗi nhóm cùng tổng chi tiêu của nhóm.

Xem lời giải & giải thích
WITH tong AS (
  SELECT o.customer_id, sum(p.amount) AS chi FROM payments p JOIN orders o ON o.id = p.order_id GROUP BY 1
), nhom AS (
  SELECT *, ntile(5) OVER (ORDER BY chi DESC) AS ngu_phan_vi FROM tong
)
SELECT ngu_phan_vi, count(*) AS so_khach, round(sum(chi) / 1e6) AS tong_trieu,
       round(100.0 * sum(chi) / sum(sum(chi)) OVER (), 1) AS ti_trong_pct
FROM nhom GROUP BY ngu_phan_vi ORDER BY ngu_phan_vi;
Kết quả
 ngu_phan_vi | so_khach | tong_trieu | ti_trong_pct
-------------+----------+------------+--------------
           1 |       11 |       3270 |         30.7
           2 |       11 |       2415 |         22.7
           3 |       11 |       2116 |         19.9
           4 |       11 |       1812 |         17.0
           5 |       10 |       1023 |          9.6
(5 rows)

Chú ý sum(sum(chi)) OVER (): hàm tổng hợp bên trong, window bên ngoài — hợp lệ sau GROUP BY. Đây là cách xem 'top 20% khách mang bao nhiêu % doanh thu' (nguyên lý Pareto).

Loại trùng dữ liệu nhậpKhó

Giả sử bảng staging có các dòng trùng email với thời điểm nạp khác nhau. Viết truy vấn giữ bản nạp mới nhất của mỗi email (dùng dữ liệu giả bằng VALUES).

Xem lời giải & giải thích
WITH staging(email, ten, nap_luc) AS (VALUES
  ('a@x.com', 'An cũ', '2024-01-01'::date), ('a@x.com', 'An mới', '2024-03-01'), ('b@x.com', 'Bình', '2024-02-01'))
SELECT email, ten, nap_luc FROM (
  SELECT *, row_number() OVER (PARTITION BY lower(email) ORDER BY nap_luc DESC) AS rn FROM staging
) t WHERE rn = 1 ORDER BY email;
Kết quả
  email  |  ten   |  nap_luc
---------+--------+------------
 a@x.com | An mới | 2024-03-01
 b@x.com | Bình   | 2024-02-01
(2 rows)

Đây là mẫu 'dedupe' phổ biến nhất trong ETL. PostgreSQL còn có DISTINCT ON (email) ... ORDER BY email, nap_luc DESC ngắn hơn (module 2) — nhưng ROW_NUMBER mang đi mọi DBMS được.

📚 Tài nguyên học tập

✅ Tự đánh giá hoàn thành

  • Giải thích được window function khác GROUP BY chỗ nào và vì sao không dùng trong WHERE
  • Giải đúng bài top-N mỗi nhóm và loại trùng bằng ROW_NUMBER/RANK có tie-breaker
  • Tính được tăng trưởng kỳ trước (LAG), luỹ kế và trung bình trượt với frame ROWS tường minh
  • Nhận ra hai bẫy frame: RANGE với giá trị trùng và LAST_VALUE mặc định