所有公司没有关闭价格的SQL日期 [英] SQL for Dates with no ClosePrice for all companies

问题描述

我有一个事实表，其中有公司股票代码和TradingDates的ClosePrice。我还有一个dimCalendar表，其中所有日期都存在，并且为交易假期保留一个标志。

I have a fact table where I have ClosePrice for Company Tickers and TradingDates. I also have a dimCalendar table where all dates are there and a flag is maintained for Trading Holidays.

不知何故，我需要一个新的事实表或物化视图或一些用于报表目的是需要在包括TradingHolidays在内的每一天都具有ClosePrice。

Somehow I need to have a new fact table or materialized view or something for reporting purposes where I need to have ClosePrice for every day including TradingHolidays.

交易假期的ClosePrice应该是该CompanyTicker的前一个交易日的收盘价。然后，我需要另一列 5天平均收盘价。对于平均计算，仅应包括交易日的收盘价。

ClosePrice for trading holidays should be previous trading day's close price for that CompanyTicker. Then I need to have another column for '5 day average Close Price'. For average calculation close price for trading days should only be included.

因此，我们假设这是当前状态。状态1

So, let's assume this is Current State.State1

以下是加入日历后的情况。

And below is After joining with Calendar.

我想第一步是左联接日历表。这使我得到了0天缺少的天的股票价格。

I guess 1st step is to Left Join Calendar Table. That gives me 0 Stock price for missing Days.

select a.date as tdate, a.datekey, b.ticker, coalesce(b.ClosePrice,0)
from dimdates a left join 
    factStockDividendCommodity b
    on a.DateKey = b.datekey --and b.ticker = 'BP'
where (a.Datekey between 20180101 and 20181231 )
order by a.Date

可以获取股票交易代码，然后获取前一个交易日的收盘价。

Not Sure how I can get the Ticker and then Previous Trading Day's close price.

推荐答案

关键思想是使用交叉连接生成行，然后填写值。在您的情况下，您可能要考虑到过去可能并非所有时间都存在存货，因此只希望在最短观察日期内存货。

The key idea is to generate the rows using a cross join and then fill in the values. In your case, you probably want to take into account that stocks may not exist at all points in the past, so you only want this for the minimum observed date.

到填写日期，您可以在标准SQL中使用 lag（忽略空值）：

To fill in the date,you can use lag(ignore nulls) in standard SQL:

select d.date as tdate, d.datekey, t.ticker, 
       coalesce(fsdc.ClosePrice,
                lag(fsdc.ClosePrice ignore nulls) over (partition by t.ticker order by d.date) as ClosePrice
from dimdates d join
     (select ticker, min(datekey) as min_date
      from factStockDividendCommodity fsdc
      group by ticker
     ) t
     on d.datekey >= t.min_datekey left join
     factStockDividendCommodity fsdc
     on fsdc.ticker = t.ticker and
        fsdc.datekey = d.datekey
where d.Datekey between 20180101 and 20181231
order by d.Date;

可惜，许多数据库- -即使是那些支持 lag（）的-不支持忽略空值选项。那么最好的方法取决于数据库。相关子查询是最通用的方法，但从性能角度来看可能不是最好的方法。

Alas, many databases -- even those that support lag() -- do not support the ignore nulls option. The best approach then depends on the database. A correlated subquery is the most general method, but perhaps not the best from a performance perspective.

编辑：

SQL Server不支持 IGNORE NULLS 选项。
这可能是使用外部应用最容易处理的：

SQL Server does not support the IGNORE NULLS option. This is probably most easily handled using OUTER APPLY:

select d.date as tdate, d.datekey, t.ticker, 
       fsdc.ClosePrice as ClosePrice
from dimdates d join
     (select ticker, min(datekey) as min_date
      from factStockDividendCommodity fsdc
      group by ticker
     ) t
     on d.datekey >= t.min_datekey outer apply
     (select top (1) fsdc.*
      from factStockDividendCommodity fsdc
      where fsdc.ticker = t.ticker and
            fsdc.datekey <= d.datekey
      order by fsdc.datekey desc
     ) fsdc
where d.Datekey between 20180101 and 20181231
order by d.Date;

但是，因为连续不超过3天或4天没有值，所以一系列 lag（）的效率更高：

However, because there are probably never more than 3 or 4 days without values in a row, a series of lag()s might be more efficient:

select d.date as tdate, d.datekey, t.ticker, 
       coalesce(fsdc.ClosePrice,
                lag(fsdc.ClosePrice, 1) over (partition by t.ticker order by d.date),
                lag(fsdc.ClosePrice, 2) over (partition by t.ticker order by d.date),
                lag(fsdc.ClosePrice, 3) over (partition by t.ticker order by d.date)
               ) as ClosePrice
from dimdates d join
     (select ticker, min(datekey) as min_date
      from factStockDividendCommodity fsdc
      group by ticker
     ) t
     on d.datekey >= t.min_datekey left join
     factStockDividendCommodity fsdc
     on fsdc.ticker = t.ticker and
        fsdc.datekey = d.datekey
where d.Datekey between 20180101 and 20181231
order by d.Date;

这篇关于所有公司没有关闭价格的SQL日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！