SQL按分钟统计分组

javaltzh123 2012-02-02 03:13:25
我有一个表 traffic_data 里面有字段date_time（DATETIME类型）、speed（int类型），里面是每2分钟一条数据，现在想以分钟（4分钟、8分钟、50分钟等等，只要是60以内的偶数分钟都有可能）来进行统计speed。
表结构：
date_time speed
2011-06-23 20:10:00 1
2011-06-23 20:12:00 2
2011-06-23 20:14:00 3
2011-06-23 20:14:00 1
2011-06-23 20:14:00 2
2011-06-23 20:16:00 2

（特别说明：date_time可能有重复的）

如：按4分钟统计，等于大于2011-06-23 20:10:00小于2011-06-23 20:14:00这段时间内speed为3。时间显示为2011-06-23 20:14:00

结果：
date_time speed
2011-06-23 20:14:00 3
2011-06-23 20:18:00 8
......... ....
--------------------------------------------------------------------
另外，我用的是MySql数据库，网上说的一些方法函数MySql貌似不支持，没做出来。本人SQL一塌糊涂，诚心请教高手，万分感谢！
...全文
702 10 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
ACMAIN_CHM 2012-02-10
  • 打赏
  • 举报
 回复
[Quote]mysql> select * from traffic_data;
+-----+---------------------+-------------+-----------+-----------+-------+--------+---------+-------------+-----------+
| irn | date_time | day_of_week | occupancy | flowcount | speed | det_id | link_id | junction_id | region_id |
+-----+---------------------+-------------+-----------+-----------+-------+--------+---------+-------------+-----------+
| 2 | 2011-06-23 20:10:00 | NULL | 1 | 1 | 1 | 1 | 1 | JNC066111 | REG001000 |
| 3 | 2011-06-23 20:12:00 | NULL | 1 | 1 | 1 | 1 | 1 | JNC066111 | REG001000 |
| 4 | 2011-06-23 20:14:00 | NULL | 1 | 1 | 2 | 2 | 2 | JNC066112 | REG001000 |
| 5 | 2011-06-23 20:14:00 | NULL | 1 | 2 | 1 | 2 | 2 | JNC066112 | REG001000 |
| 6 | 2011-06-23 20:16:00 | NULL | 1 | 3 | 1 | 6 | 1 | JNC066112 | REG002000 |
| 7 | 2011-06-23 20:18:00 | NULL | 1 | 1 | 1 | 1 | 2 | JNC066112 | REG002000 |
| 1 | 2011-06-23 20:20:00 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
+-----+---------------------+-------------+-----------+-----------+-------+--------+---------+-------------+-----------+
7 rows in set (0.00 sec)

mysql> select * from traffic_data222;
+-----+---------------------+-------------+-----------+-----------+-------+--------+---------+-------------+-----------+
| irn | date_time | day_of_week | occupancy | flowcount | speed | det_id | link_id | junction_id | region_id |
+-----+---------------------+-------------+-----------+-----------+-------+--------+---------+-------------+-----------+
| 1 | 2011-06-23 20:14:00 | NULL | 1 | 1 | 2 | 1 | 1 | JNC066111 | REG001000 |
| 2 | 2011-06-23 20:18:00 | NULL | NULL | NULL | 4 | NULL | NULL | NULL | NULL |
| 3 | 2011-06-23 20:22:00 | NULL | NULL | NULL | 1 | NULL | NULL | NULL | NULL |
+-----+---------------------+-------------+-----------+-----------+-------+--------+---------+-------------+-----------+
3 rows in set (0.00 sec)

mysql>[/Quote]

1) | 1 | 2011-06-23 20:14:00 | NULL | 1 | 1 | 2 | 1 | 1 | JNC066111 | REG001000 |
这条记录中的数据是怎么来的？det_id=1， link_id=1 这两个字段的计算逻辑是什么？如何得到的？

2) | 2 | 2011-06-23 20:18:00 | NULL | NULL | NULL | 4 | NULL | NULL | NULL | NULL |
这条记录为什么是其它字段是NULL？ speed=4 是怎么得到的？为什么和原表中的| 7 | 2011-06-23 20:18:00 的不一致？

3) 2011-06-23 20:22:00 ，由于原表中并无22分的数据，如果需要显示，则需要另外一张辅助表，或者自行在程序中处理显示。
javaltzh123 2012-02-10
  • 打赏
  • 举报
 回复
SQL语句
-- ----------------------------
-- Table structure for traffic_data
-- ----------------------------
CREATE TABLE `traffic_data` (
`irn` int(11) NOT NULL auto_increment,
`date_time` datetime default NULL,
`day_of_week` smallint(6) default NULL,
`occupancy` smallint(6) default NULL,
`flowcount` smallint(6) default NULL,
`speed` int(11) default NULL,
`det_id` char(16) NOT NULL,
`link_id` char(16) NOT NULL,
`junction_id` char(16) NOT NULL,
`region_id` char(16) NOT NULL,
PRIMARY KEY (`irn`)
) ENGINE=MyISAM AUTO_INCREMENT=41361 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `traffic_data` VALUES ('2', '2011-06-23 20:10:00', null, '1', '1', '1', '1', '1', 'JNC066111', 'REG001000');
INSERT INTO `traffic_data` VALUES ('3', '2011-06-23 20:12:00', null, '1', '1', '1', '1', '1', 'JNC066111', 'REG001000');
INSERT INTO `traffic_data` VALUES ('4', '2011-06-23 20:14:00', null, '1', '1', '2', '2', '2', 'JNC066112', 'REG001000');
INSERT INTO `traffic_data` VALUES ('5', '2011-06-23 20:14:00', null, '1', '2', '1', '2', '2', 'JNC066112', 'REG001000');
INSERT INTO `traffic_data` VALUES ('6', '2011-06-23 20:16:00', null, '1', '3', '1', '6', '1', 'JNC066112', 'REG002000');
INSERT INTO `traffic_data` VALUES ('7', '2011-06-23 20:18:00', null, '1', '1', '1', '1', '2', 'JNC066112', 'REG002000');
INSERT INTO `traffic_data` VALUES ('1', '2011-06-23 20:20:00', '1', '1', '1', '1', '1', '1', '1', '1');


想要结果SQL语句：
CREATE TABLE `traffic_data222` (
`irn` int(11) NOT NULL auto_increment,
`date_time` timestamp NULL default NULL,
`day_of_week` smallint(6) default NULL,
`occupancy` smallint(6) default NULL,
`flowcount` smallint(6) default NULL,
`speed` int(11) default NULL,
`det_id` char(16) default NULL,
`link_id` char(16) default NULL,
`junction_id` char(16) default NULL,
`region_id` char(16) default NULL,
PRIMARY KEY (`irn`)
) ENGINE=MyISAM AUTO_INCREMENT=41361 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `traffic_data222` VALUES ('1', '2011-06-23 20:14:00', null, '1', '1', '2', '1', '1', 'JNC066111', 'REG001000');
INSERT INTO `traffic_data222` VALUES ('2', '2011-06-23 20:18:00', null, null, null, '4', null, null, null, null);
INSERT INTO `traffic_data222` VALUES ('3', '2011-06-23 20:22:00', null, null, null, '1', null, null, null, null);
javaltzh123 2012-02-10
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 as_flex 的回复:]
你只看到第一个就没有去做了吧？
第二个你没有去试试？我做了是没有问题的....
你都没看清楚别人的回答你就否定了，没人愿意给你什么答案了...
还有就是你自己的需求都是那个样子，我好心为你做两层分析，你居然只看了一半，失望...
[/Quote]
select date_time,speed from traffic_data where floor(date_time+0)>20110623201000 and (abs(floor(date_time+0)-now() ) % 400)=0 order by date_time
这条SQL问题出在 (abs(floor(date_time+0)-now() ) % 400)=0 取余的余数都不等于0，所有查询出来的数据为空。
ACMAIN_CHM 2012-02-10
  • 打赏
  • 举报
 回复

建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html

1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样，（并给以简单的算法描述）
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)

这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

javaltzh123 2012-02-10
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 as_flex 的回复:]
你只看到第一个就没有去做了吧？
第二个你没有去试试？我做了是没有问题的....
你都没看清楚别人的回答你就否定了，没人愿意给你什么答案了...
还有就是你自己的需求都是那个样子，我好心为你做两层分析，你居然只看了一半，失望...
[/Quote]

第二个SQL语句我试了，查询结果为空，
select date_time,speed from traffic_data where floor(date_time+0)>20110623201000 and (abs(floor(date_time+0)-now() ) % 400)=0 order by date_time
yjl49 2012-02-09
  • 打赏
  • 举报
 回复
问题描述的不清楚，你想要什么样的结果？
As_flex 2012-02-09
  • 打赏
  • 举报
 回复
你只看到第一个就没有去做了吧？
第二个你没有去试试？我做了是没有问题的....
你都没看清楚别人的回答你就否定了，没人愿意给你什么答案了...
还有就是你自己的需求都是那个样子，我好心为你做两层分析，你居然只看了一半，失望...
javaltzh123 2012-02-07
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 as_flex 的回复:]
其实不是很明白你想要找什么样的内容，不过都给你弄出来了：
1、select date_time,speed from traffic_data where date_time between now()+0 and now()-(4*100)
这里的4就是你要的4分钟，但是建议做成触发器



2、如果你想找出所有的，但是都是每隔4分钟，也就是这些记录都是4分钟才发生的
……
[/Quote]
感谢回复，我试了下，查询出数据位空，继续求教。
javaltzh123 2012-02-07
  • 打赏
  • 举报
 回复
还有人吗
As_flex 2012-02-02
  • 打赏
  • 举报
 回复
其实不是很明白你想要找什么样的内容，不过都给你弄出来了：
1、select date_time,speed from traffic_data where date_time between now()+0 and now()-(4*100)
这里的4就是你要的4分钟，但是建议做成触发器



2、如果你想找出所有的，但是都是每隔4分钟，也就是这些记录都是4分钟才发生的
如：
2011-06-23 20:10:00 1
2011-06-23 20:12:00 2
2011-06-23 20:14:00 3
2011-06-23 20:14:00 1
2011-06-23 20:14:00 2
2011-06-23 20:16:00 2

使用查询后就只有：
2011-06-23 20:10:00 1

2011-06-23 20:14:00 3
2011-06-23 20:14:00 1
2011-06-23 20:14:00 2
这里请注意一定要确定起始时刻，如果没有的话就不行了。因为时间间隔都是相对的概念，刚说的例子就是从2011-06-23 20:10:00为时间基准点的，如果以2011-06-23 20:12:00为基准点的话，结果就是：

2011-06-23 20:12:00 2

2011-06-23 20:16:00 2
结果就完全不一样了....请记得！

select date_time,speed from where floor(date_time+0)>20110623201000 and (abs(floor(date_time+0)-20120202110541 ) % 400)=0 order by date_time

[这里400的意思就是4分钟，以2011-06-23 20:10:00为时间基准点的]

SQL按每5分钟或者10分钟分组统计数据
SQL按每5分钟或者10分钟分组统计数据
mysql按天，小时，半小时，N分钟，分钟进行数据分组统计
mysql按天，小时，半小时，N分钟，分钟进行数据分组统计mysql不同时间粒度下的分组统计按天统计按小时统计按半小时统计按N分钟统计按分钟统计 mysql不同时间粒度下的分组统计 我们在做项目或者数据分析时，经常遇到这样的需求：统计不同时间粒度下的数据分布情况，例如，每一天中每个小时网站的访问量，某路口每半个小时通过的车辆数量等。对于此类的问题，一个sql简单的查询就能实现，故特此记录下，方便以...
SQL按时间分段分组统计数据
sql中按照时间分段分组，显示及统计分段数据，最后获取总行数： 下面sql条件中48表示时间段数（一天48个0.5小时即半小时分段统计，以此类推修改）；--注释了查询条件即只统计错误或统计时间限制（hh24miss）内的数据；最后0和500分别为分页起止数。 查询行数据 select * from (select rownum as rn, a.* from (s...
SQL Server按5分钟分组统计数据
–先把时间分组然后标号 – 分组划分：（小时*60+分钟）/ 分钟值 SELECT ( DATEPART(hh,GETDATE()) * 60 + datepart(mi,GETDATE()) ) /5 例子：把时间分组放到临时表 --创建临时表获取分组区域 CREATE table #tab ( start_time DATETIME, end_time DATETIME, num INT ...
sqlserver 按五分钟分组
SQL 根据注册时间，按指定分钟数 分组进行数据报表统计  create table tb(时间 datetime , 金额 int)   insert into tb values('2007-1-1 10:00:23' ,          8 )  insert into tb values('2007-1-1 10:01:24' ,          4 )  in
MySQL

56,912

社区成员

56,754

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章