如何填补空白？ [英] How to fill the gaps?

问题描述

假设我有两个记录，包括日期和计数：

   - 日期 -  --Count- -  
 2011-09-20 00:00:00 5 
 2011-09-16 00:00:00 8 
  

如何选择这个填充时间间隔，总是取最后一个记录？

所以输出将是： / p>

   - 日期 -  --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循环来完成，但我希望这可以更容易。

解决方案

您有2个问题要解决。第一个问题是如何填补空白。第二个问题是为这些丢失的记录填充计数字段。

问题1：这可以通过使用日期查找表或通过创建递归通用表表达式。我建议为此创建一个日期查找表，如果这是一个选项。如果你不能创建这样的表，那么你将需要这样的东西。

  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：这里是一个相关的子查询会派上用场（尽可能多地远离他们）从原始表格中获取最后一个cnt：

  SELECT r.dtfield，
（SELECT TOP 1 cnt 
 FROM yourtable 
 WHERE dt< = r.dtfield 
 ORDER BY dt DESC） cnt 
 FROM RecursiveCTE r 
  

• SQL Fiddle Demo

Assuming I have two records, both with a date and a count:

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

How would you select this for filling the time gaps, always taking the last previous record?

So the output would be:

--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

I couldn't figure out a neat solution for this, yet. I guess this could be done with DATEDIFF, and a for-loop, but I hope this can be done easier.

解决方案

You have 2 issues you're trying to resolve. The first issue is how to fill the gaps. The second issue is populating the Count field for those missing records.

Issue 1: This can be resolved by either using a Dates Lookup table or by creating a recursive common table expression. I would recommend creating a Dates Lookup table for this if that is an option. If you cannot create such a table, then you're going to need something like this.

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
  )

That should create you a list of dates starting with the MIN date in your table and ending in the MAX.

Issue 2: Here is where a correlated subquery would come in handy (as much as I generally stay away from them) to get the last cnt from your original table:

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

• SQL Fiddle Demo

这篇关于如何填补空白？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！