mysql - 基于时间记录和来自同一表的后续时间记录的时间差异-6ren

mysql - 基于时间记录和来自同一表的后续时间记录的时间差异

转载作者：行者123 更新时间：2023-11-29 02:12:47

我在 SQLFiddle 上放置了一些数据样本我试图弄清楚如何根据这些数据构建查询，该查询将提供:

TIMEDIFF() scl1_fill 表的 TimeStr 字段和下一个 sc1_weight 表的 TimeStr 字段
- 示例: SELECT TIMEDIFF('2017-11-14 07:21:29','2017-11-14 07:15:56')

我想我需要效仿 ROW_NUMBER()使用 MySQL，出于某种原因，我似乎总是遇到麻烦，使用变量和子查询来获取行号等。

我尝试了很多东西，但每次都失败了，所以我什至不确定是否值得展示我的尝试，所以我将提供我所拥有的和预期的结果以进行可视化/澄清。

本质上，当 sc1_fill 获得 1 值时，设备开始填充，当 sc1_weight 获得任何值时，这意味着它满足重量设定点并在该点返回一个值。

获取两者之间的时间差将显示从 sc1_fill 开始到下一个记录时间 sc1_weight 获得任何值所花费的时间跑了。

我想我可以把它作为一个值放在 sc1_weight 记录中，其中包含 TIMEDIFF()并有一个新列，并让所有其他记录显示 NULL，这样就可以了(请参阅预期结果)。

Full Table Data

(始终且仅从一个特定日期获取数据，例如 WHERE TimeStr LIKE '2017-11-14%')

| test_tbl |               TimeStr |   Value |
----------------------------------------------
sc1_setpnt      2017-11-14 00:05:01     650
sc1_disch       2017-11-14 07:10:24     1
sc1_fill        2017-11-14 07:15:56     1
sc1_weight      2017-11-14 07:21:29     651
sc1_disch       2017-11-14 07:26:18     1
sc1_fill        2017-11-14 07:27:32     1
sc1_weight      2017-11-14 07:33:07     650
sc1_disch       2017-11-14 08:18:36     1
sc1_fill        2017-11-14 08:19:53     1
sc1_weight      2017-11-14 08:25:29     651
sc1_disch       2017-11-14 08:30:21     1
sc1_fill        2017-11-14 08:31:39     1
sc1_weight      2017-11-14 08:37:20     650
sc1_disch       2017-11-14 08:46:00     1
sc1_fill        2017-11-14 08:47:17     1
sc1_weight      2017-11-14 08:52:50     649
sc1_disch       2017-11-14 09:03:22     1
sc1_fill        2017-11-14 09:04:36     1
sc1_weight      2017-11-14 09:10:10     651
sc1_disch       2017-11-14 09:18:45     1
sc1_fill        2017-11-14 09:20:35     1
sc1_weight      2017-11-14 09:26:11     650
sc1_disch       2017-11-14 09:36:40     1
sc1_fill        2017-11-14 09:37:59     1
sc1_weight      2017-11-14 09:43:39     650
sc1_disch       2017-11-14 09:46:55     1
sc1_fill        2017-11-14 09:53:49     1
sc1_weight      2017-11-14 09:59:42     650
sc1_disch       2017-11-14 10:36:06     1
sc1_fill        2017-11-14 10:37:27     1
sc1_weight      2017-11-14 10:43:14     650
sc1_disch       2017-11-14 10:50:44     1
sc1_fill        2017-11-14 10:52:01     1
sc1_weight      2017-11-14 10:57:47     650
sc1_disch       2017-11-14 11:05:46     1
sc1_fill        2017-11-14 11:07:19     1
sc1_weight      2017-11-14 11:12:48     650
sc1_disch       2017-11-14 11:25:25     1
sc1_fill        2017-11-14 11:36:18     1
sc1_fill        2017-11-14 11:39:12     1
sc1_fill        2017-11-14 11:45:26     1
sc1_weight      2017-11-14 11:50:39     651
sc1_disch       2017-11-14 12:00:45     1
sc1_fill        2017-11-14 12:04:44     1
sc1_weight      2017-11-14 12:10:16     651
sc1_disch       2017-11-14 12:58:48     1
sc1_fill        2017-11-14 13:00:01     1
sc1_weight      2017-11-14 13:05:23     651
sc1_disch       2017-11-14 13:06:19     1
sc1_fill        2017-11-14 13:10:52     1
sc1_weight      2017-11-14 13:16:19     650
sc1_disch       2017-11-14 13:18:27     1
sc1_fill        2017-11-14 13:24:35     1
sc1_weight      2017-11-14 13:30:01     651
sc1_disch       2017-11-14 13:31:35     1
sc1_fill        2017-11-14 13:35:40     1
sc1_weight      2017-11-14 13:41:05     650
sc1_disch       2017-11-14 13:47:04     1
sc1_fill        2017-11-14 13:50:19     1
sc1_weight      2017-11-14 13:55:43     649
sc1_disch       2017-11-14 14:06:57     1
sc1_fill        2017-11-14 14:08:11     1
sc1_weight      2017-11-14 14:13:32     651
sc1_disch       2017-11-14 14:26:10     1
sc1_fill        2017-11-14 14:27:24     1
sc1_weight      2017-11-14 14:32:49     650
sc1_disch       2017-11-14 14:42:00     1
sc1_fill        2017-11-14 14:43:11     1
sc1_weight      2017-11-14 14:48:25     650

预期结果

| test_tbl |               TimeStr |   Value |     FillTime |
-------------------------------------------------------------
sc1_setpnt      2017-11-14 00:05:01     650              NULL   
sc1_disch       2017-11-14 07:10:24     1                NULL
sc1_fill        2017-11-14 07:15:56     1                NULL
sc1_weight      2017-11-14 07:21:29     651          00:05:33
sc1_disch       2017-11-14 07:26:18     1                NULL
sc1_fill        2017-11-14 07:27:32     1                NULL
sc1_weight      2017-11-14 07:33:07     650          00:05:35       
sc1_disch       2017-11-14 08:18:36     1                NULL
sc1_fill        2017-11-14 08:19:53     1                NULL
sc1_weight      2017-11-14 08:25:29     651          00:05:36
sc1_disch       2017-11-14 08:30:21     1                NULL
sc1_fill        2017-11-14 08:31:39     1                NULL
sc1_weight      2017-11-14 08:37:20     650         00:05:41
sc1_disch       2017-11-14 08:46:00     1                NULL

让事情变得更糟

好吧，更糟的是，似乎有时在随后的 sc1_weight 值之前有多个 sc1_fill 值 1 但我只会需要计算集合中最近或最新的 TimeStr 以获得填充时间(例如 SELECT TIMEDIFF('2017-11-14 11:50:39','2017-11- 14 11:45:26'))

这种情况下的预期结果

| test_tbl |               TimeStr |   Value |     FillTime |
-------------------------------------------------------------
sc1_fill        2017-11-14 11:36:18     1               NULL
sc1_fill        2017-11-14 11:39:12     1               NULL
sc1_fill        2017-11-14 11:45:26     1               NULL
sc1_weight      2017-11-14 11:50:39     651         00:05:13
sc1_disch       2017-11-14 12:00:45     1               NULL

最佳答案

虽然您没有要求此摘要输出，但我找到了一种可能有用的方法，结果如下所示

+----+---------+--------+---------------------+---------------------+----------+--------------+
|    | filllno | weight |     start_fill      |      end_fill       | duration | num_of_fills |
+----+---------+--------+---------------------+---------------------+----------+--------------+
|  1 |       0 |    651 | 14.11.2017 07:15:56 | 14.11.2017 07:21:29 | 00:05:33 |            1 |
|  2 |       1 |    650 | 14.11.2017 07:27:32 | 14.11.2017 07:33:07 | 00:05:35 |            1 |
|  3 |       2 |    651 | 14.11.2017 08:19:53 | 14.11.2017 08:25:29 | 00:05:36 |            1 |
|  4 |       3 |    650 | 14.11.2017 08:31:39 | 14.11.2017 08:37:20 | 00:05:41 |            1 |
|  5 |       4 |    649 | 14.11.2017 08:47:17 | 14.11.2017 08:52:50 | 00:05:33 |            1 |
|  6 |       5 |    651 | 14.11.2017 09:04:36 | 14.11.2017 09:10:10 | 00:05:34 |            1 |
|  7 |       6 |    650 | 14.11.2017 09:20:35 | 14.11.2017 09:26:11 | 00:05:36 |            1 |
|  8 |       7 |    650 | 14.11.2017 09:37:59 | 14.11.2017 09:43:39 | 00:05:40 |            1 |
|  9 |       8 |    650 | 14.11.2017 09:53:49 | 14.11.2017 09:59:42 | 00:05:53 |            1 |
| 10 |       9 |    650 | 14.11.2017 10:37:27 | 14.11.2017 10:43:14 | 00:05:47 |            1 |
| 11 |      10 |    650 | 14.11.2017 10:52:01 | 14.11.2017 10:57:47 | 00:05:46 |            1 |
| 12 |      11 |    650 | 14.11.2017 11:07:19 | 14.11.2017 11:12:48 | 00:05:29 |            1 |
| 13 |      12 |    651 | 14.11.2017 11:45:26 | 14.11.2017 11:50:39 | 00:05:13 |            3 |
| 14 |      13 |    651 | 14.11.2017 12:04:44 | 14.11.2017 12:10:16 | 00:05:32 |            1 |
| 15 |      14 |    651 | 14.11.2017 13:00:01 | 14.11.2017 13:05:23 | 00:05:22 |            1 |
| 16 |      15 |    650 | 14.11.2017 13:10:52 | 14.11.2017 13:16:19 | 00:05:27 |            1 |
| 17 |      16 |    651 | 14.11.2017 13:24:35 | 14.11.2017 13:30:01 | 00:05:26 |            1 |
| 18 |      17 |    650 | 14.11.2017 13:35:40 | 14.11.2017 13:41:05 | 00:05:25 |            1 |
| 19 |      18 |    649 | 14.11.2017 13:50:19 | 14.11.2017 13:55:43 | 00:05:24 |            1 |
| 20 |      19 |    651 | 14.11.2017 14:08:11 | 14.11.2017 14:13:32 | 00:05:21 |            1 |
| 21 |      20 |    650 | 14.11.2017 14:27:24 | 14.11.2017 14:32:49 | 00:05:25 |            1 |
| 22 |      21 |    650 | 14.11.2017 14:43:11 | 14.11.2017 14:48:25 | 00:05:14 |            1 |
+----+---------+--------+---------------------+---------------------+----------+--------------+

Demo here

select
      case when value = 1 then fillno else fillno-2 end filllno
    , max(value)                                        weight
    , max(case when value = 1 then TimeStr end)         start_fill
    , max(TimeStr)                                      end_fill
    , timediff(max(TimeStr),max(case when value = 1 then TimeStr end)) duration
    , count(*) - 1                                      num_of_fills
from (
      SELECT * 
      , IF(value <> 1, @prev := value, @prev) pweight
      , IF(value <> 1, @counter := @counter+1, @counter - 1) fillno
      FROM test_tbl
      cross join (select @counter :=0, @prev:=0) vars
      where tbl in ('sc1_fill','sc1_weight','sc1_setpnt')
      order by TimeStr ASC
    ) d
where tbl in ('sc1_fill','sc1_weight')
group by
    case when value = 1 then fillno else fillno-2 end
order by start_fill ASC
;

内部子查询使用将填充与权重对齐(通过“fillno”)的信息准备行，因此允许您在上面看到的聚合 View 。这些行看起来像这个示例:

+----+------------+---------------------+-------+--------------+----------+---------+--------+
|    |    tbl     |       TimeStr       | Value | @counter :=0 | @prev:=0 | pweight | fillno |
+----+------------+---------------------+-------+--------------+----------+---------+--------+
|  1 | sc1_setpnt | 14.11.2017 00:05:01 |   650 |            0 |        0 |     650 |      1 |
|  2 | sc1_fill   | 14.11.2017 07:15:56 |     1 |            0 |        0 |     650 |      0 |
|  3 | sc1_weight | 14.11.2017 07:21:29 |   651 |            0 |        0 |     651 |      2 |
|  4 | sc1_fill   | 14.11.2017 07:27:32 |     1 |            0 |        0 |     651 |      1 |
|  5 | sc1_weight | 14.11.2017 07:33:07 |   650 |            0 |        0 |     650 |      3 |
|  6 | sc1_fill   | 14.11.2017 08:19:53 |     1 |            0 |        0 |     650 |      2 |
|  7 | sc1_weight | 14.11.2017 08:25:29 |   651 |            0 |        0 |     651 |      4 |
|  8 | sc1_fill   | 14.11.2017 08:31:39 |     1 |            0 |        0 |     651 |      3 |
|  9 | sc1_weight | 14.11.2017 08:37:20 |   650 |            0 |        0 |     650 |      5 |
| 10 | sc1_fill   | 14.11.2017 08:47:17 |     1 |            0 |        0 |     650 |      4 |
| 11 | sc1_weight | 14.11.2017 08:52:50 |   649 |            0 |        0 |     649 |      6 |
| 12 | sc1_fill   | 14.11.2017 09:04:36 |     1 |            0 |        0 |     649 |      5 |
+----+------------+---------------------+-------+--------------+----------+---------+--------+

该逻辑确实会根据请求考虑 2 个或更多填充事件，但也会对这些事件进行计数，以便您可以看到已发生的任何事件。

关于mysql - 基于时间记录和来自同一表的后续时间记录的时间差异，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/47319763/

文章推荐： android - 混合列表 Activity

文章推荐： ios - 如何使用动态内容填充 Collection View

文章推荐： ios - 解析 JSON 后无法在 NSDictionary 中创建键

文章推荐： ios - 使用适用于 iOS 的 FlickrKit 进行身份验证

mysql - 同步/流式传输 MySQL 表/表(连接表)与 PostgreSQL 表/表
我有一台 MySQL 服务器和一台 PostgreSQL 服务器。需要从多个表中复制或重新插入一组数据 MySQL 流式传输/同步到 PostgreSQL 表。这种复制可以基于时间(Sync)或事
php - 从用户(表)获取数据其中用户(表)的id等于 friend (表)的id
如果两个表的 id 彼此相等，我尝试从一个表中获取数据。这是我使用的代码: SELECT id_to , email_to , name_to , status_to
sql - Excel 表 SQL 表
我有一个 Excel 工作表。顶行对应于列名称，而连续的行每行代表一个条目。如何将此 Excel 工作表转换为 SQL 表？我使用的是 SQL Server 2005。最佳答案这取决于您使用哪
mysql - 如何将两个django模型(表)合并为一个模型(表)
我想合并两个 Django 模型并创建一个模型。让我们假设我有第一个表表 A，其中包含一些列和数据。 Table A -------------- col1 col2 col3 col
mysql - 表 1、表 2 的多列左连接
我有两个表:table1，table2，如下所示 table1: id name 1 tamil 2 english 3 maths 4 science table2: p
sql - 大传感器数据最佳选择。表 SQL 与 Azure 表
关闭。此题需要details or clarity 。目前不接受答案。想要改进这个问题吗？通过 editing this post 添加详细信息并澄清问题. 已关闭 1 年前。 Improve th
dynamics-ax-2009 - 表=表与表.数据(表)
下面两个语句有什么区别？ newTable = orginalTable 或 newTable.data(originalTable) 我怀疑 .data() 方法具有性能优势，因为它在标准 AX 中
SQL Server 表 -(或可能是任何 SQL 表)没有主键会影响性能吗？
我有一个表，我没有在其中显式定义主键，它并不是真正需要的功能......但是一位同事建议我添加一个列作为唯一主键以随着数据库的增长提高性能...... 谁能解释一下这是如何提高性能的？没有使用索引(
php - 将产品详细信息插入 'product' 表，并将产品图像插入 'image' 表
如何将表“产品”中的产品记录与其不同表“图像”中的图像相关联？我正在对产品 ID 使用自动增量。我觉得不可能进行关联，因为产品 ID 是自动递增的，因此在插入期间不可用! 如何插入新产品，获取产品
python - 创建一个新的 sql 表，其中的列源自另一个 sql 表
我有一个 sql 表，其中包含关键字和出现次数，如下所示(尽管出现次数并不重要): ____________ dog | 3 | ____________ rat | 7 | ____
MySQL LAST_INSERT_ID() 与 INSERT INTO 表 SELECT FROM 表
是否可以使用目标表中的LAST_INSERT_ID更新源表？ INSERT INTO `target` SELECT `a`, `b` FROM `source` 目标表有一个自动增量键id，我想将其
mysql - 查询 - 在简单的 mysql 内连接中定义(表，表)
我正在重建一个搜索查询，因为它在“我看到的”中变得多余，我想知道什么 (albums_artists, artists) ( ) does in join? is it for boosting pe
innodb - mysqldump 备份缺少所有 innodb 表，但没有 MyISAM 表
以下是我使用 mysqldump 备份数据库的开关: /usr/bin/mysqldump -u **** --password=**** --single-transaction --databas
html - 为什么 MySQL 表中的所有行都是相同的？ (MySQL 表 > HTML 表)
我试图获取 MySQL 表中的所有行并将它们放入 HTML 表中: Exam ID Status Assigned Examiner
mysql - 查询 'photos' 表，同时查询 'bookmarks' 表，以便知道添加书签的照片
如何查询名为 photos 的表中的所有记录，并知道当前用户使用单个查询将哪些结果照片添加为书签？这是我的表格: -- -- Table structure for table `photos` -
Mysql MEMORY 表 vs InnoDB 表(很多插入，很少读取)
我的网站都在 InnoDB 表上运行，目前为止运行良好。现在我想知道在我的网站上实时发生了什么，所以我将每个页面浏览量(页面、引荐来源网址、IP、主机名等)存储在 InnoDB 表中。每秒大约有 10
mysql - 如何在 mysql 中存储客户数据(2 表 vs 1 表)
我在想我会为 mysql 准备两个表。一个用于存储登录信息，另一个用于存储送货地址。这是传统方式还是所有内容都存储在一张表中？对于两个表...有没有办法自动将表 A 的列复制到表 B，以便我可以引用
mysql - 表 1 包含名字和姓氏，表 2 包含两列引用表 1 上的名称
我不是程序员，我从这个表格中阅读了很多关于如何解决我的问题的内容，但我的搜索效果不好我有两张 table 表 1:成员 id*| name | surname -------------------
c# - 如何在 ASP.NET 中显示 "View"表(SQL 表)？
我知道如何在 ASP.NET 中显示真实表，例如 public ActionResult Index() { var s = db.StaffInfoDBSet.ToList(); r
php - INSERT INTO 表 VALUES.. 与 INSERT INTO 表 SET 错误
我正在尝试运行以下查询: "insert into visits set source = 'http://google.com' and country = 'en' and ref = '1234

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城