sql查询技巧，按时间分段进行分组，每半小时一组统计组内记录数量

今天拿到一个查询需求，需要统计某一天各个时间段内的记录数量。

具体是统计某天9:00至22:00时间段，每半小时内订单的数量，最后形成的数据形式如下：

时间段          订单数

9:00~9:30 xx个

9:30~10:00 xx个

...

 

如果说是按每个小时来统计订单数量，这个是比较简单的，只要将订单表中的OrderTime字段中的小时取出，然后根据每个小时的值进行group by就可以了。

select T.timehour,count(T.orderid) as number from

(

select o.orderid,datename(hh,o.ordertime) as timehour from orders as o 

where o.ordertime<'2013-10-20 22:00' and o.ordertime>'2013-10-20 9:00'

) as T

group by T.timehour

order by timehour asc

 

但是如果要实现上面的那种查询就有点麻烦了。我想到的一种实现方案是：取出每个订单的时间字段中的“小时和分钟”，将这个时间转换为距凌晨的分钟数。如：9:00转换后就是540，9:30转换后就是570。

计算的过程是：

1、取定一个基线值：base=540（9:00）

2、每条条订单记录的分钟数设为：timehour

   分组编号：groupid=(timehour-base)/30

 

这样可以将各个时间段内的时间统一到一个分组编号中去。如下：

时间值 groupid     

9:02     0

9:23 0

9:30 1

9:31 1

10:01 3

..

 

这样一来，就把各个时间段内的时间转换成对应的某一个分组编号了，这样我们就可以对每个groupid进行分统计每个组内的订单数了。

时间段          groupid

9:00~9:30 0

9:30~10:00 1

10:00~10:30 2

..

 

下面是完整的sql语句：

select T.groupid,count(T.orderid) as number from

(

select o.orderid,o.ordertime,((datename(hh,o.ordertime)*60+datename(mi,o.ordertime))-540)/30 as groupid from orders as o 

where o.ordertime<'2013-10-20 22:00' and o.ordertime>'2013-10-20 9:00'

) as T

group by T.groupid

order by groupid

 

现实案例：查询20200119-CCT 9:00-18:00分时段的进线数量，已每半个小时为单位；完整sql如下：

select T.groupid,COUNT(T.队列) as 进线数量 from

(

select o.队列,o.开始时间,((datename(hh,o.开始时间)*60+datename(mi,o.开始时间))-540)/30 as groupid

FROM CallInfo20200119 as o

where 队列 in ('2@default','4@default') AND 开始时间 BETWEEN '2020-01-19 09:00' AND '2020-01-19 18:00'

) as T

group by T.groupid

order by groupid