假设我有两个表T1
和T2
,如下所示
T1
:
bag_id bag_type capacity
------|--------|--------
1 A 500
2 A 300
3 A 100
4 B 200
5 B 100
T2
:
item_type item_amount
---------|-----------
A 850
B 300
表T1
中的每条记录代表一个袋子及其容量,这里我有5个袋子。我想写一个SQL,分配项目在表T2
到每个袋子具有相同的类型,即结果应该是这样的
bag_id bag_type capacity allocated_amount
------|--------|--------|----------------
1 A 500 500
2 A 300 300
3 A 100 50
4 B 200 200
5 B 100 100
因此,我正在寻找某种聚合函数,我们称其为allocate()
,它可以产生如上所述的allocated_amount
列。我猜,如果存在,它可能像这样使用
select
t1.bag_id,
t1.bag_type,
t1.capacity,
allocate(t2.item_amount, t1.capacity)
over (partition by t1.bag_type order by t1.capacity desc) as allocatd_amount
from t1, t2
where t2.item_type = t1.bag_type
我目前的解决方案是使用临时表和PL/SQL循环进行计算,但我希望我可以用一个简单的SQL。
您正在寻找累积和。像这样:
select t1.*,
(case when cumecap <= t2.item_amount
then t1.capacity
when cumecap - t1.capacity <= t2.item_amount
then t2.item_amount - (cumecap - t1.capacity)
else 0
end) as allocated_capacity
from (select t1.*,
sum(t1.capacity) over (partition by bag_type order by bag_id) as cumecap
from t1
) t1 join
t2
on t1.bag_type = t2.item_type;
这应该能奏效:
select t1.bag_id
, t1.bag_type
, t1.capacity
, least( t1.capacity -- don't over fill the bag
, greatest( 0 -- don't under fill the bag
, t2.item_amount -- to be allocated
- nvl(sum(t1.capacity) -- less previous allocations
over (partition by t1.bag_type
order by t1.capacity desc
rows between unbounded preceding and 1 preceding)
, 0))) Allocated
from t1
join t2
on t2.item_type = t1.bag_type;
BAG_ID B CAPACITY ALLOCATED
---------- - ---------- ----------
1 A 500 500
2 A 300 300
3 A 100 50
4 B 200 200
5 B 100 100
假设按袋容量递减顺序分配
with agg as (
select bag.BAG_ID, bag.BAG_TYPE, bag.CAPACITY,
SUM(bag.CAPACITY) over (partition by bag.bag_type order by bag.capacity DESC) agg_capacity,
item_amount
from bag, item
where bag.bag_type = item.item_type
)
select
BAG_ID, BAG_TYPE, CAPACITY,
case when ITEM_AMOUNT >= AGG_CAPACITY then CAPACITY /* Full allocated */
when ITEM_AMOUNT >= AGG_CAPACITY-CAPACITY then ITEM_AMOUNT - (AGG_CAPACITY-CAPACITY) /* partly allocated */
else 0 end /* not allocated */
as allocated
from agg
order by bag_type, capacity desc;
BAG_ID BAG_TYPE CAPACITY ALLOCATED
------ -------- ---------- ----------
1 A 500 500
2 A 300 300
3 A 100 50
4 B 200 200
5 B 100 100
请注意,如果您希望最大限度地减少浪费能力,那么分配的顺序很重要,并且使用不同的顺序找到最佳分配可能很困难。