时间戳

首页 > 解决方案 > Mysql：GROUP BY 自定义日期间隔

mysql - Mysql：GROUP BY 自定义日期间隔

问题描述

情况：每分钟X能量设备节省其消耗的表格。我必须计算某一天单个设备的每日消耗量（以每小时为间隔，00 - 01 - 02 - 03 - 04 ..... 23）（以创建简单的消耗小时图表）。

id | date                |  total | id_device
---------------------------------------------
0  | 2018-10-01 00:01:00 | 100    | 1
---------------------------------------------
1  | 2018-10-01 00:01:00 | 101    | 2
---------------------------------------------
2  | 2018-10-01 00:02:00 | 110    | 1
---------------------------------------------
3  | 2018-10-01 00:02:00 | 105    | 2
---------------------------------------------
.. | 2018-10-01 23:59:00 | 200    | 1
---------------------------------------------
.. | 2018-10-01 23:59:00 | 1000   | 2

我这样做是为了计算每小时的消耗

SELECT CONCAT(IF(HOUR(`date`) < 10 , '0','') , HOUR(`date`)) AS `HH`, (MAX(`total`) - MIN(`total`)) AS `total` 
FROM `mytable`
WHERE `date` BETWEEN DATE_FORMAT(?, '%Y-%m-%d 00:00:00') AND DATE_FORMAT(?, '%Y-%m-%d 23:59:59') AND id_device = ?
GROUP BY `HH`

结果

HH | total
----------
00 | 100
01 | ...
.. | ...
23 | ...

此查询正确返回总数（如果一个小时没有保存它不会显示在查询中，没问题）。

但是 GROUP BY 的工作方式如下

但是我需要这个，否则时间计算不正确

有可能有这种间隔吗？

PS：对于案例 00，我知道我必须从前一天记录的最后一个值开始更改搜索，但这不是我现在的问题。我会这样做：

WHERE 'date' BETWEEN 
  COALESCE((SELECT 'date' FROM 'mytable' WHERE 'date' < DATE_FORMAT(?, '%Y-%m-%d 00:00:00') ORDER BY 'date' DESC LIMIT 1), DATE_FORMAT(?, '%Y-%m-%d 00:00:00')) 
  AND DATE_FORMAT(?, '%Y-%m-%d 23:59:59')

更新：DB Fiddle 示例。有 3 台设备，每台设备都有 5 天的记录。

https://www.db-fiddle.com/f/ddvVguupi74TQjQ6yWJUzB/3

实际结果（id_device 1，日期 2018-10-03）：

HH  total
00  354
01  354
02  354
03  354
04  354
05  354
06  354
07  354
08  354
09  354
10  354
11  354
12  354
13  354
14  354
15  354
16  354
17  354
18  354
19  354
20  354
21  354
22  354
23  354

预期结果：

HH  total
00  360
01  360
02  360
03  360
04  360
05  360
06  360
07  360
08  360
09  360
10  360
11  360
12  360
13  360
14  360
15  360
16  360
17  360
18  360
19  360
20  360
21  360
22  360
23  360

标签： mysqlsql

解决方案

  • 使用MINUTE()函数，您可以确定分钟值是否为 59。
  • 如果它等于 59，您可以将要考虑的小时值增加 1。
  • 改为使用LPAD()函数添加前导“0”，最大字符串大小为 2。

您可以使用以下方法来确定HH：

LPAD(IF(
        MINUTE(`date`) = 59, 
        HOUR(`date`) + 1, 
        HOUR(`date`)
       ), 
     2, 
     '0'
    ) AS `HH`

但是，手头的问题是，您在 59 分钟时有两组发生冲突的行。例如：01:59:23 的一行必须在01以及02组中考虑。使用简单的 group by 是不可能的。因此，一种方法是考虑两个不同的 Select语句来获取HH一行的值。一个选择将考虑原始小时值，另一个将考虑hour + 159 分钟的情况。但是，我们将在所有其他分钟（第 59 分钟除外）都有重复。这个重复问题可以通过利用Union语句来解决。

之后，您可以将Unionized结果集用作Derived table，并做一个简单的Group By.

因此，您可以尝试以下方法（当一天发生变化时，您仍然必须处理边缘情况）：

SELECT 
  dt.HH, 
  (MAX(dt.total) - MIN(dt.total)) AS total 
FROM
    (
      SELECT LPAD(IF(MINUTE(t1.date) = 59, HOUR(t1.date) + 1, HOUR(t1.date)), 2, '0') AS HH, 
             t1.total 
      FROM mytable AS t1
      WHERE t1.date BETWEEN DATE_FORMAT(?, '%Y-%m-%d 00:00:00') AND
                            DATE_FORMAT(?, '%Y-%m-%d 23:59:59')

      UNION 

      SELECT LPAD(HOUR(t2.date), 2, '0') AS HH, 
             t2.total 
      FROM mytable AS t2
      WHERE t2.date BETWEEN DATE_FORMAT(?, '%Y-%m-%d 00:00:00') AND
                            DATE_FORMAT(?, '%Y-%m-%d 23:59:59')
    ) AS dt
GROUP BY dt.HH

推荐阅读