创建查询以获取未完成呼叫的计数 [英] Create query to get count of uncompleted calls

问题描述

表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 question Create 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 |
+----------------+-------+

这篇关于创建查询以获取未完成呼叫的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！