SQL（Metabase）计算各科目参考学生数中位数并找最大值

没问题，我来帮你搞定这个需求，分步骤拆解SQL实现，完全适配Metabase的使用场景：

核心思路 #

你之前的问题出在没对学生去重——直接COUNT(student_no)会把同一学生同一科目的多次考试都算进去，必须用COUNT(DISTINCT student_no)才能得到每个科目的真实参考学生数。然后我们需要基于这些学生数计算中位数，最后关联展示结果。

完整SQL实现（兼容大多数数据库，Metabase可用） #

-- 第一步：计算每个科目的去重参考学生数
WITH subject_student_counts AS (
    SELECT
        subject_code,
        COUNT(DISTINCT student_no) AS student_count
    FROM Results_tbl
    GROUP BY subject_code
),
-- 第二步：给每个科目的学生数排名，用于计算中位数
ranked_counts AS (
    SELECT
        student_count,
        ROW_NUMBER() OVER (ORDER BY student_count) AS row_num,
        COUNT(*) OVER () AS total_subjects
    FROM subject_student_counts
),
-- 第三步：计算全局中位数（离散型，返回实际存在的学生数）
median_result AS (
    SELECT
        student_count AS global_median
    FROM ranked_counts
    WHERE row_num = CASE
        -- 奇数个科目时取中间值，偶数个时取上中位数（也可改为取下中位数：FLOOR((total_subjects + 1)/2)）
        WHEN total_subjects % 2 = 1 THEN (total_subjects + 1)/2
        ELSE total_subjects/2 + 1
    END
)
-- 第四步：关联展示所有科目的数据和中位数，按学生数降序排列
SELECT
    s.subject_code,
    s.student_count,
    m.global_median
FROM subject_student_counts s
CROSS JOIN median_result m
ORDER BY s.student_count DESC;

代码解释 #

1. subject_student_counts：这个CTE负责统计每个科目真正的参考人数，COUNT(DISTINCT student_no)完美排除了同一学生多次考同一科目的重复统计。
2. ranked_counts：给所有科目的学生数排序，并统计总科目数，为中位数计算做准备。
3. median_result：根据总科目数的奇偶性计算中位数位置，返回实际存在的学生数（离散型中位数，比连续型更贴合业务场景）。
4. 最后关联所有结果，按学生数降序排列，你可以直接从结果里找到student_count等于global_median的科目，这些就是中位数对应的科目；如果是偶数个科目，这里取的是上中位数，你可以根据需求调整CASE里的逻辑。

简化版（支持PERCENTILE函数的数据库，比如PostgreSQL/MySQL 8.0+） #

如果你的Metabase连接的数据库支持PERCENTILE_DISC函数，可以用更简洁的写法：

WITH subject_student_counts AS (
    SELECT
        subject_code,
        COUNT(DISTINCT student_no) AS student_count
    FROM Results_tbl
    GROUP BY subject_code
),
median_calculation AS (
    SELECT
        PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY student_count) AS global_median
    FROM subject_student_counts
)
SELECT
    s.subject_code,
    s.student_count,
    m.global_median
FROM subject_student_counts s
CROSS JOIN median_calculation m
ORDER BY s.student_count DESC;

如何找中位数最高的科目 #

执行上述查询后，看结果里student_count等于global_median的行——如果有多个科目符合，这些都是中位数对应的科目；如果是偶数个科目，我们的代码取的是上中位数（数值更大的那个），所以对应的科目就是你要找的“中位数最高的科目”。

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