在 SQL Server 2008 R2 中为算术计算引用前一行值
我正在使用 SQL Server 2008 R2 并且是关系数据库的新手.我需要运行一个简单的计算，但计算涉及使用前一行值.
I am working with SQL Server 2008 R2 and new to relational database. I need to run a simple calculation but the calculation involves using a previous row value.
示例:
(Value of X) / ((Value of Y at time t + Value of Y at time t-1) / 2)
示例:
select (x/[(y@time,t + y@time,t-1)/2]) as 'Value'
from datatable
select ((c.ACHQ)/(c.RECTQ(row:n) + c.RETQ(row:n-1))/2) as 'AR'
from co_ifndq c
where c.GVKEY in
(select GVKEY
from spidx_cst
where DATADATE = '2012-03-12'
and INDEXID = '500')
and c.DATAFMT = 'std'
and c.DATADATE > '1990-12-30'
order by c.GVKEY, datadate desc
推荐答案
假设 x、y 和 t 都在同一张表上，请尝试:
Assuming x, y and t are all on the same table, try:
;with cte as (
select m.*, row_number() over (order by t) rn from mytable)
select t1.t, t1.x / ((t1.y + t0.y)/2) as [value]
from cte t1
left join cte t0 on t0.rn = t1.rn-1
基于提供的查询:
;with cte as (
select c.*, row_number() over (partition by c.GVKEY order by c.DATADATE) rn
from co_ifndq c
where c.GVKEY in
(select GVKEY
from spidx_cst
where DATADATE = '2012-03-12' and INDEXID = '500')
and c.DATAFMT = 'std'
and c.DATADATE > '1990-12-30'
)
select t1.GVKEY, t1.DATADATE, t1.ACHQ / ((t1.RETQ + t0.RETQ)/2) as [value]
from cte t1
left join cte t0 on t1.GVKEY = t0.GVKEY and t0.rn = t1.rn-1
order by t1.GVKEY, t1.datadate desc
相关文章