PostgreSQL 15生成直方图统计结果异常问题求助

问题描述 #

使用PostgreSQL 15生成直方图时，列内共25条记录，但分桶数（nbins）设置为12或13时，sum(freq)结果为26，超出实际记录数；设置为11时结果正确为25。尝试调整min(x)乘以0.99、0.999等小数修正边界，仍出现类似统计不一致问题，需求是保证sum(freq)严格等于记录总数。

现有SQL代码 #

with rnorm as
                (SELECT 
                        my_col::numeric as x
                        from my_table
                ),
bin_params as
                (select 
                        min(x) as min_x 
                        ,max(x) as max_x
                        ,13 as nbins
                from rnorm),
temp_bins as
                (SELECT
                        generate_series(min_x::numeric, max_x::numeric, ((max_x - min_x) / nbins)::numeric) as bin
                from bin_params
                ),
bin_range as
                ( select
                        lag(bin) over (order by bin) as low_bin
                        ,b.bin as high_bin

                    from temp_bins b
                ),
frequency_table as
        (   select
                        b.low_bin
                        ,b.high_bin
                        ,count(*) as freq
                from bin_range b
                left join rnorm r
                        on r.x <= b.high_bin
                        and r.x > b.low_bin
                where   b.low_bin is not null
                        and b.high_bin is not null
                group by
                        b.low_bin
                        ,b.high_bin
                        ,count_x
                order by b.low_bin
        )

select * from frequency_table

样本数据 #

|"my_col"|
|------|
|74.03|
|73.995|
|73.988|
|74.002|
|73.992|
|74.009|
|73.995|
|73.985|
|74.008|
|73.998|
|73.994|
|74.004|
|73.983|
|74.006|
|74.012|
|74|
|73.994|
|74.006|
|73.984|
|74|
|73.988|
|74.004|
|74.01|
|74.015|
|73.982|

问题原因 #

• 浮点精度误差：使用generate_series结合浮点步长生成桶边界时，浮点数的二进制存储特性会导致边界值出现微小误差，部分记录可能同时满足多个桶的过滤条件，被重复统计。
• 边界逻辑漏洞：原SQL中r.x <= b.high_bin and r.x > b.low_bin的条件，在桶边界因精度问题出现重叠时，会触发重复计数。

解决方案 #

推荐使用PostgreSQL内置的width_bucket函数实现分桶，该函数专门用于数值分桶，能避免浮点精度问题，且统计逻辑更严谨。

修正后的SQL代码 #

WITH rnorm AS (
    SELECT my_col::numeric AS x FROM my_table
),
bin_params AS (
    SELECT
        min(x) AS min_x,
        max(x) AS max_x,
        13 AS nbins
    FROM rnorm
),
bucketed_data AS (
    SELECT
        x,
        width_bucket(x, min_x, max_x, nbins) AS bucket_num
    FROM rnorm, bin_params
),
bucket_ranges AS (
    SELECT
        bucket_num,
        min_x + (bucket_num - 1) * ((max_x - min_x) / nbins) AS low_bin,
        min_x + bucket_num * ((max_x - min_x) / nbins) AS high_bin
    FROM bin_params
    CROSS JOIN generate_series(1, nbins) AS bucket_num
)
SELECT
    br.low_bin,
    br.high_bin,
    COUNT(bd.x) AS freq
FROM bucket_ranges br
LEFT JOIN bucketed_data bd ON br.bucket_num = bd.bucket_num
GROUP BY br.bucket_num, br.low_bin, br.high_bin
ORDER BY br.bucket_num;

方案说明 #

1. width_bucket函数：将每个x值分配到对应的桶编号，内部处理了浮点精度问题，确保每个值只属于一个桶。
2. 桶边界计算：通过桶编号反向计算每个桶的上下边界，保证边界连续且无重叠。
3. 左连接统计：确保所有桶都被展示，即使没有数据的桶freq为0，同时保证每条记录只被统计一次。

额外优化 #

如果需要避免最后一个桶的边界因精度问题无法包含最大值，可以将max_x稍微放大（比如max_x * 1.000001），但使用width_bucket时通常无需此操作，函数会自动处理最大值的归属。

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