英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论