t每秒 SQL 的日期时间平均值

Question

我有下表：

oDateTime              Value
----------------------------------------------
2017-01-01 00:00:00    10
2017-01-01 00:00:01    20
2017-01-01 00:00:02    10
2017-01-01 00:00:03    10
ff.

行数据是每个日期的每秒。我想要以下结果：

oDateTime              Value
----------------------------------------------
2017-01-01 01:00:00    10

所以这是每个日期的每小时平均值。
知道怎么做吗？

谢谢。

Answer 1

DATEADD(HOUR, DATEDIFF(HOUR, 0, oDateTime), 0)会将任何 DATETIME 值舍入到小时。 这使您能够将 DateHour 值保留为单个列。

SELECT
  DATEADD(HOUR, DATEDIFF(HOUR, 0, oDateTime), 0),
  SUM(value) / 60.0,
  AVG(value)
FROM
  yourTable
GROUP BY
  DATEADD(HOUR, DATEDIFF(HOUR, 0, oDateTime), 0)

编辑：重复较少，但稍长...

SELECT
  rounded.DateHour,
  SUM(value) / 60.0,
  AVG(value)
FROM
  yourTable
CROSS APPLY
  (VALUES(DATEADD(HOUR, DATEDIFF(HOUR, 0, oDateTime), 0))) AS rounded(dateHour)
GROUP BY
  rounded.DateHour

Answer 2

尝试按日期和时间聚合：

SELECT
    CONVERT(varchar, oDateTime, 112) AS date,
    DATEPART(hour, oDateTime) AS hour,
    AVG(Value) AS Value
FROM yourTable
GROUP BY
    CONVERT(varchar, oDateTime, 112),
    DATEPART(hour, oDateTime);