sql - SQL 中的无损/公平份额划分

Question

在一些情况下，我希望使用 PostgreSQL 将一个整数除以另一个整数并完成三件事:

1. 所有的结果数都是整数
2. 使所有结果数字的总和等于原始数字(无损方法)
3. 采用“公平份额”分配，避免将剩余部分倾倒到最后一个桶中的“曲棍球棒”效应

我现在要处理的具体问题是我每月有这样的数量:

Month      Part       Qty
---------  ---------  -----
1/1/2016   ABC        10
2/1/2016   ABC         9

这是我目前为止取得的成功，它实现了我的前两个目标，但没有实现我的第三个目标:

with weekly_buckets as (
  select generate_series (0, 3) as week_number
)
select
  p.month_date + 7 * w.week_number as week_date,
  p.part_number,
  case w.week_number
    when 0 then p.qty / 4
    when 1 then p.qty / 4
    when 2 then p.qty / 4
    when 3 then p.qty - 3 * (p.qty / 4)
  end as qty
from
  part_demand p
  cross join weekly_buckets w

导致:

Week       Part       Qty
---------  ---------  -----
1/1/2016   ABC        2
1/8/2016   ABC        2
1/15/2016  ABC        2
1/22/2016  ABC        4

因此在最后一周出现了 4 的曲棍球棒效应。我可以使用天花板而不是地板，但在 3, 3, 3, 1 时效果更差。

理想情况下，点差看起来像 2、3、2、3 或 3、2、3、2。两者都可以接受。这就是我所说的公平份额差价的意思。如果对此有更数学上正确的术语，请赐教。

作为引用，这些数量代表月度预测，我们正在尝试确定实际订单是否达到目标、提前或落后于预测。

另外，如果我能解决这个问题，我就可以利用逻辑来做同样的事情来为年度数量创建月度桶。

最后一个例子，如果我看到这样的东西:

Month      Part       Qty
---------  ---------  -----
1/1/2016   ABC        1

理想情况下，唯一具有非零值的一周是第 2 周或第 3 周。这真的无关紧要，但如果您想知道如何处理低值，这就是我的想法。

Answer 1

您可以使用窗口函数来分配值。这是您的第一个示例:

with b as (
      select generate_series(1, 4) as i, 10 as amt
     ),
     bb as (
      select b.*,
             count(*) over () as numbuckets,
             row_number() over (order by i) as rn,
         amt % (count(*) over () ) as remainder
      from b
     )
select bb.*,
       (amt / numbuckets +
        (case when rn <= remainder then 1 else 0 end)
       ) as partitioned
from bb;

想法是为每个桶分配amt/numbuckets。然后使用 row_number() 将余数分散到桶中。如果您希望值随机分布，则使用 order by random() 来定义 rn。