两表关联SQL查询结果中，如何提取指定列的中位数？

嘿，这个需求挺常见的，我来给你拆解一下——首先咱们得把你原来的查询结果作为基础数据集，然后针对不同的SQL数据库，用对应的方法来计算中位数。下面分几种主流数据库给你具体的实现方案：

通用思路 #

先把你的原查询封装成一个临时数据集（比如CTE或者子查询），然后对这个数据集中的column2（我给它改名叫column2_count避免和原表字段混淆）排序，找到中间位置的值：如果是偶数行，中位数是中间两个数的平均值；奇数行就是中间那个数。

1. PostgreSQL（推荐，内置函数直接用） #

PostgreSQL有专门的中位数函数，PERCENTILE_CONT和PERCENTILE_DISC，前者会在中间值之间插值（适合连续型数据），后者会取实际存在的数值（适合离散型）。直接套你的原查询就行：

WITH query_results AS (
    SELECT table1.column1, COUNT(DISTINCT table2.column2) AS column2_count
    FROM table1 
    LEFT JOIN table2 ON table1.column1 = table2.column4 
    WHERE column3 = 1 
    GROUP BY table1.column1
)
SELECT 
    -- 连续型中位数，偶数行取中间两数的平均值
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column2_count) AS median_cont,
    -- 离散型中位数，取中间位置的实际值
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY column2_count) AS median_disc
FROM query_results;

用你给的示例数据（4行：4,5,5,5），两个函数返回的都是5，刚好符合预期。

2. MySQL 8.0+（支持窗口函数） #

MySQL 8.0及以上可以用窗口函数来给结果编号，然后筛选中间行计算平均值：

WITH query_results AS (
    SELECT table1.column1, COUNT(DISTINCT table2.column2) AS column2_count
    FROM table1 
    LEFT JOIN table2 ON table1.column1 = table2.column4 
    WHERE column3 = 1 
    GROUP BY table1.column1
),
ranked_results AS (
    SELECT 
        column2_count,
        ROW_NUMBER() OVER (ORDER BY column2_count) AS row_num,
        COUNT(*) OVER () AS total_rows
    FROM query_results
)
SELECT AVG(column2_count) AS median
FROM ranked_results
WHERE row_num IN (FLOOR((total_rows + 1)/2), CEIL((total_rows + 1)/2));

这个语句会自动判断行数奇偶：如果是奇数行，两个位置是同一个数，平均值就是它自己；偶数行就取中间两个的平均。

3. MySQL 5.x（无窗口函数，用变量实现） #

如果你的MySQL版本比较旧，只能用用户变量来实现：

SELECT AVG(column2_count) AS median
FROM (
    SELECT 
        @row_num := @row_num + 1 AS row_num,
        column2_count
    FROM (
        -- 这里是你的原查询
        SELECT COUNT(DISTINCT table2.column2) AS column2_count
        FROM table1 
        LEFT JOIN table2 ON table1.column1 = table2.column4 
        WHERE column3 = 1 
        GROUP BY table1.column1
        ORDER BY column2_count
    ) AS sub_query,
    -- 初始化变量
    (SELECT @row_num := 0) AS init_var
) AS ranked_data
WHERE row_num IN (FLOOR((@row_num + 1)/2), CEIL((@row_num + 1)/2));

4. SQL Server #

SQL Server 2012及以上也支持PERCENTILE_CONT/PERCENTILE_DISC，或者用窗口函数：

方法一：用内置函数 #

WITH query_results AS (
    SELECT table1.column1, COUNT(DISTINCT table2.column2) AS column2_count
    FROM table1 
    LEFT JOIN table2 ON table1.column1 = table2.column4 
    WHERE column3 = 1 
    GROUP BY table1.column1
)
SELECT 
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column2_count) OVER () AS median_cont,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY column2_count) OVER () AS median_disc
FROM query_results
ORDER BY median_cont
OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY;

方法二：窗口函数编号 #

WITH query_results AS (
    SELECT table1.column1, COUNT(DISTINCT table2.column2) AS column2_count
    FROM table1 
    LEFT JOIN table2 ON table1.column1 = table2.column4 
    WHERE column3 = 1 
    GROUP BY table1.column1
),
ranked_results AS (
    SELECT 
        column2_count,
        ROW_NUMBER() OVER (ORDER BY column2_count) AS row_num,
        COUNT(*) OVER () AS total_rows
    FROM query_results
)
SELECT AVG(CAST(column2_count AS DECIMAL(10,2))) AS median
FROM ranked_results
WHERE row_num BETWEEN (total_rows + 1)/2 AND (total_rows + 2)/2;

你可以根据自己使用的数据库类型和版本，选对应的方法测试一下。用你给的示例数据，所有方法都会返回5，完全符合中位数的要求。

内容的提问来源于stack exchange，提问作者kalett