sql - 在每个 bin 中创建具有唯一值的 bin

Question

我想以这样的方式对数字列 (var) 进行分类，使每个分类中的行数大致相同。我的附加要求是该列中的一个(唯一)值不能分配给多个 bin。例如，如果 var 列中的值 1 分配给 bin 1，则不允许将值 1 也分配给 bin 2。

我知道 ntile() 或 percent_rank() 函数，但我不知道如何将它们用于手头的任务。

drop table if exists binme;
create table binme (var numeric);

insert into binme values
    (0), (0), (0),
    (1), (1), (1.5), (1.5),
    (2), (2), (2), (2.5),
    (3), (3), (3.5), (4.5),
    (5), (6), (7), (10), (11);

select (var * 100)::int, ntile(5) over(order by var), percent_rank() over(order by var) 
from binme;

对于我的示例和 5 个 bin，所需的结果将是:

var ntile required_bin
0   1   1   
0   1   1   
0   1   1   
1   1   1   
1   2   1   Has to be in bin 1
1.5 2   2   
1.5 2   2   
2   2   2   
2   3   2   
2   3   2   Has to be in bin 2
2.5 3   3   
3   3   3   
3   4   3   
3.5 4   3   
4.5 4   4   
5   4   4   
6   5   4   
7   5   4   
10  5   5   
11  5   5   

我不知何故直觉地觉得可能有必要首先按 var 进行分组，获取每个值的行数，然后使用一些递归查询将 bin 分配给原始数据。应该可以从以下几点推算出来:

select 
    var, 
    cnt, 
    sum(cnt) over(order by var) as nrows
from 
    (select var, count(*) cnt from binme group by var) a;

Answer 1

如果您只是在寻找一个近似值(确保将相同的值放在同一个桶中)，那么您确实可以使用 width_bucket如@greg所述, 但为了平衡每个桶的项目数，它必须应用于累计总和而不是 var本身的值(value)。这是一个演示( SQL fiddle ，下面改进的解决方案):

SELECT
   o.var,
   WIDTH_BUCKET(o.cumSum, 1, o.cnt + 1, 5) bucket
FROM
   (SELECT
       b.var,
       (SELECT COUNT(*) FROM binme t) AS cnt,
       (SELECT COUNT(*) FROM binme t WHERE t.var <= b.var) AS cumSum
    FROM
       binme b
    ) o
;

累计总和(或累计计数可能更精确)至少为 1 (包括最小值)和最大值(不包括)cnt + 1 ，第三个参数指定桶的数量。第一个桶是 1 (不是 0 ，从 0 开始的桶号减一)。

或者您可以使用<而不是 <=并将范围设置为 [0,cnt) ，这可能是更好的解决方案:SQL fiddle .