如何计算同一列的百分比差异(增加和减少)?我何时需要用例?

Question

我想根据前一个值和新值计算百分比差。但是，我希望数据显示在1列中。下面的公式计算了增加的百分比，但我还需要添加减少的百分比公式，它将是

(b。[关闭]——[关闭])/b。[关闭])* (100)% G/L的

我需要做一个case when函数还是有一个更简单的方法?

我当前的公式如下:

select 
,a.[close] - b.[close] as '$ G/L'
,((a.[close] - b.[close])/b.[close])*(100) as '% G/L'
FROM
(SELECT *,
ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY date DESC) AS rn
FROM [Stocks].[dbo].[Live_Ticker_Data]
WHERE time between  '09:29' and '16:01'
) a INNER JOIN
(SELECT *
FROM Historical_Data
WHERE Date = ( SELECT MAX(Date) 
FROM Historical_Data
WHERE Date < ( SELECT MAX(Date) 
FROM Historical_Data
))) b
ON a.Ticker = b.Ticker
WHERE rn = 1

Answer 1

如果您只希望增益或损失为正值，则添加ABS()。