值在范围内时的mysql分组-6ren

值在范围内时的mysql分组

转载作者：可可西里更新时间：2023-11-01 07:57:02

我到处搜索，似乎找不到任何关于如何处理我的查询的信息。如果我问的是一个愚蠢的问题，我提前道歉，但我真的需要一些帮助。

我有一系列以不同时间间隔记录的值。数据如下所示:

 timeStamp           | RPM 
 2012-05-01 01:02:56 | 802
 2012-05-01 01:03:45 | 845
 2012-05-01 01:04:50 | 825
 2012-05-01 01:05:55 | 810
 2012-05-01 01:07:00 | 1000
 2012-05-01 01:08:03 | 1005
 2012-05-01 01:09:05 | 1145
 2012-05-01 01:10:15 | 1110
 2012-05-01 01:11:20 | 800
 2012-05-01 01:12:22 | 812
 2012-05-01 01:13:20 | 820
 2012-05-01 01:14:20 | 820
 2012-05-01 01:15:20 | 1200

示例中的 RPM 是发动机 RPM。

当 RPM 在 800-900 范围内时，我需要开始和结束时间戳，因为这被视为引擎空转。我还希望能够返回每个非空闲时间段的开始和结束时间。

我想要得到的结果是这样的:

Period    | startTime           | endTime             | duration 
 Idle1    | 2012-05-01 01:02:56 | 2012-05-01 01:05:55 | 179 seconds 
 nonIdle1 | 2012-05-01 01:07:00 | 2012-05-01 01:10:15 | 195 seconds 
 idle2    | 2012-05-01 01:11:20 | 2012-05-01 01:14:20 | 180 seconds

在此先感谢您的帮助。

谢谢

最佳答案

试试这个:http://www.sqlfiddle.com/#!2/e9372/1

在数据库端这样做的好处是您不仅可以在 PHP 上使用查询，还可以在 Java、C#、Python 等上使用它。而且在数据库端这样做速度很快

select 
  if(idle_state = 1, 
       concat('Idle ', idle_count), 
       concat('NonIdle ', non_idle_count) ) as Period,
  startTime, endTime, duration
from
(

  select 

    @idle_count := @idle_count + if(idle_state = 1,1,0) as idle_count,
    @non_idle_count := @non_idle_count +if(idle_state = 0,1,0) as non_idle_count,

    state_group, idle_state,
    min(timeStamp) as startTime, max(timeStamp) as endTime,
    timestampdiff(second, min(timeStamp), max(timeStamp)) as duration
  from
  (
    select *,        
      @idle_state := if(rpm between 800 and 900, 1, 0) as idle_state,
      @state_group := @state_group + 
                      if(@idle_state = @prev_state,0,1) as state_group,
      @prev_state := @idle_state
    from (tbl, (select @state_group := 0 as y) as vars)
    order by tbl.timeStamp
  ) as x
  ,(select @idle_count := 0 as y, @non_idle_count := 0 as z) as vars
  group by state_group, idle_state

) as summary

输出:

|    PERIOD |                  STARTTIME |                    ENDTIME | DURATION |
|-----------|----------------------------|----------------------------|----------|
|    Idle 1 | May, 01 2012 01:02:56-0700 | May, 01 2012 01:05:55-0700 |      179 |
| NonIdle 1 | May, 01 2012 01:07:00-0700 | May, 01 2012 01:10:15-0700 |      195 |
|    Idle 2 | May, 01 2012 01:11:20-0700 | May, 01 2012 01:14:20-0700 |      180 |
| NonIdle 2 | May, 01 2012 01:15:20-0700 | May, 01 2012 01:15:20-0700 |        0 |

在此处查看查询进度:http://www.sqlfiddle.com/#!2/e9372/1

工作原理:

五个步骤。

首先，将空闲与非空闲分开:

select *,
  @idle_state := if(rpm between 800 and 900, 1, 0) as idle_state
from (tbl, (select @state_group := 0 as y) as vars)
order by tbl.timeStamp;

输出:

|                  TIMESTAMP |  RPM | Y | IDLE_STATE |
|----------------------------|------|---|------------|
| May, 01 2012 01:02:56-0700 |  802 | 0 |          1 |
| May, 01 2012 01:03:45-0700 |  845 | 0 |          1 |
| May, 01 2012 01:04:50-0700 |  825 | 0 |          1 |
| May, 01 2012 01:05:55-0700 |  810 | 0 |          1 |
| May, 01 2012 01:07:00-0700 | 1000 | 0 |          0 |
| May, 01 2012 01:08:03-0700 | 1005 | 0 |          0 |
| May, 01 2012 01:09:05-0700 | 1145 | 0 |          0 |
| May, 01 2012 01:10:15-0700 | 1110 | 0 |          0 |
| May, 01 2012 01:11:20-0700 |  800 | 0 |          1 |
| May, 01 2012 01:12:22-0700 |  812 | 0 |          1 |
| May, 01 2012 01:13:20-0700 |  820 | 0 |          1 |
| May, 01 2012 01:14:20-0700 |  820 | 0 |          1 |
| May, 01 2012 01:15:20-0700 | 1200 | 0 |          0 |

其次，将更改分成几组:

select *,  
  @idle_state := if(rpm between 800 and 900, 1, 0) as idle_state,
  @state_group := @state_group + 
                  if(@idle_state = @prev_state,0,1) as state_group,
  @prev_state := @idle_state

from (tbl, (select @state_group := 0 as y) as vars)
order by tbl.timeStamp;

输出:

|                  TIMESTAMP |  RPM | Y | IDLE_STATE | STATE_GROUP | @PREV_STATE := @IDLE_STATE |
|----------------------------|------|---|------------|-------------|----------------------------|
| May, 01 2012 01:02:56-0700 |  802 | 0 |          1 |           1 |                          1 |
| May, 01 2012 01:03:45-0700 |  845 | 0 |          1 |           1 |                          1 |
| May, 01 2012 01:04:50-0700 |  825 | 0 |          1 |           1 |                          1 |
| May, 01 2012 01:05:55-0700 |  810 | 0 |          1 |           1 |                          1 |
| May, 01 2012 01:07:00-0700 | 1000 | 0 |          0 |           2 |                          0 |
| May, 01 2012 01:08:03-0700 | 1005 | 0 |          0 |           2 |                          0 |
| May, 01 2012 01:09:05-0700 | 1145 | 0 |          0 |           2 |                          0 |
| May, 01 2012 01:10:15-0700 | 1110 | 0 |          0 |           2 |                          0 |
| May, 01 2012 01:11:20-0700 |  800 | 0 |          1 |           3 |                          1 |
| May, 01 2012 01:12:22-0700 |  812 | 0 |          1 |           3 |                          1 |
| May, 01 2012 01:13:20-0700 |  820 | 0 |          1 |           3 |                          1 |
| May, 01 2012 01:14:20-0700 |  820 | 0 |          1 |           3 |                          1 |
| May, 01 2012 01:15:20-0700 | 1200 | 0 |          0 |           4 |                          0 |

第三，将它们分组，并计算持续时间:

select 
  state_group, idle_state,
  min(timeStamp) as startTime, max(timeStamp) as endTime,
  timestampdiff(second, min(timeStamp), max(timeStamp)) as duration
from
(
  select *,    
    @idle_state := if(rpm between 800 and 900, 1, 0) as idle_state,
    @state_group := @state_group + 
                    if(@idle_state = @prev_state,0,1) as state_group,
    @prev_state := @idle_state
  from (tbl, (select @state_group := 0 as y) as vars)
  order by tbl.timeStamp
) as x
group by state_group, idle_state;

输出:

| STATE_GROUP | IDLE_STATE |                  STARTTIME |                    ENDTIME | DURATION |
|-------------|------------|----------------------------|----------------------------|----------|
|           1 |          1 | May, 01 2012 01:02:56-0700 | May, 01 2012 01:05:55-0700 |      179 |
|           2 |          0 | May, 01 2012 01:07:00-0700 | May, 01 2012 01:10:15-0700 |      195 |
|           3 |          1 | May, 01 2012 01:11:20-0700 | May, 01 2012 01:14:20-0700 |      180 |
|           4 |          0 | May, 01 2012 01:15:20-0700 | May, 01 2012 01:15:20-0700 |        0 |

第四，获取空闲和非空闲计数:

select 

  @idle_count := @idle_count + if(idle_state = 1,1,0) as idle_count,
  @non_idle_count := @non_idle_count + if(idle_state = 0,1,0) as non_idle_count,

  state_group, idle_state,
  min(timeStamp) as startTime, max(timeStamp) as endTime,
  timestampdiff(second, min(timeStamp), max(timeStamp)) as duration
from
(
  select *,        
    @idle_state := if(rpm between 800 and 900, 1, 0) as idle_state,
    @state_group := @state_group + 
                    if(@idle_state = @prev_state,0,1) as state_group,
    @prev_state := @idle_state
  from (tbl, (select @state_group := 0 as y) as vars)
  order by tbl.timeStamp
) as x
,(select @idle_count := 0 as y, @non_idle_count := 0 as z) as vars
group by state_group, idle_state;

输出:

| IDLE_COUNT | NON_IDLE_COUNT | STATE_GROUP | IDLE_STATE |                  STARTTIME |                    ENDTIME | DURATION |
|------------|----------------|-------------|------------|----------------------------|----------------------------|----------|
|          1 |              0 |           1 |          1 | May, 01 2012 01:02:56-0700 | May, 01 2012 01:05:55-0700 |      179 |
|          1 |              1 |           2 |          0 | May, 01 2012 01:07:00-0700 | May, 01 2012 01:10:15-0700 |      195 |
|          2 |              1 |           3 |          1 | May, 01 2012 01:11:20-0700 | May, 01 2012 01:14:20-0700 |      180 |
|          2 |              2 |           4 |          0 | May, 01 2012 01:15:20-0700 | May, 01 2012 01:15:20-0700 |        0 |

最后，删除暂存变量:

select 
  if(idle_state = 1, 
       concat('Idle ', idle_count), 
       concat('NonIdle ', non_idle_count) ) as Period,
  startTime, endTime, duration
from
(

  select 

    @idle_count := @idle_count + if(idle_state = 1,1,0) as idle_count,
    @non_idle_count := @non_idle_count +if(idle_state = 0,1,0) as non_idle_count,

    state_group, idle_state,
    min(timeStamp) as startTime, max(timeStamp) as endTime,
    timestampdiff(second, min(timeStamp), max(timeStamp)) as duration
  from
  (
    select *,        
      @idle_state := if(rpm between 800 and 900, 1, 0) as idle_state,
      @state_group := @state_group + 
                      if(@idle_state = @prev_state,0,1) as state_group,
      @prev_state := @idle_state
    from (tbl, (select @state_group := 0 as y) as vars)
    order by tbl.timeStamp
  ) as x
  ,(select @idle_count := 0 as y, @non_idle_count := 0 as z) as vars
  group by state_group, idle_state

) as summary

输出:

|    PERIOD |                  STARTTIME |                    ENDTIME | DURATION |
|-----------|----------------------------|----------------------------|----------|
|    Idle 1 | May, 01 2012 01:02:56-0700 | May, 01 2012 01:05:55-0700 |      179 |
| NonIdle 1 | May, 01 2012 01:07:00-0700 | May, 01 2012 01:10:15-0700 |      195 |
|    Idle 2 | May, 01 2012 01:11:20-0700 | May, 01 2012 01:14:20-0700 |      180 |
| NonIdle 2 | May, 01 2012 01:15:20-0700 | May, 01 2012 01:15:20-0700 |        0 |

在此处查看查询进度:http://www.sqlfiddle.com/#!2/e9372/1

更新

查询可以缩短http://www.sqlfiddle.com/#!2/418cb/1

如果您注意到，周期数只是串联(idle-nonIdle、idle-nonIdle 等等)。你可以这样做:

select 


  case when idle_state then
     concat('Idle ', @rn := @rn + 1) 
  else
     concat('Non-idle ', @rn )
  end as Period,


  min(timeStamp) as startTime, max(timeStamp) as endTime,

  timestampdiff(second, min(timeStamp), max(timeStamp)) as duration

from
(
  select *,        
  @idle_state := if(rpm between 800 and 900, 1, 0) as idle_state,
  @state_group := @state_group + if(@idle_state = @prev_state,0,1) as state_group,
  @prev_state := @idle_state
  from (tbl, (select @state_group := 0 as y) as vars)
  order by tbl.timeStamp
) as x,
(select @rn := 0) as rx
group by state_group, idle_state

输出:

|     PERIOD |                  STARTTIME |                    ENDTIME | DURATION |
|------------|----------------------------|----------------------------|----------|
|     Idle 1 | May, 01 2012 01:02:56-0700 | May, 01 2012 01:05:55-0700 |      179 |
| Non-idle 1 | May, 01 2012 01:07:00-0700 | May, 01 2012 01:10:15-0700 |      195 |
|     Idle 2 | May, 01 2012 01:11:20-0700 | May, 01 2012 01:14:20-0700 |      180 |
| Non-idle 2 | May, 01 2012 01:15:20-0700 | May, 01 2012 01:15:20-0700 |        0 |

关于值在范围内时的mysql分组，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/10842885/

文章推荐： mysql - 忽略基于 where 子句的列

文章推荐： c# - 关于锁的一些令人困惑的事情

文章推荐： mysql - 需要在 MySQL 中选择日期时间范围

jQuery - "each"范围内？
我有三个 td，并且正在尝试将每个内部的函数限制为仅该 td。我该怎么做呢？使用此代码，它会获取所有 3 个图像并将它们全部插入到 h2 之前: jQuery("td.frontpage_news")
excel - 大约在 Excel 范围内？
这是所需的通用公式:if((b2-b1)=c1,True,False但是，我需要 b2-b1约等于 c1 , 在 5 内大约单位(在本例中为秒)。有没有可以处理这个的函数？最佳答案你也可以试试这个
java - 验证变量在 Java 范围内
我有三个整数，作为命令行参数传入后赋值给变量。我想验证每个整数都在 1-5 范围内。有没有一种方法可以在不使用如下所示的 if 语句的情况下在 Java 中完成此操作？我想避免这样做(注意伪代码):
Javascript:检查一个数字是否在另一个数字的 n 范围内
检查某个变量 X 是否在某个变量 Z 的 n 个数字之内的最简洁方法是什么。n 是任意定义的数字(即 3)。所以我想要 if (z {something} x){ // run code i
Gradle 全局变量不在 buildscript 范围内
我的顶级 build.gradle (Gradle 2.2) 中有类似的东西 ext.repo = "https://my-artifactory-repo" buildscript { re
java - 如何在jsp中将请求对象存储在 session 范围内
我只是在我的 jsp 页面中进行随机技巧和测试。我想使用 Attributes 将 request 范围对象存储在 session 范围对象中。存储后，当尝试从请求属性中提取值(存储在 session
java - 如何将属性值保留在 session 范围内？
我正在使用 Spring 。我有一个外部化属性文件。我正在按如下方式加载它。现在我如何将 session 中的属性作为键值对保存？我尝试编写一个扩展 ServletContextListene
mysql - 获取行数在内连接 rails 范围内
我有以下范围: scope :billable, -> (range_start = nil, range_end = nil) { joins(:bids) .where("au
c# - 如何检测对象是否在 'using' 范围内？
请看我的示例代码: var testObject = new SomeClass(); using (testObject) { //At this point how can the te
c++ - 如何保持在 vector 范围内？
我目前在保持在 vector 范围内时遇到一些问题。在下面的代码中，我试图检查正在检查的数字是否小于或等于它后面的数字 #include #include #include bool fun(
javascript - 迷失在 javascript 范围内
有人可以帮我解决下面的(简化的)代码吗？我试图从幻灯片事件函数中调用 doTheSlide() 函数。我对 JS 范围的理解仍然有点可疑。实现这一目标的正确方法是什么？我收到此错误: Uncaugh
ios - 检查时间是否在 Swift 范围内
如何在 Swift 中检查时间是否在下午 6 点到晚上 11 点之间？我在使用 NSDateFormatter 时遇到了困难，我觉得一定有更简单的方法。最佳答案使用NSCalendar: let
c++ - 将世界保持在视口(viewport)范围内
我目前正在尝试创建一个 2D 横向滚动条，并且我目前有我的“世界”绘图(暂时是一个大白框)，但我无法弄清楚世界地图的边缘与边缘之间的任何关系确保视口(viewport)始终完全被 map 覆盖。我的
c++ - 模板声明不能出现在 block 范围内
我正在学习李普曼，而且我只是在学习。我在这里尝试编写一个代码，该代码将返回 vector 中的最小元素。当我在 Codeblocks 中编译我的代码时，它说:“模板声明不能出现在 block 范围内”
javascript - 范围内 JS 日期对象的百分比
我有三个日期对象。我该如何比较它们才能确定它们之间的相对差异。 oldDate = newDate() - 5; midDate = newDate() - 2.5; newDate = newDat
javascript - 检查变量是否在 Javascript 范围内
我需要检查对象“objCR”是否存在于当前范围内。我尝试使用以下代码。 if(objCR == null) alert("object is not defined"); 让我知道哪里错了。最佳答案
java - 检查ipAddress是否在私有(private)范围内
如何检查 IP 地址是否属于私有(private)类别？ if(isPrivateIPAddress(ipAddress)) { //do something } 如有
javascript - 如何查看用户选择是否在 HTML 范围内
我正在开发一个 Firefox 插件，它可以转换用户通过用户选择突出显示的屏幕温度。转换后，用户选择将替换为 ID 为 alreadyconverted 的 span HTML 元素，其中包含原始温度
Python:有效地检查整数是否在 *many* 范围内
我正在开发一个邮资应用程序，该应用程序需要根据多个邮政编码范围检查整数邮政编码，并根据邮政编码匹配的范围返回不同的代码。每个代码都有多个邮政编码范围。例如，如果邮政编码在 1000-2429、254
excel - 在 Excel 范围内 - 忽略空单元格
我正在使用 excel 范围进行连接:Set rng = Range("A1:A8")如果范围内的单元格之一为空，则会添加一个空格。你如何阻止这个空间被添加？最佳答案假设您在那些非空单元格中有常

可可西里

个人简介

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

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

值在范围内时的mysql分组

工作原理: