?
有一个数据表，只有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，而是除于两个日期
?
之间相差的天数。感兴趣的朋友可以做下，就当练练手。
