金融时报系列- SQL Server语言 - 从多个列中提取有序数据，当一些列为NULL

Question

想象一下MS SQL Server表中名为"tblPrices"的金融交易和报价数据。

SeqNo       Ticker     TradeType    JulianTime     Price    BidPrice    AskPrice    
1           CLK4       Quote        735706.11      NULL     103         NULL
2           CLK4       Quote        735706.59      NULL     NULL        105
3           CLK4       Trade        735706.59      103      NULL        NULL

NULL用于节省表中的空间。在提取值为"Trade"的"TradeType"数据时，我还想提取最近的数据'BidPrice'和'AskPrice'，根据JulianTime不为NULL。

ie。上面的SeqNo 3的输出可以是:

    JulianTime  TradeType   Price   BidPrice    AskPrice
    735706.59   Trade       103     103          105

注:SeqNo是一个步长为1的恒等式。JulianTime是MATLAB发明的DateTime的数值。

我该怎么做?

Answer 1

SELECT Ticker, 
       Min(CASE 
             WHEN TradeType = 'Trade' 
                  AND Price IS NOT NULL THEN JulianTime 
           END), 
       Min(Price), 
       Min(BidPrice), 
       Max(AskPrice) 
FROM   tblPrices 
GROUP  BY Ticker 

Answer 2

WITH cte 
     AS (SELECT ticker, 
                juliantime, 
                tradetype, 
                price 
         FROM   tblprices 
         WHERE  tradetype = 'Trade') 
SELECT cte.ticker, 
       cte.juliantime, 
       cte.tradetype, 
       cte.price, 
       bid.bidprice, 
       ask.askprice 
FROM   cte 
       OUTER apply (SELECT TOP 1 t1.bidprice 
                    FROM   tblprices t1 
                    WHERE  t1.ticker = cte.ticker 
                           AND t1.bidprice IS NOT NULL 
                           AND t1.juliantime <= cte.juliantime 
                    ORDER  BY t1.juliantime DESC) bid 
       OUTER apply (SELECT TOP 1 t1.askprice 
                    FROM   tblprices t1 
                    WHERE  t1.ticker = cte.ticker 
                           AND t1.askprice IS NOT NULL 
                           AND t1.juliantime <= cte.juliantime 
                    ORDER  BY t1.juliantime DESC) ask 

Answer 3

你有一个误解- NULL值消耗更多的空间和更多的CPU比不允许NULL的等效设计。每个标记都记录了一个操作，该操作仅是BID、ASK和SALE中的一个，因此合适的表设计是

CREATE TABLE tblPrices(
     ID           int identity not null primary key
    ,Ticker       varchar(10)  not null references tblTicker(Code)
    ,TradeType    char(3)      not null -- must be one of BID, ASK, SLD
    ,JulianTime   datetime     not null
    ,Price        money        not null
)

使用这种表设计，查询变成

elect
     JulianTime
    ,Price
    ,(select top 1 Price 
      from tblPrices bid 
      where bid.JulianTime < sld.JulianTime
        and bid.Ticker     = sld.Ticker
        and bid.TradeType  = 'BID'
      order by JulianTime desc
     ) as BidPrice
    ,(select top 1 Price 
      from tblPrices bid 
      where bid.JulianTime < sld.JulianTime
        and bid.Ticker     = sld.Ticker
        and bid.TradeType  = 'ASK'
       order by JulianTime desc
    ) as AskPrice
from tblPrices  sld
where sld.TradeType = 'SLD';