sql - 如何使用静态数据填充 sql 中缺失的行？

Question

我收集了数周内一些证券的一分钟“滴答声”股价数据，并将这些数据存储在一个名为“日内”的表中。该表包含每个交易日 08:01:00 至 16:30:00 之间的一分钟报价数据。

某些证券并非每天每分钟都进行交易，并且当它们不进行交易时，不会创建任何行(缺少刻度)。我希望为所有缺失的价格变动插入行，这将影响之前股价的股价，这些价格变动的交易量为“0”。

例如，目前对于一种证券，我存储了以下内容:

ticker     date           tick                     cls        volume

ASC        20151231       1899-12-30 12:30:00      3453       2743
ASC        20151231       1899-12-30 12:29:00      3449       3490
ASC        20151231       1899-12-30 12:28:00      3436       930
ASC        20151231       1899-12-30 12:27:00      3435       255
ASC        20151231       1899-12-30 12:26:00      3434       4
ASC        20151231       1899-12-30 12:23:00      3444.59    54

(对每个刻度的烦人的 1899-12-30 日期表示歉意 - 这些使它看起来有点困惑，但没有坏处，因此它们目前仍保留在那里)

我理想地希望存储的是:

ticker     date           tick                     cls        volume

ASC        20151231       1899-12-30 12:30:00      3453       2743
ASC        20151231       1899-12-30 12:29:00      3449       3490
ASC        20151231       1899-12-30 12:28:00      3436       930
ASC        20151231       1899-12-30 12:27:00      3435       255
ASC        20151231       1899-12-30 12:26:00      3434       4
ASC        20151231       1899-12-30 12:25:00      3444.59    0          < new row
ASC        20151231       1899-12-30 12:24:00      3444.59    0          < new row
ASC        20151231       1899-12-30 12:23:00      3444.59    54

因此，对于每个不同的代码和日期值，每分钟在 08:01:00 到 16:30:00 之间都会有一个值范围。有些是当前存储的状态，其他的成交量为 0，收盘价复制前一个收盘价。

我完全被难住了，非常感谢您能就此提供的任何帮助!

亲切的问候。

Answer 1

使用递归 CTE 为您需要的范围创建时间表。我只用了 12 分钟来使用您样本中的数据，但您可以延长时间。在不使用游标的情况下获取最后一个非空值有点棘手，但这是可能的。 Here如果您有兴趣，可以对此进行更全面的解释。

以下代码演示:http://rextester.com/QDQR73738

设置:

create table test_data(ticker varchar(5), date integer, tick datetime, cls decimal(10,2), volume integer);
create table test_data2(ticker varchar(5), date integer, tick datetime, cls decimal(10,2), volume integer);

insert into test_data
    select 'ASC', 20151231, '1899-12-30 12:30:00', 3453,    2743 union all
    select 'ASC', 20151231, '1899-12-30 12:29:00', 3449,    3490 union all
    select 'ASC', 20151231, '1899-12-30 12:28:00', 3436,    930  union all
    select 'ASC', 20151231, '1899-12-30 12:27:00', 3435,    255  union all
    select 'ASC', 20151231, '1899-12-30 12:26:00', 3434,    4    union all
    select 'ASC', 20151231, '1899-12-30 12:23:00', 3444.59, 54   union all

    select 'BSC', 20151231, '1899-12-30 12:23:00', 3444.59, 54   union all
    select 'BSC', 20151231, '1899-12-30 12:28:00', 3436,    930 
;

查询:

Declare @tickers Table (ticker varchar(5));
Insert into @tickers select distinct ticker from test_data;

Declare @ticker varchar(5);
While exists (Select * From @tickers)
  BEGIN
    select @ticker = min(ticker) from @tickers;

    with cte(tm) 
    as( Select cast('1899-12-30 12:23:00' as datetime) as tm
        union all
    Select dateadd(minute, 1, tm)
        from cte
        where tm < cast('1899-12-30 12:31:00' as datetime)
    )    

    insert into test_data2 
    select 
      max(ticker) over (partition by grp order by tick rows unbounded preceding) ticker,
      max(date) over (partition by grp order by tick rows unbounded preceding) date,
      tick,
      max(cls) over (partition by grp order by tick rows unbounded preceding) cls,
      volume
    from (
          select
              ticker,
              date,
              tick,
              cls,
              volume,
              id,
              max(id1) OVER(ORDER BY tick ROWS UNBOUNDED PRECEDING) AS grp
          from (
                select 
                  td.ticker,
                  td.date,
                  coalesce(td.tick, cte.tm) tick,
                  td.cls,
                  coalesce(td.volume, 0) volume,
                  row_number() over (order by tick) id
                from test_data td
                right outer join cte
                on td.tick = cte.tm
                and td.ticker = @ticker
         ) cte2
         CROSS APPLY ( VALUES( CASE WHEN ticker IS NOT NULL THEN id END) )
            AS A(id1)
     ) cte3;

      Delete from @tickers where ticker = @ticker;
    End

select * from test_data2
order by ticker, tick;