Suppose I have 2 tables `T1` and `T2` as follows

`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

```

Each record in table `T1` represents a bag and its capacity, here I have 5 bags. I want to write an SQL that allocate items in table `T2` into each bag with the same type, i.e. the result should be like this

```
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

```

Therefore, **I am finding some kind of aggregation function, let's call it `allocate()`, that can produce the column `allocated_amount` as above.** I have a guess that, if exists, it might be used like this

```
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

```

My current solution is to use a temp table and PL/SQL loop for calculation, but I hope I can do it with one simple SQL.