SQL从数据表中计算增长率

Question

我在SQL Server 2012 DB中有以下数据表:

DATE          TICKER    PRICE
2015-09-25      A         10
2015-09-24      A         9
2015-09-23      A         8
2015-09-25      B         3
2015-09-24      B         4
2015-09-23      B         5

我想用sql查询选择以下值:

TICKER   VALUE
 A       1.25
 B       0.6

其中股票代码A的值为10/8(最新日期和具体日期，在本例中为2015-09-23，它应该是可变的)。

票子B的值为3/5。

这是我尝试过的:

SELECT DISTINCT TICKER, 
ROUND(
((SELECT PRICE FROM INDICEPRICEHISTORY WHERE TICKER = 'A' AND EOD =  '2015-09-25') /
(SELECT PRICE FROM INDICEPRICEHISTORY WHERE TICKER = 'A' AND EOD = '2015-09-23')),2)
AS VALUE
FROM INDICEPRICEHISTORY

所以我至少需要做的是让WHERE TICKER = 'A'相对于所选的实际行情。并且EOD (EOD =日期)是可变的…

提前感谢。

Answer 1

您可以使用first_value和last_value分别获得每个跟踪器的第一个和最后一个价格。从那以后，就只有数学了:

SELECT DISTINCT [tracker], 
       LAST_VALUE([price]) OVER (PARTITION BY [tracker] 
                                 ORDER BY [date]
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND
                                              UNBOUNDED FOLLOWING) /
       FIRST_VALUE([price]) OVER (PARTITION BY [tracker] 
                                  ORDER BY [date]  
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND 
                                               UNBOUNDED FOLLOWING)
       AS [Growth]                                
FROM   mytable

SQLFiddle

Answer 2

您可以使用CTE来获取最后的价格，然后与您正在查找的日期的价格进行连接:

;WITH
    LastPrice
    (
        SELECT      Ticker, Price,
                    RowNumber = ROW_NUMBER() OVER (PARTITION BY Ticker ORDER BY EOD DESC)
        FROM        INDICEPRICEHISTORY
    )
SELECT      I.Ticker,
            Value = L.Price / I.Price
FROM        INDICEPRICEHISTORY  I
INNER JOIN  LastPrice           L ON I.Ticker = L.Ticker
WHERE       I.EOD = '2015-09-23' AND L.RowNumber = 1