用于统计的SQL语句求解

Question

遇到这样一道题：一个JAVA数据库程序 实现对如下的一张表进行统计分析有这样一张日志表：
唯一编号    时间                     值
...
14          2010-05-10 08:10:00.0    5
15          2010-05-10 08:11:20.0    10
16          2010-05-10 08:12:00.0    46
17          2010-05-10 08:13:10.0    17
18          2010-05-10 08:13:20.0    20
19          2010-05-10 08:13:35.0    11
20          2010-05-10 08:15:11.0    7 
21          2010-05-10 08:19:22.0    31
22          2010-05-10 08:19:23.0    4
23          2010-05-10 08:19:50.0    6
24          2010-05-10 08:20:00.0    54
25          2010-05-10 08:23:00.0    34
26          2010-05-10 08:25:09.0    6
27          2010-05-10 08:25:14.0    9
...现在想按时间段来统计一段时间内有多少条日志 并对这些日志的值求和
比如设定时间段为5分钟 给定起始时间2010-05-10 08:00:00 终止时间2010-05-10 08:20:00 预期效果
起始时间（大于等于）   终止时间(小于)         条数 和
2010-05-10 08:00:00    2010-05-10 08:05:00    0    0
2010-05-10 08:05:00    2010-05-10 08:10:00    0    0
2010-05-10 08:10:00    2010-05-10 08:15:00    6    109
2010-05-10 08:15:00    2010-05-10 08:20:00    4    48我只回答了在程序中用循环做。现在想请问下各位大虾2个问题：1、对于这样一张只有3列的表（表中记录很多，往千万、亿条上考虑），唯一编号列是自增字段，对时间列加索引是否可行，能否提高查询效率（因为查询日志的时候肯定是给定一个起始时间，一个终止时间，所以唯一编号几乎用不上）？2、能否不通过程序逻辑，直接用select语句实现这样的查找结果？

Answer 1

显然创建这个基于时间的索引！如果你的SQL语句是 select ... from .... where  时间 between '2010-05-10 08:00:00' and '2010-05-10 08:20:00'的话理论可以，但反而效果不好，因为你的记录会有0的统计结果。这种仅一张表一个SQL语句无法实现。 当然会有人建议添加一个临时表或者日历时间表，但对你这个需求并不适应。比较高效的一种做法：select 时间-interval second(时间) second - interval (minute(时间)+1)%5-1 minute,count(*),sum(值)
from 有这样一张日志表 
where 时间 between '2010-05-10 08:00:00' and '2010-05-10 08:20:00'
group by 时间-interval second(时间) second - interval (minute(时间)+1)%5-1 minute然后利用程序把0补齐。

Answer 2

日志类表最好最好显式建议使用时间索引。SELECT ... FROM table USE INDEX (index_name) WHERE ... 甚至是
SELECT ... FROM table FORCE INDEX (index_name) WHERE ...有时候MySQL可能会选用其他索引（你这个例子可能字段少不一定会出现）。但是这个时间索引显然更快。因为相同时间访问日志的数据肯定都是连在一起的行。即使在索引之后仍要扫描，相对它的速度也会快一些。当然上面的内容与2的语句没有太大帮助。

Answer 3

1、在时间字段上建立索引；
2、建立一个临时表，字段
BEGTIME ENDTIME开始时间 、结束时间
用SP OR 程序生成
时间段为5(N)分钟的所有记录，再与工作表连接。

Answer 4

(minute(时间)+1)%5-1 没看明白 希望能解释下为什么+1-1另外对于这样的SQL 如果想按照100分钟分段 似乎就不太好算了 

Answer 5

(minute(时间)+1)%5-1 没看明白 希望能解释下为什么+1-1:
简单地说，就是将时间
2010-05-10 08:13:10.0
变成
2010-05-10 08:10:00

Answer 6

minute(时间)%5 不就可以了么 为什么要 +1 再-1 

Answer 7

你在EXCEL中把 00 - 59 都列出来，然后算一下就明白了。

Answer 8

但是 我拿机器人模拟插入了一定量的记录 尝试了下
结果 似乎应该是minute(时间)%5才对 +1再-1分组出来的结果不对 所以我才会有这样的疑问
我用类似如下的SQL进行的验证：
select '2010-05-10 08:00:00' as 起始时间, count(*) from 有这样一张日志表 where 时间 >='2010-05-10 08:00:00' and 时间 < '2010-05-10 08:05:00'
union
select '2010-05-10 08:05:00' as 起始时间, count(*) from 有这样一张日志表 where 时间 >='2010-05-10 08:05:00' and 时间 < '2010-05-10 08:10:00'
union
select '2010-05-10 08:10:00' as 起始时间, count(*) from 有这样一张日志表 where 时间 >='2010-05-10 08:10:00' and 时间 < '2010-05-10 08:15:00'
union
select '2010-05-10 08:15:00' as 起始时间, count(*) from 有这样一张日志表 where 时间 >='2010-05-10 08:15:00' and 时间 < '2010-05-10 08:20:00'
union
select '2010-05-10 08:20:00' as 起始时间, count(*) from 有这样一张日志表 where 时间 >='2010-05-10 08:20:00' and 时间 < '2010-05-10 08:25:00'

Answer 9

可以实现每100分钟，或者每40分钟进行统计。建议你能提供测试环境。
   建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
   参考一下这个贴子的提问方式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)
   
   这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。   

Answer 10

关于(minute(时间)+1)%5-1的问题， 可以简单的计算一下07:59:00~08:03:00 计算后是 08:00:00 
08:04:00~08:08:00 计算后是 08:05:00 
08:09:00~08:13:00 计算后是 08:10:00 
...也就是说查询结果中显示出来的时间 
所代表的时间段是：大于等于该时间-1分钟 小于该时间+N-1分钟
而不是：          大于等于该时间       小于该时间+N分钟这个预期结果我在问题中应该有所描述当然这个问题不是很重要，我问这样的问题也只是求一个解题思路。对于时间段不固定的问题我也是看了给出的SQL之后 临时想到的
我参照这个思路重写了类似如下的SQL，欢迎讨论指正首先将时间段N在程序中换算成秒数M
select 
  FROM_UNIXTIME(UNIX_TIMESTAMP(时间) - UNIX_TIMESTAMP(时间)%M) as 起始时间,
  FROM_UNIXTIME(UNIX_TIMESTAMP(时间) - UNIX_TIMESTAMP(时间)%M + M) as 终止时间,
  count(*),
  sum(值)
from 有这样一张日志表 
where 时间 >= '2010-05-10 08:00:00' and 时间 < '2010-05-10 08:20:00'
group by 起始时间

Answer 11

关于(minute(时间)+1)%5-1的问题， 可以简单的计算一下07:59:00~08:03:00 计算后是 08:00:00 
08:04:00~08:08:00 计算后是 08:05:00 
08:09:00~08:13:00 计算后是 08:10:00 
...也就是说查询结果中显示出来的时间 
所代表的时间段是：大于等于该时间-1分钟 小于该时间+N-1分钟
而不是：          大于等于该时间       小于该时间+N分钟这个预期结果我在问题中应该有所描述当然这个问题不是很重要，我问这样的问题也只是求一个解题思路。对于时间段不固定的问题我也是看了给出的SQL之后 临时想到的
我参照这个思路重写了类似如下的SQL，欢迎讨论指正首先将时间段N在程序中换算成秒数M
select 
  FROM_UNIXTIME(UNIX_TIMESTAMP(时间) - UNIX_TIMESTAMP(时间)%M) as 起始时间,
  FROM_UNIXTIME(UNIX_TIMESTAMP(时间) - UNIX_TIMESTAMP(时间)%M + M) as 终止时间,
  count(*),
  sum(值)
from 有这样一张日志表 
where 时间 >= '2010-05-10 08:00:00' and 时间 < '2010-05-10 08:20:00'
group by 起始时间

Answer 12

没看明白。建议楼主换个方法，直接给出你的 create table xx, insert into .. 语句给出测试数据，这样别人可以直接在自己的机器创建与你相同的环境。参考一下这个贴子 http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html

Answer 13

没看明白。建议楼主换个方法，直接给出你的 create table xx, insert into .. 语句给出测试数据，这样别人可以直接在自己的机器创建与你相同的环境。参考一下这个贴子 http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html

Answer 14

MYSQL版本5.1.42 WINDOWS平台drop table if exists logtable;
create table logtable (
  dwLogId int(1) unsigned not null auto_increment,
  strLogTime datetime not null,
  dwValue int(1) unsigned not null,
  primary key (dwLogId),
  key strLogTime(strLogTime)
)engine=MyISAM default charset=utf8;insert into logtable(strLogTime,dwValue)
values
('2010-05-12 08:10:00', 5),
('2010-05-12 08:11:20', 10),
('2010-05-12 08:12:00', 46),
('2010-05-12 08:13:10', 17),
('2010-05-12 08:13:20', 20),
('2010-05-12 08:13:35', 11),
('2010-05-12 08:15:11', 7),  
('2010-05-12 08:19:22', 31),
('2010-05-12 08:19:23', 4),
('2010-05-12 08:19:50', 6),
('2010-05-12 08:20:00', 54),
('2010-05-12 08:23:00', 34),
('2010-05-12 08:25:09', 6),
('2010-05-12 08:25:14', 9);按照
select 
  strLogTime - interval second(strLogTime) second - interval (minute(strLogTime)+1)%5-1 minute,
  count(*),
  sum(dwValue)
from logtable
where strLogTime between '2010-05-12 08:10:00' and '2010-05-12 08:25:00'
group by strLogTime-interval second(strLogTime) second - interval (minute(strLogTime)+1)%5-1 minute;其结果与如下SQL相同
select '2010-05-12 08:10:00', count(*), sum(dwValue) from logtable where strLogTime>='2010-05-12 08:09:00' and strLogTime<'2010-05-12 08:14:00'
union
select '2010-05-12 08:15:00', count(*), sum(dwValue) from logtable where strLogTime>='2010-05-12 08:14:00' and strLogTime<'2010-05-12 08:19:00'
union
select '2010-05-10 08:20:00', count(*), sum(dwValue) from logtable where strLogTime>='2010-05-12 08:19:00' and strLogTime<'2010-05-12 08:24:00'
按照
select  
  FROM_UNIXTIME(UNIX_TIMESTAMP(strLogTime) - UNIX_TIMESTAMP(strLogTime)%M) as 起始时间,
  FROM_UNIXTIME(UNIX_TIMESTAMP(strLogTime) - UNIX_TIMESTAMP(strLogTime)%M + M) as 终止时间,
  count(*),
  sum(dwValue)
from logtable  
where strLogTime >= '2010-05-12 08:10:00' and strLogTime < '2010-05-12 08:25:00'
group by 起始时间其结果与如下SQL相同
select '2010-05-12 08:10:00', count(*), sum(dwValue) from logtable where strLogTime>='2010-05-12 08:10:00' and strLogTime<'2010-05-12 08:15:00'
union
select '2010-05-12 08:15:00', count(*), sum(dwValue) from logtable where strLogTime>='2010-05-12 08:15:00' and strLogTime<'2010-05-12 08:20:00'
union
select '2010-05-10 08:20:00', count(*), sum(dwValue) from logtable where strLogTime>='2010-05-12 08:20:00' and strLogTime<'2010-05-12 08:25:00'

Answer 15

mysql> select * from logtable;
+---------+---------------------+---------+
| dwLogId | strLogTime          | dwValue |
+---------+---------------------+---------+
|       1 | 2010-05-12 08:10:00 |       5 |
|       2 | 2010-05-12 08:11:20 |      10 |
|       3 | 2010-05-12 08:12:00 |      46 |
|       4 | 2010-05-12 08:13:10 |      17 |
|       5 | 2010-05-12 08:13:20 |      20 |
|       6 | 2010-05-12 08:13:35 |      11 |
|       7 | 2010-05-12 08:15:11 |       7 |
|       8 | 2010-05-12 08:19:22 |      31 |
|       9 | 2010-05-12 08:19:23 |       4 |
|      10 | 2010-05-12 08:19:50 |       6 |
|      11 | 2010-05-12 08:20:00 |      54 |
|      12 | 2010-05-12 08:23:00 |      34 |
|      13 | 2010-05-12 08:25:09 |       6 |
|      14 | 2010-05-12 08:25:14 |       9 |
+---------+---------------------+---------+
14 rows in set (0.00 sec)你期望的结果是什么？+---------------------+----------+--------------+
| 2010-05-12 08:10:00 | count(*) | sum(dwValue) |
+---------------------+----------+--------------+
| 2010-05-12 08:10:00 |        6 |          109 |
| 2010-05-12 08:15:00 |        1 |            7 |
| 2010-05-10 08:20:00 |        5 |          129 |
+---------------------+----------+--------------+
还是
+---------------------+----------+--------------+
| 2010-05-12 08:10:00 | count(*) | sum(dwValue) |
+---------------------+----------+--------------+
| 2010-05-12 08:10:00 |        6 |          109 |
| 2010-05-12 08:15:00 |        4 |           48 |
| 2010-05-10 08:20:00 |        2 |           88 |
+---------------------+----------+--------------+上现两个哪个是正确结果？？？另外你不是还要按100 ,2000的分吗？ 建议一次性把需求都贴出来。问题说明越详细，回答也会越准确！参见如何提问。（提问的智慧）

Answer 16

当然 现在 为0的查不出来 自然也就没有 
也就是结果应该为
2010-05-12 08:10:00 2010-05-12 08:15:00 6 109
2010-05-12 08:15:00 2010-05-12 08:20:00 4 48
另外 我自己上面两条验证SQL最后一条写错了select '2010-05-12 08:10:00', count(*), sum(dwValue) from logtable where strLogTime>='2010-05-12 08:09:00' and strLogTime<'2010-05-12 08:14:00'
union
select '2010-05-12 08:15:00', count(*), sum(dwValue) from logtable where strLogTime>='2010-05-12 08:14:00' and strLogTime<'2010-05-12 08:19:00'
union
select '2010-05-12 08:20:00', count(*), sum(dwValue) from logtable where strLogTime>='2010-05-12 08:19:00' and strLogTime<'2010-05-12 08:24:00'
select '2010-05-12 08:10:00', count(*), sum(dwValue) from logtable where strLogTime>='2010-05-12 08:10:00' and strLogTime<'2010-05-12 08:15:00'
union
select '2010-05-12 08:15:00', count(*), sum(dwValue) from logtable where strLogTime>='2010-05-12 08:15:00' and strLogTime<'2010-05-12 08:20:00'
union
select '2010-05-12 08:20:00', count(*), sum(dwValue) from logtable where strLogTime>='2010-05-12 08:20:00' and strLogTime<'2010-05-12 08:25:00'
比如对于100分钟 也就是M=6000秒select   
  FROM_UNIXTIME(UNIX_TIMESTAMP(strLogTime) - UNIX_TIMESTAMP(strLogTime)%6000) as 起始时间,
  FROM_UNIXTIME(UNIX_TIMESTAMP(strLogTime) - UNIX_TIMESTAMP(strLogTime)%6000 + 6000) as 终止时间,
  count(*),
  sum(dwValue)
from logtable   
where strLogTime >= '2010-05-12 08:10:00' and strLogTime < '2010-05-12 08:25:00'
group by 起始时间

Answer 17

猜来猜去！为什么楼主不能直接回答你要的结果是什么样？
比如你可以直接说。当输入5分钟的时候结果应该如下：
+---------------------+---------------------+----------+--------------+
| k1                  | k2                  | count(*) | sum(dwValue) |
+---------------------+---------------------+----------+--------------+
| 2010-05-12 08:10:00 | 2010-05-12 08:15:00 |        6 |          109 |
| 2010-05-12 08:15:00 | 2010-05-12 08:20:00 |        4 |           48 |
| 2010-05-12 08:20:00 | 2010-05-12 08:25:00 |        2 |           88 |
| 2010-05-12 08:25:00 | 2010-05-12 08:30:00 |        2 |           15 |
+---------------------+---------------------+----------+--------------+
如果你要的结果如上的话，可以用这个ＳＱＬ语句！mysql> set @t1='2010-05-10 08:00:00';
Query OK, 0 rows affected (0.00 sec)mysql> set @t2='2010-05-10 08:20:00';
Query OK, 0 rows affected (0.00 sec)mysql> set @i=5*60;
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> select
    ->  FROM_UNIXTIME(((UNIX_TIMESTAMP(strLogTime)-UNIX_TIMESTAMP(@t1)) div @i)*@i+UNIX_TIMESTAMP(@t1)) as k1,
    ->  FROM_UNIXTIME(((UNIX_TIMESTAMP(strLogTime)-UNIX_TIMESTAMP(@t1)) div @i)*@i+UNIX_TIMESTAMP(@t1)+@i) as k2,
    ->  count(*),sum(dwValue)
    -> from logtable
    -> group by k1,k2;
+---------------------+---------------------+----------+--------------+
| k1                  | k2                  | count(*) | sum(dwValue) |
+---------------------+---------------------+----------+--------------+
| 2010-05-12 08:10:00 | 2010-05-12 08:15:00 |        6 |          109 |
| 2010-05-12 08:15:00 | 2010-05-12 08:20:00 |        4 |           48 |
| 2010-05-12 08:20:00 | 2010-05-12 08:25:00 |        2 |           88 |
| 2010-05-12 08:25:00 | 2010-05-12 08:30:00 |        2 |           15 |
+---------------------+---------------------+----------+--------------+
4 rows in set (0.00 sec)mysql>
再友情提醒一次，请楼主看一下这个贴子的提问方法。
   参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
   

Answer 18

貌似我在问题中 给出了24条记录 并且对于这24条记录的预期结果有如下描述：比如设定时间段为5分钟 给定起始时间2010-05-10 08:00:00 终止时间2010-05-10 08:20:00 预期效果
起始时间（大于等于） 终止时间(小于) 条数 和
2010-05-10 08:00:00 2010-05-10 08:05:00 0 0
2010-05-10 08:05:00 2010-05-10 08:10:00 0 0
2010-05-10 08:10:00 2010-05-10 08:15:00 6 109
2010-05-10 08:15:00 2010-05-10 08:20:00 4 48我不知道这一段是没显示出来还是怎么回事
导致你总是问我 我期望的输出结果什么 还是说必须要SQL code引用上的才算我的需求结果
如果是这样的话 我很抱歉 我这电脑上没有装MYSQL 这是一台仅供上网使用的电脑 以上所有数据和SQL都是手敲的 可能你看的不是很习惯 我实在没法像你那样很方便的去复制粘贴

Answer 19

mysql> select * from logtable;
+---------+---------------------+---------+
| dwLogId | strLogTime          | dwValue |
+---------+---------------------+---------+
|       1 | 2010-05-12 08:10:00 |       5 |
|       2 | 2010-05-12 08:11:20 |      10 |
|       3 | 2010-05-12 08:12:00 |      46 |
|       4 | 2010-05-12 08:13:10 |      17 |
|       5 | 2010-05-12 08:13:20 |      20 |
|       6 | 2010-05-12 08:13:35 |      11 |
|       7 | 2010-05-12 08:15:11 |       7 |
|       8 | 2010-05-12 08:19:22 |      31 |
|       9 | 2010-05-12 08:19:23 |       4 |
|      10 | 2010-05-12 08:19:50 |       6 |
|      11 | 2010-05-12 08:20:00 |      54 |
|      12 | 2010-05-12 08:23:00 |      34 |
|      13 | 2010-05-12 08:25:09 |       6 |
|      14 | 2010-05-12 08:25:14 |       9 |
+---------+---------------------+---------+
14 rows in set (0.00 sec)mysql> set @t1='2010-05-12 08:00:00';
Query OK, 0 rows affected (0.00 sec)mysql> set @t2='2010-05-12 08:20:00';
Query OK, 0 rows affected (0.00 sec)mysql> set @i=5*60;
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> select
    ->  FROM_UNIXTIME(((UNIX_TIMESTAMP(strLogTime)-UNIX_TIMESTAMP(@t1)) div @i)*@i+UNIX_TIMESTAMP(@t1)) as k1,
    ->  FROM_UNIXTIME(((UNIX_TIMESTAMP(strLogTime)-UNIX_TIMESTAMP(@t1)) div @i)*@i+UNIX_TIMESTAMP(@t1)+@i) as k2,
    ->  count(*),sum(dwValue)
    -> from logtable
    -> where strLogTime>=@t1 and strLogTime<@t2
    -> group by k1,k2;
+---------------------+---------------------+----------+--------------+
| k1                  | k2                  | count(*) | sum(dwValue) |
+---------------------+---------------------+----------+--------------+
| 2010-05-12 08:10:00 | 2010-05-12 08:15:00 |        6 |          109 |
| 2010-05-12 08:15:00 | 2010-05-12 08:20:00 |        4 |           48 |
+---------------------+---------------------+----------+--------------+
2 rows in set (0.00 sec)mysql>为0的记录用程序或者存储过程补齐。