gpt4 book ai didi

mysql - 同一列和按天分组的值的日期时间差异总和

转载 作者:行者123 更新时间:2023-11-29 15:45:40 26 4
gpt4 key购买 nike

我有一个表,其中列事件和另一列日期时间中包含“ON”和“OFF”值。

id(AUTOINCREMENT) id_device     activity    datetime
1 a ON 2017-05-26 22:00:00
2 b ON 2017-05-26 05:00:00
3 a OFF 2017-05-27 04:00:00
4 b OFF 2017-05-26 08:00:00
5 a ON 2017-05-28 12:00:00
6 a OFF 2017-05-28 15:00:00

我需要获取每天的总开启时间

day           id_device     total_minutes_on
2017-05-26 a 120
2017-05-26 b 180
2017-05-27 a 240
2017-05-27 b 0
2017-05-28 a 180
2017-05-28 b 0

我已经搜索并尝试了其他帖子的答案,我尝试了时差,并且得到了正确的总时间。我找不到按日期分组的总时间的方法

感谢您的帮助

最佳答案

我不会将其作为明确的答案发布,而是对我来说这是一个实验,希望您会发现它对您的情况有用。另外我想提一下,我正在使用的 MySQL 数据库版本相当旧,因此我使用的方法至少可以说也是非常手动的。

首先让我们提取您的预期输出:

  1. id_device ab 中的day 中的日期值需要重复两次。
  2. 分钟数根据事件计算;如果事件在明天之前处于“开启”状态,则需要计算到当日结束时的 24:00:00,而第二天将计算直到事件关闭为止的分钟数。

我想到的是:

  • 创建条件 (1):
SELECT * FROM
(SELECT DATE(datetime) dtt FROM mytable GROUP BY DATE(datetime)) a,
(SELECT id_device FROM mytable GROUP BY id_device) b
ORDER BY dtt,id_device;

上面的查询将返回以下结果:

+------------+-----------+
| dtt | id_device |
+------------+-----------+
| 2017-05-26 | a |
| 2017-05-26 | b |
| 2017-05-27 | a |
| 2017-05-27 | b |
| 2017-05-28 | a |
| 2017-05-28 | b |
+------------+-----------+

*以上仅适用于表中的所有日期。如果您想要所有日期,无论是否有事件,我建议您创建一个日历表(引用:Generating a series of dates)。

因此这成为基本查询。然后我添加了一个外部查询,将上面的查询与原始数据表左连接:

SELECT v.*,
GROUP_CONCAT(w.activity ORDER BY w.datetime SEPARATOR ' ') activity,
GROUP_CONCAT(TIME_TO_SEC(TIME(w.datetime)) ORDER BY w.datetime SEPARATOR ' ') tr
FROM
-- this was the first query
(SELECT * FROM
(SELECT DATE(datetime) dtt FROM mytable GROUP BY DATE(datetime)) a,
(SELECT id_device FROM mytable GROUP BY id_device) b
ORDER BY a.dtt,b.id_device) v
--
LEFT JOIN
mytable w
ON v.dtt=DATE(w.datetime) AND v.id_device=w.id_device
GROUP BY DATE(v.dtt),v.id_device

查询中的新增功能是对从日期时间列中提取的事件和时间值添加 GROUP_CONCAT 操作,并将其转换为秒值。您会注意到,在两个 GROUP_CONCAT 中都有一个类似的 ORDER BY 条件,这对于获取确切的对应值非常重要。上面的查询将返回以下结果:

+------------+-----------+----------+-------------+
| dtt | id_device | activity | tr |
+------------+-----------+----------+-------------+
| 2017-05-26 | a | ON | 79200 |
| 2017-05-26 | b | ON OFF | 18000 28800 |
| 2017-05-27 | a | OFF | 14400 |
| 2017-05-27 | b | (NULL) | (NULL) |
| 2017-05-28 | a | ON OFF | 43200 54000 |
| 2017-05-28 | b | (NULL) | (NULL) |
+------------+-----------+----------+-------------+

从这里开始,我在外部添加了另一个查询来计算需要多少分钟并尝试获得预期结果:

SELECT dtt,id_device,
CASE
WHEN SUBSTRING_INDEX(activity,' ',1)='ON' AND SUBSTRING_INDEX(activity,' ',-1)='OFF'
THEN (SUBSTRING_INDEX(tr,' ',-1)-SUBSTRING_INDEX(tr,' ',1))/60

WHEN activity='ON' THEN 1440-(tr/60)

WHEN activity='OFF' THEN tr/60

WHEN activity IS NULL AND tr IS NULL THEN 0

END AS 'total_minutes_on'
FROM
-- from the last query
(SELECT v.*,
GROUP_CONCAT(w.activity ORDER BY w.datetime SEPARATOR ' ') activity,
GROUP_CONCAT(TIME_TO_SEC(TIME(w.datetime)) ORDER BY w.datetime SEPARATOR ' ') tr
FROM
-- this was the first query
(SELECT * FROM
(SELECT DATE(datetime) dtt FROM mytable GROUP BY DATE(datetime)) a,
(SELECT id_device FROM mytable GROUP BY id_device) b
ORDER BY a.dtt,b.id_device) v
--
LEFT JOIN
mytable w
ON v.dtt=DATE(w.datetime) AND v.id_device=w.id_device
GROUP BY DATE(v.dtt),v.id_device
--
) z

我做的最后一部分是,如果activity值在同一天同时具有ONOFF,那么(OFF-ON)/60secs=总分钟数。如果 activity 值仅为 ON,则“24:00:00”的分钟值 > 24 小时*60 分钟= 1440-(ON/60secs)= 总分钟数,如果事件仅OFF,我只需将秒转换为分钟,因为无论如何一天都是从 00:00:00 开始的。

+------------+-----------+------------------+
| dtt | id_device | total_minutes_on |
+------------+-----------+------------------+
| 2017-05-26 | a | 120 |
| 2017-05-26 | b | 180 |
| 2017-05-27 | a | 240 |
| 2017-05-27 | b | 0 |
| 2017-05-28 | a | 180 |
| 2017-05-28 | b | 0 |
+------------+-----------+------------------+

希望这能给您一些想法。 ;)

关于mysql - 同一列和按天分组的值的日期时间差异总和,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/57086317/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com