问题描述

我正在使用 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