Geek Answers Handbook

How can I fill in missing rows in sql with static data?

I collected one-minute data on stock prices per thousand securities for several weeks and saved them in a table called "intraday". This table includes one-minute data from 8:01:00 to 16:30:00 of each trading day.

Some securities do not trade every minute of every day, and when they do not trade, not a single line is created (missing ticks). I was hoping to insert lines for all missing ticks that would bear the stock price compared to the previous stock price with a trade volume of “0” for these ticks.

For example, currently for one security, I have the following:

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

(apologies for the annoying 1899-12-30 dates for each tick - this makes it look a little dirty, but not harm, so why do they stay there for now)

Ideally, I would like to keep this:

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

Thus, for each individual tick and date value, the value will be a range of values ​​for each minute between 08:01:00 and 16:30:00. Some of them will be as they are currently stored, while others will have a volume indicator of 0 and a close value that copies the previous close value.

I am absolutely shocked and will be grateful for any help you could offer on this!

Sincerely.

+4
source share
2 answers

CTE , . 12 , , . , , . , .

: 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;
+2

, . , , . , , . . , .

CREATE TABLE #Stocks (  ticker VARCHAR(5),
                    date DATE,
                    tick datetime,
                    cls money,
                    volume money)

INSERT INTO #Stocks (ticker, date, tick, cls, volume)
VALUES
('ASC','2015-12-31','1899-12-30 12:30:00',3453,2743),
('ASC','2015-12-31','1899-12-30 12:29:00',3449,3490),
('ASC','2015-12-31','1899-12-30 12:28:00',3436,930),
('ASC','2015-12-31','1899-12-30 12:27:00',3435,255),
('ASC','2015-12-31','1899-12-30 12:26:00',3434,4),
('ASC','2015-12-31','1899-12-30 12:23:00',3444.59,54)



DECLARE @TotalRows1 INT
SELECT @TotalRows1 = COUNT(*) FROM #Stocks
DECLARE @Row INT = 1
WHILE @Row < @TotalRows1
BEGIN
DECLARE @TotalRows INT
SELECT @TotalRows = COUNT(*) FROM #Stocks
 IF (SELECT DATEADD(MI,1,tick) FROM (SELECT ROW_NUMBER() OVER (ORDER BY tick) RowNumber, * FROM #Stocks) AS T1 WHERE T1.RowNumber = @Row)
<>
(SELECT T2.tick FROM (SELECT ROW_NUMBER() OVER (ORDER BY tick) RowNumber, * FROM #Stocks) AS T2 WHERE T2.RowNumber = @Row + 1)
BEGIN
INSERT INTO #Stocks
SELECT T.ticker, T.Date, DATEADD(MI,1,T.tick) tick, T.cls, 0.00
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY tick) RowNumber, * FROM #Stocks) AS T
    WHERE T.RowNumber = @Row
SET @Row = @Row +1
END
ELSE
SET @Row = @Row +1
END

SELECT *
FROM #Stocks
ORDER BY tick DESC
+1

More articles:


All Articles