首页 / MYSQL / 当值在一个范围内时,mysql分组
当值在一个范围内时,mysql分组
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了当值在一个范围内时,mysql分组,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含10417字,纯文字阅读大概需要15分钟。
内容图文
我搜索了高低,似乎无法找到有关如何处理我的查询的任何信息.如果我问一个愚蠢的问题,我会提前道歉,但我真的需要一些帮助.
我有一系列以不同间隔记录的值.数据如下所示:
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
在DB端执行此操作的优点是,您不仅可以在PHP上使用查询,还可以在Java,C#,Python等上使用它.并且它在DB端快速执行
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
UPDATE
查询可以缩短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分组全部内容,希望文章能够帮你解决当值在一个范围内时,mysql分组所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。