Menu
Học tập
Khóa học Lộ trình nghề nghiệp Roadmap BA → CPO BA → Product Manager Tài liệu & Sách
Cộng đồng
Cộng đồng Interview Questions
Công cụ
Phân tích CV Theo dõi ứng tuyển Hiring Pipeline Luyện phỏng vấn Tối ưu LinkedIn Đàm phán lương
Tài khoản
ESC

Nhập từ khóa để tìm kiếm

↑↓ Di chuyển
Enter Mở
ESC Đóng

Time-series basic: GROUP BY ngày/tuần/tháng

SQL and Data Analysis cho BA Bài 34/60

Mở đầu — vì sao bài này quan trọng

Nếu bạn làm BA (Business Analyst) đủ lâu, bạn sẽ nhận ra một sự thật: gần như mọi câu hỏi quan trọng của doanh nghiệp đều có yếu tố thời gian trong đó. "Tuần này đơn hàng tăng hay giảm so với tuần trước?", "Doanh thu tháng 6 so với tháng 5 thế nào?", "Có bao nhiêu khách mới mỗi ngày trong chiến dịch khuyến mãi?". Tất cả những câu hỏi này đều cần bạn nhóm dữ liệu theo đơn vị thời gian — ngày, tuần, tháng — rồi tính toán trên từng nhóm.

Đó chính là time-series analysis cơ bản, và nó là một trong những kỹ năng SQL mà BA dùng nhiều nhất, gần như hằng ngày. Báo cáo doanh thu theo ngày, biểu đồ tăng trưởng người dùng theo tháng, dashboard theo dõi đơn hàng theo tuần — tất cả đều được xây dựng trên nền tảng GROUP BY kết hợp với các hàm xử lý ngày tháng.

Điều thú vị là kỹ thuật cốt lõi ở đây không hề phức tạp. Bạn đã học GROUP BY và các aggregate function ở những bài trước. Bài này chỉ là sự kết hợp giữa hai thứ đó với một mảnh ghép mới: cách "cắt" một cột timestamp (ví dụ created_at chính xác đến từng giây) thành một đơn vị thời gian gọn gàng để nhóm. Nghe đơn giản, nhưng đây chính là nơi rất nhiều BA mắc lỗi tinh vi khiến báo cáo sai mà không hề hay biết. Chúng ta sẽ đi qua cả phần dễ lẫn phần bẫy.

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

Vấn đề: timestamp quá chi tiết để nhóm

Trong database, cột thời gian thường được lưu dưới dạng DATETIME hoặc TIMESTAMP, ví dụ 2026-06-15 14:32:07. Nếu bạn GROUP BY created_at trực tiếp, mỗi bản ghi gần như sẽ rơi vào một nhóm riêng (vì hiếm khi hai đơn hàng có cùng giây tạo). Kết quả là báo cáo của bạn vô dụng.

Giải pháp là "làm tròn" timestamp về đơn vị thời gian mong muốn trước khi nhóm. Đây gọi là truncation (cắt bớt độ chi tiết).

Nhóm theo ngày

Cách phổ biến nhất là dùng hàm DATE() để lấy phần ngày, bỏ phần giờ:

SELECT
  DATE(created_at) AS day,
  COUNT(*) AS orders,
  COUNT(DISTINCT customer_id) AS unique_customers,
  SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE(created_at)
ORDER BY day;

Ở đây DATE('2026-06-15 14:32:07') trả về 2026-06-15, nên mọi đơn trong cùng một ngày được gom lại. Đây là pattern "xương sống" của báo cáo daily.

Lưu ý cú pháp khác nhau giữa các hệ quản trị:

  • MySQL: DATE(created_at) hoặc DATE_FORMAT(created_at, '%Y-%m-%d')
  • PostgreSQL: DATE(created_at) hoặc created_at::date hoặc DATE_TRUNC('day', created_at)

Nhóm theo tháng

Với tháng, bạn cần giữ cả năm lẫn tháng để không bị gộp nhầm tháng 6/2025 với tháng 6/2026.

MySQL:

SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS month,
  COUNT(*) AS orders,
  SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month;

PostgreSQL dùng DATE_TRUNC — đây là hàm rất đáng để bạn ghi nhớ vì nó "đẹp" và nhất quán:

SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS orders,
  SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

DATE_TRUNC('month', '2026-06-15 14:32:07') trả về 2026-06-01 00:00:00 — tức là "làm tròn xuống" đầu tháng. Ưu điểm của DATE_TRUNC là kết quả vẫn là kiểu ngày/giờ thật (chứ không phải chuỗi), nên dễ sắp xếp và tính toán tiếp.

Nhóm theo tuần

Tuần là phần dễ gây tranh cãi nhất, vì có hai câu hỏi: tuần bắt đầu vào thứ Hai hay Chủ Nhật, và bạn muốn nhãn tuần trông như thế nào.

MySQL có hàm YEARWEEK() và WEEK():

SELECT
  YEARWEEK(created_at, 1) AS year_week,   -- mode 1: tuần bắt đầu thứ Hai
  COUNT(*) AS orders
FROM orders
GROUP BY YEARWEEK(created_at, 1)
ORDER BY year_week;

PostgreSQL gọn hơn với DATE_TRUNC('week', ...) — mặc định coi thứ Hai là đầu tuần:

SELECT
  DATE_TRUNC('week', created_at) AS week_start,
  COUNT(*) AS orders
FROM orders
GROUP BY DATE_TRUNC('week', created_at)
ORDER BY week_start;

Một mẹo thực dụng: thay vì để nhãn tuần là một con số khó đọc như 202624, nhiều BA thích hiển thị "ngày đầu tuần" (tức thứ Hai của tuần đó) vì nó trực quan hơn cho người đọc báo cáo. DATE_TRUNC('week', ...) của PostgreSQL cho bạn đúng điều đó.

Vai trò của ORDER BY

Một điều dễ quên: GROUP BY không đảm bảo thứ tự kết quả. Báo cáo time-series gần như luôn cần ORDER BY theo cột thời gian để các dòng xếp đúng trình tự ngày tháng — nếu không, biểu đồ của bạn sẽ nhảy loạn xạ.

Tình huống thực tế

Ví dụ 1: Tiki theo dõi doanh thu hằng ngày trong dịp 6.6

Hãy tưởng tượng bạn là BA tại một sàn thương mại điện tử kiểu Tiki, đang trong chiến dịch sale 6.6. Đội marketing cần một báo cáo daily để xem mỗi ngày có bao nhiêu đơn, bao nhiêu khách duy nhất mua hàng, và tổng doanh thu, nhằm đánh giá hiệu quả các đợt flash sale.

SELECT
  DATE(created_at) AS day,
  COUNT(*) AS total_orders,
  COUNT(DISTINCT customer_id) AS unique_buyers,
  SUM(total_amount) AS revenue,
  ROUND(AVG(total_amount), 0) AS avg_order_value
FROM orders
WHERE created_at >= '2026-06-01'
  AND created_at <  '2026-06-11'
  AND status = 'completed'
GROUP BY DATE(created_at)
ORDER BY day;

Kết quả có thể trông như:

daytotal_ordersunique_buyersrevenueavg_order_value
2026-06-058,4006,1002,940,000,000350,000
2026-06-0631,20022,80012,480,000,000400,000
2026-06-079,1006,7003,185,000,000350,000
Diễn giải: Ngày 6.6 đơn hàng tăng gần 4 lần so với ngày thường, và quan trọng hơn, giá trị đơn trung bình (AOV) cũng nhích từ 350K lên 400K — chứng tỏ khách không chỉ mua nhiều đơn hơn mà còn mua giỏ hàng lớn hơn. COUNT(DISTINCT customer_id) cho thấy 22.800 khách duy nhất trên 31.200 đơn, nghĩa là một số khách đặt nhiều đơn trong ngày.

Bài học: Đừng chỉ đếm số đơn. Việc tách COUNT(*) (số đơn) khỏi COUNT(DISTINCT customer_id) (số khách) là cực kỳ quan trọng — chúng kể hai câu chuyện khác nhau. Một báo cáo daily tốt luôn nên có cả hai để bạn phân biệt được "tăng do nhiều khách hơn" hay "tăng do khách cũ mua nhiều hơn".

Ví dụ 2: Một startup SaaS Việt Nam xem tăng trưởng người dùng theo tháng

Giả sử bạn làm cho một startup SaaS, cần báo cáo cho nhà đầu tư về số người dùng đăng ký mới mỗi tháng trong năm qua. Bảng users có cột signup_date.

SELECT
  DATE_FORMAT(signup_date, '%Y-%m') AS month,
  COUNT(*) AS new_users
FROM users
WHERE signup_date >= '2025-07-01'
GROUP BY DATE_FORMAT(signup_date, '%Y-%m')
ORDER BY month;

Kết quả:

monthnew_users
2025-07320
2025-08410
2025-09560
......
2026-061,940
Diễn giải: Báo cáo này cho thấy đà tăng trưởng tháng-qua-tháng. Nhà đầu tư thường muốn nhìn xu hướng đi lên đều đặn hơn là một con số tổng. Khi vẽ thành biểu đồ cột, đường tăng trưởng này kể câu chuyện thuyết phục hơn nhiều so với "chúng tôi có 8.500 người dùng".

Bài học: Khi nhóm theo tháng, bắt buộc phải đưa cả năm vào (%Y-%m), không bao giờ chỉ %m. Nếu bạn chỉ nhóm theo số tháng, dữ liệu tháng 6/2025 và tháng 6/2026 sẽ bị gộp chung thành một dòng "06" — một lỗi kinh điển khiến báo cáo sai hoàn toàn mà nhìn qua vẫn tưởng đúng.

Ví dụ 3: Ứng dụng gọi xe kiểu Be xem nhịp đặt xe theo tuần

Bạn là BA cho một ứng dụng gọi xe. Đội vận hành muốn biết số chuyến xe theo từng tuần để lên kế hoạch điều phối tài xế, và họ muốn tuần bắt đầu từ thứ Hai. Database chạy PostgreSQL.

SELECT
  DATE_TRUNC('week', requested_at) AS week_start,
  COUNT(*) AS total_trips,
  COUNT(DISTINCT driver_id) AS active_drivers,
  ROUND(AVG(fare), 0) AS avg_fare
FROM trips
WHERE requested_at >= '2026-04-01'
  AND status = 'completed'
GROUP BY DATE_TRUNC('week', requested_at)
ORDER BY week_start;

Kết quả:

week_starttotal_tripsactive_driversavg_fare
2026-05-25142,0005,80048,000
2026-06-01158,0006,10047,000
2026-06-08121,0005,20051,000
Diễn giải: Tuần 8/6 số chuyến giảm rõ rệt nhưng giá cước trung bình tăng — có thể do tuần đó mưa nhiều, ít tài xế hoạt động (active_drivers giảm còn 5.200), cầu vượt cung đẩy giá lên. Việc nhóm theo tuần làm mượt các biến động ngày-qua-ngày (như cuối tuần thường cao hơn ngày thường), giúp đội vận hành nhìn xu hướng rõ hơn.

Bài học: Nhóm theo tuần là lựa chọn tuyệt vời khi dữ liệu ngày quá "nhiễu" (nhiều biến động lên xuống do ngày trong tuần) nhưng nhóm theo tháng lại quá thô để ra quyết định kịp thời. Tuần là điểm cân bằng đẹp giữa độ chi tiết và độ ổn định.

Hướng dẫn từng bước

Khi bạn nhận một yêu cầu báo cáo time-series, hãy đi theo trình tự sau:

  • Xác định cột thời gian gốc. Tìm đúng cột timestamp phản ánh sự kiện bạn quan tâm: created_at cho đơn hàng, signup_date cho đăng ký, requested_at cho chuyến xe. Đừng nhầm created_at với updated_at — chúng có ý nghĩa khác nhau.
  • Chọn đơn vị thời gian (granularity). Hỏi rõ người yêu cầu: họ cần theo ngày, tuần hay tháng? Quy tắc nhanh: theo dõi chiến dịch ngắn hạn dùng ngày; vận hành đều đặn dùng tuần; báo cáo lãnh đạo và xu hướng dài hạn dùng tháng.
  • Viết biểu thức truncation. Bọc cột thời gian bằng hàm phù hợp: DATE(), DATE_FORMAT(..., '%Y-%m'), DATE_TRUNC('week', ...). Đây là cột sẽ vừa nằm trong SELECT vừa nằm trong GROUP BY.
  • Lọc khoảng thời gian bằng WHERE. Hầu như không bao giờ chạy báo cáo trên toàn bộ lịch sử. Thêm điều kiện WHERE created_at >= '...' AND created_at < '...'. Lưu ý dùng < ngày_kết_thúc (không phải <=) để tránh bẫy ranh giới — sẽ nói kỹ ở phần lỗi.
  • Chọn các metric. Quyết định bạn cần đếm gì: COUNT(*), COUNT(DISTINCT ...), SUM(...), AVG(...). Đặt tên alias rõ ràng cho từng cột.
  • Thêm GROUP BY và ORDER BY. GROUP BY đúng biểu thức truncation, rồi ORDER BY theo cột thời gian để kết quả xếp đúng trình tự.
  • Kiểm tra lại bằng mắt. Nhìn vài dòng đầu và cuối: số liệu có hợp lý không? Có ngày nào bị thiếu không (có thể là ngày không có giao dịch)? Tổng các dòng có khớp với con số tổng bạn biết không?

Lỗi thường gặp & mẹo

Lỗi 1: Chỉ nhóm theo tháng mà quên năm. Như đã nói ở Ví dụ 2 — DATE_FORMAT(created_at, '%m') sẽ gộp các tháng cùng số của những năm khác nhau. Luôn dùng '%Y-%m'. Đây là lỗi gây hậu quả nghiêm trọng nhưng khó phát hiện.

Lỗi 2: Bẫy ranh giới với BETWEEN và <=. Khi lọc "tháng 6", nhiều người viết WHERE created_at BETWEEN '2026-06-01' AND '2026-06-30'. Vấn đề: '2026-06-30' được hiểu là 2026-06-30 00:00:00, nên mọi đơn hàng trong ngày 30 sau 0 giờ sáng bị bỏ sót. Cách an toàn: dùng created_at >= '2026-06-01' AND created_at < '2026-07-01'. Pattern "lớn hơn hoặc bằng đầu kỳ, nhỏ hơn đầu kỳ tiếp theo" luôn đúng và không bao giờ sót.

Lỗi 3: Quên ORDER BY. GROUP BY không tự sắp xếp. Nếu thiếu ORDER BY, biểu đồ của bạn có thể nhảy lung tung. Luôn sắp theo cột thời gian.

Lỗi 4: Ngày trống bị "biến mất". Nếu một ngày không có đơn hàng nào, GROUP BY đơn giản là không tạo ra dòng cho ngày đó — bạn sẽ thấy báo cáo nhảy từ 14/6 sang 16/6, thiếu 15/6. Với báo cáo trình bày cho người khác, điều này gây hiểu nhầm (tưởng dữ liệu lỗi). Mẹo: nếu cần đủ mọi ngày kể cả ngày trống, bạn phải JOIN với một bảng/danh sách ngày liên tục (gọi là "date dimension" hoặc "calendar table") — kỹ thuật này nâng cao hơn nhưng rất đáng biết.

Lỗi 5: Bỏ qua múi giờ. Nếu created_at được lưu theo giờ UTC nhưng doanh nghiệp của bạn vận hành theo giờ Việt Nam (UTC+7), thì việc cắt theo ngày có thể bị lệch — một đơn lúc 23h tối ngày 15 giờ VN có thể bị tính sang ngày 16 nếu chưa quy đổi. Mẹo: hỏi rõ team data ngày giờ được lưu theo múi giờ nào, và dùng hàm chuyển múi giờ (như CONVERT_TZ trong MySQL) khi cần.

Mẹo nâng cao về hiệu năng: Khi bạn bọc cột bằng hàm như DATE(created_at) trong WHERE, database thường không dùng được index trên cột đó, khiến query chậm trên bảng lớn. Vì vậy hãy lọc khoảng thời gian bằng cột thô (WHERE created_at >= ... AND created_at < ...) và chỉ dùng hàm DATE() ở phần SELECT/GROUP BY. Cách này vừa đúng kết quả vừa tận dụng được index.

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

Giả sử bạn có bảng orders với các cột: order_id, customer_id, created_at (DATETIME), total_amount (số tiền), status.

  • Daily cơ bản: Viết query đếm số đơn completed mỗi ngày trong tháng 6/2026, kèm tổng doanh thu, sắp xếp theo ngày tăng dần. Nhớ dùng pattern ranh giới an toàn cho WHERE.
  • Khách duy nhất theo tuần: Viết query đếm số khách hàng duy nhất (COUNT(DISTINCT customer_id)) theo từng tuần trong quý 2/2026. Nếu dùng MySQL hãy thử YEARWEEK(created_at, 1); nếu PostgreSQL hãy thử DATE_TRUNC('week', created_at).
  • So sánh tháng: Viết query trả về số đơn và doanh thu theo từng tháng của cả năm 2025 và 2026. Đảm bảo bạn dùng nhãn '%Y-%m' để không gộp nhầm các tháng cùng số.
  • Tư duy: Trong báo cáo daily ở câu 1, nếu sếp hỏi "tại sao ngày 12/6 không xuất hiện trong kết quả?", bạn sẽ giải thích thế nào, và bạn sẽ kiểm tra điều gì để chắc chắn đó là do không có đơn chứ không phải do query sai?
Hãy tự viết và chạy thử trên môi trường MySQL/PostgreSQL bạn đã cài ở Bài 5. Sau đó đối chiếu kết quả bằng cách tính tổng tay một mẫu nhỏ.

Tóm tắt

Time-series cơ bản chỉ là sự kết hợp giữa ba thứ bạn đã biết: truncation (cắt timestamp về đơn vị ngày/tuần/tháng), GROUP BY trên đơn vị đó, và các aggregate function để tính metric. Nhớ ba công cụ chính: DATE() cho ngày, DATE_FORMAT(..., '%Y-%m') (MySQL) hoặc DATE_TRUNC (PostgreSQL) cho tháng và tuần.

Hãy luôn tách COUNT(*) khỏi COUNT(DISTINCT customer_id) để phân biệt số sự kiện và số người. Hãy chọn đơn vị thời gian phù hợp với mục đích: ngày cho chiến dịch ngắn, tuần cho vận hành, tháng cho xu hướng dài hạn. Và hãy ghi nhớ bốn cái bẫy lớn nhất: quên năm khi nhóm theo tháng, bẫy ranh giới <=, ngày trống bị biến mất, và sai lệch múi giờ.

Nắm vững bài này, bạn đã có nền tảng cho hầu hết các báo cáo theo thời gian mà một BA cần dựng hằng ngày — và là bệ phóng cho các kỹ thuật time-series nâng cao hơn ở những bài sau.

Trước Tiếp