英文:
SQL Server - calculated column
问题
我需要在一个视图中创建一个列,其派生如下。
计算的列是从Amount列派生的,其中一行的值是通过从Amount的前一行值中减去得到的,每个开始的第一个合同将没有值,因为它是该合同的第一行。
有人能否建议我如何实现这一点?
| 合同 | 日期 | 金额 | 计算列 |
|---|---|---|---|
| 111 | 20-June-2023 | 100 | |
| 111 | 21-June-2023 | 150 | 50 |
| 111 | 21-June-2023 | 250 | 100 |
| 111 | 21-June-2023 | 200 | 50 |
| 222 | 20-June-2023 | 300 | |
| 222 | 21-June-2023 | 350 | 50 |
| 222 | 22-June-2023 | 450 | 100 |
| 333 | 23-June-2023 | 100 | |
| 333 | 24-June-2023 | 200 | 100 |
| 333 | 24-June-2023 | 250 | 50 |
欢迎任何建议。
英文:
I need to create a column in a view which is derived as below.
The calculated column is derived from Amount column where a row value is derived by subtracting from previous row value of Amount, and every 1st contract that starts will have no value as it's the first row of that contract.
Can someone please suggest what option I have to achieve this?
| Contract | DateValue | Amount | Calculated Column |
|---|---|---|---|
| 111 | 20-June-2023 | 100 | |
| 111 | 21-June-2023 | 150 | 50 |
| 111 | 21-June-2023 | 250 | 100 |
| 111 | 21-June-2023 | 200 | 50 |
| 222 | 20-June-2023 | 300 | |
| 222 | 21-June-2023 | 350 | 50 |
| 222 | 22-June-2023 | 450 | 100 |
| 333 | 23-June-2023 | 100 | |
| 333 | 24-June-2023 | 200 | 100 |
| 333 | 24-June-2023 | 250 | 50 |
Any suggestions are welcome.
答案1
得分: 2
这是使用窗口函数 LAG() 来获取前一个值的方法:
select *, Amount - lag(Amount) over (partition by Contract order by DateValue) as [Calculated Column]
from mytable
英文:
This is a way to do it using window function LAG() to get the previous value :
select *, Amount - lag(Amount) over (partition by Contract order by DateValue) as [Calculated Column]
from mytable
答案2
得分: 0
你的数据无法确定重复的DateValues的顺序,所以我会假设你也有一个identity id列。
(关系是无序集合!)
为什么 200 - 250 = 50?
选择 id、[合同]、DateValue、金额
,金额 -
LAG(金额) 在(按[合同]分区 按DateValue、id排序)上 OVER FROM YourTable AS CalcValue
英文:
Your data has no way to determine the order of duplicate DateValues so I will assume you also have an identity id column.
(A relation is an unordered set!)
Why does 200 - 250 = 50?
SELECT id, [Contract], DateValue, Amount
,Amount -
LAG(Amount) OVER (PARTITION BY [Contract] ORDER BY DateValue, id) AS CalcValue
FROM YourTable;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论