求对24小时的时间分布统计的sql

icecools 2014-02-23 08:36:54
我有一张表有2个字段:StartTime 和 EndTime, 记录用户做某件时间开始几点到结束几点
我现在想统计的是出现24小时里每个小时他用了多少分钟,没有的用0, 跨小时的分2个小时算.比如下面的数据
11:30 11:50
12:40 13:05
14:10 14: 20
14:30 14:37

希望出来的结果是
0 0
1 0
.
.
.
11 20
12 20
13 5
14 17
15 0
16 0
.
.
.
就是1个小时之内的要合并,跨小时的希望能分到不同的时间段去,如果没用的不显示0也可以.
感觉这玩意一条sql不太好些,看看大家有些啥想法.谢谢!
我用的是sqlite 但是估计一般语法也都可以.
...全文
599 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
ACMAIN_CHM 2014-02-24
  • 打赏
  • 举报
 回复
需要另外一张表，包括所有的时间段 比如 0:00 , 1:00 2:00 ... 23:00 然后可以通过 left join 生成查询，之后，则可以根据起始时间来统计落在每时间段的时长统计。 建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。 参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382 1. 你的 create table xxx .. 语句 2. 你的 insert into xxx ... 语句 3. 结果是什么样，（并给以简单的算法描述） 4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL) 这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。
wwwwb 2014-02-24
  • 打赏
  • 举报
 回复
11:30 11:50 0 12:40 13:05 1 14:10 14: 20 0 14:30 14:37 0 结果是这样？举例说明
ACMAIN_CHM 2014-02-24
  • 打赏
  • 举报
 回复 
sqlite> select * from hh;
t1|t2
00:00:00|00:59:59
01:00:00|01:59:59
02:00:00|02:59:59
03:00:00|03:59:59
04:00:00|04:59:59
05:00:00|05:59:59
06:00:00|06:59:59
07:00:00|07:59:59
08:00:00|08:59:59
09:00:00|09:59:59
10:00:00|10:59:59
11:00:00|11:59:59
12:00:00|12:59:59
13:00:00|13:59:59
14:00:00|14:59:59
15:00:00|15:59:59
16:00:00|16:59:59
17:00:00|17:59:59
18:00:00|18:59:59
19:00:00|19:59:59
20:00:00|20:59:59
21:00:00|21:59:59
22:00:00|22:59:59
23:00:00|23:59:59
sqlite> select * from UsageHistory;
serial|RecDate|StartTime|EndTime
0|2014-02-17|07:55:00|07:59:34
1|2014-02-17|08:55:00|08:59:34
2|2014-02-17|09:45:00|09:57:34
3|2014-02-17|10:55:00|11:20:34
4|2014-02-17|12:10:00|12:11:34
5|2014-02-17|12:55:00|12:59:34
6|2014-02-17|13:55:00|13:55:34
7|2014-02-17|14:55:00|14:59:34
8|2014-02-17|15:55:00|16:13:34
9|2014-02-17|16:55:00|16:59:34
10|2014-02-17|17:55:00|17:59:34
11|2014-02-17|18:55:00|18:59:34
12|2014-02-17|19:55:00|19:59:34
13|2014-02-18|20:55:00|20:59:34
14|2014-02-18|21:55:00|21:59:34
15|2014-02-18|22:55:00|22:59:34
16|2014-02-18|23:55:00|23:59:34
17|2014-02-18|00:55:00|00:59:34
18|2014-02-18|01:55:00|01:59:34
19|2014-02-18|02:55:00|02:59:34
20|2014-02-18|03:55:00|03:59:34
21|2014-02-18|04:55:00|04:59:34
22|2014-02-18|05:55:00|05:59:34
23|2014-02-18|06:55:00|06:59:34
24|2014-02-19|07:55:00|07:59:34
25|2014-02-19|08:55:00|08:59:34
26|2014-02-19|09:55:00|09:59:34
27|2014-02-19|10:55:00|10:59:34
28|2014-02-19|11:55:00|11:59:34
29|2014-02-19|12:55:00|12:59:34
30|2014-02-19|13:55:00|13:59:34
31|2014-02-19|14:55:00|14:59:34
sqlite>

sqlite> select t1,
   ...>         sum(strftime('%s', min( hh.t2, EndTime ))-strftime('%s', max(hh.t1, StartTime)))/60.0 as d
   ...> from hh left join UsageHistory u on hh.t1<u.EndTime and hh.t2>u.StartTime
   ...> group by t1;
t1|d
00:00:00|4.56666666666667
01:00:00|4.56666666666667
02:00:00|4.56666666666667
03:00:00|4.56666666666667
04:00:00|4.56666666666667
05:00:00|4.56666666666667
06:00:00|4.56666666666667
07:00:00|9.13333333333333
08:00:00|9.13333333333333
09:00:00|17.1333333333333
10:00:00|9.55
11:00:00|25.1333333333333
12:00:00|10.7
13:00:00|5.13333333333333
14:00:00|9.13333333333333
15:00:00|4.98333333333333
16:00:00|18.1333333333333
17:00:00|4.56666666666667
18:00:00|4.56666666666667
19:00:00|4.56666666666667
20:00:00|4.56666666666667
21:00:00|4.56666666666667
22:00:00|4.56666666666667
23:00:00|4.56666666666667
sqlite>
suek225 2014-02-24
  • 打赏
  • 举报
 回复
用SQLServer写几行，给你个思路，加个时间段的表 
create table A(s TIME ,  e TIME)
GO
insert into A 
VALUES ('11:30','11:50'),( '12:40','13:05'),( '14:10','14: 20'),( '14:30','14:37')

CREATE TABLE B (h INT,tm1 TIME,tm2 time)
GO
INSERT B VALUES(0,'0:00','1:00'),(1,'1:00','2:00'),(2,'2:00','3:00'),
	(3,'3:00','4:00'),(4,'4:00','5:00'),(5,'5:00','6:00'),(6,'6:00','7:00'),
	(7,'7:00','8:00'),(8,'8:00','9:00')
	,(9,'9:00','10:00'),(10,'10:00','11:00'),(11,'11:00','12:00'),(12,'12:00','13:00'),
	(13,'13:00','14:00'),(14,'14:00','15:00'),(15,'15:00','16:00')
GO
SELECT * FROM dbo.a,b WHERE a.e>b.tm1 AND a.s<b.tm2
结果 s e h tm1 tm2 11:30:00.0000000 11:50:00.0000000 11 11:00:00.0000000 12:00:00.0000000 12:40:00.0000000 13:05:00.0000000 12 12:00:00.0000000 13:00:00.0000000 12:40:00.0000000 13:05:00.0000000 13 13:00:00.0000000 14:00:00.0000000 14:10:00.0000000 14:20:00.0000000 14 14:00:00.0000000 15:00:00.0000000 14:30:00.0000000 14:37:00.0000000 14 14:00:00.0000000 15:00:00.0000000 到了这一步，跨小时的问题就解决了，只要不是跨天就行 剩下的计算问题就简单了吧，数据库不一样函数也不同我就不写了
icecools 2014-02-24
  • 打赏
  • 举报
 回复
引用 2 楼 ACMAIN_CHM 的回复:
需要另外一张表，包括所有的时间段 比如 0:00 , 1:00 2:00 ... 23:00 然后可以通过 left join 生成查询，之后，则可以根据起始时间来统计落在每时间段的时长统计。 建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。 参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382 1. 你的 create table xxx .. 语句 2. 你的 insert into xxx ... 语句 3. 结果是什么样，（并给以简单的算法描述） 4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL) 这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。
整理了一下,数据库是sqlite 3, 现在就一个表,是记录用户行为的表,下面有一些数据,现在是想把某一天用户24小时每个小时的用时给列出来. 不知道一个sql能不能写出来, 因为有的startTime和endTime跨几个小时,要把它分到所属的小时去. CREATE TABLE [UsageHistory] ( [serial] integer PRIMARY KEY AUTOINCREMENT, [RecDate] Date DEFAULT NULL, [StartTime] Time DEFAULT NULL, [EndTime] TIME); insert into UsageHistory values ( 0, '2014-02-17', '07:55:00', '07:59:34' ); insert into UsageHistory values ( 1, '2014-02-17', '08:55:00', '08:59:34' ); insert into UsageHistory values ( 2, '2014-02-17', '09:45:00', '09:57:34' ); insert into UsageHistory values ( 3, '2014-02-17', '10:55:00', '11:20:34' ); insert into UsageHistory values ( 4, '2014-02-17', '12:10:00', '12:11:34' ); insert into UsageHistory values ( 5, '2014-02-17', '12:55:00', '12:59:34' ); insert into UsageHistory values ( 6, '2014-02-17', '13:55:00', '13:55:34' ); insert into UsageHistory values ( 7, '2014-02-17', '14:55:00', '14:59:34' ); insert into UsageHistory values ( 8, '2014-02-17', '15:55:00', '16:13:34' ); insert into UsageHistory values ( 9, '2014-02-17', '16:55:00', '16:59:34' ); insert into UsageHistory values ( 10, '2014-02-17', '17:55:00', '17:59:34' ); insert into UsageHistory values ( 11, '2014-02-17', '18:55:00', '18:59:34' ); insert into UsageHistory values ( 12, '2014-02-17', '19:55:00', '19:59:34' ); insert into UsageHistory values ( 13, '2014-02-18', '20:55:00', '20:59:34' ); insert into UsageHistory values ( 14, '2014-02-18', '21:55:00', '21:59:34' ); insert into UsageHistory values ( 15, '2014-02-18', '22:55:00', '22:59:34' ); insert into UsageHistory values ( 16, '2014-02-18', '23:55:00', '23:59:34' ); insert into UsageHistory values ( 17, '2014-02-18', '00:55:00', '00:59:34' ); insert into UsageHistory values ( 18, '2014-02-18', '01:55:00', '01:59:34' ); insert into UsageHistory values ( 19, '2014-02-18', '02:55:00', '02:59:34' ); insert into UsageHistory values ( 20, '2014-02-18', '03:55:00', '03:59:34' ); insert into UsageHistory values ( 21, '2014-02-18', '04:55:00', '04:59:34' ); insert into UsageHistory values ( 22, '2014-02-18', '05:55:00', '05:59:34' ); insert into UsageHistory values ( 23, '2014-02-18', '06:55:00', '06:59:34' ); insert into UsageHistory values ( 24, '2014-02-19', '07:55:00', '07:59:34' ); insert into UsageHistory values ( 25, '2014-02-19', '08:55:00', '08:59:34' ); insert into UsageHistory values ( 26, '2014-02-19', '09:55:00', '09:59:34' ); insert into UsageHistory values ( 27, '2014-02-19', '10:55:00', '10:59:34' ); insert into UsageHistory values ( 28, '2014-02-19', '11:55:00', '11:59:34' ); insert into UsageHistory values ( 29, '2014-02-19', '12:55:00', '12:59:34' ); insert into UsageHistory values ( 30, '2014-02-19', '13:55:00', '13:59:34' ); insert into UsageHistory values ( 31, '2014-02-19', '14:55:00', '14:59:34' );
icecools 2014-02-24
  • 打赏
  • 举报
 回复
引用 4 楼 wwwwb 的回复:
需要1个表保存24小时，再与工作表连接处理
谢谢版主,假设有个hours表好了,但是我感觉问题难在跨时间上,比如 12:40 - 13:05 这个其实应该是算12点有20分钟,13点有5分钟,当然如果13点这个段后面有还有记录的的话(比如13:30-13:40) 那么13点这个就应该是5 + 10等于15分钟了.
wwwwb 2014-02-24
  • 打赏
  • 举报
 回复
需要1个表保存24小时，再与工作表连接处理
icecools 2014-02-24
  • 打赏
  • 举报
 回复
引用 1 楼 wwwwb 的回复:
11:30 11:50 0 12:40 13:05 1 14:10 14: 20 0 14:30 14:37 0 结果是这样？举例说明
不是的,结果是24行,就是0到23点,在上面的雷子里就是11点这行是20分钟,12点这行是20分钟,13点是5分钟,没有的比如23点就是0. 就是看一个人24个小时每小时工作了多少分钟.
记录sql使用，按照小时统计24小时数据
按照时间、月份统计数据，固定返回24行、12行，没有数据时值为0
SQL语句，根据时间段统计
SQL语句根据时间段统计，比如七天的数据划分这24小时，00:00-01:00,01:00-02:00,02:00-03:00,03:00-04:00…23:00-00:00。 先看sql语句 SELECT strftime('%H:00', field_name) AS time, count(field_name) AS num FROM table_name group by strftime('%H:00',field_na
sql 按时间段统计数据个数
sql 按时间段统计数据个数
ORACLE 将数据按照一天中的24小时进行次数的统计
ORACLE 将数据按照一天中的24小时进行次数的统计 #首先我的数据表是这样的 表中有开始时间(start_time)和结束时间(end_time) , 我要做的是查询一个日期时间段内(例如2019-10-01 00:00:00至2019-10-31 23:59:59) 的数据 然后再按照一天24小时,统计每条数据的开始和结束的时间段在24小时中的分布情况,最后已表中的000002为分...
mysql配合java代码按每两小时为一个时间区间统计数据
最近有个需求，是这样的： 当时看到这个需求的第一反应是挺简单的，应该一条sql就能统计完，后面发现真正实现的时候，用sql会面临一些些问题，比如说在某一些时间段，可能会没有数据，这样就导致某一些时间段统计出来可能没有数据。又因为时间比较紧，懒得折腾，所以先采用java + sql的方式来统计，后面有时间再来研究研究纯sql统计。 根据时间（小时统计出每个小时的扫码量） --查询当日的亮码统计 SELECT count(*) AS num, date_format( insert_time, '%
MySQL

56,687

社区成员

56,710

社区内容

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

加载中

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

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

+ 用AI写文章