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

Question

需要另外一张表，包括所有的时间段 比如 
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)
   
   这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

Answer 1

谢谢版主,假设有个hours表好了,但是我感觉问题难在跨时间上,比如
12:40 - 13:05 这个其实应该是算12点有20分钟,13点有5分钟,当然如果13点这个段后面有还有记录的的话(比如13:30-13:40) 那么13点这个就应该是5 + 10等于15分钟了.

Answer 2

整理了一下,数据库是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' );

Answer 3

用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到了这一步，跨小时的问题就解决了，只要不是跨天就行
剩下的计算问题就简单了吧，数据库不一样函数也不同我就不写了

Answer 4

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>