AVG（TIMESTAMPDIFF）mySQL返回错误的答案

Question

下面是我用来计算用户的平均会话持续时间的代码。

SELECT 
    tbl.create_Date 
    ,HourOfDay 
    ,HourOfDay_AMPM 

    ,AVG(TIMESTAMPDIFF(SECOND, tbl.minDt, tbl.maxDt))/60 AS Duration_mins 

    FROM (SELECT 
      i.session_id, 
      i.createDate, 
      DATE(CONVERT_TZ(i.createDate, '+00:00', '-04:00')) as create_Date, 
      HOUR(CONVERT_TZ(i.createDate, '+00:00', '-04:00')) as HourOfDay, 
      DATE_FORMAT(CONVERT_TZ(i.createDate,'+00:00','-04:00'), '%l%p') as HourOfDay_AMPM, 
      min(i.createDate) minDt, 
      max(i.createDate) maxDt, 
      (max(i.createDate) - min(i.createDate))/60 as Duration 
     FROM impressions i 

    WHERE i.createDate >= current_date 
    AND HOUR(CONVERT_TZ(i.createDate, '+00:00', '-04:00')) >=9 
    AND HOUR(CONVERT_TZ(i.createDate, '+00:00', '-04:00')) < 22 
    AND i.session_Id <> '' 


    GROUP BY i.session_id 
    HAVING Duration > 0 
    ORDER BY i.createDate, i.session_id 

     ) as tbl 
GROUP BY tbl.create_DATE, tbl.HourOfDay 

ORDER by tbl.create_Date 

注意，在数据库中的时区是在UTC，我需要在美国东部时间来显示结果，这就是为什么我用CONVERT_TZ命令。

问题：我跑的内查询和粘贴的原始数据转换成Excel中，生成的透视表，并得到下面的结果

Hour Avg_duration_mins 
9AM  14.43 
10AM 59.17 
11AM 24.55 
12PM 12.69 
2PM  1.27 

然而，运行整个查询作为给我以下结果

Hour Avg_duration_mins 
9AM 6.98 
10AM 18.78 
11AM 9.40 
12PM 7.49 
2PM 1.21 

有手册经过检查，Excel的结果是准确的，是有道理的。为什么SQL变得疯狂？我有一种感觉，问题在于AVG函数和max和min的聚合。

更新：为表的印象可以有相同session_id

session_id  | createDate  | actions  | 
    023awv  2014-10-09 12:02  some action 
    023awv  2014-10-09 12:12  some action 
    023awv  2014-10-09 12:22  some action 
    023awv  2014-10-09 12:32  some action 
    011awv  2014-10-09 12:42  some action 
    023awv  2014-10-09 12:42  some action 
    023awv  2014-10-09 12:52  some action 
    023awv  2014-10-09 12:53  some action 
    052brw  2014-10-09 13:02  some action 
    023awv  2014-10-09 13:05  some action 
    023awv  2014-10-09 13:06  some action 
    023awv  2014-10-09 13:08  some action 
    023awv  2014-10-09 13:12  some action 

我想在每小时/每天让每个会话的平均持续时间的多个条目。

任何帮助将不胜感激。

Answer 1

如果您在Excel计算中使用(max(i.createDate) - min(i.createDate))/60 as Duration作为分钟数，那么这是错误的。减去日期提供了某种间隔表示法：

select timestamp('2014-10-09 14:12') - timestamp('2014-10-09 13:04'); 

> 10800 

这是“1小时8分钟”而不是4080秒。

您的内部查询有一个分组，但还包括非聚合的非分组列。简单来说：

select 
    session_id, 
    createDate -- this isn't grouped or aggregated 
from 
    impressions i 
group by 
    session_id 

大多数数据库不会让你这样做。 MySQL会，它会返回第一个createDate它发生在每个session_id。所以你的内部查询会产生不稳定的结果。单独运行它的查询计划可能与用于一起运行查询的查询计划不同。所以它最终会在每种情况下返回不同的值。

说出印象表包含以下两行：

session_id | createDate 
-------------------------------- 
     1 | 2014-10-09 13:30:00 
     1 | 2014-10-09 15:30:00 

应该采取什么内部查询的回报？外部查询应该返回什么？要解决这个问题

一种方法是基于最小日期为斗结果：

select 
    tbl.Create_Date, 
    tbl.HourOfDay, 
    tbl.HourOfDay_AMPM, 
    avg(timestampdiff(second, tbl.minDt, tbl.maxDt))/60 as Duration_mins 
from (
    select 
     i.session_id, 
     date(convert_tz(min(i.createDate), '+00:00', '-04:00')) as create_Date, 
     hour(convert_tz(min(i.createDate), '+00:00', '-04:00')) as HourOfDay, 
     date_format(convert_tz(min(i.createDate), '+00:00', '-04:00'), '%l%p') as HourOfDay_AMPM, 
     min(i.createDate) minDt, 
     max(i.createDate) maxDt, 
     (max(i.createDate) - min(i.createDate))/60 as Duration 
    from 
     impressions i 
    where 
     i.createDate >= current_date and 
     hour(convert_tz(i.createDate, '+00:00', '-04:00')) >=9 and 
     hour(convert_tz(i.createDate, '+00:00', '-04:00')) < 22 and 
     i.session_Id <> '' 
    group by 
     i.session_id 
    having 
     Duration > 0 
    ) as tbl 
group by 
    tbl.Create_Date, 
    tbl.HourOfDay, 
    tbl.HourOfDay_AMPM 
order by 
    tbl.create_Date, 
    tbl.HourOfDay 

这里，我已经基本上取代i.CreateDate的每一个非聚集发生在内部查询使用分钟（ i.CreatDate）。这使得内部查询得到了很好的定义。即只有一个结果集可以返回。

从阅读MySQL手册中，很难弄清楚在这种情况下，在内部查询中执行什么命令会起什么作用。手册说外部订单将优先于内部订单。