某一时间段的取平均值

一、某一 时间段的取平均值，一条select可以吗

=====================================================================

1、某一时间段内，每阁Ｎ段时间取一个平均值
例如：时间段2009-01-01~2010-01-01 ,n=2天，
则算出在 2009-01-01~2010-01-01中每两天的一个平均值
一条select语句可以吗？

IF NOT OBJECT_ID ( ' TB ' ) IS NULL   DROP TABLE TB
GO
CREATE TABLE TB( [ ADATE ] DATETIME , [ NUM ] INT )
INSERT TB
SELECT ' 2009-01-01 ' , 10 UNION ALL
SELECT ' 2009-01-02 ' , 70 UNION ALL
SELECT ' 2009-01-03 ' , 40 UNION ALL
SELECT ' 2009-01-04 ' , 30 UNION ALL
SELECT ' 2009-01-05 ' , 20 UNION ALL
SELECT ' 2009-01-06 ' , 80 UNION ALL
SELECT ' 2009-01-07 ' , 70 UNION ALL
SELECT ' 2009-01-08 ' , 60 UNION ALL
SELECT ' 2009-01-09 ' , 50 UNION ALL
SELECT ' 2009-01-10 ' , 40 UNION ALL
SELECT ' 2009-01-11 ' , 20 UNION ALL
SELECT ' 2009-01-12 ' , 30
GO
SELECT BDATE = CONVERT ( VARCHAR ( 10 ), MIN (ADATE), 120 ) + ' ~ ' + CONVERT ( VARCHAR ( 10 ), MAX (ADATE), 120 ),
     AVGNUM = AVG (NUM) FROM TB
GROUP BY DATEDIFF ( DAY , ' 2009-01-01 ' ,ADATE) / 2


/*
BDATE                 AVGNUM     
--------------------- -----------
2009-01-01~2009-01-02 40
2009-01-03~2009-01-04 35
2009-01-05~2009-01-06 50
2009-01-07~2009-01-08 65
2009-01-09~2009-01-10 45
2009-01-11~2009-01-12 25

（所影响的 行数为 6 行）
*/

 

=====================================================================

 

2、如果要统计同样上面的内容，但是我希望数据库里面不存在的时间值也参与计算
例如
2009-01-01,
2009-01-04,
2009-01-05,
2009-01-06,
我 们看到上面是没有２号和３号的
但是我希望能够将空值返回，这样
可以实现吗？
例如按每天计算的时候
它会算出每天的平均值
不 存在的月份就以空返回

IF NOT OBJECT_ID ( ' TB ' ) IS NULL   DROP TABLE TB
GO
CREATE TABLE TB( [ ADATE ] DATETIME , [ NUM ] INT )
INSERT TB
SELECT ' 2009-01-01 ' , 10 UNION ALL
SELECT ' 2009-01-04 ' , 30 UNION ALL
SELECT ' 2009-01-05 ' , 20 UNION ALL
SELECT ' 2009-01-06 ' , 80 UNION ALL
SELECT ' 2009-01-07 ' , 70 UNION ALL
SELECT ' 2009-01-08 ' , 60 UNION ALL
SELECT ' 2009-01-09 ' , 50 UNION ALL
SELECT ' 2009-01-10 ' , 40 UNION ALL
SELECT ' 2009-01-11 ' , 20 UNION ALL
SELECT ' 2009-01-12 ' , 30
GO

select   ADATE = CONVERT ( VARCHAR ( 10 ), MIN (ADATE), 120 ) + ' ~ ' + CONVERT ( VARCHAR ( 10 ), MAX (ADATE), 120 ), NUM = avg ( isnull (NUM, 0 ))
from spt_values A left join TB B 
on ADATE = dateadd (dd, number ,( select min (ADATE) from TB))
where type = ' p ' and dateadd (dd, number ,( select min (ADATE) from TB)) <= ( select max (ADATE) from TB)
group by number / 2

/*
ADATE                 NUM        
--------------------- -----------
2009-01-01~2009-01-01 5
2009-01-04~2009-01-04 15
2009-01-05~2009-01-06 50
2009-01-07~2009-01-08 65
2009-01-09~2009-01-10 45
2009-01-11~2009-01-12 25

（所影响的行数为 6 行）

警告: 聚合或其它 SET 操作消除了空值。
*/