create table sensor_fivemin_data(
sensor_code decimal(10, 2),
fivemin_time datetime,
fivemin_max float,
fivemin_min float,
fivemin_ave float
)insert sensor_fivemin_data select 1, '2007-1-14 10:15:00', 10, 10, 2
insert sensor_fivemin_data select 2, '2007-1-14 11:15:00', 10, 10, 2
insert sensor_fivemin_data select 3, '2007-1-14 11:25:00', 10, 10, 2insert sensor_fivemin_data select 4, '2007-1-15 07:15:00', 10, 10, 2
insert sensor_fivemin_data select 5, '2007-1-15 08:45:00', 10, 10, 2
insert sensor_fivemin_data select 6, '2007-1-15 09:15:00', 10, 10, 2select
sensor_code=max(sensor_code),
hour_time=convert(char(13), fivemin_time, 120),
hour_max=max(fivemin_max),
hour_min=min(fivemin_min),
hour_ave=avg(fivemin_ave)
from sensor_fivemin_data
group by convert(char(13), fivemin_time, 120)
sensor_code decimal(10, 2),
fivemin_time datetime,
fivemin_max float,
fivemin_min float,
fivemin_ave float
)insert sensor_fivemin_data select 1, '2007-1-14 10:15:00', 10, 10, 2
insert sensor_fivemin_data select 2, '2007-1-14 11:15:00', 10, 10, 2
insert sensor_fivemin_data select 3, '2007-1-14 11:25:00', 10, 10, 2insert sensor_fivemin_data select 4, '2007-1-15 07:15:00', 10, 10, 2
insert sensor_fivemin_data select 5, '2007-1-15 08:45:00', 10, 10, 2
insert sensor_fivemin_data select 6, '2007-1-15 09:15:00', 10, 10, 2select
sensor_code=max(sensor_code),
hour_time=convert(char(13), fivemin_time, 120),
hour_max=max(fivemin_max),
hour_min=min(fivemin_min),
hour_ave=avg(fivemin_ave)
from sensor_fivemin_data
group by convert(char(13), fivemin_time, 120)
我想是没有把问题说清楚
sensor_code fivemin_time fivemin_max fivemin_min fivemin_ave
1 2007-1-1 00:35:00 3.11 1.31 2.71
2 2007-1-1 00:35:00 3.12 1.32 2.72
.
.
.
9 2007-1-1 00:35:00 3.19 1.39 2.79
1 2007-1-1 00:40:00 3.31 1.21 2.41
2 2007-1-1 00:40:00 3.32 1.22 2.42
.
.
.
9 2007-1-1 00:40:00 3.39 1.29 2.49
.
.
根据这样的表得到如下的视图(为了方便说明,我把想要得到的数据如下显示)
sensor_code hour_time hour_max hour_min hour_ave
1 2007-1-1 00:00:00 3.31 1.39 2.79
2 2007-1-1 00:00:00 3.32 1.32 2.72
.
.
9 2007-1-1 00:00:00 3.39 1.39 2.72
.
.
CREATE VIEW view_sensor_hour_data AS
select sensor_code,convert(char(10),sensor_fivemin_data,120) as hour_time,
max(fivemin_max) as hour_max,min(fivemin_min) as hour_min,
avg(fivemin_ave) as hour_ave
FROM sensor_fivemin_data
GROUP BY sensor_code,convert(char(10),sensor_fivemin_data,120)
sensor_code(decimal)
fivemin_time(datetime)
fivemin_max(float)
fivemin_min(float)
fivemin_ave(float)
其中表里有大量的数据,数据如下所示:
sensor_code fivemin_time fivemin_max fivemin_min fivemin_ave
1 2007-1-1 00:35:00 3.11 1.31 2.71
2 2007-1-1 00:35:00 3.12 1.32 2.72
.
.
.
9 2007-1-1 00:35:00 3.19 1.39 2.79
1 2007-1-1 00:40:00 3.31 1.21 2.41
2 2007-1-1 00:40:00 3.32 1.22 2.42
.
.
.
9 2007-1-1 00:40:00 3.39 1.29 2.49
fivemin_time是每5分钟所有的sensor_code都有一个记录.想要得到的结果如下所示:
用视图或者存储过程实现均可:
sensor_code hour_time hour_max hour_min hour_ave
1 2007-1-1 00:00:00 3.31 1.39 2.79
2 2007-1-1 00:00:00 3.32 1.32 2.72
.
.
9 2007-1-1 00:00:00 3.39 1.39 2.72
.
其中hour_time字段都是以小时为单位的(是从fivemin_time字段提取出来的),hour_max是对应的sensor_code 在这个小时内的最大值, hour_min和hour_ave是和hour_max是一样的.不知道我现在说的意思,能明白吗?谢谢指点!
我想要得到的hour_time字段是一小时为单位的,hour_max是对应的sensor_code 在这个小时内的最大值,不是所有的最大值!
希望能给继续指点!
sensor_code int,
fivemin_time datetime,
fivemin_max float,
fivemin_min float,
fivemin_ave float
)insert sensor_fivemin_data select 1, '2007-1-1 00:35:00', 3.11, 1.31, 2.71
union all select 2, '2007-1-1 00:35:00', 3.12, 1.32, 2.72
union all select 3, '2007-1-1 00:35:00', 3.19, 1.39, 2.79 union all select 1, '2007-1-1 00:40:00', 3.31, 1.21, 2.41
union all select 2, '2007-1-1 00:40:00', 3.32, 1.22, 2.42
union all select 3, '2007-1-1 00:40:00', 3.39, 1.29, 2.49union all select 1, '2007-1-1 01:25:00', 3.11, 1.31, 2.71
union all select 2, '2007-1-1 01:25:00', 3.12, 1.32, 2.72
union all select 3, '2007-1-1 01:25:00', 3.19, 1.39, 2.79 union all select 1, '2007-1-1 01:30:00', 3.31, 1.21, 2.41
union all select 2, '2007-1-1 01:30:00', 3.32, 1.22, 2.42
union all select 3, '2007-1-1 01:30:00', 3.39, 1.29, 2.49
select sensor_code, hour_time=convert(char(13), fivemin_time, 120),
hour_max=cast(max(fivemin_max) as decimal(10,2)),
hour_min=cast(max(fivemin_min)as decimal(10, 2)),
hour_ave=cast(max(fivemin_ave) as decimal(10, 2))
from sensor_fivemin_data
group by sensor_code, convert(char(13), fivemin_time, 120)--result
sensor_code hour_time hour_max hour_min hour_ave
----------- ------------- ------------ ------------ ------------
1 2007-01-01 00 3.31 1.31 2.71
2 2007-01-01 00 3.32 1.32 2.72
3 2007-01-01 00 3.39 1.39 2.79
1 2007-01-01 01 3.31 1.31 2.71
2 2007-01-01 01 3.32 1.32 2.72
3 2007-01-01 01 3.39 1.39 2.79(6 row(s) affected)
convert(char(13), fivemin_time, 120)就把时间后面的给截断了,只显示小时了group by sensor_code, convert(char(13), fivemin_time, 120)
max(fivemin_max)我还以为是从全部里算呢?
分组之后max(fivemin_max)是怎么一个的规律呢?
是我学艺不精.理解有误!
你的是对的,
能给解释一下,分组后聚合函数怎么理解?两个组的情况是怎么样的?