每天/每小时获取平均值而不需要 Group By

Question

在一张单独的表中，假设我有一个里程和时间戳的日志。我想获取每天和每小时的平均里程数。由于日期格式的原因，我无法使用本身的“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                      

所以，理想的结果集应该如下所示（每天）（注意，日期格式不重要）：

  Date                 Average
  ------------------------------------------------
  08/11/2014           60
  08/12/2014           90
  08/13/2014           70

理想的结果集应如下所示（每小时）（注意，日期格式不重要）：

  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

请注意，这里的示例纯粹是理论和简化的，并不一定反映实现现实世界所需的确切标准。这仅仅是为了推动我的学习，因为我发现所有类似事情的例子都非常复杂，使得学习变得困难。

- Beems

Answer 1

尝试使用此版本进行日期选择。

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;

- peter.petrov

---

谢谢Peter。 “按天”查询非常清晰。另一方面，“按小时”查询生成一个错误，我无法理解：Msg 164，级别15，状态1，第4行 每个GROUP BY表达式必须包含至少一个不是外部引用的列。 - Beems

我修复了另一个。那个也可以吧？ - peter.petrov

很抱歉，按下键盘上的“回车”键添加了评论，而不是换行。当它提交时，我还没有完成我的评论。 - Beems

听起来很有趣，让我试试看。 - peter.petrov

现在它已经可以工作了，谢谢！上面看到的查询现在确实完美无瑕地工作了。的确，日期格式很特别。我还不太明白日期是如何连接的，但我会按照您给出的指导进行学习，并自行尝试其他示例。非常感谢，这太棒了。 - Beems

Answer 2

我认为这应该适用于“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);

- Gordon Linoff

---

SQL Server 告诉我： Msg 195，Level 15，State 10，Line 1 “date”不是一个被认可的内置函数名称。 - Beems

@Beems 下次在你的问题中明确指定它是SQL Server，并指定版本。 - peter.petrov

抱歉，我标记为T-SQL，认为那已经足够了。我忘记了人们可能只是根据“SQL”标签来到这里。 - Beems

@Beems...我错过了tsql标签，是我的错。无论如何，我刚刚更新了语法。 - Gordon Linoff

谢谢，这个像冠军一样运作。这个地方总是让我惊叹于快速的答案。 - Beems