时间戳

首页 > 解决方案 > 根据带有分钟时间戳的行获取每小时平均金额

mysql - 根据带有分钟时间戳的行获取每小时平均金额

问题描述

我有一个看起来像这样的表结构：

+---------+------------+----------+---------+-----------+------------+
| id      | account_id | hashrate | workers | sharerate | timestamp  |
+---------+------------+----------+---------+-----------+------------+
| 1227368 |         42 |   405211 |       1 |      6183 | 1534264380 |
| 1227367 |         12 |   450077 |       1 |      6868 | 1534264380 |
+---------+------------+----------+---------+-----------+------------+

每分钟为具有活动工作人员的用户创建一行。

我正在尝试显示一个图表，该图表将显示 24 小时内每小时的平均哈希率和共享率。

到目前为止，我的 sql 查询是：

  SELECT 
    timestamp,
    SUM(hashrate) as hashes,
    SUM(sharerate) AS shares
  FROM statistics_users
  WHERE FROM_UNIXTIME(timestamp) >= DATE_SUB(NOW(), INTERVAL 1 DAY)
  GROUP BY timestamp;

返回：

+------------+------------+----------+
| timestamp  | hashes     | shares   |
+------------+------------+----------+
| 1534177980 | 2282744244 | 34831913 |

这将返回 1440 个对象（一天中的每一分钟一个）。我想按小时对项目进行分组，并将从我的分钟查询返回的所有总和平均到一个小时。不知道从哪里开始，我对编写 sql 查询还很陌生，所以任何帮助都将不胜感激。

标签： mysqlsqldatabase

解决方案

我们可以使用一个表达式，它返回一个小时的表示，基本上修剪掉分钟和秒，然后GROUP BY是那个表达式。一种可能是使用 MySQLDATE_FORMAT函数。

 SELECT DATE_FORMAT(FROM_UNIXTIME(t.timestamp),'%Y-%m-%d %H') AS ts_hr
      , AVG(t.hashes)
      , AVG(t.shares)
   FROM ( 
             -- original query goes here as inline view
             SELECT timestamp
                 , SUM(hashrate) as hashes
                 , SUM(sharerate) AS shares
              FROM statistics_users
             WHERE FROM_UNIXTIME(timestamp) >= NOW() + INTERVAL -1 DAY
             GROUP BY timestamp

        ) t 
  GROUP 
     BY DATE_FORMAT(FROM_UNIXTIME(t.timestamp),'%Y-%m-%d %H')
  --    ^^^^^^^^^^^^                          ^^^^^^^^^^^^^^^

请注意，WHERE 子句中的条件不考虑小时边界，因此我们将获得第一个小时的部分结果和最后一小时的部分结果。

规范不清楚，至于我们是否想要每个时间戳的总和的平均值，或者更简单

 SELECT DATE_FORMAT(FROM_UNIXTIME(t.timestamp),'%Y-%m-%d %H') AS ts_hr
      , AVG(t.hashrate)
      , AVG(t.sharerate)
   FROM statistics_users t
  WHERE FROM_UNIXTIME(t.timestamp) >= NOW() + INTERVAL -1 DAY
  GROUP 
     BY DATE_FORMAT(FROM_UNIXTIME(t.timestamp),'%Y-%m-%d %H')
 --     ^^^^^^^^^^^^                          ^^^^^^^^^^^^^^^

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

推荐阅读