今天拿到一个查询需求,需要统计某一天各个时间段内的记录数量。
具体是统计某天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