Làm thế nào để MySQL tính toán có nghĩa là?

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

Nội dung chính Show

chuẩn bị
Tính trung vị theo cách “cổ điển”
Tính trung bình một cách linh hoạt
Tiền thưởng - Tính toán trung vị cho nhiều cột
Trung bình hoạt động như thế nào trong MySQL?
Làm cách nào để tính tỷ lệ phần trăm trung bình trong MySQL?
MySQL có chức năng trung bình không?

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 properties
useSSL: 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 median
FROM ( 
    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: true

1 là một biến SQL được bắt đầu trong câu lệnh

# User properties
useSSL: false
allowPublicKeyRetrieval: true

2 và được cập nhật cho mỗi hàng trong câu lệnh

# User properties
useSSL: false
allowPublicKeyRetrieval: true

Cột có trung vị sẽ được tính (cột
```
# User properties
useSSL: false
allowPublicKeyRetrieval: true
```
4 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: true
```
5 và
```
# User properties
useSSL: false
allowPublicKeyRetrieval: true
```
6 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: true

# User properties
useSSL: false
allowPublicKeyRetrieval: true

8 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

SELECT
    category,
    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: true

8 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: true

8 lên

SET GROUP_CONCAT_MAX_LEN = 100000;

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 đó

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
;

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: true

9 đượ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ó

-- Categoy A, 5 rows:
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

SELECT
    sub2.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 properties
useSSL: false
allowPublicKeyRetrieval: true

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: true

8 và

# User properties
useSSL: false
allowPublicKeyRetrieval: true

9. 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

Trung bình hoạt động như thế nào trong MySQL?

Hàm AVG của MySQL được sử dụng để tìm giá trị trung bình của một trường trong nhiều bản ghi khác nhau . Bạn có thể lấy trung bình của các bản ghi khác nhau được đặt bằng mệnh đề GROUP BY. Ví dụ sau sẽ lấy trung bình tất cả các bản ghi liên quan đến một người và bạn sẽ có các trang được nhập trung bình bởi mỗi người.

Làm cách nào để tính tỷ lệ phần trăm trung bình trong MySQL?

Để tính tỷ lệ phần trăm của cột trong MySQL, bạn chỉ cần nối chéo cột sum() of sale với bảng ban đầu . Nếu bạn muốn thêm mệnh đề where để lọc dữ liệu của mình, bạn cần đặt nó sau CROSS JOIN, như hình bên dưới. Nếu không, bạn sẽ gặp lỗi.

MySQL có chức năng trung bình không?

Chúng tôi tính trung bình của Khoảng cách từ bảng demo. ĐẶT @rowindex. = -1; . SELECT AVG(d. khoảng cách) dưới dạng Trung bình TỪ (CHỌN @rowindex. =@rowindex + 1 AS rowindex, demo.