计算分配/ Netty 的 SQL 查询

Question

这是我的源数据，

Group | Item | Capacity
-----------------------
1     | A    | 100 
1     | B    | 80 
1     | C    | 20 
2     | A    | 90 
2     | B    | 40 
2     | C    | 20 

以上数据显示了每个项目消耗“某物”的能力。现在假设我最多为每个组分配了 100 个。我想将这个“100”分配给每个组，直到项目的最大容量。所以我想要的输出是这样的:

Group | Item | Capacity | consumption
-------------------------------------
1     | A    | 100      | 100
1     | B    | 80       | 0
1     | C    | 20       | 0
2     | A    | 90       | 90
2     | B    | 40       | 10
2     | C    | 20       | 0

我的问题是如何在单个 SQL 查询中执行此操作(最好避免任何子查询构造)。请注意，每组中的项目数量不固定。

我正在尝试 LAG() 运行 SUM()，但无法完全产生所需的输出...

select 
group, item, capacity,
sum (capacity) over (partition by group order by item range between UNBOUNDED PRECEDING AND CURRENT ROW) run_tot,
from table_name

Answer 1

没有仅使用分析 SUM 函数的子查询:

SQL> create table mytable (group_id,item,capacity)
  2  as
  3  select 1, 'A' , 100 from dual union all
  4  select 1, 'B' ,  80 from dual union all
  5  select 1, 'C' ,  20 from dual union all
  6  select 2, 'A' ,  90 from dual union all
  7  select 2, 'B' ,  40 from dual union all
  8  select 2, 'C' ,  20 from dual
  9  /

Table created.

SQL> select group_id
  2       , item
  3       , capacity
  4       , case
  5         when sum(capacity) over (partition by group_id order by item) > 100 then 100
  6         else sum(capacity) over (partition by group_id order by item)
  7         end -
  8         case
  9         when nvl(sum(capacity) over (partition by group_id order by item rows between unbounded preceding and 1 preceding),0) > 100 then 100
 10         else nvl(sum(capacity) over (partition by group_id order by item rows between unbounded preceding and 1 preceding),0)
 11         end consumption
 12    from mytable
 13  /

  GROUP_ID I   CAPACITY CONSUMPTION
---------- - ---------- -----------
         1 A        100         100
         1 B         80           0
         1 C         20           0
         2 A         90          90
         2 B         40          10
         2 C         20           0

6 rows selected.