在单个表中,假设我有一个里程和时间戳的日志。我想要得到每天和每小时的平均行驶里程。由于日期格式的原因,我不能使用固有的"Group By"子句。
下面是一些示例数据:
Table: tb_mileage
===============================================
f_mileagetimestamp f_mileage
-----------------------------------------------
2014-08-11 11:13:02.000 50
2014-08-11 16:12:55.000 100
2014-08-11 16:55:00.000 30
2014-08-12 11:12:50.000 80
2014-08-12 16:12:49.000 100
2014-08-13 08:12:46.000 40
2014-08-13 08:45:31.000 100
因此,理想的结果集如下所示(PER DAY)(注意,日期的格式无关紧要):
Date Average
------------------------------------------------
08/11/2014 60
08/12/2014 90
08/13/2014 70
理想的结果集如下所示(PER HOUR)(注意,日期的格式无关紧要):
Date Average
------------------------------------------------
08/11/2014 11:00:00 50
08/11/2014 16:00:00 65
08/12/2014 11:00:00 80
08/12/2014 16:00:00 100
08/13/2014 08:00:00 70
请注意,这里的示例是纯理论和简化的,并不一定反映现实世界实现所需的确切标准。这只是为了推动我自己的学习,因为我找到的所有做类似事情的例子都非常复杂,使学习变得困难。
在日期版本中试试这个。
select cast(t.f_mileagetimestamp as date) as dt, avg(t.f_mileage) as avg_mileage
from
tb_mileage t
group by cast(t.f_mileagetimestamp as date)
order by cast(t.f_mileagetimestamp as date) asc;
对于小时版本,您可以使用
select t2.dt, avg(t2.f_mileage) as avg_mileage
from
(
select substring(CONVERT(nvarchar(100), t1.f_mileagetimestamp, 121), 1, 13) + ':00' as dt, t1.f_mileage
from
tb_mileage t1
) t2
group by t2.dt
order by t2.dt asc;
我认为这应该适用于"day"版本:
select cast(f_mileagetimestamp as date), avg(f_mileage)
from tb_mileage
group by cast(f_mileagetimestamp as date)
order by cast(f_mileagetimestamp as date);
对于一个小时,我只使用函数:
select cast(f_mileagetimestamp as date), datepart(hour, f_mileagetimestamp), avg(f_mileage)
from tb_mileage
group by cast(f_mileagetimestamp as date), datepart(hour, f_mileagetimestamp)
order by cast(f_mileagetimestamp as date), datepart(hour, f_mileagetimestamp);