时间戳

首页 > 解决方案 > SQL Server 填补时间序列中的空白

sql - SQL Server 填补时间序列中的空白

问题描述

我在处理 SQL 请求时遇到问题。

我有两张表，一张代表日期向量，另一张代表不同证券价格的时间序列：

日期：

日期 ID 日期
1 2021-01-01
2 2021-01-02
3 2021-01-03

时间系列：

安全 ID 日期 ID 价值
1 1 0.25
1 3 0.32
2 1 0.41
2 2 0.67

时间序列可能存在间隙，例如在上表中，SecurityId=1 在 DateId=2 处没有任何行，而 SecurityId=2 在 DateId=3 处没有任何行。

我需要建立一个请求，用时间序列中的最后一个值填补空白。导致所有证券和所有日期的值如下：

安全 ID 日期 ID 价值
1 1 0.25
1 2 0.25
1 3 0.32
2 1 0.41
2 2 0.67
2 3 0.67

我试图开始将所有日期与正确的连接匹配

 SELECT  * from [TimeSerie] px RIGHT JOIN Dates dt on dt.DateId = px.Dateid

不幸的是，这不起作用，因为始终存在与 DateId 上的相等性匹配的安全性，因此我没有得到没有值的 TimeSeries 行。

我正在研究 SQL Server 标准 2019，我的目标是基于单个查询的解决方案（避免使用临时表或游标的过程）。

标签： sqlsql-serverjoingaps-in-data

解决方案

您可以使用cross join生成行，然后left join：

select d.*, s.*, ts.value
from dates d cross join
     (select distinct securityid from timeseries) s left join
     (select ts.*,
             lead(ts.dateid) over (partition by securityid order by dateid) as next_dateid
      from timeseries ts
     ) ts
     on s.securityid = ts.securityid and
        d.dateid >= ts.dateid and
        (d.dateid < ts.next_dateid or ts.next_dateid is null);

这假设dateids 是连续的，这似乎是一个合理的假设。

推荐阅读