SQL中如何统计低于某列指定百分位的行数及其占比？

以下是可直接落地的实现逻辑，默认你使用的表名为data_table，存储数值的目标列名为value_column，需要计算的指定百分位为第25百分位：

方案1：易理解的CTE分步骤写法 #

适合数据量不大、优先可读性的场景，分三步计算阈值、总数据量、匹配统计：

WITH percentile_threshold AS (
    -- 计算指定百分位的阈值，需要其他百分位可修改括号内的数值，如0.5对应第50百分位
    SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY value_column) AS target_percentile_val
    FROM data_table
),
total_count AS (
    -- 统计全表总数据行数
    SELECT COUNT(*) AS total_rows FROM data_table
)
SELECT
    COUNT(*) AS below_threshold_rows,
    ROUND(COUNT(*) * 100.0 / total_count.total_rows, 2) AS below_threshold_ratio_percent,
    total_count.total_rows AS all_rows
FROM data_table, percentile_threshold, total_count
WHERE value_column < percentile_threshold.target_percentile_val
GROUP BY total_count.total_rows;

方案2：单表扫描的窗口函数高效写法 #

适合大数据量表，仅需扫描一次表即可完成所有计算，性能更好：

SELECT
    SUM(CASE WHEN value_column < target_percentile_val THEN 1 ELSE 0 END) AS below_threshold_rows,
    ROUND(SUM(CASE WHEN value_column < target_percentile_val THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS below_threshold_ratio_percent,
    COUNT(*) AS all_rows
FROM (
    SELECT
        value_column,
        percentile_cont(0.25) WITHIN GROUP (ORDER BY value_column) OVER () AS target_percentile_val
    FROM data_table
) AS temp_calc

注意事项 #

• 如果需要统计低于或等于指定百分位的行数，把WHERE/CASE判断中的<改为<=即可
• 代码中的ROUND函数第二个参数是占比的小数保留位数，可根据业务需求自行调整
• 上述写法兼容PostgreSQL、Oracle、MySQL 8.0及以上、SQL Server等支持标准SQL语法的数据库

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