MariaDB MEDIAN() 函数的基础用法与实例

MariaDB MEDIAN() 函数用来计算一组数值的中位数。本文将介绍该函数的语法、用法实例以及相关函数。

语法

MariaDB MEDIAN() 函数的语法如下：

MEDIAN(median_expression) OVER (
  [ PARTITION BY partition_expression ]
)

median_expression：要计算中位数的表达式。
PARTITION BY partition_expression：可选。用于对结果集进行分区,在每个分区内计算中位数。

该函数返回 median_expression 的中位数。

实例

计算整个表的数值字段的中位数

本实例将计算 products 表中 price 字段的中位数。

DROP TABLE IF EXISTS products;
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2)
);

INSERT INTO products VALUES
(1, 'Product 1', 10.50),
(2, 'Product 2', 18.99),
(3, 'Product 3', 7.99),
(4, 'Product 4', 15.75),
(5, 'Product 5', 22.50);

SELECT MEDIAN(price) OVER () AS median_price
FROM products;

以下是该语句的输出：

+---------------+
| median_price  |
+---------------+
| 15.7500000000 |
| 15.7500000000 |
| 15.7500000000 |
| 15.7500000000 |
| 15.7500000000 |
+---------------+

上例计算了整个 products 表中 price 字段的中位数 15.75。

计算按分区的中位数

本实例将计算 products 表中按 id 分区后每个分区内 price 字段的中位数。

SELECT id, price,
       MEDIAN(price) OVER (PARTITION BY id) AS median_price
FROM products;

以下是该语句的输出：

+------+-------+---------------+
| id   | price | median_price  |
+------+-------+---------------+
|    1 | 10.50 | 10.5000000000 |
|    2 | 18.99 | 18.9900000000 |
|    3 |  7.99 |  7.9900000000 |
|    4 | 15.75 | 15.7500000000 |
|    5 | 22.50 | 22.5000000000 |
+------+-------+---------------+

由于每个分区只有一行数据,所以每个分区的中位数就是该行的 price 值。

计算按多个字段分区的中位数

本实例将计算 orders 表按照 customer_id 和 order_status 两个字段进行分区,然后计算每个分区内 amount 字段的中位数。

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  amount DECIMAL(10,2),
  order_status VARCHAR(20)
);

INSERT INTO orders VALUES
(1, 1, 120.50, 'Paid'),
(2, 1, 77.00, 'Paid'),
(3, 2, 54.25, 'Unpaid'),
(4, 2, 99.99, 'Paid'),
(5, 3, 89.80, 'Paid');

SELECT customer_id, order_status, amount,
       MEDIAN(amount) OVER (PARTITION BY customer_id, order_status) AS median_amount
FROM orders;

以下是该语句的输出：

+-------------+--------------+--------+---------------+
| customer_id | order_status | amount | median_amount |
+-------------+--------------+--------+---------------+
|           1 | Paid         | 120.50 | 98.7500000000 |
|           1 | Paid         |  77.00 | 98.7500000000 |
|           2 | Paid         |  99.99 | 99.9900000000 |
|           2 | Unpaid       |  54.25 | 54.2500000000 |
|           3 | Paid         |  89.80 | 89.8000000000 |
+-------------+--------------+--------+---------------+

上例按照 customer_id 和 order_status 两个字段进行分区,然后在每个分区内计算 amount 字段的中位数。

对 NULL 值的处理

下面的实例演示了 MEDIAN() 函数对 NULL 值的处理。

DROP TABLE IF EXISTS scores;
CREATE TABLE scores (
  student VARCHAR(50),
  score INT
);

INSERT INTO scores VALUES
('Alice', 85),
('Bob', NULL),
('Charlie', 92),
('David', 77),
('Emma', NULL);

SELECT MEDIAN(score) OVER () AS overall_median,
       MEDIAN(COALESCE(score, 0)) OVER () AS median_non_null
FROM scores;

以下是该语句的输出：

+----------------+-----------------+
| overall_median | median_non_null |
+----------------+-----------------+
|  85.0000000000 |   77.0000000000 |
|  85.0000000000 |   77.0000000000 |
|  85.0000000000 |   77.0000000000 |
|  85.0000000000 |   77.0000000000 |
|  85.0000000000 |   77.0000000000 |
+----------------+-----------------+

上例中 MEDIAN(score) 将 NULL 值视为 0 进行计算,而 MEDIAN(COALESCE(score, 0)) 将 NULL 值替换为 0 后再计算中位数。

结论

本文介绍了 MariaDB MEDIAN() 函数的语法、用法实例以及相关函数。该函数可用于计算整个数据集或按分区计算指定表达式的中位数。在分组计算或处理大型数据集时,MEDIAN() 函数非常有用。需要注意该函数对 NULL 值的处理方式。