问题描述
表waiter_log为
There is table waiter_log as
+---------+----------------+--------------+--------------+
| call_id | queue_num_curr | ast_num_curr | proceed_wait |
+---------+----------------+--------------+--------------+
| f27de4f | 9010 | 2 | 1 |
| f27de4f | 9002 | 5 | 1 |
| f27de4f | 9003 | 1 | 0 |
| asdf231 | 9010 | 2 | 1 |
| asdf231 | 9002 | 5 | 1 |
| rete125 | 9010 | 2 | 1 |
| rete125 | 9009 | 5 | 1 |
| a7rf5gs | 9003 | 2 | 1 |
| a7rf5gs | 9006 | 5 | 1 |
| a7rf5gs | 9009 | 1 | 0 |
| qawe234 | 9003 | 2 | 1 |
| qawe234 | 9008 | 5 | 1 |
| qawe234 | 9004 | 1 | 0 |
| 49c43ad | 9004 | 2 | 1 |
| 49c43ad | 9007 | 5 | 1 |
+---------+----------------+--------------+--------------+
呼叫ID为'f27de4f'的呼叫始于9010，于9003年结束，因为有一条记录，呼叫ID ='f27de4f'的proceed_wait = 0
呼叫ID为'asdf231'的呼叫始于9010，仍在9002继续进行，但尚未结束，因为没有呼叫ID为'asdf231'的proced_wait = 0记录同样，对于具有呼叫标识"rete125"的呼叫，也没有记录，proced_wait = 0，并且此呼叫也未完成.因此，对于队列9010，查询结果应为2(未完成呼叫的数量)对于9003，结果应为0，因为对9003的所有调用("a7rf5gs"和"qawe234")均已完成.对于9004，结果应为1，因为没有呼叫ID为'49c43ad'的呼叫的proceed_wait = 0的记录.
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 2 (count of uncompleted calls)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'.
如何创建查询以将未完成的呼叫计为:
How to create a query to get count on uncompleted calls as:
queue_num count
9010 2
9004 1
UPD: 在这里我更新了我的问题创建查询以获取未完成呼叫的计数按2个字段分组
UPD:Here i updated my questionCreate query to get count of uncompleted calls group by 2 fields
推荐答案
这是另一种无需相关子查询或窗口函数即可工作的方法:
Here's another method that works without correlated subqueries or window functions:
对于每行w1，请尝试查找具有相同call_id和0表示呼叫已完成的另一行w2.使用LEFT OUTER JOIN，我们可以测试给定call_id没有w2行的情况.
For each row w1, try to find another row w2 with the same call_id and a 0 indicating the call is complete. Using a LEFT OUTER JOIN, we can test for cases where no w2 row exists for a given call_id.
然后使用相同的call_id和较小的ast_num_curr值再次连接到假设行w3.同样，使用外部联接，我们可以检查是否没有这样的行.这意味着w1必须具有该call_id的ast num的最小值.
Then do another join to a hypothetical row w3 with the same call_id and a lesser ast_num_curr value. Again, using outer join, we can check that no such row exists. This means w1 must have the least value for ast num for that call_id.
SELECT w1.call_id, w1.queue_num_curr
FROM waiter_log AS w1
LEFT OUTER JOIN waiter_log AS w2
ON w1.call_id = w2.call_id AND w2.proceed_wait = 0
LEFT OUTER JOIN waiter_log AS w3
ON w1.call_id = w3.call_id AND w1.ast_num_curr > w3.ast_num_curr
WHERE w2.call_id IS NULL AND w3.call_id IS NULL;
输出:
+---------+----------------+
| call_id | queue_num_curr |
+---------+----------------+
| 49c43ad | 9004 |
| asdf231 | 9010 |
| rete125 | 9010 |
+---------+----------------+
要获取每个queue_num_curr的计数，请将上面的查询包装在派生表子查询中，然后在外部查询中进行计数:
To get the counts per queue_num_curr, wrap the query above in a derived-table subquery, and do the count in the outer query:
SELECT queue_num_curr, COUNT(*) AS count
FROM (
SELECT w1.call_id, w1.queue_num_curr
FROM waiter_log AS w1
LEFT OUTER JOIN waiter_log AS w2
ON w1.call_id = w2.call_id AND w2.proceed_wait = 0
LEFT OUTER JOIN waiter_log AS w3
ON w1.call_id = w3.call_id AND w1.ast_num_curr > w3.ast_num_curr
WHERE w2.call_id IS NULL AND w3.call_id IS NULL
) AS t
GROUP BY queue_num_curr;
输出:
+----------------+-------+
| queue_num_curr | count |
+----------------+-------+
| 9004 | 1 |
| 9010 | 2 |
+----------------+-------+
这篇关于创建查询以获取未完成呼叫的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！