如何填补空白？

Question

假设我有两条记录，都有日期和计数：

--Date--                   --Count--
2011-09-20 00:00:00        5
2011-09-16 00:00:00        8

您将如何选择此项以填补时间空白，并始终保持最近的记录？

因此输出将是：

--Date--                   --Count--
2011-09-20 00:00:00        5
2011-09-19 00:00:00        8
2011-09-18 00:00:00        8
2011-09-17 00:00:00        8
2011-09-16 00:00:00        8

我还没有找到一个整齐的解决方案。我想可以使用DATEDIFF和for循环来完成此操作，但我希望可以更轻松地完成此操作。

Answer 1

您有2个要解决的问题。第一个问题是如何填补空白。第二个问题是为那些缺少的记录填充“计数”字段。

问题1：这可以通过使用Dates Lookup table或创建一个来解决recursive common table expression。如果可以的话，我建议为此创建一个“日期查询”表。如果您不能创建这样的表，那么您将需要这样的东西。

WITH CTE AS (
  SELECT MAX(dt) maxdate, MIN(dt) mindate
  FROM yourtable
  ),
RecursiveCTE AS (
  SELECT mindate dtfield
  FROM CTE
  UNION ALL
  SELECT DATEADD(day, 1, dtfield)
  FROM RecursiveCTE R 
    JOIN CTE T
      ON R.dtfield < T.maxdate
  )

这将为您创建一个日期列表，该MIN日期列表从表中的日期开始，以结束MAX。

问题2：在这里可以correlated subquery派上用场（就像我通常在远离它们的地方一样），可以从原始表中获取最后一个cnt：

SELECT r.dtfield, 
   (SELECT TOP 1 cnt
    FROM yourtable 
    WHERE dt <= r.dtfield 
    ORDER BY dt DESC) cnt
FROM RecursiveCTE r

• SQL小提琴演示