根据先前的行创建计算字段Oracle SQL [英] Create calculated field based on previous rows Oracle SQL
问题描述
我正在尝试创建一个手动计算的列，以跟踪当前的广告资源.
I am trying to create a manually calculated column where I keep track of a current inventory.
当前，我有一个看起来像这样的表:
Currently, I have a table that looks like this:
| Group | Part | Operation Type | Transaction Amount |
|--------------|------|----------------|--------------------|
| Concrete | A | STOCK | 100 |
| Concrete | A | Buy | 25 |
| Concrete | A | Make | -10 |
| Concrete | A | Make | -10 |
| Concrete | A | Make | -10 |
| Concrete | A | Make | -10 |
| Concrete | A | Make | -10 |
| Concrete | B | STOCK | -10 |
| Concrete | B | Make | -10 |
| Concrete | B | Make | -10 |
| Concrete | B | Make | -10 |
| Concrete | B | Make | -10 |
| Concrete | B | Make | 150 |
| Construction | C | STOCK | 10 |
| Construction | C | Make | -1 |
| Construction | C | Make | -1 |
| Construction | C | Make | -1 |
| Construction | C | Make | -1 |
| Construction | D | STOCK | 5 |
| Construction | D | Make | -5 |
该表首先按group排序，然后按part排序，然后始终将STOCK显示为第一个值.这个想法是创建一个新的手动计算列curr_inventory，该列使我们能够跟踪当前库存，并查看给定零件，给定组的库存是否或何时下降到0以下.
The table is first ordered by group then by part, and then STOCK is always shown as the first value. The idea is to create a new manually calculated column, curr_inventory, that allows for us to keep track of current inventory and see if or when our inventory for a given part, for a given group, dips below 0.
理想情况下，最终结果将如下所示:
Ideally, the end results would look like this:
| Group | Part | Operation Type | Transaction Amount | New_Inventory_Column |
|:------------:|:----:|:--------------:|:------------------:|:--------------------:|
| Concrete | A | STOCK | 100 | 100 |
| Concrete | A | Buy | 25 | 125 |
| Concrete | A | Make | -10 | 115 |
| Concrete | A | Make | -10 | 105 |
| Concrete | A | Make | -10 | 95 |
| Concrete | A | Make | -10 | 85 |
| Concrete | A | Make | -10 | 75 |
| Concrete | B | STOCK | 10 | 10 |
| Concrete | B | Make | -10 | 0 |
| Concrete | B | Make | -10 | -10 |
| Concrete | B | Make | -10 | -20 |
| Concrete | B | Make | -10 | -30 |
| Concrete | B | Make | 150 | 120 |
| Construction | C | STOCK | 10 | 10 |
| Construction | C | Make | -1 | 9 |
| Construction | C | Make | -1 | 8 |
| Construction | C | Make | -1 | 7 |
| Construction | C | Make | -1 | 6 |
| Construction | D | STOCK | 5 | 5 |
| Construction | D | Make | -5 | 0 |
最终结果将是一列，该列在零件号更改且操作类型为STOCK时启动，然后开始(使用交易金额)计算当前库存量. 我不知道从哪里可以开始执行此操作的SQL查询.直观地，伪代码看起来像:
The end result would be a column that initiates when the part number has changed and the operation type is STOCK, and then begins to calculate (using the transaction amount) what the current inventory is. I am not sure where to start on a SQL query that would allow for this. Intuitively, the pseudocode would look something like:
for each row in table:
if operation_type == "stock":
curr_inv = stock.value
else:
curr_inv = previous_curr_inv + transaction_amount
但是，我不确定如何开始为此编写SQL.我通常尝试发布正在使用的SQL，但我什至不知道从哪里开始.我在SO上在线查看了各种帖子，包括像这样的帖子 ，和，及此，但我看不到如何将所选答案用作解决方案.
However, I am not sure how to even begin writing SQL for this. I typically try to post what SQL I am working with but I don't even know where to begin. I have looked at various posts online, on SO, including posts like this, and this, and this, but I could not see how the selected answers could be used as a solution.
推荐答案
我使用了window函数来计算运行总计.
I used the window function to calculate the running total.
我在子查询中添加了row_number列.
I added the row_number column in the subquery.
尝试一下:
select t1."Group",t1."Part",t1."Operation Type", t1."Transaction Amount",
sum(t1."Transaction Amount") over (partition by t1."Group",t1."Part" order by t1.rownumber)
from (
select row_number() over (order by null) as rownumber, t.*
from test t ) t1
测试结果:
这篇关于根据先前的行创建计算字段Oracle SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
