在最近的可用日期进行 SQL 联接

Question

我目前有这些表:

CREATE TABLE #SECURITY_TEMP (ID CHAR(30))
CREATE TABLE #SECURITY_TEMP_PRICE_HISTORY (ID CHAR(30), PRICEDATE DATE, PRICE FLOAT)
CREATE TABLE #SECURITY_POST (ID CHAR(30), SECPOS int)

INSERT INTO #SECURITY_TEMP (ID) VALUES ('APPL') ,('VOD'),('VOW3'), ('AAA')
INSERT INTO #SECURITY_TEMP_PRICE_HISTORY (ID,PRICEDATE, PRICE) VALUES 
('APPL', '20150101',10.4), ('APPL', '20150116',15.4), ('APPL', '20150124',22.4), 
('VOD', '20150101', 30.5), ('VOD', '20150116',16.5), ('VOD', '20150124',16.5), 
('VOW3', '20150101', 45.5), ('VOW3', '20150116',48.8) ,('VOW3', '20150124',50.55), 
('AAA', '20100118', 0.002)

INSERT INTO #SECURITY_POST (ID,SECPOS) VALUES ('APPL', 100), ('VOD', 350), ('VOW3', 400)

我想要一个干净的表格，显示证券 ID、证券头寸以及该证券在某个日期过去后的最新可用价格。

现在当我执行以下操作时:

SELECT sec.ID, sec.SECPOS, t.PRICE
FROM #SECURITY_POST as SEC INNER JOIN #SECURITY_TEMP_PRICE_HISTORY as t
ON sec.ID = t.ID
WHERE t.PriceDate = '20150101'
GROUP BY sec.ID, secPos, t.price

我得到了正确的结果

 1. ID  SECPOS  PRICE 
 2. APPL  100   10.4
 3. VOD   350   30.5
 4. VOW3  400   45.5

但是，在个别情况下，可能无法获得股票价格。从这个意义上说，我因此希望能够获得最新的价格。

正在做

SELECT sec.ID, sec.SECPOS, t.PRICE  
FROM #SECURITY_POST as SEC INNER JOIN
     #SECURITY_TEMP_PRICE_HISTORY as t
     ON sec.ID = t.ID
WHERE t.PriceDate = '20150117'
GROUP BY sec.ID, secPos, t.price

由于没有数据而返回0行

SELECT sec.ID, sec.SECPOS, t.PRICE  
FROM #SECURITY_POST as SEC INNER JOIN
     #SECURITY_TEMP_PRICE_HISTORY as t
     ON sec.ID = t.ID
WHERE t.PriceDate <= '20150117'
GROUP BY sec.ID, sec.secPos, t.price
HAVING sec.secpos <> 0

返回重复的行。

我尝试了很多不同的方法，但我无法得到我想要的输出。此外，我还希望能够获取一列最接近某个日期的价格(称为 START_DATE)和一列最接近第二个日期的价格(称为 END_DATE) code>) 和一列将成为位置 Price@END_DATE - Price@START_DATE。价格始终取自同一个#SECURITY_TEMP_PRICE_HISTORY。

但是，我的 SQL 知识很糟糕，而且我无法找到一种有效的方法来做到这一点。任何帮助，将不胜感激。另请注意，#SECURITY_PRICE_HISTORY 表可能包含比 #SECURITY_POST 表更多的证券。

Answer 1

这应该可以解决问题。 OUTER APPLY 是一个连接运算符(如CROSS APPLY)，允许派生表具有外部引用。

SELECT
   s.ID,
   s.SecPos,
   t.Price
   t.PriceDate
FROM
   #SECURITY_POST s
   OUTER APPLY (
      SELECT TOP 1 *
      FROM #SECURITY_TEMP_PRICE_HISTORY t
      WHERE
         s.ID = t.ID
         AND t.PriceDate <= '20150117'
      ORDER BY t.PriceDate DESC
   ) t
;

您可能还需要考虑标记非常旧的证券价格，或将最近证券的查找限制在特定时期(一周或一个月等)。

确保您的价格历史表具有 (ID, PriceDate) 索引，以便子查询查找可以使用范围查找，并且性能会良好。确保您在安全表而不是历史表上进行任何日期数学运算，否则您将强制价格查找子查询不可控制，这对性能不利，因为无法进行范围查找。

如果没有找到证券的价格，OUTER APPLY 仍将允许该行存在，因此价格将显示为 NULL。如果您希望在找不到合适的价格时不显示证券，请使用CROSS APPLY。

对于问题的第二部分，您可以使用两个 OUTER APPLY 操作来完成此操作，如下所示:

DECLARE
   @StartDate date = '20150101',
   @EndDate date = '20150118';

SELECT
   S.ID,
   S.SecPos,
   StartDate = B.PriceDate,
   StartPrice = B.Price,
   EndDate = E.PriceDate,
   EndPrice = E.Price,
   Position = B.Price - E.Price
FROM
   #SECURITY_POST S
   OUTER APPLY (
      SELECT TOP 1 *
      FROM #SECURITY_TEMP_PRICE_HISTORY B
      WHERE
         S.ID = B.ID
         AND B.PriceDate <= @StartDate
      ORDER BY B.PriceDate DESC
   ) B
   OUTER APPLY (
      SELECT TOP 1 *
      FROM #SECURITY_TEMP_PRICE_HISTORY E
      WHERE
         S.ID = E.ID
         AND E.PriceDate <= @EndDate
      ORDER BY E.PriceDate DESC
   ) E
;

根据您的数据，将产生以下结果集:

ID    SecPos  StartDate   StartPrice  EndDate     EndPrice  Position
----  ------  ----------  ----------  ----------  --------  --------
APPL  100     2015-01-01  10.4        2015-01-16  15.4       -5
VOD   350     2015-01-01  30.5        2015-01-16  16.5       14
VOW3  400     2015-01-01  45.5        2015-01-16  48.8       -3.3

最后，虽然并非所有人都同意，但我鼓励您使用 SecurityID 中的表名称来命名 ID 列，而不是 ID 。根据我的经验，使用 ID 只会导致问题。

注意:有一种方法可以使用 Row_Number() 窗口函数来解决此问题。如果与股票数量相比，您的价格点相对较少，并且您正在查找历史表中大多数股票的价格，那么使用该方法可能会获得更好的性能。但是，如果每只股票有大量的价格点，或者您只过滤到几只股票，那么使用我向您展示的方法可能会获得更好的效果。