sql - 如何以每条记录都与 "previous"记录连接的方式自连接表？

Question

我有一个 MS SQL 表，其中包含具有以下列的股票数据:Id、Symbol、Date、Open、High、Low、Close。

我想自行加入该表，这样我就可以获得关闭的每日百分比变化。

我必须创建一个查询，该查询将以每条记录还包含上一个 session 的数据的方式将表与其自身连接起来(请注意，我不能使用昨天的日期)。

我的想法是做这样的事情:

select * from quotes t1
inner join quotes t2
on t1.symbol = t2.symbol and
t2.date = (select max(date) from quotes where symbol = t1.symbol and date < t1.date)

但是我不知道这是否是正确/最快的方法。在考虑性能时我应该考虑什么？ (例如，将 UNIQUE 索引放在(符号、日期)对上会提高性能吗？)

此表中每年大约会出现 100,000 条新记录。我正在使用 MS SQL Server 2008

Answer 1

一种选择是使用递归 cte(如果我正确理解您的要求):

WITH RNCTE AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY date) rn
        FROM quotes
  ),
CTE AS (
  SELECT symbol, date, rn, cast(0 as decimal(10,2)) perc, closed
  FROM RNCTE
  WHERE rn = 1
  UNION ALL
  SELECT r.symbol, r.date, r.rn, cast(c.closed/r.closed as decimal(10,2)) perc, r.closed
  FROM CTE c 
    JOIN RNCTE r on c.symbol = r.symbol AND c.rn+1 = r.rn
  )
SELECT * FROM CTE
ORDER BY symbol, date

SQL Fiddle Demo

如果您需要每个符号的运行总计来用作百分比变化，那么很容易为该金额添加一个附加列 - 不完全确定您的意图是什么，所以上面只是除以当前的收盘价金额除以之前的平仓金额。