mysql - Mysql - 获取2个表之间的平均时间差异

Question

我有这两张桌子：

customer
  id
  name
  created_at

和 ：

action
  id
  customer_id
  type
  created_at

我想要和之间的平均时间差customer.created_at，action.created_at其中action.type = "call0"和动作是第一个条目（动作可能有很多type = "call0"）

现在，我有这个查询：

SELECT AVG(TIME_TO_SEC(timediff(action.created_at, customer.created_at)) / 60) AS diff
    FROM action
     WHERE action.type = "call0"
    JOIN customer ON action.customer_id = customer.id
    GROUP BY customer.id
    ORDER BY action.created_at ASC

不知道为什么，这个查询返回了几行——每行都用于操作条目。

如何返回整个平均计算？

标签： mysql

Answer 1

如果您想要总结果，请不要按 customer.id 分组

  SELECT AVG(TIME_TO_SEC(timediff(action.created_at, customer.created_at)) / 60) AS diff
    FROM action
    JOIN customer ON action.customer_id = customer.id
    WHERE action.type = "call0"

否则，如果您想要每个 customer.id 的平均值，请尝试显示相应的 customer.id

SELECT customer.id, AVG(TIME_TO_SEC(timediff(action.created_at, customer.created_at)) /60) AS diff
  FROM action
 JOIN customer ON action.customer_id = customer.id
  WHERE action.type = "call0"
  GROUP BY customer.id
  ORDER BY action.created_at ASC