发一个小题目

Question

表名称 table_a
表结构
单号 doccode varchar(20)
操作 operation varchar(50)
操作时间 operDate  datetime一个单据有多种操作,比如说创建单据,请求审核,审核完成,确认单据等等.
现在要求统计,2010-08-01至2010-08-02,每相邻的半小时内某操作的平均数量.注意这里的半小时并不是指自然时间的半小时,而是单据的操作时间间隔再明确点,例如有如下数据doccode                 operation  operDate  
RW20100801000002 请求审核 2010-08-01 09:29:58 
RW20100801000001 请求审核 2010-08-01 09:33:13 
GH20100731000022 请求审核 2010-08-01 09:34:25 
GH20100731000020 请求审核 2010-08-01 09:35:04 
GH20100731000019 请求审核 2010-08-01 09:35:28 
GH20100731000018 请求审核 2010-08-01 09:35:45 
RW20100731000267 请求审核 2010-08-01 09:51:18 
RW20100801000003 请求审核 2010-08-01 09:54:14 
RW20100801000005 请求审核 2010-08-01 10:04:59 
RW20100801000006 请求审核 2010-08-01 10:06:39 
RW20100801000004 请求审核 2010-08-01 10:09:07 
RW20100801000011 请求审核 2010-08-01 10:16:2409:29:58.760~10:04:59.773 半小时内有9个
09:35:28.720~10:06:39.590 半小时内有7个--复制以下代码生成表结构及数据
--创建表
Create Table table_a(
 doccode  varchar(20),
 operation varchar(50),
 operDate   datetime)
--生成数据
Insert into Table_a 
Select 'RW20100801000002','请求审核','2010-08-01 09:29:58'
Union ALL
Select 'RW20100801000001','请求审核','2010-08-01 09:33:13'
Union ALL
Select 'GH20100731000022','请求审核','2010-08-01 09:34:25'
Union ALL
Select 'GH20100731000020','请求审核','2010-08-01 09:35:04'
Union ALL
Select 'GH20100731000019','请求审核','2010-08-01 09:35:28'
Union ALL
Select 'GH20100731000018','请求审核','2010-08-01 09:35:45'
Union ALL
Select 'GH20100731000017','请求审核','2010-08-01 09:36:00'
Union ALL
Select 'GH20100731000016','请求审核','2010-08-01 09:36:20'
Union ALL
Select 'GH20100731000015','请求审核','2010-08-01 09:38:47'
Union ALL
Select 'RW20100731000267','请求审核','2010-08-01 09:51:18'
Union ALL
Select 'RW20100801000003','请求审核','2010-08-01 09:54:14'
Union ALL
Select 'RW20100801000005','请求审核','2010-08-01 10:04:59'
Union ALL
Select 'RW20100801000006','请求审核','2010-08-01 10:06:39'
Union ALL
Select 'RW20100801000004','请求审核','2010-08-01 10:09:07'
Union ALL
Select 'RW20100801000011','请求审核','2010-08-01 10:16:24'
Union ALL
Select 'RW20100801000009','请求审核','2010-08-01 10:17:02'
Union ALL
Select 'RW20100801000008','请求审核','2010-08-01 10:18:35'
Union ALL
Select 'RW20100731000347','请求审核','2010-08-01 10:27:05'
Union ALL
Select 'RW20100731000310','请求审核','2010-08-01 10:27:28'

Answer 1

--先上结果吧
--下面是每相信半小时内操作次数的结果
a a1 b
2010-08-01 09:29:58.000 3 2010-08-01 10:04:59.000
2010-08-01 09:35:04.000 5 2010-08-01 10:06:39.000
2010-08-01 09:38:47.000 1 2010-08-01 10:09:07.000
2010-08-01 09:51:18.000 2 2010-08-01 10:27:05.000--最终计算平均值的就不写了 结果平均值为 2.750000

Answer 2

是这个意思？从每一个操作时间开始向后计算半小时统计次数，然后再分组就品均值？
select c.operation,avg(operation_count) avg_operation_count from(
select a.operation,a.operDate,count(*) operation_count 
  from Table_a a left join Table_a b on a.operation =b.operation and b.operDate <= dateadd(minute,30,a.operDate)
 group by a.operation,a.operDate) c
 where c.operation = '请求审核'
 group by c.operation
/*
operation avg_operation_count
请求审核             15
*/

Answer 3

修改一下连接条件，刚才有问题，统计时间按精确到秒，同时贴出部分中间结果，和最终结果。select c.operation,avg(operation_count) avg_operation_count from(
select a.operation,a.operDate begin_stat_operDate ,max(b.operDate) end_stat_operDate ,count(*) operation_count 
  from Table_a a left join Table_a b on datediff(ss,a.operDate,b.operDate) between 0 and 1800
 group by a.operation,a.operDate) c 
 where c.operation = '请求审核'
 group by c.operation
/* 
operation  begin_stat_operDate     end_stat_operDate       operation_count
---------- ----------------------- ----------------------- ---------------
请求审核       2010-08-01 09:29:58.000 2010-08-01 09:54:14.000 11
请求审核       2010-08-01 09:33:13.000 2010-08-01 09:54:14.000 10
请求审核       2010-08-01 09:34:25.000 2010-08-01 09:54:14.000 9
请求审核       2010-08-01 09:35:04.000 2010-08-01 10:04:59.000 9
请求审核       2010-08-01 09:35:28.000 2010-08-01 10:04:59.000 8
请求审核       2010-08-01 09:35:45.000 2010-08-01 10:04:59.000 7
请求审核       2010-08-01 09:36:00.000 2010-08-01 10:04:59.000 6
请求审核       2010-08-01 09:36:20.000 2010-08-01 10:04:59.000 5
请求审核       2010-08-01 09:38:47.000 2010-08-01 10:06:39.000 5
请求审核       2010-08-01 09:51:18.000 2010-08-01 10:18:35.000 8
请求审核       2010-08-01 09:54:14.000 2010-08-01 10:18:35.000 7
请求审核       2010-08-01 10:04:59.000 2010-08-01 10:27:28.000 8
请求审核       2010-08-01 10:06:39.000 2010-08-01 10:27:28.000 7
请求审核       2010-08-01 10:09:07.000 2010-08-01 10:27:28.000 6
请求审核       2010-08-01 10:16:24.000 2010-08-01 10:27:28.000 5
请求审核       2010-08-01 10:17:02.000 2010-08-01 10:27:28.000 4
请求审核       2010-08-01 10:18:35.000 2010-08-01 10:27:28.000 3
请求审核       2010-08-01 10:27:05.000 2010-08-01 10:27:28.000 2
请求审核       2010-08-01 10:27:28.000 2010-08-01 10:27:28.000 1(19 row(s) affected)*//*
operation avg_operation_count
请求审核 6
*/

Answer 4

我的理解和11楼的理解有所差异,但11楼的似乎更切题. WITH cte AS(
SELECT  min(a.operDate) a ,min(b.operDate) b FROM table_a a 
LEFT JOIN table_a b ON DATEDIFF(n,a.operDate,b.operDate)>=30
WHERE b.operation='请求审核'
AND a.operDate BETWEEN '2010-08-01' AND '2010-08-02'
AND a.doccode<>b.doccode
GROUP BY a.operDate
)
, cte_a as(SELECT  min(a) a ,1.0*count(a) a1 ,b  FROM  cte
GROUP BY b)
SELECT AVG(a1) average FROM cte_a