补充缺失日期及对应数据

数据库环境：SQL SERVER 2008R2

　　有一个数据表，只有2个字段，一个是日期字段，另一个是数据字段，其中，

日期字段的日期是不连续的。要求：补全日期，对应的数据为上一个日期的数据除于7。

　　现有数据如图1，

   

　　实现的效果如图2（数据太多，已省略部分）

    

　　实现思路：

　　1.用数字辅助表补全缺失的日期

　　2.将当前开始补录日期到下一个补录日期之间的日期视为一组，不包括下一个补录日期

　　3.分析函数求得分组内的最大数据，并计算结果

　　

　　建表，导入测试数据

CREATE TABLE test(cdate DATE,num NUMERIC(6,2))
INSERT INTO test VALUES ('2015-10-03','21')
INSERT INTO test VALUES ('2015-10-10','49')
INSERT INTO test VALUES ('2015-10-17','147')
INSERT INTO test VALUES ('2015-10-24','63')

　　实现

/*取得最小、最大日期*/
WITH    x0
          AS ( SELECT   MIN(cdate) AS date_begin ,
                        MAX(cdate) AS date_end
               FROM     test
             ),/*生成最小、最大日期之间的所有日期*/
        x1
          AS ( SELECT   DATEADD(DAY, number, date_begin) AS cdate ,
                        number AS rn
               FROM     x0
                        CROSS APPLY master..spt_values sv
               WHERE    sv.type = 'P'
                        AND sv.number <= DATEDIFF(DAY, date_begin, date_end)
             ),/*和原表左连接，取到num*/
        x2
          AS ( SELECT   x1.cdate ,
                        t.num ,
                        rn ,
                        CASE WHEN t.num IS NOT NULL THEN 1
                             ELSE 0
                        END AS gp
               FROM     x1
                        LEFT JOIN test t ON t.cdate = x1.cdate
             ),/*生成分组依据*/
        x3
          AS ( SELECT   cdate ,
                        num ,
                        ( SELECT    SUM(gp)
                          FROM      x2 x
                          WHERE     x.rn <= x2.rn
                        ) AS gp
               FROM     x2
             )/*计算结果*/
    SELECT  cdate ,
            CASE WHEN num IS NOT NULL THEN num
                 ELSE MAX(num / 7) OVER ( PARTITION BY gp )
            END AS num
    FROM    x3

　　实现的方法不止这一种，也有网友提供了另一种解法。

WITH    tmp
          AS ( SELECT   DATEADD(d, number, '2015-10-03') d ,
                        number % 7 number
               FROM     master..spt_values
               WHERE    type = 'P'
                        AND DATEADD(d, number, '2015-10-03') <= '2015-10-24'
             )
    SELECT  d ,
            CASE WHEN tmp1.cdate IS NULL THEN t.num / 7
                 ELSE tmp1.num
            END AS num
    FROM    tmp
            LEFT JOIN test tmp1 ON tmp.d = tmp1.cdate
            OUTER APPLY ( SELECT TOP 1
                                    *
                          FROM      test tmp1
                          WHERE     tmp.d > cdate
                          ORDER BY  cdate DESC
                        ) t

　　我们还可以再升级一下需求，再计算结果的步骤，不再是除于固定值7，而是除于两个日期

之间相差的天数。感兴趣的朋友可以做下，就当练练手。