How to fill the gaps?

问题:

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.

回答1:

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

回答2:

My solution goes like this.

Step 1: Have a Date table which has all the dates. - you can use many methods ex: Get a list of dates between two dates

Step 2: Do a Left outer from the date table to your result set. - which would result you with the below resultset: Call this table as "TEST_DATE_COUnt"

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

Step 3: Do a Recursive query like below:

SELECT t1.date_x, t1.count_x,
   (case when count_x=0 then (SELECT max(COUNT_X)
    FROM TEST_DATE_COUNT r 
    WHERE r.DATE_X <= t1.DATE_X) 
    else COUNT_X
    end) 
    cnt
FROM TEST_DATE_COUNT t1

Please let me know if this works. I tested and it worked.