Việc tính toán giá trị trung bình của một mảng dữ liệu khá đơn giản trong bất kỳ ngôn ngữ lập trình nào, ngay cả trong Excel, nơi có thể sử dụng trực tiếp hàm trung bình tích hợp sẵn hoặc của bên thứ ba. Tuy nhiên, trong MySQL, chức năng trung bình không được hỗ trợ nguyên bản. Để có được trung vị, chúng ta cần viết một số truy vấn thông minh với các truy vấn con
Trong bài đăng này, chúng tôi sẽ làm sáng tỏ các truy vấn để tính trung bình trong MySQL. Đặc biệt, chúng tôi sẽ trình bày cách tính trung vị của một trường bằng cách nhóm. Nếu một cột có nhiều danh mục có giá trị trung bình cần được tính riêng, thì việc tính toán phương tiện cho từng danh mục sẽ trở nên cồng kềnh. Với bài đăng này, bạn sẽ có thể tính trung bình của tất cả các danh mục bằng một truy vấn duy nhất. Bên cạnh đó, bạn còn được học cách tính trung vị của nhiều cột cùng lúc
chuẩn bị
Để bắt đầu, chúng ta cần có một máy chủ MySQL, tạo cơ sở dữ liệu và bảng, đồng thời chèn một số dữ liệu giả để chơi với. Chúng tôi sẽ sử dụng Docker để khởi động máy chủ MySQL 8 cục bộ
# Create a volume to persist the data.$ docker volume create mysql8-data
# Create the container for MySQL.
$ docker run --name mysql8 -d -e MYSQL_ROOT_PASSWORD=root -p 13306:3306 -v mysql8-data:/var/lib/mysql mysql:8
# Connect to the local MySQL server in Docker.
$ docker exec -it mysql8 mysql -u root -proot
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.31 |
+-----------+
1 row in set (0.00 sec)
Để viết các truy vấn SQL phức tạp, bạn nên sử dụng IDE thay vì bảng điều khiển. Lợi ích của IDE là hoàn thành mã, định dạng mã, đánh dấu cú pháp, v.v. Chúng tôi sẽ sử dụng DBeaver trong bài đăng này, đây là một công cụ quản trị cơ sở dữ liệu phổ quát có thể được sử dụng để quản lý tất cả các loại cơ sở dữ liệu quan hệ và NoQuery. Tuy nhiên, bất kỳ IDE nào cũng sẽ hoạt động. Bạn thậm chí có thể sao chép các truy vấn được trình bày trong bài đăng này và chạy chúng trực tiếp trong bảng điều khiển MySQL
Đối với DBeaver, nếu bạn gặp lỗi “Public Key Retrieval is not allow”, bạn nên chỉnh sửa kết nối và thêm hai thuộc tính người dùng sau cho trình điều khiển
# User propertiesuseSSL: false
allowPublicKeyRetrieval: true
Để biết thêm cài đặt của DBeaver, vui lòng kiểm tra bài đăng này
Sau đó chạy các truy vấn sau để tạo cơ sở dữ liệu, tạo bảng và chèn một số dữ liệu giả
CREATE DATABASE products;CREATE TABLE `products`.`prices` (
`pid` int(11) NOT NULL AUTO_INCREMENT,
`category` varchar(100) NOT NULL,
`price` float NOT NULL,
PRIMARY KEY (`pid`)
);
INSERT INTO products.prices
(pid, category, price)
VALUES
(1, 'A', 2),
(2, 'A', 1),
(3, 'A', 5),
(4, 'A', 4),
(5, 'A', 3),
(6, 'B', 6),
(7, 'B', 4),
(8, 'B', 3),
(9, 'B', 5),
(10, 'B', 2),
(11, 'B', 1)
;
Tính trung vị theo cách “cổ điển”
Bây giờ cơ sở dữ liệu và dữ liệu đã được thiết lập, chúng ta có thể bắt đầu tính trung bình. Giải pháp cổ điển là sử dụng các biến SQL
SELECT AVG(sub.price) AS medianFROM (
SELECT @row_index := @row_index + 1 AS row_index, p.price
FROM products.prices p, (SELECT @row_index := -1) r
WHERE p.category = 'A'
ORDER BY p.price
) AS sub
WHERE sub.row_index IN (FLOOR(@row_index / 2), CEIL(@row_index / 2))
;
median|
------+
3.0|
Các điểm chính cho truy vấn này
- # User properties
useSSL: false
allowPublicKeyRetrieval: true1 là một biến SQL được bắt đầu trong câu lệnh # User properties
useSSL: false
allowPublicKeyRetrieval: true2 và được cập nhật cho mỗi hàng trong câu lệnh # User properties
useSSL: false
allowPublicKeyRetrieval: true3 - Cột có trung vị sẽ được tính (cột # User properties
useSSL: false
allowPublicKeyRetrieval: true4 trong ví dụ này) nên được sắp xếp. Nó không quan trọng nếu nó được sắp xếp theo thứ tự tăng dần hay giảm dần - Theo định nghĩa của trung vị, trung vị là giá trị của phần tử ở giữa (tổng là số lẻ) hoặc giá trị trung bình cộng của 2 phần tử ở giữa (tổng là số chẵn). Trong ví dụ này, danh mục A có 5 hàng và do đó, trung vị là giá trị của hàng thứ ba sau khi sắp xếp. Các giá trị của cả # User properties
useSSL: false
allowPublicKeyRetrieval: true5 và # User properties
useSSL: false
allowPublicKeyRetrieval: true6 là 2, là hàng thứ ba. Mặt khác, đối với loại B có 6 hàng thì trung vị là giá trị trung bình cộng của hàng thứ ba và thứ tư
Giải pháp này đơn giản và dễ hiểu. Tuy nhiên, nếu bảng có nhiều danh mục, chúng ta sẽ cần chạy truy vấn cho từng danh mục, điều này rất cồng kềnh và kết quả không dễ lưu trữ và so sánh
Để giải quyết vấn đề này, chúng ta cần có một giải pháp phi cổ điển bằng cách sử dụng # User properties
useSSL: false
allowPublicKeyRetrieval: true7, # User properties
useSSL: false
allowPublicKeyRetrieval: true8 và_______1_______9
Tính trung bình một cách linh hoạt
Hãy làm từng bước một. Truy vấn cuối cùng có thể trông phức tạp ngay từ cái nhìn đầu tiên. Tuy nhiên, khi bạn biết cách thức hoạt động của nó, nó thực sự dễ hiểu hơn và bạn có thể tự do thay đổi nó cho các trường hợp sử dụng của mình
Trước tiên, hãy lấy tất cả các mức giá được sắp xếp cho từng danh mục
SELECTcategory,
GROUP_CONCAT(price ORDER BY p.price) AS prices,
COUNT(*) AS total
FROM products.prices p
GROUP BY p.category
;
category|prices |total|
--------+-----------+-----+
A |1,2,3,4,5 | 5|
B |1,2,3,4,5,6| 6|
Lưu ý rằng nếu bảng của bạn có nhiều dữ liệu, thì # User properties
useSSL: false
allowPublicKeyRetrieval: true8 sẽ không chứa tất cả dữ liệu. Trong trường hợp này, bạn tăng giới hạn cho # User properties
useSSL: false
allowPublicKeyRetrieval: true8 lên
Bạn có thể đặt giới hạn thành một số phù hợp với trường hợp sử dụng của mình. Tuy nhiên, nếu bảng của bạn chứa quá nhiều dữ liệu, bạn có thể gặp vấn đề về bộ nhớ. Trong trường hợp này, bạn sẽ cần viết một số đoạn mã để thực hiện việc xử lý dữ liệu và tính toán một cách thông minh hơn. Tuy nhiên, giải pháp được cung cấp trong bài đăng này có thể áp dụng cho hầu hết các bảng có kích thước vừa và nhỏ
Sau đó, hãy lấy các phần tử ở giữa cho mỗi danh mục, chúng ta cần kiểm tra xem tổng số là số chẵn hay số lẻ và xử lý theo đó
SELECTsub1.category,
sub1.total,
CASE WHEN MOD(sub1.total, 2) = 1 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sub1.prices, ',', CEIL(sub1.total/2)), ',', '-1')
WHEN MOD(sub1.total, 2) = 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sub1.prices, ',', sub1.total/2 + 1), ',', '-2')
END AS mid_prices
FROM
(
SELECT
p.category,
GROUP_CONCAT(p.price ORDER BY p.price) AS prices,
COUNT(*) AS total
FROM products.prices p
GROUP BY p.category
) sub1
;
category|total|mid_prices|
--------+-----+----------+
A | 5|3 |
B | 6|3,4 |
Chúng ta sử dụng hàm CREATE DATABASE products;
CREATE TABLE `products`.`prices` (
`pid` int(11) NOT NULL AUTO_INCREMENT,
`category` varchar(100) NOT NULL,
`price` float NOT NULL,
PRIMARY KEY (`pid`)
);
INSERT INTO products.prices
(pid, category, price)
VALUES
(1, 'A', 2),
(2, 'A', 1),
(3, 'A', 5),
(4, 'A', 4),
(5, 'A', 3),
(6, 'B', 6),
(7, 'B', 4),
(8, 'B', 3),
(9, 'B', 5),
(10, 'B', 2),
(11, 'B', 1)
;2 để kiểm tra xem tổng số đếm là số chẵn hay số lẻ. Hàm # User properties
useSSL: false
allowPublicKeyRetrieval: true9 được sử dụng hai lần để trích xuất các phần tử ở giữa. Hãy chứng minh chi tiết hơn một chút về cách thức hoạt động của nó
SUBSTRING_INDEX('1,2,3,4,5', ',', CEIL(5/2)) => '1,2,3'
SUBSTRING_INDEX('1,2,3', ',', -1) => 3
-- Categoy B, 6 rows:
SUBSTRING_INDEX('1,2,3,4,5,6', ',', 6/2 + 1) => '1,2,3,4'
SUBSTRING_INDEX('1,2,3,4', ',', -2) => '3,4'
Cuối cùng, hãy tính trung bình cộng của các phần tử ở giữa để lấy trung vị cho từng danh mục
SELECTsub2.category,
CASE WHEN MOD(sub2.total, 2) = 1 THEN sub2.mid_prices
WHEN MOD(sub2.total, 2) = 0 THEN (SUBSTRING_INDEX(sub2.mid_prices, ',', 1) + SUBSTRING_INDEX(sub2.mid_prices, ',', -1)) / 2
END AS median
FROM
(
SELECT
sub1.category,
sub1.total,
CASE WHEN MOD(sub1.total, 2) = 1 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sub1.prices, ',', CEIL(sub1.total/2)), ',', '-1')
WHEN MOD(sub1.total, 2) = 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sub1.prices, ',', sub1.total/2 + 1), ',', '-2')
END AS mid_prices
FROM
(
SELECT
p.category,
GROUP_CONCAT(p.price ORDER BY p.price) AS prices,
COUNT(*) AS total
FROM products.prices p
GROUP BY p.category
) sub1
) sub2
;
category|median|
--------+------+
A |3 |
B |3.5 |
Chúc mừng. Các giá trị trung bình được tính toán chính xác cho cả hai loại. Đó là một chút mã hơn so với giải pháp cổ điển. Tuy nhiên, nó minh bạch hơn và do đó dễ hiểu hơn. Ngoài ra, nó linh hoạt hơn và bạn có thể dễ dàng điều chỉnh các truy vấn cho các trường hợp sử dụng khác nhau, không chỉ để lấy các giá trị trung bình của các danh mục khác nhau bằng cách sử dụng nhóm
Tiền thưởng - Tính toán trung vị cho nhiều cột
Trên trung vị chỉ được tính cho một cột. Với giải pháp mới, chúng ta có thể dễ dàng tính trung bình của nhiều cột. Trước tiên, hãy tạo một bảng mới với một số dữ liệu giả để chơi
CREATE TABLE `products`.`orders` (`order_id` int(11) NOT NULL AUTO_INCREMENT,
`price` float NOT NULL,
`quantity` float NOT NULL,
PRIMARY KEY (`order_id`)
);
INSERT INTO products.orders
(order_id, price, quantity)
VALUES
(1, 2, 50),
(2, 1, 40),
(3, 5, 10),
(4, 3, 30),
(5, 4, 20)
;
Lưu ý rằng dữ liệu là giả mạo và chỉ nhằm mục đích trình diễn, do đó càng đơn giản càng tốt
Truy vấn để tính trung bình của nhiều cột có thể dễ dàng điều chỉnh từ truy vấn ở trên. Sự khác biệt là chúng ta không cần nhóm theo danh mục nữa mà thay vào đó cần lặp lại các truy vấn cho từng cột cần tính trung bình
# User propertiesuseSSL: false
allowPublicKeyRetrieval: true0
Chúc mừng một lần nữa, nó hoạt động như mong đợi
Hiện tại, chức năng tính trung bình chưa được triển khai trong MySQL và do đó chúng ta cần tự viết một số truy vấn để tính toán chúng. Hai giải pháp được giới thiệu trong bài viết này. Giải pháp đầu tiên là một giải pháp cổ điển sử dụng các biến SQL. Cái thứ hai là cái mới được hoàn thành với # User properties
useSSL: false
allowPublicKeyRetrieval: true8 và # User properties
useSSL: false
allowPublicKeyRetrieval: true9. Cái thứ hai là nhiều mã hơn nhưng có thể mở rộng hơn nhiều. Bạn có thể sử dụng nó để tính trung vị của cùng một trường với các danh mục khác nhau và cả trung vị của nhiều trường