MySQL优化avg、sum查询具有left join嵌套select

Question

是不是要为MySQL 5.1优化这个查询？

我有“呼叫”表，有字段“呼叫者”(从号码)，“被呼叫者”(到号码)和“持续时间”，这个查询在SQLite中工作得很好，但它在MySQL中非常慢，有140亿条巨大的数据记录。

我有Caller字段的索引。

select A.Caller, ifnull(aCallOut, 0) as CallOut, round((ifnull(aDuration, 0) / 60), 2) as Duration, round((ifnull(aAverage, 0) / 60), 2) as Average, Times, 0, 0, 0, 0
from
  (
    select Caller, sum(duration) as aDuration, count(*) Times -- sum of duration of each caller
    from Calls
    group by Caller
  ) As A

left join
  (
    select Caller, avg(duration) as aAverage -- i want avg duration of numbers that calling more than 0 seconds
    from Calls
    where Duration > 0
    group by Caller
  ) as C
  on A.Caller = C.Caller

left join
  (
    select Caller, count(*) as aCallOut -- i need to count how many numbers that caller called, ignoring the duplicating calls
    from
      (
        select Caller, count(*) aCount
        from Calls
        group by Caller, Callee
      ) as D
    group by Caller
  ) as B
  on A.Caller = B.Caller

Answer 1

您可以使用条件聚合来完成所有这些操作：

select Caller, sum(duration) as aDuration, count(*) as Times,
       avg(case when duration > 0 then duration end) as aAverage,
       count(distinct Callee) as aCallOut
from Calls
group by Caller;

注意:如果Callee曾经是NULL，那么aCallOut可能会减少1。

Answer 2

要合并前两个...

SELECT Caller
, sum(duration) as aDuration -- sum of duration of each caller
, count(*) Times
, SUM(IF(duration>0, duration, 0)) / NULLIF(SUM(IF(duration>0, 1, 0)),0)  -- avg(duration) as aAverage
FROM Calls
GROUP BY Caller

编辑:如果调用方没有duration > 0行，则可能会出现除以零错误。

Edit#2:在NULLIF(,0)中包装分母可以避免这种错误。