SQL Server – 计算列

huangapple go评论59阅读模式
英文:

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

Demo here

答案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;

huangapple
  • 本文由 发表于 2023年6月25日 18:00:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/76549834.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定