Hàm tổng hợp — COUNT / SUM / AVG / MIN / MAX

Dữ liệu chúng ta sẽ dùng — bảng score

Trong bài này chúng ta làm việc với hàm tổng hợp (hàm gộp nhiều dòng thành một giá trị duy nhất). Có bao nhiêu dòng, tổng là bao nhiêu, trung bình, nhỏ nhất, lớn nhất — tất cả những điều này nén nhiều dòng xuống thành một con số duy nhất. Năm hàm cơ bản là COUNT / SUM / AVG / MIN / MAX, và hầu hết các con số tổng kết trong một báo cáo đều có thể dựng nên chỉ với những hàm này.

Tập dữ liệu là bảng score (30 dòng = 10 người × 3 môn Math / English / Science). Nhắm vào điểm số ở cột score, bạn sẽ làm phần tổng hợp toàn bảng trước, rồi đến tổng hợp sau khi thu hẹp các dòng bằng WHERE. Tổng hợp theo từng nhóm (như trung bình theo môn) sẽ được trình bày ở bài tiếp theo, nên ở đây chúng ta tập trung vào việc gộp toàn bảng — hoặc toàn bộ phạm vi đã thu hẹp bằng WHERE — thành một giá trị duy nhất.

Trước khi lao vào các bài tập, hãy nhìn qua định nghĩa cột và một mẫu dữ liệu của bảng score.

① Chạy PRAGMA table_info(score); để kiểm tra tên cột, kiểu dữ liệu và khóa chính.

② Chạy SELECT * FROM score LIMIT 5; để xem trước 5 dòng dữ liệu đầu tiên.

Hàm tổng hợp: «nhiều dòng → một giá trị»

Hàm tổng hợp được đặt ở vị trí của một cột trong SELECT.

Điều cần lưu ý ở đây là không trộn lẫn một cột thường và một hàm tổng hợp trong cùng một `SELECT`. Một cột thường như name có giá trị khác nhau ở mỗi dòng, trong khi một hàm tổng hợp như COUNT(*) gộp toàn bảng (hoặc toàn bộ tập đã thu hẹp bằng `WHERE`) thành một giá trị duy nhất. Nếu bạn viết cả hai, như trong SELECT name, COUNT(*) FROM score;, phía tổng hợp là một giá trị duy nhất cho cả tập trong khi name không có câu trả lời xác định cho «nên hiển thị giá trị của dòng nào?». Trong hầu hết các cơ sở dữ liệu, đây là một lỗi.

-- Lấy chung số lượng, số loại khác nhau, tổng và trung bình
SELECT
  COUNT(*)             AS row_count,
  COUNT(DISTINCT name) AS name_kinds,
  SUM(score)           AS total,
  AVG(score)           AS avg_raw
FROM score
WHERE subject = 'English';

Sự khác nhau giữa COUNT(*) và COUNT(cột)

COUNT(*) đếm bản thân số dòng. COUNT(cột) chỉ đếm những dòng mà cột đó không phải NULL. Bảng score không có NULL nên cả hai đều ra cùng số 30, nhưng với một cột chứa NULL bạn sẽ thấy có chênh lệch ở đây (chúng ta sẽ xác nhận điều này sau bằng một bảng có NULL). Thêm nữa, viết COUNT(DISTINCT cột) sẽ đếm số loại khác nhau sau khi loại bỏ trùng lặp (dùng nó trên subject thì bạn được 3, là số môn khác nhau).

Mẹo — hàm tổng hợp và NULL

SUM / AVG / MIN / MAX không đưa các dòng NULL vào tính toán (chúng bỏ qua). Đặc biệt, AVG được tính bằng tổng các giá trị không NULL ÷ số lượng giá trị không NULL. Vì vậy với một cột chứa NULL, SUM(cột) / COUNT(*) (mẫu số = tất cả các dòng) và AVG(cột) (mẫu số = số lượng không NULL) cho ra kết quả khác nhau. Khi bạn muốn tính trung bình mà coi NULL như 0, hãy ghi rõ ràng bằng cái gì đó như AVG(COALESCE(cột, 0)).

Hãy hình dung một yêu cầu: «Mình muốn hiển thị quy mô tổng thể của dữ liệu điểm số trên một dashboard chỉ trong một dòng.» (Chạy đúng thì phần giải thích sẽ hiện ra.)

① Từ bảng score, dùng năm hàm tổng hợp để lấy ra 5 cột sau đây trong một dòng duy nhất.

② Đặt bí danh row_count cho số lượng toàn bộ dòng, total cho tổng điểm, avg_score cho trung bình điểm, min_score cho điểm thấp nhất và max_score cho điểm cao nhất, theo thứ tự đó.

③ Vì trung bình hiển thị với phần thập phân dài, hãy làm tròn nó đến 2 chữ số thập phân và đặt bí danh là avg_score.

Thu hẹp đối tượng bằng WHERE trước khi tổng hợp

Thêm WHERE khiến chỉ những dòng khớp điều kiện trở thành đầu vào của các hàm tổng hợp. Thứ tự thực thi là «FROM đọc tất cả các dòng → WHERE thu hẹp các dòng → các dòng còn lại được gộp bởi các hàm tổng hợp». Nói cách khác, các hàm tổng hợp chỉ thấy những dòng đã đi qua WHERE, và tổng hay trung bình được tính sau khi lọc. Thêm WHERE subject = 'Math' thì chỉ 10 dòng Math là đối tượng, nên trung bình trở thành trung bình của riêng Math.

-- Số lượng / trung bình / điểm cao nhất chỉ cho 10 dòng Science
SELECT
  COUNT(*)             AS row_count,
  ROUND(AVG(score), 1) AS avg_science,
  MAX(score)           AS top_science
FROM score
WHERE subject = 'Science';

Hãy hình dung một yêu cầu: «Mình muốn tách riêng điểm Math ra và tính trung bình, lớn nhất, nhỏ nhất.»

① Từ bảng score, chỉ nhắm vào những dòng mà subject là Math.

② Lấy ra, trong một dòng duy nhất, số lượng dòng đối tượng với bí danh math_count, điểm trung bình với avg_math, điểm cao nhất với max_math và điểm thấp nhất với min_math, theo thứ tự đó.

③ Làm tròn trung bình đến 2 chữ số thập phân.

COUNT(cột) và NULL, và làm tròn AVG

Bảng score từ trước đến giờ không có NULL. Hãy xác nhận việc tổng hợp thay đổi thế nào tùy theo có NULL hay không, dùng bảng customer vốn có chỗ trống (8 người, với NULL ở age / email / country). COUNT(*) là số dòng nên vẫn là 8, nhưng COUNT(email) chỉ đếm những dòng có điền email nên là 4. Chạy COUNT(cột) trên một cột chứa NULL sẽ cho ra kết quả nhỏ hơn COUNT(*) như thế này.

Ngoài ra, vì AVG là phép chia nên nó tạo ra số thập phân như 31.5. Trong một báo cáo thì dễ xử lý hơn nếu bạn làm tròn bằng ROUND(biểu_thức, số_chữ_số), như trong ROUND(AVG(age), 2).

-- COUNT và AVG thay đổi thế nào khi có NULL
SELECT
  COUNT(*)            AS rows_all,
  COUNT(country)      AS with_country,
  ROUND(AVG(age), 2)  AS avg_age
FROM customer;

Hãy hình dung một yêu cầu: «Dữ liệu thành viên có các trường chưa điền (NULL). Mình muốn so sánh tổng số lượng với số lượng mà thực sự có giá trị.»

① Từ bảng customer, lấy ra, trong một dòng duy nhất, tổng số dòng với bí danh all_rows, số lượng có điền email với with_email, số lượng có điền age với with_age, và số giá trị khác nhau của country với country_kinds, theo thứ tự đó.

② Xác nhận rằng chạy COUNT(cột) trên một cột có NULL cho ra kết quả nhỏ hơn COUNT(*).

Hãy hình dung một yêu cầu: «Mình muốn lấy ra chỉ những điểm vượt trung bình tổng thể và xem số lượng cùng trung bình của chúng.» Trung bình tổng thể là 77.93… (bạn đã xác nhận ở bài tập trước).

① Từ bảng score, chỉ nhắm vào những dòng mà score từ 78 trở lên.

② Lấy ra, trong một dòng duy nhất, số lượng dòng đối tượng với bí danh high_count, điểm trung bình của chúng với avg_high, và điểm cao nhất với max_high, theo thứ tự đó.

③ Làm tròn trung bình đến 2 chữ số thập phân.