创建查询以按2个字段获取未完成呼叫的计数 [英] Create query to get count of uncompleted calls group by 2 fields
问题描述
此问题的小更新创建查询以获取未完成计数通话
表waiter_log为
There is table waiter_log as
+---------------------+---------+----------------+--------------+--------------+
| call_time | call_id | queue_num_curr | ast_num_curr | proceed_wait |
+---------------------+---------+----------------+--------------+--------------+
| 2019-11-18 08:14:30 | f27de4f | 9010 | 2 | 1 |
| 2019-11-18 08:14:35 | f27de4f | 9002 | 5 | 1 |
| 2019-11-18 08:14:41 | f27de4f | 9003 | 1 | 0 |
| 2019-11-18 08:14:45 | asdf231 | 9010 | 2 | 1 |
| 2019-11-18 08:14:50 | asdf231 | 9002 | 5 | 1 |
| 2019-11-18 08:14:55 | rete125 | 9010 | 2 | 1 |
| 2019-11-18 08:15:00 | rete125 | 9009 | 5 | 1 |
| 2019-11-18 08:15:05 | a7rf5gs | 9003 | 2 | 1 |
| 2019-11-18 08:15:10 | a7rf5gs | 9006 | 5 | 1 |
| 2019-11-18 08:15:15 | a7rf5gs | 9009 | 1 | 0 |
| 2019-11-18 08:15:20 | qawe234 | 9003 | 2 | 1 |
| 2019-11-18 08:15:25 | qawe234 | 9008 | 5 | 1 |
| 2019-11-18 08:15:30 | qawe234 | 9004 | 1 | 0 |
| 2019-11-18 08:15:35 | 49c43ad | 9004 | 2 | 1 |
| 2019-11-18 08:15:41 | 49c43ad | 9007 | 5 | 1 |
| 2019-11-18 08:15:45 | bxfdrtr | 9010 | 3 | 1 |
| 2019-11-18 08:15:50 | bxfdrtr | 9012 | 4 | 1 |
| 2019-11-18 08:15:55 | tofnt62 | 9010 | 5 | 1 |
| 2019-11-18 08:16:00 | tofnt62 | 9021 | 1 | 1 |
+---------------------+---------+----------------+--------------+--------------+
呼叫ID为'f27de4f'的呼叫始于9010,并于9003年结束,因为存在一条记录,其中call-id ='f27de4f的proceed_wait = 0,而呼叫ID为'asdf231'的呼叫始于9010 9002尚未完成,因为没有针对call-id ='asdf231'的proceed_wait = 0的记录.类似地,对于具有call-id'rete125'的call也没有具有proceed_wait = 0的记录,并且此调用也未完成.因此,对于队列9010,查询结果应为:
Call with call-id 'f27de4f' started in 9010 and finished in 9003 because there is a record with proceed_wait = 0 for call-id='f27de4f' Call with call-id 'asdf231' started in 9010, still proceed in 9002 and not finished yet because there is no record with proceed_wait = 0 for call-id='asdf231' Similarly for call with call-id 'rete125' there is no record with proceed_wait = 0 and this call is not completed too. So,for queue 9010 query result should be:
queue_num ast_num count
9010 2 2
9010 3 1
9010 5 1
对于9003,结果应为0,因为对9003的所有调用("a7rf5gs"和"qawe234")均已完成.对于9004,结果应为1,因为没有呼叫ID为'49c43ad'的呼叫的proceed_wait = 0的记录.
For 9003 result should be 0 , because all calls for 9003 ('a7rf5gs' and 'qawe234') are completed. For 9004 result should be 1 because there is no record with proceed_wait = 0 for call with call-id '49c43ad'.
所以结果应该是:
queue_num ast_num count
9010 2 2
9010 3 1
9010 5 1
9004 2 1
推荐答案
您可以将表与聚合查询联接,以检索未完成呼叫的最小call_time
par call_id
.未完成的呼叫是在proceed_wait = 0
处没有记录的呼叫.
You could join the table with an aggregate query that retrieves the minimum call_time
par call_id
of unfinished calls. A unfinished call is a call that has no record where proceed_wait = 0
.
select t.queue_num_curr, t.ast_num_curr, count(*)
from mytable t
inner join (
select call_id, min(call_time) call_time
from mytable
group by call_id
having max(proceed_wait = 0) = 0
) tmin on tmin.call_id = t.call_id and tmin.call_time = t.call_time
group by t.queue_num_curr, t.ast_num_curr
order by t.queue_num_curr, t.ast_num_curr
Demo on DB Fiddle:
queue_num_curr | ast_num_curr | count(*)
-------------: | -----------: | -------:
9004 | 2 | 1
9010 | 2 | 2
9010 | 3 | 1
9010 | 5 | 1
注意:我认为在结果中,queue_num = 9004
应该具有ast_num = 2
而不是1
(应该对应于call_id 49c43ad
).
NB: I think that in the results, queue_num = 9004
should have ast_num = 2
instead of 1
(that should correspond to call_id 49c43ad
).
这篇关于创建查询以按2个字段获取未完成呼叫的计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!