SQL 计算中位数

中位数是指将一组数据按从小到大（或从大到小）的顺序排列，位于中间位置的数值，即能将该组数据均分成两部分的数值。

通常有两种方式计算中位数：

对于数量为奇数的数据，中位数就是中间那个数;
对于数量为偶数的数据，中位数是中间两个数的平均值。

以下是SQL计算中位数的攻略：

方法一：使用SQL函数计算中位数

SQL函数包含一些针对特定数据类型的内置函数，可直接使用并取得计算结果。其中，针对中位数的函数包括PERCENTILE_CONT和PERCENTILE_DISC。

PERCENTILE_CONT:用于计算一个分组中中位数的值，可以得到一个连续的结果；
PERCENTILE_DISC:用于计算一个分组中中位数的值，取得最靠近中位数的数。

下面是PERCENTILE_CONT函数和PERCENTILE_DISC函数的使用方法。假设有一个存储在goods表中的商品价格列表：

CREATE TABLE goods(
id INT,
name VARCHAR(20),
price INT
);

INSERT INTO goods (id, name, price)
VALUES
(1, 'A', 10),
(2, 'B', 20),
(3, 'C', 30),
(4, 'D', 40),
(5, 'E', 50);

使用PERCENTILE_CONT函数计算中位数

在使用PERCENTILE_CONT函数时，需要指定中位数的排位值，也就是说，如果要计算数据表中的中位数，得先知道数据表的总数是多少。可以使用COUNT函数统计总数。

计算奇数成员的中位数：

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) as median_price 
FROM goods;

结果为30，表示该表中所有商品价格的中位数为30。

计算偶数成员的中位数：

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) as median_price 
FROM (
SELECT price 
FROM goods 
ORDER BY price LIMIT 2 OFFSET 2
) AS t;

LIMIT 2：表示获取两行数据；
OFFSET 2：表示从第三行开始获取数据，即跳过头两行数据。

计算商品表中的中位数，输出结果为30。

使用PERCENTILE_DISC函数计算中位数

计算奇数成员的中位数：

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY price) as median_price 
FROM goods;

结果为30。

计算偶数成员的中位数：

SELECT (PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY price) + 
        PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY price))/2 as median_price 
FROM (
SELECT price 
FROM goods 
ORDER BY price LIMIT 2 OFFSET 2
) AS t;

输出结果与之前的函数一样，也是30。这里按照数据表的总数统计，再计算中位数。

方法二：使用SQL语句计算中位数

下面是使用SQL语句计算中位数的具体实现。我们可以按照以下步骤进行处理：

找到数字列中的最小值(minimum)和最大值(maximum)；
使用AVG函数计算出两个值的平均值，以得到即将被用作中位数的值(median)；
根据该中位数值，使用COUNT()函数计算在它之前或之后的行数(row_num)，然后将其存入到集合(c)中；
在条件分支中，分辨数字列行数的个数(n)是偶数还是奇数以如下方式计算中位数：
- 如果n是偶数，则将c中的两个数值相加并求平均值，则为结果；
- 如果n是奇数，则选择c中位置(row_num)为n/2的数。

下面是实现该算法的SQL业务流程：

SELECT
AVG(m.price) as median_price
FROM goods as m, goods as u
WHERE 1=1
AND u.price BETWEEN m.price - 1 AND m.price + 1
GROUP BY m.price 
HAVING SUM(CASE 
    WHEN m.price >= u.price THEN 1
    ELSE 0
END) >= SUM(CASE 
    WHEN m.price <= u.price THEN 1
    ELSE 0
END);

用法如下所示：

使用m和u两个表进行计算；
设置m.price为中位数；
使用HAVING子句来查找集合中的数字，并将其与行数一起存储到该表格；
根据所存储的行数来计算中位数。

最终的结果是30，与方法一的结果一致。

实例1：获得数据表中所有数字的中位数和平均数

假设有一个名为“numbers”的数据表，其中包含了一些整数，我们可以使用方法一的PERCENTILE_CONT和AVG函数来获得所有数字的中位数和平均值。

SELECT 
AVG(num) AS mean_num, 
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY num) AS median_num
FROM numbers;

实例2：获得数据表中某一列的中位数

假设有一个名为“salary”的数据表，其中包含了一些员工的薪资信息，我们想要求出“salary”列的中位数。

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) as median_salary 
FROM salary;

以上就是SQL计算中位数的详细攻略，包括使用SQL函数和SQL语句两种方式。

本站文章如无特殊说明，均为本站原创，如若转载，请注明出处：SQL 计算中位数 - Python技术站

SQL 计算中位数