mysql - 连接两个查询并用于数值除法

Question

我有两个查询 A 和 B 说，如下:

SELECT SUM(TIME_TO_SEC(TIMEDIFF(od.created_at, oc.created_at))) AS `sum (secs)`
FROM on_connected oc
JOIN on_disconnected od ON od.call_id = oc.call_id
WHERE oc.ext_num = 3205006;
+------------+
| sum (secs) |
+------------+
|    14      |
+------------+
1 row in set (0.00 sec)

和

SELECT count(TIME_TO_SEC(TIMEDIFF(od.created_at, oc.created_at))) AS `total calls`
FROM on_connected oc
JOIN on_disconnected od 
ON od.call_id = oc.call_id
WHERE oc.ext_num = 3205006;
+-----------------+
| total calls     |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.00 sec)

我想在 MySQL 中使用除法运算的结果，如下所示:

SELECT A DIV B

这会给我14/3 = 4的结果。我尝试了什么？

SELECT
(SELECT SUM(TIME_TO_SEC(TIMEDIFF(od.created_at, oc.created_at))) AS `sum (secs)`
FROM on_connected oc
JOIN on_disconnected od ON od.call_id = oc.call_id) a

INNER JOIN

(SELECT count(TIME_TO_SEC(TIMEDIFF(od.created_at, oc.created_at))) AS `total calls`
FROM on_connected oc
JOIN on_disconnected od ON od.call_id = oc.call_id) b
WHERE oc.ext_num = 3205006;

INNER JOIN 语法妨碍了我的 DIV 运算符？

Answer 1

这应该可以。

SELECT
  SUM(TIME_TO_SEC(TIMEDIFF(od.created_at, oc.created_at))) /
  COUNT(*) AS `sec_per_call`
FROM on_connected oc
JOIN on_disconnected od ON od.call_id = oc.call_id
WHERE oc.ext_num = 3205006

您不需要两个子查询，因为两个子查询的条件是相同的。您只需将两个聚合相除即可。

此外，count(TIME_TO_SEC(TIMEDIFF(od.created_at, oc.created_at))) 并没有真正的意义。 COUNT() 函数只是计算非空值，不需要在其中进行复杂的转换，例如 TIMEDIFF 和 TIME_TO_SEC 。如果您只想知道调用次数，则只需匹配的行数，即 COUNT(*)。

其实还可以进一步简化。 SUM/COUNT 只是平均值，SQL 有一个内置的 AVG 函数:

SELECT AVG(TIME_TO_SEC(TIMEDIFF(od.created_at, oc.created_at))) AS `sec_per_call`
FROM on_connected oc
JOIN on_disconnected od ON od.call_id = oc.call_id
WHERE oc.ext_num = 3205006