在 SQL Server 2008 R2 中引用前一行值进行算术计算

我正在使用 SQL Server 2008 R2，并且是关系数据库的新手。我需要运行一个简单的计算，但计算涉及使用前一行的值。

Example:

(Value of X) / ((Value of Y at time t + Value of Y at time t-1) / 2)

Example:

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

据我了解，您想根据日期差异而不是真正的行顺序进行计算，对吗？

如果是的话，如果你有一张这样的桌子

CREATE TABLE YourTable(
    ACHQ float ,
    RECTQ float,
    DATE datetime)

INSERT INTO YourTable VALUES    (100,10,'20100101')
INSERT INTO YourTable VALUES    (200,20,'20110101')
INSERT INTO YourTable VALUES    (300,30,'20120101')
INSERT INTO YourTable VALUES    (400,40,'20130101')
INSERT INTO YourTable VALUES    (500,50,'20140101')
INSERT INTO YourTable VALUES    (600,60,'20150101')

你可以做这样的事情

SELECT
    ((c.ACHQ)/(c.RECTQ + cPreviousYear.RECTQ)/2) as 'AR'
FROM
    YourTable c
        LEFT JOIN YourTable cPreviousYear
            ON YEAR(c.Date) - 1 = YEAR(cPreviousYear.Date)

我简化了计算，只是为了表明您可以将表本身直接链接到具有所需日期差异的行，然后计算值。你甚至可以使用ON DATEADD(y, -1, c.Date) = cPrevious.Date如果你想要真实的日期差异

抱歉，如果我错过了重点。