你的位置:首页 > 数据库

[数据库]补充缺失日期及对应数据


数据库环境:SQL SERVER 2008R2

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

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

  现有数据如图1,

    图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')

View Code

  实现

/*取得最小、最大日期*/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

View Code

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

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

View Code

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

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