SQL 将时间以整天为单位分别计算

Question

SQL 怎么按照时间以整天为单位分别计算？
有表 test
字段： 时间，数值，  均为varchar型 。数据为：
时间   数值
2011-1-1 1:00:00   5
2011-1-1 5:00:00   7
2011-1-1 9:00:00   6.5
2011-1-2 1:00:00   8
2011-1-2 5:00:00   7
2011-1-2 6:00:00   8.2
2011-1-2 9:00:00   5.2
2011-1-3 1:00:00   5.5
2011-1-3 5:00:00   4.8
2011-1-3 9:00:00   6.5需要SQL来计算，计算的方法是：每天的数据求平均值*24，然后将各自的值相加（每天的数据条数不定）。即，计算方法为
（5+7+6.5）/3*24 + （8+7+8.2+5.2）/4*24 + （5.5+4.8+6.5）/3*24执行后的 452.8就是可以按照1月1号的计算完，再计算1月2号的，之后再相加。
这种SQL应如何实现呢？ 非常感谢！

Answer 1

--> 测试数据: [test]
if object_id('[test]') is not null drop table [test]
go 
create table [test] (时间 datetime,数值 numeric(2,1))
insert into [test]
select '2011-1-1 1:00:00',5 union all
select '2011-1-1 5:00:00',7 union all
select '2011-1-1 9:00:00',6.5 union all
select '2011-1-2 1:00:00',8 union all
select '2011-1-2 5:00:00',7 union all
select '2011-1-2 6:00:00',8.2 union all
select '2011-1-2 9:00:00',5.2 union all
select '2011-1-3 1:00:00',5.5 union all
select '2011-1-3 5:00:00',4.8 union all
select '2011-1-3 9:00:00',6.5select cast(SUM(n) as decimal(18,1)) as n
from 
(
select convert(varchar(10),时间,120) as sj,avg(数值) *24 as n
from [test]
group by  convert(varchar(10),时间,120)
)t
n
---------------------------------------
452.8(1 row(s) affected)

Answer 2

with cte as
(
    select count(*) cnt,sum(num) num
    from tb
    group by convert(varchar(8),[date],112)
)select sum(num*1./cnt)*24 as total
from cte

Answer 3

with cte as
(
    select count(*) cnt,avg(num) num         --打错，是AVG
    from tb
    group by convert(varchar(8),[date],112)
)select sum(num*1./cnt)*24 as total
from cte

Answer 4

create table [test] (时间 datetime,数值 numeric(2,1))
insert into [test]
select '2011-1-1 1:00:00',5 union all
select '2011-1-1 5:00:00',7 union all
select '2011-1-1 9:00:00',6.5 union all
select '2011-1-2 1:00:00',8 union all
select '2011-1-2 5:00:00',7 union all
select '2011-1-2 6:00:00',8.2 union all
select '2011-1-2 9:00:00',5.2 union all
select '2011-1-3 1:00:00',5.5 union all
select '2011-1-3 5:00:00',4.8 union all
select '2011-1-3 9:00:00',6.5with a as(
select convert(varchar(10),时间,120) as cdate,
       sum(数值)/count(时间)*24 as value
from test 
group by convert(varchar(10),时间,120) )
select t1.cdate, SUM(t2.value)
from a t1 join a t2 on t1.cdate>=t2.cdate
group by t1.cdate
order by t1.cdate;

Answer 5

create table [test] (时间 datetime,数值 numeric(2,1))
insert into [test]
select '2011-1-1 1:00:00',5 union all
select '2011-1-1 5:00:00',7 union all
select '2011-1-1 9:00:00',6.5 union all
select '2011-1-2 1:00:00',8 union all
select '2011-1-2 5:00:00',7 union all
select '2011-1-2 6:00:00',8.2 union all
select '2011-1-2 9:00:00',5.2 union all
select '2011-1-3 1:00:00',5.5 union all
select '2011-1-3 5:00:00',4.8 union all
select '2011-1-3 9:00:00',6.5with a as(
select convert(varchar(10),时间,120) as cdate,
       sum(数值)/count(时间)*24 as value
from test 
group by convert(varchar(10),时间,120) )
select t1.cdate, SUM(t2.value) as value
from a t1 join a t2 on t1.cdate>=t2.cdate
group by t1.cdate
order by t1.cdate;----------------------------------
cdate value
2011-01-01 147.999984
2011-01-02 318.399984
2011-01-03 452.799984

Answer 6

create table [test] (时间 datetime,数值 numeric(2,1))
insert into [test]
select '2011-1-1 1:00:00',5 union all
select '2011-1-1 5:00:00',7 union all
select '2011-1-1 9:00:00',6.5 union all
select '2011-1-2 1:00:00',8 union all
select '2011-1-2 5:00:00',7 union all
select '2011-1-2 6:00:00',8.2 union all
select '2011-1-2 9:00:00',5.2 union all
select '2011-1-3 1:00:00',5.5 union all
select '2011-1-3 5:00:00',4.8 union all
select '2011-1-3 9:00:00',6.5
select * From testselect cast(SUM(val) as decimal(18,1)) as val from 
(
select Convert(varchar(10),时间,112) as dtime, (avg(数值)*24) as val
from test group by Convert(varchar(10),时间,112)
) A ==================
val
---------------------------------------
452.8(1 行受影响)