创建查询以按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屋！
