高手进来看看，遇到面试难题

Question

CREATE TABLE TimeLog
(
LogTime DATETIME PRIMARY KEY NOT NULL,
RealValue real not null
)-- 算出移动平均值,即要算出 13:00-14:00, 13:15-:14:15, 13:30-14:30..的平均值
INSERT INTO TimeLog VALUES('2010/01/01 13:00', 2.42);
INSERT INTO TimeLog VALUES('2010/01/01 13:15', 7.1);
INSERT INTO TimeLog VALUES('2010/01/01 13:30', 6.1);
INSERT INTO TimeLog VALUES('2010/01/01 13:45', 7.4);
INSERT INTO TimeLog VALUES('2010/01/01 14:00', 7.1);
INSERT INTO TimeLog VALUES('2010/01/01 14:15', 1.42);
INSERT INTO TimeLog VALUES('2010/01/01 14:30', 7.2);
INSERT INTO TimeLog VALUES('2010/01/01 14:45', 9.1);
INSERT INTO TimeLog VALUES('2010/01/01 15:00', 1.24);--得到的结果
/*
时间段 平均值
13:00-14:00     **
13:15-:14:15 **
13:30-14:30     **
.. ...
*/
请会的高手写下，想了很久都没有写出来，

Answer 1

CREATE TABLE TimeLog
(
LogTime DATETIME PRIMARY KEY NOT NULL,
RealValue real not null
)-- 算出移动平均值,即要算出 13:00-14:00, 13:15-:14:15, 13:30-14:30..的平均值
INSERT INTO TimeLog VALUES('2010/01/01 13:00', 2.42);
INSERT INTO TimeLog VALUES('2010/01/01 13:15', 7.1);
INSERT INTO TimeLog VALUES('2010/01/01 13:30', 6.1);
INSERT INTO TimeLog VALUES('2010/01/01 13:45', 7.4);
INSERT INTO TimeLog VALUES('2010/01/01 14:00', 7.1);
INSERT INTO TimeLog VALUES('2010/01/01 14:15', 1.42);
INSERT INTO TimeLog VALUES('2010/01/01 14:30', 7.2);
INSERT INTO TimeLog VALUES('2010/01/01 14:45', 9.1);
INSERT INTO TimeLog VALUES('2010/01/01 15:00', 1.24);
select
begintime,
endtime,
(select avg(isnull(RealValue,0)) from TimeLog where convert(varchar(5),LogTime,108) between a.begintime and a.endtime ) as AvgValue
from
(
  select 
    convert(varchar(5),dateadd(n,number*15,0),108) as begintime,
    convert(varchar(5),dateadd(n,number*15+60,0),108) as endtime
   from master..spt_values 
   where type = 'P' and number <= 23*60/15
) a/*begintime endtime AvgValue
--------- ------- ----------------------
00:00     01:00   NULL
00:15     01:15   NULL
00:30     01:30   NULL
00:45     01:45   NULL
01:00     02:00   NULL
01:15     02:15   NULL
01:30     02:30   NULL
01:45     02:45   NULL
02:00     03:00   NULL
02:15     03:15   NULL
02:30     03:30   NULL
02:45     03:45   NULL
03:00     04:00   NULL
03:15     04:15   NULL
03:30     04:30   NULL
03:45     04:45   NULL
04:00     05:00   NULL
04:15     05:15   NULL
04:30     05:30   NULL
04:45     05:45   NULL
05:00     06:00   NULL
05:15     06:15   NULL
05:30     06:30   NULL
05:45     06:45   NULL
06:00     07:00   NULL
06:15     07:15   NULL
06:30     07:30   NULL
06:45     07:45   NULL
07:00     08:00   NULL
07:15     08:15   NULL
07:30     08:30   NULL
07:45     08:45   NULL
08:00     09:00   NULL
08:15     09:15   NULL
08:30     09:30   NULL
08:45     09:45   NULL
09:00     10:00   NULL
09:15     10:15   NULL
09:30     10:30   NULL
09:45     10:45   NULL
10:00     11:00   NULL
10:15     11:15   NULL
10:30     11:30   NULL
10:45     11:45   NULL
11:00     12:00   NULL
11:15     12:15   NULL
11:30     12:30   NULL
11:45     12:45   NULL
12:00     13:00   2.42000007629395
12:15     13:15   4.75999999046326
12:30     13:30   5.20666662851969
12:45     13:45   5.75499999523163
13:00     14:00   6.02399997711182
13:15     14:15   5.82399995326996
13:30     14:30   5.84399993419647
13:45     14:45   6.4440000295639
14:00     15:00   5.21200001239777
14:15     15:15   4.74000003933907
14:30     15:30   5.84666673342387
14:45     15:45   5.17000019550323
15:00     16:00   1.24000000953674
15:15     16:15   NULL
15:30     16:30   NULL
15:45     16:45   NULL
16:00     17:00   NULL
16:15     17:15   NULL
16:30     17:30   NULL
16:45     17:45   NULL
17:00     18:00   NULL
17:15     18:15   NULL
17:30     18:30   NULL
17:45     18:45   NULL
18:00     19:00   NULL
18:15     19:15   NULL
18:30     19:30   NULL
18:45     19:45   NULL
19:00     20:00   NULL
19:15     20:15   NULL
19:30     20:30   NULL
19:45     20:45   NULL
20:00     21:00   NULL
20:15     21:15   NULL
20:30     21:30   NULL
20:45     21:45   NULL
21:00     22:00   NULL
21:15     22:15   NULL
21:30     22:30   NULL
21:45     22:45   NULL
22:00     23:00   NULL
22:15     23:15   NULL
22:30     23:30   NULL
22:45     23:45   NULL
23:00     00:00   NULL(93 row(s) affected)*/

Answer 2

select convert(varchar(10),LogTime,120) 日期,
convert (varchar(5),LogTime,14)+'-'+convert (varchar(5),dateadd(hour,1,LogTime),14) 时间段,
(select avg(RealValue) from TimeLog 
where convert(varchar(10),LogTime,120) = convert(varchar(10),t.LogTime,120)
and convert (varchar(5),LogTime,14) 
between convert (varchar(5),t.LogTime,14)
and convert (varchar(5),dateadd(hour,1,LogTime),14)) 平均值
from TimeLog t
--结果，也许可以
/*日期         时间段         平均值                                                   
---------- ----------- ----------------------------------------------------- 
2010-01-01 13:00-14:00 5.4533333381017046
2010-01-01 13:15-14:15 5.8324999958276749
2010-01-01 13:30-14:30 5.6514285802841187
2010-01-01 13:45-14:45 5.5766666928927107
2010-01-01 14:00-15:00 5.2120000123977661
2010-01-01 14:15-15:15 4.7400000393390656
2010-01-01 14:30-15:30 5.8466667334238691
2010-01-01 14:45-15:45 5.1700001955032349
2010-01-01 15:00-16:00 1.2400000095367432（所影响的行数为 9 行）
*/

Answer 3

一楼的答案正确，但是不太好懂，能否用APPLY函数来实现阿？二楼的结果有问题，我开始也是跟你这样想的，但发现结果不对，也不知道哪里出错了

Answer 4

错误的原因是少了一个t.
select convert(varchar(10),LogTime,120) 日期,
convert (varchar(5),LogTime,14)+'-'+convert (varchar(5),dateadd(hour,1,LogTime),14) 时间段,
(select avg(RealValue) from TimeLog 
where convert(varchar(10),LogTime,120) = convert(varchar(10),t.LogTime,120)
    and convert (varchar(5),LogTime,14) 
    between convert (varchar(5),t.LogTime,14)
    and convert (varchar(5),dateadd(hour,1,t.LogTime),14)) 平均值
from TimeLog t
/*
日期         时间段         平均值                                                   
---------- ----------- ----------------------------------------------------- 
2010-01-01 13:00-14:00 6.0239999771118162
2010-01-01 13:15-14:15 5.8239999532699587
2010-01-01 13:30-14:30 5.8439999341964723
2010-01-01 13:45-14:45 6.4440000295639042
2010-01-01 14:00-15:00 5.2120000123977661
2010-01-01 14:15-15:15 4.7400000393390656
2010-01-01 14:30-15:30 5.8466667334238691
2010-01-01 14:45-15:45 5.1700001955032349
2010-01-01 15:00-16:00 1.2400000095367432（所影响的行数为 9 行）*/

Answer 5

SELECT
时间段 = CONVERT(char(5),MIN(tl2.LogTime),108)+'-'+CONVERT(char(5),MAX(tl2.LogTime),108),
平均值 = AVG(tl2.RealValue)
FROM #TimeLog tl
INNER JOIN #TimeLog tl2
ON tl2.LogTime BETWEEN tl.LogTime AND DATEADD(hour,1,tl.LogTime)
GROUP BY tl.LogTime
ORDER BY 时间段

Answer 6

--1)表和数据CREATE TABLE TimeLog
(
LogTime DATETIME PRIMARY KEY NOT NULL,
RealValue real not null
)-- 算出移动平均值,即要算出 13:00-14:00, 13:15-:14:15, 13:30-14:30..的平均值
INSERT INTO TimeLog VALUES('2010/01/01 13:00', 2.42);
INSERT INTO TimeLog VALUES('2010/01/01 13:15', 7.1);
INSERT INTO TimeLog VALUES('2010/01/01 13:30', 6.1);
INSERT INTO TimeLog VALUES('2010/01/01 13:45', 7.4);
INSERT INTO TimeLog VALUES('2010/01/01 14:00', 7.1);
INSERT INTO TimeLog VALUES('2010/01/01 14:15', 1.42);
INSERT INTO TimeLog VALUES('2010/01/01 14:30', 7.2);
INSERT INTO TimeLog VALUES('2010/01/01 14:45', 9.1);
INSERT INTO TimeLog VALUES('2010/01/01 15:00', 1.24);--2)查询SELECT Interval = CONVERT(varchar(10),F.LogTime1,108) + ' - ' + CONVERT(varchar(10),F.LogTime2,108),
--F.LogTime1,F.LogTime2,
AVG = (RealValue1 + RealValue2 + SUM_RealValue)/(2 + Num) FROM
(
SELECT  LogTime1 = C.LogTime,RealValue1 = C.RealValue,LogTime2 = D.LogTime,RealValue2 = D.RealValue
FROM TimeLog C
INNER JOIN
TimeLog D
ON C.LogTime < D.LogTime
WHERE DATEDIFF(ss,C.LogTime,D.LogTime) = 3600
)F
INNER JOIN
(
SELECT SUM_RealValue = SUM(RealValue),Num = COUNT(LogTime1),LogTime2 FROM TimeLog A
INNER JOIN #test B
ON A.LogTime > B.LogTime1 AND A.LogTime < B.LogTime2
GROUP BY B.LogTime2
)E
ON E.LogTime2 = F.LogTime2--3)结果Interval                AVG
----------------------- ----------------------
13:00:00 - 14:00:00     6.02400007247925
13:15:00 - 14:15:00     5.82399988174438
13:30:00 - 14:30:00     5.84399983882904
13:45:00 - 14:45:00     6.44399993419647
14:00:00 - 15:00:00     5.21200006008148(5 row(s) affected)

Answer 7

--1)查询SELECT Interval = CONVERT(varchar(10),DATEADD(hh,-1,E.LogTime2),108) +' - '+ CONVERT(varchar(10),E.LogTime2,108)
,AVG = (SUM(RealValue)+ E.RealValue2)/(COUNT(RealValue) + 1)--,E.LogTime2 
FROM TimeLog C 
INNER JOIN
(
SELECT  LogTime1 = C.LogTime,RealValue1 = C.RealValue,LogTime2 = D.LogTime,RealValue2 = D.RealValue
FROM TimeLog C
INNER JOIN
TimeLog D
ON C.LogTime < D.LogTime
WHERE DATEDIFF(ss,C.LogTime,D.LogTime) = 3600
)E 
ON LogTime >=E.LogTime1 AND LogTime < E.LogTime2
GROUP BY E.RealValue2,E.LogTime2--2)结果Interval                AVG
----------------------- ----------------------
13:00:00 - 14:00:00     6.02399997711182
13:15:00 - 14:15:00     5.82399995326996
13:30:00 - 14:30:00     5.84399993419647
13:45:00 - 14:45:00     6.4440000295639
14:00:00 - 15:00:00     5.21200001239777(5 row(s) affected)

Answer 8

搞不明白 为什么 同一个表TimeLog 用了一个标识t后 就可变成两张表 操作了？