如何以每条记录都与“前一个"记录连接的方式自连接表?记录? [英] How to self-join table in a way that every record is joined with the "previous" record?

问题描述

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

I have a MS SQL table that contains stock data with the following columns: Id, Symbol, Date, Open, High, Low, Close.

我想自行加入表格，这样我就可以获得 Close 的日常百分比变化.

I would like to self-join the table, so I can get a day-to-day % change for Close.

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

I must create a query that will join the table with itself in a way that every record contains also the data from the previous session (be aware, that I cannot use yesterday's date).

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

My idea is to do something like this:

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 索引放在 (Symbol, Date) 对上会提高性能吗?)

However I do not know if that's the correct/fastest way. What should I take into account when thinking about performance? (E.g. will putting UNIQUE index on a (Symbol, Date) pair improve performance?)

此表中每年将有大约 100,000 条新记录.我使用的是 MS SQL Server 2008

There will be around 100,000 new records every year in this table. I am using MS SQL Server 2008

推荐答案

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

One option is to use a recursive cte (if I'm understanding your requirements correctly):

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 演示

如果您需要每个交易品种的运行总数用作百分比变化，那么很容易为该数量添加一个额外的列——不完全确定您的意图是什么，所以以上只是将当前关闭的金额按上次关闭金额.

If you need a running total for each symbol to use as the percentage change, then easy enough to add an additional column for that amount -- wasn't completely sure what your intentions were, so the above just divides the current closed amount by the previous closed amount.

这篇关于如何以每条记录都与“前一个"记录连接的方式自连接表?记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！